Bottom Line: Learn a technique for applying conditional formatting to the new icons in Excel.
Skill Level: Intermediate
Download the Excel File
The file that I work with in the video can be found below. You can also download the “Follow Along” version if you'd like to practice building out the report.
Compatibility: This file uses the new Dynamic Array Functions that are only available on the latest version of Office 365. This includes both the desktop and web app versions of Excel.
I have also posted a bonus episode in this series that covers how to make the dashboard with older versions of Excel using pivot tables instead.
Building an Attendance Dashboard
This post is the final part of a six-part series explaining how to build an interactive dashboard for attendance. This attendance report was an entry for the Excel Hash competition. I recommend you check out the first five posts here:
- How to Use the XOR Function
- How to Use XLOOKUP for Reverse Order Search
- Return Multiple Values for a Lookup Formula in Excel with FILTER and UNIQUE
- How to Sort with a Formula in Excel Using SORT and SORTBY Functions
- How to Create a Dynamic Drop-down List that Automatically Expands
Specially Formatted Icons
For our final task in creating the attendance dashboard, we want to create icons that change depending on departmental performance. Specifically, when a department exceeds its attendance goal, a firework icon will be colored while the storm cloud icon is grayed out.
And conversely, when the department does not meet its goal, the storm cloud is colored and the firework is gray.
To find the icons you want, you can use the new Icons window, which has a search feature. To access this window, go to the Insert tab on the Ribbon. Select Illustrations, and then Icons.
Select the icons you want to insert. (You can insert more than one at a time.)
Formatting Your Images
We're going to change our icons to a picture by making some alterations to it. Start by changing the color of the icons to a very light gray. You can do this by selecting Graphics Fill on the Graphics Format tab on the Ribbon.
Next, insert a white square, which will be the background to your icon. You can insert this square by going to the Insert tab on the Ribbon, choosing the Illustrations button, and selecting Shapes. You'll get a menu of shapes to choose from, and you can select a rectangular shape.
Draw the square around the icon and change the Shape Fill to white. Change the Shape Outline setting to No Outline. Then right-click on the shape and choose Send to Back from the menu that appears. That places a white background behind your icon.
With the background shape selected, hold the Ctrl button on the keyboard and select the icon as well. Then right-click and choose Group to group the two shapes together.
Copy this new group and then paste it elsewhere on the sheet. But don't do a normal paste. Right-click and choose Paste Options, then Picture. What this does is it takes that group of two shapes and makes it into one image.
With your new image selected, go to the Picture Format tab on the Ribbon and select the Color option. Near the bottom of that menu is Set Transparent Color.
This changes your mouse cursor so that whatever you click on next will have a transparent fill. In other words, it will be clear instead of colored.
You'll want to repeat all of that formatting for your second icon as well.
Now that we have icons you can see through, we are going to set them on specific cells that we can reference in our conditional formatting.
From the Home tab, you can select Conditional Formatting and choose Manage Rules. This pulls up the Conditional Formatting Rules Manager. I've set rules for both of the cells that have icons in them.
One of the rules fills the storm cloud with a gradient blue if the actual attendance number is lower than the target number. The other rule fills the firework with a gradient orange color if the actual is higher than the target. If the conditions are not met to format them in these ways, the icon color will be just a light gray instead.
Now that we have created our conditional formatting rules for those two cells, we can copy and paste them onto the Dashboard sheet. When we paste, we want to Paste Special as a Linked Picture.
Adding Text Boxes
Lastly, we are going to add text boxes, below each icon, that are also conditional. These text boxes will display a number or remain blank depending on if the the department average is higher or lower than the overall goal. That's because these text boxes are linked to the cell on the Calc sheet that has the IF formula that spells out those conditions.
Adding the text boxes is easily done. Just go to the Insert tab on the Ribbon and click on the Text button, and select Text Box. You can then draw the text box on the worksheet with your mouse.
To link the text box, just click on it, type the equals sign (=) in the formula bar, and select the cell on the Calc sheet that you want to link it to. You can format the text box however you like.
I've got two other posts about conditional formatting that I suggest you check out:
- 2 Ways to Apply Conditional Formatting to Shapes
- How to Apply Conditional Formatting to Rows Based on Cell Value
We did it! We recreated the interactive attendance report that Michael Scott from The Office wanted to see, complete with storm clouds and fireworks.
I hope this post, and the whole series, has proven helpful to you and that you walk away with some new skills and techniques that you can implement in your office.
If you have questions about any of the steps we took to create these conditionally formatted icons, please let me know in the comments. Thanks for following along!