Formatting Issues

Hide Excel Icon Set Icons When Related Cells Are Blank

Bushra ParveenBushra Parveen Nov 22, 2026 868 views

Question details

Hide conditional formatting icons when required related cells are blank.

Hide Excel Icon Set Icons When Related Cells Are Blank
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses an icon set to indicate whether data in related cells is complete.
Observed behavior
Icons should appear only when the required cells contain data.
Before you start

Identify the cells that must contain data and choose an unused helper column for the icon set. The example below checks D6, G6, and H6; change these references to match your worksheet.

Solution 1Recommended

Use a helper column and hide the lower icon thresholds

Use this method to display an icon only when all required cells in a row contain data.

1
Create the helper formula

In an unused cell on row 6, enter =IF(AND(D6<>"",G6<>"",H6<>""),1,0). Drag the fill handle down to apply the formula to the remaining rows.

2
Apply an icon set

Select the helper formula range, then choose Home > Conditional Formatting > Icon Sets and select a three-icon set.

3
Edit the rule

With the helper range selected, open Home > Conditional Formatting > Manage Rules, select the icon set rule, and click Edit Rule. Select Show Icon Only.

4
Set the thresholds

Set both threshold types to Number. Set the top icon threshold to >= 1 and the middle threshold to >= 0. In the icon dropdowns, keep the top icon and select No Cell Icon for both the middle and bottom icons, then click OK.

5
Test the results

Leave one of the checked cells blank and confirm that no icon appears. Then enter data in D6, G6, and H6 and confirm that the top icon appears.

Use a helper column and hide the lower icon thresholds
Adjust the required cells: Remove or change references in the AND formula if your icon should depend on a different set of cells. The helper formula returns 0 for incomplete rows and 1 for complete rows.
WPS Spreadsheets

Show a completion symbol only for filled rows in WPS Spreadsheets

In WPS Spreadsheets, use a helper formula to display a symbol when all required cells contain data and leave the result blank otherwise. This provides a simple alternative to an Excel icon set.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Enter a symbol formula: In an unused cell on row 6, enter =IF(AND(D6<>"",G6<>"",H6<>""),"●",""). Change the cell references to match the fields you want to check.
  3. 3. Fill the formula down: Select the formula cell and drag its fill handle down through the rows you want to check.
  4. 4. Check and save: Test a blank and a complete row, then use File > Save As to save the workbook in XLSX format.
Show a completion symbol only when required cells are filledOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and fill controlsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the helper formula return 0 and 1?

The formula returns 1 when all checked cells contain data and 0 otherwise. The icon set rule displays an icon for 1 and uses No Cell Icon for 0.

Can I hide the helper values but keep the icons?

Yes. Select the helper range, open Home > Conditional Formatting > Manage Rules, edit the icon set rule, and select Show Icon Only.

How do I check only one related cell?

Use a formula such as =IF(D6="",0,1) in the helper column, then apply the same icon thresholds: an icon for values of at least 1 and No Cell Icon for lower values.

What if a related cell contains a formula that returns an empty string?

The checks D6<>"", G6<>"", and H6<>"" treat an empty-string result as blank, so the helper formula returns 0 until each required cell has a nonempty result.