In this article, I will show you how to create a visual warning using icon sets in conditional formatting.
A warning comes in many shapes in Excel. It can be the warning that pops up when you are about to close your workbook without saving. It can be a warning that shows that something has gone wrong.
But a warning is really just a message from the program to the user.
In this article, I have created a tool that calculates a huge one-time employee bonus. It is a simple calculation that is not really important for the purpose of this article. It is simply just a way of showing one of the many things conditional formatting can be used for.
Take a look at this Animation below:
When a name is selected from the drop-down menu the bonus is automatically calculated based on data from another sheet using ‘VLOOKUP’.
But the real beauty here is the little icon that pops up, and changes, when new names are selected from the drop-down menu. This gives the user an indication of whether the bonus is extremely low or extremely high.
Let me show you how it is done.
I have included a project file for you to download in this article. To learn this conditional formatting trick I recommend that you open it and follow along. The examples you will find below has all been created in Excel 2016 for Windows.
Step 1: Getting to the right dialog box
Select cell E6 in the project file. That is the cell that holds the bonus when the different employees are selected.
Then go to the ‘Home’ tab of the ribbon and click ‘Conditional Formatting’. In the menu that appears click ‘New Rule…’
Step 2: Choosing the Icon
Now you see the new dialog box named ‘New Formatting Rule’.
From here you make sure ‘Format all cells based on their values’ are selected under ‘Rule Type’. After this, you change the ‘Format Style’ to ‘Icon Sets’ instead of ‘2-Color Scale’.
Now you have to choose the icons you want for your warnings.
1. Choose the icon you want for when the bonus is high.
2. Pick ‘No Cell Icon’ here. Unless you want an icon to appear when the size of the bonus is neither high nor low.
3. Choose the icon you want for when the bonus is low
Step 3:Modifying when User Icons Available
1+2) Change the ‘Type’ from ‘Percent’ to ‘Number’ in both fields.
3) Type in what the bonus should be equal to or higher than to “trigger” the icon that should appear when the bonus is high.
4) Type in what the bonus should be equal to or less than to “trigger” the icon that should appear when the bonus is low.
5) Click ‘OK’.
Step 4: Enjoy
Now you have set up your icons to give you little warnings about the calculated bonus. Try it out for yourself with the project file for this article.
There are plenty of applications for this kind of conditional formatting trick.It may seem like a small thing compared to what conditional formatting is otherwise capable of. But a small trick like this increases the overall user experience with your sheet.
So especially if you are creating spreadsheets that will be used by co-workers and bosses, then look out for tricks like this.
Got Questions ? Let me know in the comments Below
Special Thanks: Kasper and Mikkel for their amazing contribution at Exploring Excel and their own website spreadsheeto.com