Icons with Conditional Formatting in Excel

Last Updated on 23/11/2024
Reading time: 2 minutes

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?

  1. Select your data.
  2. Go to Home > Conditional Formatting > Icon Sets.
  3. Pick an icon set that suits your dashboard.
add conditional formatting icons

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.

  1. Select your data: Highlight the cells with icons.
  2. Go to the Home tab on the ribbon
  3. Select Conditional Formatting
  4. Click on Manage Rules
  5. DON'T miss any rules by selecting in the upper dropdown 'This Worksheet'
  6. Double-click on the rule you want to update
Rule Manager with icons conditional formatting

The icon sets are set in percentage by default and split by third.

Default settings for the icon set

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.
Change the icon sets settings to number

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:

  1. To show trends like increase, decrease, or no change in values.
  2. Highlight categories or ranges of data, like top performers.
  3. 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.

Related articles

Leave a Reply

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

Icons with Conditional Formatting in Excel

Reading time: 2 minutes
Last Updated on 23/11/2024

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?

  1. Select your data.
  2. Go to Home > Conditional Formatting > Icon Sets.
  3. Pick an icon set that suits your dashboard.
add conditional formatting icons

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.

  1. Select your data: Highlight the cells with icons.
  2. Go to the Home tab on the ribbon
  3. Select Conditional Formatting
  4. Click on Manage Rules
  5. DON'T miss any rules by selecting in the upper dropdown 'This Worksheet'
  6. Double-click on the rule you want to update
Rule Manager with icons conditional formatting

The icon sets are set in percentage by default and split by third.

Default settings for the icon set

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.
Change the icon sets settings to number

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:

  1. To show trends like increase, decrease, or no change in values.
  2. Highlight categories or ranges of data, like top performers.
  3. 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.

Related articles

Leave a Reply

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