Conditional formatting is a powerful tool in Excel that allows you to apply formatting to cells based on certain conditions. One particularly useful feature of conditional formatting is the ability to use icon sets to visually represent data.
To use icon sets in Excel, first select the cells that you want to apply the formatting to. Then, go to the “Home” tab and click on the “Conditional Formatting” button in the “Styles” group. From the dropdown menu, select “Icon Sets” and choose the icon set that you want to use.
There are several options to choose from, including arrows, shapes, and checkmarks. You can also customize the icon set by clicking on the “More Rules” option and selecting “Manage Rules.” This will open the “Conditional Formatting Rules Manager” window, where you can edit the conditions and formatting for each icon in the set.
Once you have selected an icon set and set the conditions, the icons will automatically appear in the selected cells based on the values in those cells. For example, if you are using the “3 Arrows” icon set and have set the conditions to be greater than 75, cells with values above 75 will display a green upward arrow icon, while cells with values below 75 will display a red downward arrow icon.
Let us try an example
We have a spreadsheet that lists employees’ health and safety training. Each employee must have done the training within the last year. Our spreadsheet tells us if the training is within date or not.
As we can see, Louise needs to redo the training. Now how about we try and change this spreadsheet to use icons and conditional formatting?
Selecting column C we select conditional formatting and choose the icons set with a tick, exclamation mark and a cross. Then we select Manage Rules from the conditional formatting option, and then highlight the rule, and click Edit Rules.
In the above screenshot you can see that I have changed the order of the icons, as we only want to use two. So now the icon will display if the value in the cell is a 1 or a 0. At the moment we are displaying “Yes” and “No”, so we need to change our formula on the spreadsheet to show 1 or 0. We could check if the cells contain “Yes” and “No”, but working with conditional formatting icons is easier with numbers.
So after changing our formula to show a 1 or a 0 we can now see the icons. If we go back to editing the rule again, we can set it to show icons only. We could have done this whilst we were first setting up the rule, but I always feel it is better at first to see both the formula result and the icon so that you can check the logic is working.
So now when we look at our spreadsheet we have a nice icon showing us the status of the training.
Conclusion
Icon sets are a great way to visually represent data and make it easier to understand at a glance. They can be particularly useful in large data sets where it may be difficult to quickly identify patterns or trends. Give them a try the next time you are working with data in Excel!
Facebook Comments