Skip to main content
formatting result

How to highlight all blank Cells in Excel [Conditional Formatting]

In this blog post I will show you multiple methods on how to Highlight blank cells. We will use conditional formatting to give these cells a background color.

Method 1: Dynamic

Step 1: Select the whole table. The easiest method is selecting the top left cell, then select the bottom right cell while holding down the shift button. Other methods might not work because the table is missing certain values.

Step 2: Go to Home tab –> Highlight Cells Rules –> More Rules… –>  a new window will open.

condition formatting more rules

Step 3: Format only cells with: Blanks –> format.

new formatting rule

Step 4: Select a background color.

Format empty cells

Step 5: press OK –> OK.

Video example from my youtube channel:

Method 2: Isblank()

Step 1: Select the whole table. The easiest method is selecting the top left cell, then select the bottom right cell while holding down the shift button. Other methods might not work because the table is missing certain values.

Step 2: Go to Home tab –> Conditional Formatting –> new rule…

Step 3: Select Use a formula to determine which cells to format.

New formatting rule for blank cells in excel

Step 4: Now we will use the formula =ISBLANK(A2). This is the first cell (top left) of your table.

Step 5: Click on Format. A new window will appear.

Format empty cells

Step 6: Click on OK and check the result.

formatting result

Method 3: LEN( )=0

Use following formula:  =LEN(A2)=0

Previous methods have 1 little downside. empty strings “” will not be highlighted by those methods. Using LEN might be a better option because it highlights both.

Download my files

Feel free to download my workbook so you can try this example yourself:

Download Workbook

Leave a Reply

%d bloggers like this: