Formatting Issues

How to Set Excel Conditional Formatting Icon Sets for Numbers 1 to 5

Phi Hung VoPhi Hung Vo Oct 15, 2026 873 views

Question details

The user needs to configure conditional formatting icon sets so that entering the exact numbers 1 through 5 displays specific, corresponding icons without overlapping ranges.

How to Set Excel Conditional Formatting Icon Sets for Numbers 1 to 5
Product
Excel
Device & OS
not provided
Scenario
Applying a 5-icon set to a specific dataset where each integer from 1 to 5 must map precisely to a distinct visual indicator.
Observed behavior
By default, Excel uses percentage-based thresholds which causes overlap or inaccurate icon assignments for small integer ranges. The goal is to enforce strict numeric thresholds for accurate visualization.
Before you start

Ensure your target cell range contains the integer values 1 through 5, and decide on the specific 5-icon style you want to display for these values.

Solution 1Recommended

Configure Rule Settings for Exact Numbers

Modify the conditional formatting rule to use exact numeric thresholds instead of the default percentage calculations to correctly target the numbers 1 to 5.

Excel icon sets default to percentage-based distributions, which analyze the highest and lowest numbers in your range. To link specific icons to the integers 1 through 5, you must manually change the threshold type to 'Number' and define the exact limits.

1
Select the target data range

Highlight the cells containing the numbers you want to format.

2
Apply a basic icon set

Navigate to the 'Home' tab, click on 'Conditional Formatting', hover over 'Icon Sets', and choose a 5-icon set from the directional, shapes, or indicators categories.

3
Open the rule manager

Click 'Conditional Formatting' again and select 'Manage Rules'. Select the icon set rule you just applied and click 'Edit Rule'.

4
Change threshold types to Number

In the 'Edit Formatting Rule' dialog box, locate the 'Type' dropdown menus for each icon. Change every dropdown from 'Percent' to 'Number'.

5
Set the exact integer values

Assign the exact values from top to bottom. Set the first threshold to '>= 5', the second to '>= 4', the third to '>= 3', and the fourth to '>= 2'. The final icon will automatically cover values less than 2. Click 'OK' to apply the formatting.

Configure Rule Settings for Exact Numbers
Show Icon Only: If you want to hide the underlying numbers and only display the visual icons in the cells, check the 'Show Icon Only' box located above the threshold settings in the Edit Rule menu.
Free Spreadsheet Editor

Easily Apply Conditional Formatting Icon Sets in WPS Spreadsheet

WPS Spreadsheet provides intuitive and highly compatible data visualization tools, allowing you to configure precise conditional formatting rules just like you would in Microsoft Excel.

  1. 1. Select your data: Highlight the cells in WPS Spreadsheet containing your values from 1 to 5.
  2. 2. Choose an icon set: Go to the Home tab, click 'Conditional Formatting', navigate to 'Icon Sets', and select a 5-icon style.
  3. 3. Edit the rule: Click 'Conditional Formatting' > 'Manage Rules'. Select your rule and choose 'Edit Rule'.
  4. 4. Adjust thresholds: Change the Type from 'Percent' to 'Number' and input values 5, 4, 3, and 2 sequentially to perfect your data visualization.
Fully compatible with Microsoft Excel's conditional formatting and icon sets.Lightweight, free-to-use Office alternative with zero learning curve.Easily configure exact numeric thresholds to visualize your data accurately.
microsoft office alternative - wps office

Frequently Asked Questions

Why are my Excel icon sets displaying the wrong icons for my numbers?

By default, Excel assigns icons based on percentages of the highest and lowest values in your selected range. To fix this, you need to edit the conditional formatting rule, change the calculation Type from 'Percent' to 'Number', and manually input your desired exact values.

Can I hide the numbers and only show the icons in the cells?

Yes. In the 'Edit Formatting Rule' window where you configure your icon thresholds, check the box labeled 'Show Icon Only'. This keeps the numbers in the cells for calculation purposes but makes them invisible, leaving only the graphic icons.

How do I reverse the order of the icons in my conditional formatting rule?

When editing the conditional formatting rule, look for the 'Reverse Icon Order' button. Clicking this will swap the highest value icon with the lowest value icon, which is especially useful if a lower number represents a better or more positive outcome in your specific scenario.