Formatting Issues

Ignore Zero Values in Excel Three-Color Conditional Formatting

Nimra MalikNimra Malik Nov 14, 2026 868 views

Question details

Exclude zero values when calculating an Excel three-color conditional-formatting scale.

Ignore Zero Values in Excel Three-Color Conditional Formatting
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.
Before you start

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.

Solution 1Recommended

Use a Helper Column to Exclude Zeros from the Color Scale

Use this method when the scale must calculate its colors from nonzero values.

1
Enter the helper formula

In cell C2, enter =IF(A2=0,FALSE,A2). The formula returns FALSE for a zero and otherwise returns the source value.

2
Copy the formula down

Drag the fill handle from C2 through C21 to match the source range A2:A21.

3
Apply the color scale

Select C2:C21, then choose Home > Conditional Formatting > Color Scales and select a three-color scale.

4
Check the result

Compare column C with column A. Nonzero numbers should receive scale colors, while FALSE values are ignored by the scale.

Use a Helper Column to Exclude Zeros from the Color Scale
Formatting appears in the helper column: The color scale is applied to C2:C21, not the original cells. FALSE values may remain visible in the helper column.
Work with color scales in WPS

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and identify the source range, such as A2:A21.
  2. 2. Create a helper column: In C2, enter =IF(A2=0,FALSE,A2), then drag the fill handle down through C21.
  3. 3. Apply a color scale: Select C2:C21 and choose Home > Conditional Formatting > Color Scales, then select a three-color scale.
  4. 4. Save the workbook: Choose File > Save or Save As to save the workbook in an Excel-compatible format.
Create a color scale based on nonzero valuesOpen and edit Microsoft Excel XLSX and XLS filesUse familiar formulas and conditional-formatting controls
QA img-9

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.