Formula Errors

How to Highlight Formula Cells That Appear Blank in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs a way to visually highlight cells that contain active formulas but display as blank, without removing the underlying formulas.

Product: Excel
Device & OS: not provided
Scenario: Working with datasets where formulas like VLOOKUP return empty strings or spaces, making it difficult to distinguish them from genuinely empty cells.
Observed behavior: Cells contain active formulas but appear completely blank in the spreadsheet, requiring a visual indicator to spot them instantly.

Before you start

Identify the specific range of cells you need to format and confirm that your formulas are returning empty results rather than calculation errors like #REF! or #N/A.

Solution 1Recommended

Use Conditional Formatting with ISFORMULA and TRIM

Apply a custom formula rule in Conditional Formatting to detect and highlight cells that both contain a formula and appear blank.

This method combines the ISFORMULA function to check if the cell contains an active formula, and the TRIM function to check if the visual result is essentially an empty string or spaces.

Select the target range

Highlight the range of cells where you want to apply the formatting rule. Note the address of the very first cell in your selection, such as A1.

Open Conditional Formatting

Navigate to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Enter the custom formula

Choose the option 'Use a formula to determine which cells to format'. In the formula input box, type =AND(ISFORMULA(A1),TRIM(A1)=""). Ensure you replace 'A1' with the actual first cell of your selected range.

Set the highlight format

Click the 'Format' button, navigate to the 'Fill' tab, choose a background color to highlight the formula cells, and click 'OK' twice to apply the rule.

Use Relative Cell References: Ensure you do not use absolute references (like $A$1) in the conditional formatting formula so it can correctly evaluate every individual cell across your selected range.

WPS Spreadsheet Solution

Highlight Blank Formula Cells Easily in WPS Office

WPS Office provides a highly capable Spreadsheet application where you can use the exact same conditional formatting formulas to highlight visually blank results seamlessly and effectively.

1. Open your spreadsheet: Launch WPS Spreadsheet and open your existing workbook.
2. Select the data: Highlight the range of cells containing the formulas you want to check.
3. Access Conditional Formatting: Go to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'.
4. Input the formula rule: Choose 'Use a formula to determine which cells to format' and input =AND(ISFORMULA(A1),TRIM(A1)="").
5. Apply the highlight: Click 'Format' to select your preferred highlight color, then click 'OK' to save.

Fully compatible with Microsoft Excel formulas, including ISFORMULA and TRIM.Supports advanced conditional formatting rules without slowing down large datasets.Preserves perfect file formatting when opening, editing, and saving .xlsx files.Lightweight and free alternative with a familiar user interface for immediate productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why use TRIM in the conditional formatting formula instead of just checking for a blank?

Formulas might return spaces or empty text strings that look blank but are not technically completely empty. The TRIM function removes these extra spaces, ensuring that cells returning just spaces are accurately identified as visually blank.

Will applying this conditional formatting delete my existing formulas?

No, conditional formatting only changes the visual appearance, such as the background color or font styling, based on the rules you set. Your formulas like VLOOKUP will remain fully intact and functional.

How can I highlight cells that are truly empty and don't contain any formulas?

To highlight genuinely empty cells, you can use the built-in 'Format only cells that contain' rule in Conditional Formatting, select 'Blanks' from the dropdown, or use the ISBLANK function.

Excel & Data Problems