Formatting Issues

Fix Excel Conditional Formatting Icon Sets That Do Not Appear

Khadija KhanKhadija Khan Nov 20, 2026 869 views

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.

Fix Excel Conditional Formatting Icon Sets That Do Not Appear
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.
Before you start

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.

Solution 1Recommended

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.

1
Inspect the existing references

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.

2
Update the grid formula

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.

3
Check the results

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.

4
Edit the icon rule

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.

5
Show only the icons

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.

Correct the formula and icon-set rule
Check the end-date convention: The example includes dates from the start date through start date plus duration. If duration counts the start day as day one, adjust the end-date calculation to match your schedule.
Free Microsoft Office alternative

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.

Edit spreadsheet formulas used to calculate goal and milestone values.Open and save Microsoft Excel formats, including XLSX.Use a familiar spreadsheet interface for an easier transition from Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.