Highlight Rows with Blank Cells in Excel
- Average speed
- km/h
- Result
- Finish time
- 50 min
- You entered
- Distance
- 10 km
- You entered
This guide explains how to highlight entire rows in Microsoft Excel when any cell in that row is blank, using conditional formatting. You will learn the formula-based approach that works for any column range, along with step-by-step instructions and common pitfalls to avoid.
Formula
The core idea is to use a conditional formatting formula that checks whether any cell in the row is blank. For a row starting at row 2 (with headers in row 1), and columns A through E, the formula is:
=COUNTBLANK($A2:$E2)>0
This formula counts the number of blank cells in the range $A2:$E2. If the count is greater than zero, the row is highlighted. The dollar signs lock the columns so the rule applies to the entire row, while the row number (2) is relative and changes for each row.
Worked Example
Suppose you have a dataset with headers in row 1 and data in rows 2 to 10, with columns A (Name), B (Age), C (City), D (Email), E (Phone). You want to highlight any row where at least one of these columns is blank.
- Select the range A2:E10 (or the entire rows 2 to 10 if you prefer).
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- In the formula box, enter:
=COUNTBLANK($A2:$E2)>0 - Click Format, choose a fill color (e.g., yellow), and click OK.
- Click OK again to apply the rule.
Now, if row 4 has a blank in column D, the entire row 4 will be highlighted. The formula works because the relative row reference (2) adjusts for each row in the selected range.
When to Use This
Highlighting rows with blank cells is useful for data cleaning, reviewing survey responses, checking completeness of records, or preparing reports where missing information needs attention. It helps you quickly spot incomplete entries without manually scanning each column.
How to Read the Result
After applying the rule, any row with at least one blank cell in the specified columns will be shaded with the color you chose. Rows with no blank cells remain unformatted. This visual cue lets you focus on the rows that need completion.
Common Mistakes
- Wrong range selection: If you select entire rows (e.g., A:E) but the formula uses $A2:$E2, it will still work, but if you select only data cells, the rule may not apply to the whole row. Always select the range you want to format.
- Relative vs. absolute references: Forgetting the dollar signs ($A2) will cause the rule to check different columns for each cell, leading to incorrect highlighting.
- Blank cells from formulas: A cell containing a formula that returns an empty string (“”) is not considered blank by COUNTBLANK. Use
=SUMPRODUCT(--(TRIM($A2:$E2)=""))>0to treat such cells as blank. - Applying to the wrong sheet: The rule applies only to the selected range; if you add new rows, you need to update the range or use a table.
Alternatives and Variations
If you want to highlight rows where all cells are blank, use =COUNTBLANK($A2:$E2)=COLUMNS($A2:$E2). To highlight rows where a specific column is blank, use =$A2="" (for column A). You can also combine conditions, e.g., highlight rows where column A is blank OR column B is blank: =OR($A2="",$B2="").
Frequently Asked Questions
Why isn’t my row highlighting when a cell looks empty?
Cells that contain spaces, line breaks, or formulas returning empty strings are not truly blank. Use the TRIM function in the formula to ignore spaces: =SUMPRODUCT(--(TRIM($A2:$E2)=""))>0.
How do I highlight rows with blank cells in Google Sheets?
The same formula works in Google Sheets. Select the range, go to Format > Conditional formatting, choose “Custom formula is”, and enter the same COUNTBLANK formula.
Can I highlight only rows where a specific column is blank?
Yes, use a formula like =$C2="" to highlight rows where column C is blank. Adjust the column letter as needed.
Will the highlight update if I add new rows later?
Only if you include those rows in the conditional formatting range. To make it dynamic, convert your data to an Excel Table (Ctrl+T) and use structured references, or apply the rule to a large range like $A$2:$E$1000.
How do I remove the highlighting?
Select the range, go to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Alternatively, manage rules in the Conditional Formatting Rules Manager and delete the specific rule.
Does this work for blank cells in non-adjacent columns?
Yes, you can include non-adjacent columns by using a comma in the range, e.g., =COUNTBLANK($A2,$C2,$E2)>0. However, note that COUNTBLANK with multiple arguments treats each argument as a separate range, so it counts blanks across all of them.
Related
Formula used
in t | Finish time | min | 50 | 0 in d | Distance | km | 10 | 0 out pace | Average pace | min/km | t / d | 2 out speed | Average speed | km/h | d / (t / 60) | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.