Excel Conditional Formatting Formula if Blank

Formulas & functions · Updated November 22, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.