The formula to highlight blank cells in Excel conditional formatting is =ISBLANK(A1) (or =A1=""), applied to the range you want to check. For example, select A2:A20, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =ISBLANK(A2), and set a fill color.
Why Use a Formula Instead of the Built-In “Blanks” Option?
Excel’s built-in conditional formatting has a “Blanks” rule under Home > Conditional Formatting > Highlight Cells Rules > More Rules > Format only cells that contain > Blanks. That works for a single column or range, but it has two limitations: it only checks the cell itself, not other cells in the same row, and it cannot be combined with other conditions. A formula gives you full control—for instance, you can highlight a whole row when one cell in that row is blank, or highlight blank cells only if another column has a value.
How the ISBLANK Formula Works
When you create a conditional formatting rule with a formula, Excel evaluates the formula for the top-left cell of the selected range, then adjusts relative references for every other cell. So if you select A2:A20 and enter =ISBLANK(A2), Excel checks A2, then A3, then A4, and so on. The rule applies the formatting only when the formula returns TRUE.
Key syntax notes:
=ISBLANK(A2)returns TRUE only if A2 is truly empty—no formula, no space, no apostrophe.=A2=""returns TRUE if A2 is empty OR contains a formula that returns an empty string (like=IF(B2="","",B2)). Use this if you want to treat formulas that output “” as blank.- Both formulas must be entered without quotes around the cell reference (except the empty string in the second one).
Highlighting Entire Rows When a Cell Is Blank
To highlight the whole row when, say, column C is blank, select the range A2:E20 (with A2 as the active cell), then create a new rule with the formula =$C2="". The dollar sign locks the column reference so that every cell in the row checks column C. For example, if C5 is blank, the rule returns TRUE for A5, B5, C5, D5, and E5, and the entire row gets the fill.
If you prefer ISBLANK, use =ISBLANK($C2)—but remember it won’t catch formulas that return “”.
Common Problems and Fixes
Several issues trip up users new to formula-based conditional formatting:
- Wrong active cell: If you select A2:A20 but your formula references A1, Excel applies the rule shifted by one row. Always make the formula reference the active cell (the first cell in your selection, shown in the Name Box).
- Absolute vs. relative references: For a single column, use relative (A2). For a whole row, lock the column with $C2. For a whole column, lock the row with C$2.
- Spaces or invisible characters: A cell with a space looks blank but ISBLANK returns FALSE. Use
=TRIM(A2)=""to ignore spaces, or clean the data with Find & Select > Go to Special > Blanks. - Rule order: If you have multiple rules, Excel stops at the first TRUE rule unless “Stop If True” is checked. Put the blank rule first if you want it to override others.
Using Conditional Formatting to Count or Sum Blanks
Conditional formatting only changes appearance; it doesn’t calculate. To count blank cells, use =COUNTBLANK(A2:A20) in a cell. To sum values only when a corresponding cell is not blank, use =SUMIF(A2:A20,"<>",B2:B20) (with values in B2:B20 and criteria in A2:A20). For example, if A2:A20 has dates and B2:B20 has amounts, =SUMIF(A2:A20,"<>",B2:B20) sums amounts where the date is not blank.
Alternatives: Data Validation and Go To Special
Conditional formatting is visual, but sometimes you need to act on blanks:
- Data Validation: To prevent blanks from being entered, select the range, go to Data > Data Validation > Settings, choose “Custom,” and enter
=A2<>""(adjusting A2 to the active cell). This blocks empty entries but doesn’t highlight existing blanks. - Go to Special: Press F5 (or Ctrl+G), click Special, choose “Blanks,” and Excel selects all blank cells in the current range. You can then type a value and press Ctrl+Enter to fill them all at once.
- Find & Select: Home > Find & Select > Go to Special > Blanks works the same way and is often faster for one-off cleanup.
Frequently Asked Questions
Why doesn’t my ISBLANK formula highlight cells that look empty?
Those cells likely contain a space, a non-breaking space (CHAR(160)), or a formula that returns an empty string. ISBLANK only returns TRUE for truly empty cells. Use =TRIM(A2)="" to ignore spaces, or =A2="" to catch formula-generated blanks.
Can I highlight blanks in multiple non-adjacent columns?
Yes. Select the first column range, create the rule, then repeat for the other columns—or select all columns at once if they are adjacent. For non-adjacent, hold Ctrl while selecting, but note that the formula must reference the active cell of each selection. Easier: apply the rule to one column, then use Format Painter to copy it to the other columns.
How do I highlight blank cells only if another cell in the row is filled?
Use an AND condition. For example, to highlight A2 if B2 is not blank and A2 is blank, select A2:A20 and enter =AND($B2<>"",A2=""). This is useful for required-field lists.
Does this work in Google Sheets?
Yes, the same formula works, but the menu path differs: in Google Sheets, select the range, go to Format > Conditional formatting, choose “Custom formula is,” and enter the same formula. Google Sheets uses the same ISBLANK and “” syntax.
Why does my rule highlight the wrong cells?
Check the “Applies to” range in the Conditional Formatting Rules Manager (Home > Conditional Formatting > Manage Rules). If the range is wrong, edit it. Also confirm the formula’s relative reference matches the active cell—if your selection starts at B2, the formula should reference B2, not A2.
Can I use conditional formatting to hide blank rows?
No—conditional formatting cannot hide rows or columns; it only changes formatting. To hide rows with blanks, use a filter (Data > Filter) and filter out blanks, or use a macro (VBA) to hide rows, but that requires saving a copy of the workbook first because macros cannot be undone with Ctrl+Z.
Related
- X++ String Split: How to Use str2con and Other Methods
- Excel Before Close Event: How to Run Code on Save
- SUBSTRING in Snowflake: Syntax, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIFS in Excel with Dates: Examples and TipsTo sum values for dates in Excel, use SUMIFS with the amount range first and the date range…
- SUMIF Positive Numbers in Excel: Formula and ExamplesTo sum positive numbers in Excel, use SUMIF with the criterion ">0". If the numbers are in B2:B10,…
- Debt Payment Excel Formula: Calculate Your Monthly CostIn Excel, use the PMT function to calculate a fixed monthly debt payment: =-PMT(annual_rate/12,number_of_months,balance). For example, a $12,000…
- Excel INDIRECT Function: Reference Another SheetYes. In Excel, INDIRECT can turn a text string containing a worksheet reference into a usable reference. For…
- Excel Sort Numbers Low to High: Steps, Tips, and FixesTo sort numbers from low to high in Excel for Microsoft 365 on Windows, click a cell in…
- Excel Data Validation Formula: Rules, Examples & FixesAn Excel data validation formula is a custom rule that controls what users can enter in selected cells.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.