Conditional formatting icons, such as arrows, traffic lights, or shapes, are a great way to visualize data in Excel. They make dashboards easier to understand and highlight trends or key data points quickly, saving users time.
How to Set Icon Rules?
- Select your data.
- Go to Home > Conditional Formatting > Icon Sets.
- Pick an icon set that suits your dashboard.
Customize the range of application
By default, Excel divides your data into three equal parts (one-third each). But most of the time it's not what you want. This is why when you want to add icons set in your cells, you must adjust the level or each icons.
In the previous example, April is yellow with 10.5% but it should be green. This is why you must call the Rule Manager to change the color of the icons.
- Select your data: Highlight the cells with icons.
- Go to the Home tab on the ribbon
- Select Conditional Formatting
- Click on Manage Rules
- DON'T miss any rules by selecting in the upper dropdown 'This Worksheet'
- Double-click on the rule you want to update
The icon sets are set in percentage by default and split by third.
In this case, we want yellow arrows representing values between -5% and 5%. To achieve this:
- Change the type to Number.
- Set the new values to -0.05 and 0.05.
Now, March has a green arrow and August is red. Previously, those months had a yellow arrow.
When Should You Use Icons in Your Dashboard?
Use icons in Excel in these situations:
- To show trends like increase, decrease, or no change in values.
- Highlight categories or ranges of data, like top performers.
- Add visual appeal to your reports and make them easier to read.
Icons are helpful when you want to guide attention to specific data or show comparisons without needing additional charts.