Formatting Issues

How to Fix Excel Conditional Formatting Ignoring Zero Values

Chanuka GeekiyanageChanuka Geekiyanage Nov 10, 2026 869 views

Question details

The user is trying to hide £0.00 currency values using a conditional formatting rule, but one specific cell continues to display £0.00 and ignores the rule.

How to Fix Excel Conditional Formatting Ignoring Zero Values
Product
Excel
Device & OS
not provided
Scenario
Hiding zero currency values in a spreadsheet to clean up the visual presentation.
Observed behavior
One cell ignores the conditional formatting rule set for zero values, displaying £0.00 instead of having its font and fill turned white.
Before you start

Check the affected cell's actual underlying value by temporarily increasing the displayed decimal places; you may find that the value is a microscopic fraction (like 0.00000000123) rather than a true zero.

Solution 1Recommended

Adjust the Conditional Formatting Rule for Small Fractions

Modify your conditional formatting rule to account for tiny floating-point decimals that are formatted as zero.

Spreadsheet calculations sometimes produce floating-point errors, leaving minuscule decimal values that appear as 0.00 due to currency formatting. Adjusting your rule condition easily bypasses this issue.

1
Select the Affected Cells

Highlight the cell or range of cells where the conditional formatting is failing to hide the zero values.

2
Open the Rules Manager

Navigate to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'Manage Rules'.

3
Edit the Existing Rule

Select the rule you created to hide zeros and click the 'Edit Rule' button.

4
Update the Condition

Change the condition from 'Equal to 0' to 'Less than 0.01' (the smallest denomination for standard currencies like GBP or USD).

5
Apply and Save

Click 'OK' to close the editor, then click 'Apply' in the Rules Manager to enforce the updated rule.

Verified Fix: Setting the threshold to less than 0.01 successfully captures and hides any microscopic values that round to zero in currency format.
Seamless Data Formatting

Apply Precise Conditional Formatting in WPS Spreadsheet

Handling complex data and hidden decimal values is effortless with WPS Spreadsheet. You can quickly apply, manage, and troubleshoot conditional formatting to hide zero values or highlight specific data trends.

  1. 1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the workbook containing your currency data.
  2. 2. Select the Target Data: Highlight the cells or columns where you want to hide the zero values.
  3. 3. Access Conditional Formatting: Go to the Home tab on the top menu and click the 'Conditional Formatting' icon.
  4. 4. Set Up the Rule: Choose 'Highlight Cells Rules', select 'Less Than', and enter '0.01' in the value field.
  5. 5. Apply Custom Format: Choose a custom format setting both the font and fill color to white, then click 'OK' to apply.
100% compatible with Microsoft Excel (.xlsx) conditional formatting and formulasUser-friendly interface for managing complex and custom formatting rulesBuilt-in support for advanced mathematical functions like ROUNDFree and lightweight alternative for daily data analysis
microsoft office alternative - wps office

Frequently Asked Questions

Why does my spreadsheet show a value as 0.00 when it is not actually zero?

Spreadsheet programs use floating-point arithmetic for calculations. This can sometimes result in minuscule leftover decimals (like 0.000000001) after complex operations. The currency number format visually rounds this tiny number to 0.00, but the underlying cell value is not a true zero.

How can I hide all zero values in an entire worksheet without conditional formatting?

You can hide zero values globally by going to File > Options > Advanced. Scroll down to the 'Display options for this worksheet' section and uncheck the box that says 'Show a zero in cells that have zero value'.

Does the ROUND function permanently change the cell's value?

Yes, using the ROUND function in your formula alters the actual calculated result stored in the cell to the specified number of decimal places. This effectively removes any hidden floating-point fractions and makes the result a definitive number.