Fix Excel Conditional Formatting Icon Sets That Do Not Appear
Question details
The user needs to correct a copied worksheet's formula and conditional formatting so goal and milestone icons appear without their underlying values.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet was copied into a workbook with different category, start-date, duration, or date-header columns.
- Observed behavior
- The conditional formatting icon set does not appear as expected after the worksheet structure changed.
Identify the category, start-date, duration, and date-header cells in the copied worksheet. Confirm that the dates and durations are stored as numeric Excel values and determine whether the end date should be start date plus duration.
Correct the formula and icon-set rule
Use this approach when the copied worksheet has different columns and the icon set depends on formula results.
An icon set displays icons based on the values in its formatted cells. The example below uses a formula in each cell of the icon grid to return 2 for a goal, 1 for a milestone, or an empty string otherwise.
The example assumes categories are in column E, start dates in F, durations in G, date headers in row 5, and the first icon cell is H6. Replace these references with the locations in your worksheet.
Select the first cell of the icon grid and inspect its formula in the formula bar. Check that the category, start-date, duration, and date-header references point to the correct cells in the copied worksheet.
For the example layout, enter =IF(AND(ISNUMBER($F6),ISNUMBER($G6),ISNUMBER(H$5),H$5>=$F6,H$5<=$F6+$G6),IF($E6="Goal",2,IF($E6="Milestone",1,"")),"") in H6. Adjust the references and category names to match your worksheet, then fill the formula across and down the icon grid.
Select sample cells in the grid and check the formula bar or temporarily remove the icon rule. A matching goal should return 2, a matching milestone should return 1, and a date outside the specified range should return an empty string.
Select the icon grid, then choose Home > Conditional Formatting > Manage Rules. Edit the icon-set rule and verify that Applies to covers the intended grid. For a three-icon set, set both threshold types to Number, with thresholds of 2 and 1; choose the desired goal and milestone icons and set the lowest icon to No Cell Icon.
In the Edit Formatting Rule dialog, select Show Icon Only, then click OK and Apply. If the rule is still incorrect, delete it in Manage Rules and create a new icon set for the selected grid.

Work with Spreadsheet Formulas in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface for working with spreadsheets. Open compatible Excel files and continue editing formulas and formatting as you move between suites.

Frequently Asked Questions
Why did the icons disappear after I copied the worksheet?
The formula or the rule's Applies to range may still point to the original layout. Inspect the grid formula in the formula bar, then check Home > Conditional Formatting > Manage Rules.
How do I hide the numbers and keep the icons?
Select the formatted cells, open Home > Conditional Formatting > Manage Rules, edit the icon-set rule, and select Show Icon Only.
What values should the icon-set formula return?
For the example rule, return the number 2 for a goal and 1 for a milestone when the date conditions are met. Return an empty string otherwise, and set the lowest icon to No Cell Icon.
Why are the wrong icons showing for goals and milestones?
In Manage Rules, edit the icon-set rule and check that both thresholds use the Number type. Set the upper threshold to 2 and the next threshold to 1, then assign the intended icons to those levels.




