Hide Excel Icon Set Icons When Related Cells Are Blank
Question details
Hide conditional formatting icons when required 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.
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.
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.
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.
Select the helper formula range, then choose Home > Conditional Formatting > Icon Sets and select a three-icon set.
With the helper range selected, open Home > Conditional Formatting > Manage Rules, select the icon set rule, and click Edit Rule. Select Show Icon Only.
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.
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.

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. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 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. Fill the formula down: Select the formula cell and drag its fill handle down through the rows you want to check.
- 4. Check and save: Test a blank and a complete row, then use File > Save As to save the workbook in XLSX format.

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.




