Highlight Blank Cells: Excel & Google Sheets Guide
- Average speed
- km/h
- Result
- Finish time
- 50 min
- You entered
- Distance
- 10 km
- You entered
This guide explains how to highlight blank cells in Microsoft Excel and Google Sheets using conditional formatting. You’ll learn the exact steps for both applications, how to write the necessary formulas, and how to troubleshoot common issues like false positives or formatting not applying.
Formula
The core formula for highlighting blank cells is simple: =ISBLANK(A1) or =A1="". Both return TRUE for a truly empty cell. The difference is that ISBLANK() only returns TRUE for cells with absolutely nothing in them, while A1="" also returns TRUE for cells containing a formula that returns an empty string (like =IF(1=2,"","x")). In conditional formatting, you apply this formula to the top-left cell of your selected range, and the formatting will automatically adjust for all other cells.
Worked Example
Suppose you have a column of data from A1 to A10, and you want to highlight the blank cells. Select the range A1:A10. In Excel, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. In the formula box, enter =ISBLANK(A1). Click Format, choose a fill color (e.g., yellow), and click OK. Now every cell in A1:A10 that is empty will be highlighted. The formula uses A1 because it’s the active cell in the selection; Excel adjusts it for each row.
Why Highlight Blank Cells?
Blank cells often indicate missing data, incomplete entries, or errors in a dataset. Highlighting them makes it easy to spot gaps in a list, find missing values before analysis, or ensure a form is fully filled. For example, a teacher might highlight blank cells in a gradebook to see which students haven’t submitted an assignment. An accountant might highlight blank cells in a budget to find missing expense categories.
How to Highlight Blank Cells in Excel
- Select the range of cells you want to check (e.g., A1:A20).
- Go to the Home tab, click Conditional Formatting, then New Rule.
- Choose Use a formula to determine which cells to format.
- In the formula box, type
=ISBLANK(A1)(replace A1 with the top-left cell of your selection). - Click Format, go to the Fill tab, pick a color, and click OK.
- Click OK again to apply the rule.
Alternatively, you can use the built-in “Blanks” option: select your range, go to Conditional Formatting > Highlight Cells Rules > More Rules, select “Blanks” from the drop-down, and set a format.
How to Highlight Blank Cells in Google Sheets
- Select the range (e.g., A1:A20).
- Click Format > Conditional formatting.
- Under “Format cells if,” choose Custom formula is.
- Enter
=ISBLANK(A1)(adjust A1 to the top-left cell of your selection). - Set a fill color under “Formatting style.”
- Click Done.
Google Sheets also has a preset: in the “Format cells if” dropdown, select “Is empty” to highlight blank cells without a formula.
Common Mistakes and Misconceptions
- Using =”” instead of ISBLANK: If your data contains formulas that return empty strings,
=ISBLANK()won’t highlight them. Use=A1=""if you want to include those. - Spaces look blank but aren’t: A cell with a space character (” “) is not blank.
ISBLANK()returns FALSE. Use=TRIM(A1)=""to treat spaces as blank. - Applying the rule to the wrong range: The formula must reference the top-left cell of your selected range. If you select A2:A10 and use
=ISBLANK(A1), the formatting will be off by one row. - Merged cells: Conditional formatting can behave unpredictably with merged cells. Unmerge or format the individual cells instead.
How to Choose Between ISBLANK and =””
Use =ISBLANK() when you want to highlight only truly empty cells—no formulas, no spaces, no invisible characters. Use =A1="" when you want to also highlight cells with formulas that return an empty string. If you suspect invisible spaces, combine with TRIM: =TRIM(A1)="". For most data entry tasks, =ISBLANK() is the safer choice because it avoids false positives.
Limitations and Edge Cases
Conditional formatting is dynamic—it updates as you edit cells. However, it does not work on cells that are truly empty if the range includes entire columns (e.g., A:A) because that would highlight every empty cell in the column, which is usually not desired. Also, if you copy and paste values into the range, the formatting will apply to the new blanks. If you delete rows, the rule adjusts automatically. For very large datasets (tens of thousands of rows), conditional formatting may slow down performance, but it’s generally fine for typical use.
Frequently Asked Questions
Why is my blank cell not highlighting?
Check if the cell contains a space, a formula returning an empty string, or a non-printing character. Use =TRIM(A1)="" to catch spaces. Also verify that the formula references the correct top-left cell and that the range in the rule matches your selection.
How do I highlight blank cells in a specific column only?
Select just that column (e.g., B1:B100) and apply the rule with =ISBLANK(B1). The range is limited to that column, so only blanks there are highlighted.
Can I highlight blank cells with a different color than other cells?
Yes, conditional formatting allows you to set any fill color, font color, or border. Just choose the format in the rule’s Format dialog.
Does highlighting blank cells work with data validation?
Yes, but data validation only restricts input; it doesn’t automatically highlight blanks. You still need conditional formatting to highlight them.
How do I remove the highlighting?
Select the range, go to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells (Excel) or Format > Conditional formatting > Remove rule (Google Sheets).
Can I highlight blank cells in a pivot table?
Conditional formatting works on pivot table cells, but blank cells in a pivot table are often structural (e.g., blank rows/columns). You may need to apply the rule to the pivot table’s data range or use a different approach like filtering.
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.