Formatting Issues

Excel Conditional Formatting for Blank, Zero, and Nonzero Formula Results

Amos GikundaAmos Gikunda Nov 18, 2026 869 views

Question details

Prevent conditional formatting from treating a formula-generated empty string as a numeric result.

Excel Conditional Formatting for Blank, Zero, and Nonzero Formula Results
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses a formula such as =IF(A1="","",A1) and needs different formatting for zero and nonzero results.
Observed behavior
A cell displaying an empty string can receive number-based conditional formatting even though it appears blank.
Before you start

Identify the range containing the formula results and note its top-left cell. Check Home > Conditional Formatting > Manage Rules for existing rules that might also format the range.

Solution 1Recommended

Create rules that exclude empty-string results

Use formula-based rules to format zero and nonzero results while leaving empty strings unformatted.

The formulas below assume A1 is the top-left cell of the selected range. Replace A1 with the top-left cell of your range and keep the reference relative so Excel evaluates each cell.

1
Select the result range

Select the cells containing the formula results, starting with the range's top-left cell.

2
Create the zero rule

Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(A1<>"",A1=0), replacing A1 if needed.

3
Set the zero format

Click Format, choose a green fill or another desired format, and click OK to save the rule.

4
Create the nonzero rule

With the same range selected, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(A1<>"",A1<>0), choose a red format, and save the rule.

5
Review the rules

Open Home > Conditional Formatting > Manage Rules and verify that both rules apply to the intended range. Edit or remove conflicting rules if empty-string results are still formatted.

Create rules that exclude empty-string results
Text values: The nonzero rule can also match nonempty text. To format only nonzero numbers, use =AND(ISNUMBER(A1),A1<>0) instead.
Work with spreadsheets in WPS Office

Format blank and numeric formula results in WPS Spreadsheet

Use formula-based conditional formatting in WPS Spreadsheet to give zero and nonzero results different colors while excluding empty strings.

  1. 1. Open the workbook: Open your spreadsheet in WPS Spreadsheet and select the range containing the formula results.
  2. 2. Add the zero rule: Choose Home > Conditional Formatting > New Rule and select the option to use a formula. Enter =AND(A1<>"",A1=0), using the top-left cell of your selected range in place of A1.
  3. 3. Choose the zero format: Click Format, select a green fill or another preferred format, and confirm the rule.
  4. 4. Add the nonzero rule: Create another formula-based rule for the same range with =AND(A1<>"",A1<>0), replacing A1 as needed. Choose a red format and confirm.
  5. 5. Check the results: Use Home > Conditional Formatting to review the rules and their applied range. Test cells returning an empty string, zero, and a nonzero number.
Apply separate rules for zero and nonzero formula results.Exclude formula-generated empty strings from formatting.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface for everyday work.
microsoft office alternative - wps office

Frequently Asked Questions

Why does a cell that looks blank receive conditional formatting?

A formula can return an empty string using "", so the cell looks blank even though it contains a formula. Add a condition such as A1<>"" to the formatting rule to exclude that result.

What reference should I use if my selected range starts in B2?

Use B2 as the relative reference in both formulas: =AND(B2<>"",B2=0) and =AND(B2<>"",B2<>0). Apply the rules to the full range beginning at B2.

How do I format only nonzero numbers, not text?

In the formula-based conditional formatting rule, use =AND(ISNUMBER(A1),A1<>0), replacing A1 with the top-left cell of the selected range.

Why are empty-string results still colored after I add the rules?

Open Home > Conditional Formatting > Manage Rules and check for other rules applying to the same cells. Edit or remove any conflicting rule, and verify the Applies to range for each rule.