Ignore Zero Values in Excel Three-Color Conditional Formatting
Question details
Exclude zero values when calculating an Excel three-color conditional-formatting scale.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to apply a three-color scale to a range while basing its colors on nonzero values.
- Observed behavior
- Excel's three-color scale does not provide a direct setting to exclude zeros from its calculations.
Identify the numeric range you want to format and reserve an empty helper column of the same length. The examples below use A2:A21 as the source range and column C as the helper column.
Use a Helper Column to Exclude Zeros from the Color Scale
Use this method when the scale must calculate its colors from nonzero values.
In cell C2, enter =IF(A2=0,FALSE,A2). The formula returns FALSE for a zero and otherwise returns the source value.
Drag the fill handle from C2 through C21 to match the source range A2:A21.
Select C2:C21, then choose Home > Conditional Formatting > Color Scales and select a three-color scale.
Compare column C with column A. Nonzero numbers should receive scale colors, while FALSE values are ignored by the scale.

Give Zero Cells a Neutral Fill
Use this method when you only need zeros to look neutral in the original range; it does not exclude them from the scale calculation.
Exclude Zeros from a Color Scale in WPS Spreadsheets
Use an IF formula in a helper column, then apply a three-color scale to the helper range so that zero values are represented by FALSE instead of numbers.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and identify the source range, such as A2:A21.
- 2. Create a helper column: In C2, enter =IF(A2=0,FALSE,A2), then drag the fill handle down through C21.
- 3. Apply a color scale: Select C2:C21 and choose Home > Conditional Formatting > Color Scales, then select a three-color scale.
- 4. Save the workbook: Choose File > Save or Save As to save the workbook in an Excel-compatible format.

Frequently Asked Questions
Can I exclude zeros directly in Excel's three-color scale settings?
The three-color scale has no direct exclude-zeros setting. Use a helper column that returns FALSE for zeros, then apply the scale to that column.
Does hiding zero values remove them from the color-scale calculation?
No. In Excel, File > Options > Advanced lets you hide the display of zeros for a worksheet, but those values can still affect conditional-formatting calculations.
Will the helper-column colors appear on my original cells?
No. A color scale applied to C2:C21 colors the helper cells, not the corresponding cells in A2:A21.
Can I make zero cells look blank without changing the scale calculation?
Yes. Add a Cell Value Equal To 0 rule with a neutral fill, place it above the color-scale rule in Conditional Formatting > Manage Rules, and select Stop If True. Zeros can still affect the scale's thresholds.




