How to Fix Excel Conditional Formatting Ignoring Zero Values
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.

- 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.
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.
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.
Highlight the cell or range of cells where the conditional formatting is failing to hide the zero values.
Navigate to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'Manage Rules'.
Select the rule you created to hide zeros and click the 'Edit Rule' button.
Change the condition from 'Equal to 0' to 'Less than 0.01' (the smallest denomination for standard currencies like GBP or USD).
Click 'OK' to close the editor, then click 'Apply' in the Rules Manager to enforce the updated rule.
Use the ROUND Function in Your Formula
Round the calculation result to two decimal places to ensure the output is exactly zero.
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. Open Your Spreadsheet: Launch WPS Spreadsheet and open the workbook containing your currency data.
- 2. Select the Target Data: Highlight the cells or columns where you want to hide the zero values.
- 3. Access Conditional Formatting: Go to the Home tab on the top menu and click the 'Conditional Formatting' icon.
- 4. Set Up the Rule: Choose 'Highlight Cells Rules', select 'Less Than', and enter '0.01' in the value field.
- 5. Apply Custom Format: Choose a custom format setting both the font and fill color to white, then click 'OK' to apply.

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.




