How to Set Excel Conditional Formatting Icon Sets for Numbers 1 to 5
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.

- 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.
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.
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.
Highlight the cells containing the numbers you want to format.
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.
Click 'Conditional Formatting' again and select 'Manage Rules'. Select the icon set rule you just applied and click 'Edit Rule'.
In the 'Edit Formatting Rule' dialog box, locate the 'Type' dropdown menus for each icon. Change every dropdown from 'Percent' to 'Number'.
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.

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. Select your data: Highlight the cells in WPS Spreadsheet containing your values from 1 to 5.
- 2. Choose an icon set: Go to the Home tab, click 'Conditional Formatting', navigate to 'Icon Sets', and select a 5-icon style.
- 3. Edit the rule: Click 'Conditional Formatting' > 'Manage Rules'. Select your rule and choose 'Edit Rule'.
- 4. Adjust thresholds: Change the Type from 'Percent' to 'Number' and input values 5, 4, 3, and 2 sequentially to perfect your data visualization.

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.




