How to Create Icons with Conditional Formatting in Excel

Bottom Line: Learn a technique for applying conditional formatting to the new icons in Excel.

Skill Level: Intermediate

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

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.

Dunder Mifflin Attendance Report - Follow Along.xlsx (24.4 KB)

Dunder Mifflin Attendance Report - Final.xlsx (166.6 KB)

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:

  1. How to Use the XOR Function
  2. How to Use XLOOKUP for Reverse Order Search
  3. Return Multiple Values for a Lookup Formula in Excel with FILTER and UNIQUE
  4. How to Sort with a Formula in Excel Using SORT and SORTBY Functions
  5. 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.

Good attendance report

And conversely, when the department does not meet its goal, the storm cloud is colored and the firework is gray.

Bad attendance report

Selecting Icons

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.

Insert Icons Window

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.

Group 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.

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.

Conditional Formatting

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.

Conditional Formatting Rules Manager

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.

Paste Special as 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.

Text Boxes Link to Calculations

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.

Related Posts

I've got two other posts about conditional formatting that I suggest you check out:

Conclusion

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!

Add comment

Your email address will not be published. Required fields are marked *

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly