Creating impressive charts with reaction labels can greatly enhance your data visualization.
In this article I will show you how to turn dull report into an amazing Dynamic chart with emojis, that tells the story about your data.
Table of Contents
The Finished Project
So, Let’s see the finished project
My report shows a list of managers and their sales amount for Summer, Fall and Winter. We have a Sales Goal in Cell B1 and I want to communicate all this information in an easy visual way to be able to make an informed business decision at a glance
Here is the source Data
Here is my Interactive Chart with reactions.
Sales meeting the goal in cell B1 appear in Green columns but if they do not meet the goal they are in Red. A smiling Face or a Sad Face tells how the management feels like. I also have a custom legend to interpret colors.
For the Interactivity, I can do that with 2 different methods, either by using Scenarios and then Add them to the QAT, or by using Functions which is what I will demonstrate in this article.
NB: To learn about Scenarios, Check my Tutorial:►
Preparing My Data
I copied my source Data (From the “Data” Worksheet)
In the Start worksheet, I pasted it twice in A3 & again in A13. The Sales Goal is in cell B1.
Delete all values from the upper setup from B3:D9
Create a Data Validation Drop List
- In B3 Create a Drop List for the different Seasons. You can read my article on creating Drop Lists by Clicking Here.
- Change the labels in C3 to read “Met” and in D3 to read “Goal”
Creating Functions
- In cell B4 create an XLOOKUP function that returns the values for the season selected in B3 (The Drop List).
=XLOOKUP(B3,B13:D13,B14:D19)
The Function Spills down
Test the Drop List and watch the values changing
- Create a helper column in cell C4 that returns the sales for managers hitting the target.
=IF($B4>=$B$1,$B4,””)
- Format as Currency (CTRL +SHIFT + 4)
- Copy the function to the range C4:D9 9Fill without Formatting)
- Modify the function in D4 by replacing the Value if true with a Smiling Face Emoji in double Quotes and replace the Value if False with a Sad Face Emoji in double quotes.
=IF($B4>=$B$1,” “,” “)
You select the Emoji by opening the Emojis window with the Shortcut:
- Copy the new function in D4 down to D9
NB: Emoji icons are black and white in the sheet.
Creating the Chart
- Select the range A4:B9 and hit ALT + F1
- A default Column Chart is created
- Delete:
- The Horizontal grid Lines
- The Vertical Axis
- Drag the Chart Title to the upper left corner
Dynamic Chart Title
In Cell F1 type the Formula:
=B3&” Sales Goal by Manager”
Change the Font Color to white to hide the text
- Select the Chart Title and Hit F2
- Click in the Formula bar and type: =F1 ►Enter
You would have created a Dynamic Chart Title that changes with your selection from the Drop List.
Formatting Columns:
Select any Column:
- On the Format Tab ►Shape Fill ► Red
- On the Format Tab ►Shape Effects ► Inner Shadow
- Hit CTRL +1 ► Format Data Series pane ► Gap Width► set it to 115%
Formatting Data Labels
From the Chart Element Button (Upper Right Corner of the Chart) ►Select Data Labels ► More Options:
The format data Labels Pane opens
- Click on label Options
- Select ►Values From Cells ► select the Range D4:D9
- Uncheck Values
- Then close the Format Data Labels
With the Emojis Selected (the Data Labels) ►Home Tab ► Increase the Font Size to make them bigger
NB: Emojis appear in full colors in the chart
Adding a Second Data Series
Right Click on the Chart and click ► Select Data
Click Add
- Series Name: Numbers (could be anything else)
- Series Values: delete what you see and Select C4:C9
- Hit Ok then OK
- A new series is Added
- Select any Colum of the New Series.
- On the Format Tab ►Shape Fill ► Green
- On the Format Tab ►Shape Effects ► Inner Shadow
- Hit CTRL +1 ► Format Data Series pane ► Series Overlap► set it to 100%
- Click on the New Data Labels (The Numbers) hit CTRL +1
- On the “Size & Properties” Tab ► Text Direction ► rotate All Text 270
- On The “Label Options “ Tab ► Label Position ► Inside base
- On the Home Tab select White Font size11pt
Create a Custom Legend
- Create 2 Rectangles (Red & Geen) and 2 Text Boxes “Met” & “Not Met”
- Format all that then group them and position them in the Upper Left Corner of the Chart.
- Add a Black Outer Border to the Chart
- Bold the Horizontal Axis
Improving Appearance and Testing
Make all Preparation Values in Ranges C3:D9 and A13:D19 invisible by setting The Fill color to No Fill and The Font Color to White.
On the View Tab ► Uncheck Gridlines.
Test from the Drop List in Cell B3 and Enjoy
For any Season, Managers with Green Columns Met the Sales Goal and show a Smiling Face. While Managers who did not meet the Sales Goal Show a Sad Face and a Red Columns. Everything is Dynamic.
Impress your Boss and colleagues with this beautiful Chart and let me know in a Comment your feedback.
Enjoy your Impressive Dynamic Chart with Reactions.
Download Exercise File and Follow Along
