Formatting Issues

How to Use Excel Conditional Formatting to Highlight Empty Form Fields

Algirdas Jasaitis Oct 7, 2026 868 views

Question details

The user wants to automatically highlight a specific range of cells in yellow when they are empty, but only if a corresponding cell in another column contains text. The highlight should disappear when the user fills in the field.

How to Highlight Empty Form Fields with Conditional Formatting in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating an interactive data entry form where mandatory fields are visually flagged if incomplete.
Observed behavior: Cells need to dynamically change their background color based on their own empty status and the non-empty status of a reference cell in the same row.

Before you start

Verify the exact range of your form fields and ensure that the reference column containing the trigger data is correctly identified. Make sure the target cells do not contain hidden spaces, as a space is treated as text by default.

Solution 1Recommended

Apply a Custom Conditional Formatting Formula

Use the AND function in a conditional formatting rule to check both conditions simultaneously: if the reference cell has text and the target cell is blank.

By combining the AND function with relative and absolute cell references, you can apply a single rule across an entire range. The absolute reference locks the condition to a specific column, while the relative reference applies the rule cell-by-cell.

Select the target range

Highlight the cells you want to format (e.g., C6:G17). Ensure that the top-left cell of your selection (C6) is the active cell, as the formula will be based on it.

Open Conditional Formatting

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

Choose the formula rule type

In the New Formatting Rule dialog box, click on 'Use a formula to determine which cells to format'.

Enter the formula

Type the formula =AND($B6<>"",C6="") into the text box. The $ symbol locks the check to column B, while C6 checks each individual cell in the selected range.

Set the highlight format

Click the Format button, go to the Fill tab, select a yellow background color, and click OK twice to confirm and apply the rule.

Apply a Custom Conditional Formatting Formula

Dynamic Highlighting: The conditional formatting is now active. The yellow highlight will automatically disappear the moment a user types data into the empty field.

Use WPS Spreadsheet

Easily Manage Conditional Formatting with WPS Office

WPS Spreadsheet provides a highly intuitive interface for setting up advanced conditional formatting rules, making it simple to build dynamic forms and track missing data without hassle.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet module.
2. Select the form cells: Highlight the range of cells that require mandatory data entry.
3. Access Conditional Formatting: Go to the Home tab, click Conditional Formatting, and choose New Rule.
4. Apply the logic: Select the formula option, input =AND($B6<>"",C6=""), set a fill color, and save.

Fully compatible with Microsoft Excel conditional formatting rules and formulas.Free, lightweight, and fast spreadsheet tool for processing large datasets.User-friendly interface for managing, editing, and clearing multiple formatting rules.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the conditional formatting highlighting the wrong cells in my form?

This usually happens if your active cell when selecting the range doesn't match the cell reference in your formula. Ensure the relative reference (e.g., C6) perfectly matches the top-left cell of your highlighted selection before creating the rule.

Does this formula treat cells with accidental spaces as empty?

No, the basic formula C6="" checks for truly blank cells. If a cell contains a space, Excel treats it as filled. You can modify the formula to =AND($B6<>"",TRIM(C6)="") to account for accidental spaces.

How do I remove or edit the conditional formatting rule later?

Select the cells in your form, go to the Home tab, click Conditional Formatting, and select Manage Rules. From there, you can edit the formula, change the color, or delete the rule entirely.

Document & File Issues