Excel Conditional Formatting for Blank, Zero, and Nonzero Formula Results
Question details
Prevent conditional formatting from treating a formula-generated empty string as a numeric result.

- 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.
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.
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.
Select the cells containing the formula results, starting with the range's top-left cell.
Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(A1<>"",A1=0), replacing A1 if needed.
Click Format, choose a green fill or another desired format, and click OK to save the 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.
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.

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. Open the workbook: Open your spreadsheet in WPS Spreadsheet and select the range containing the formula results.
- 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. Choose the zero format: Click Format, select a green fill or another preferred format, and confirm the rule.
- 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. 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.

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.




