Highlight Blank Cells in Excel Columns E and F in Red
Question details
Apply red formatting to blank cells in columns E and F.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to visually identify missing entries in two worksheet columns.
- Observed behavior
- Blank cells in the selected ranges should display with a red fill automatically.
Select only the data rows in columns E and F that you want to check. If your sheet contains formulas that return an empty string, use a formula-based rule instead of the built-in Blanks condition.
Create a Conditional Formatting Rule for Blank Cells in Columns E and F
Use this method when truly empty cells in the selected ranges should be highlighted red.
Conditional Formatting applies the red fill automatically whenever a cell in the selected portion of columns E or F is blank.
Select the required range in columns E and F, such as E2:F1000. Avoid selecting header cells if they should not be highlighted.
On the Home tab, select Conditional Formatting > New Rule.
Choose Format only cells that contain, then set the rule description to Blanks.
Select Format, open the Fill tab, choose a red color, and select OK.
Select OK again to create the rule. Blank cells in the selected E and F ranges will now appear red.
Use a Formula Rule for Cells That Appear Blank Because of Formulas
Use this option when cells contain formulas such as =IF(A2="","",A2) and display as blank text.
Highlight Missing Data in WPS Spreadsheets with Conditional Formatting
WPS Spreadsheets lets you create conditional formatting rules that mark blank cells in columns E and F with a red fill. Its familiar spreadsheet interface makes it easy to apply, review, and edit the rule while working with Excel files.
- 1. Select columns E and F: In WPS Spreadsheets, select the data range in columns E and F, such as E2:F1000.
- 2. Open Conditional Formatting: Select Home > Conditional Formatting > New Rule.
- 3. Choose the blank-cell rule: Choose the option to format cells based on their content, then set the condition to Blank.
- 4. Apply a red fill: Select Format or Fill, choose a red background color, and confirm the formatting settings.
- 5. Save the workbook: Select OK to apply the rule, then save the workbook in .xlsx format if you need to share it with Excel users.
Frequently Asked Questions
How do I highlight blanks in only columns E and F, not the entire worksheet?
Select only the required range, such as E2:F500, before choosing Home > Conditional Formatting > New Rule. The rule will apply only to the selected cells.
Will the red color disappear after I enter a value in the cell?
Yes. A conditional formatting rule based on Blanks updates automatically, so the red fill is removed when the cell contains a value.
Why are cells with formulas that show nothing not being highlighted?
Those cells are not technically empty because they contain formulas. Create a formula-based rule through Home > Conditional Formatting > New Rule > Use a formula, and use a test such as =E2="".
Can I use a different color instead of red for blank cells?
Yes. In the New Formatting Rule window, select Format > Fill and choose any available fill color before confirming the rule.
How can I edit or remove the blank-cell formatting rule later?
Go to Home > Conditional Formatting > Manage Rules. Select the rule for columns E and F, then choose Edit Rule to change it or Delete Rule to remove it.
