How to Highlight Blank Cells in Excel

This post will guide you how to highlight blank cells in Excel. How do I use Conditional Formatting to highlight blank cells in Excel.

Highlight Blank Cells with Go To Special Feature


If you want to highlight all blank cells in your select range of cells, you need to select all blank cells firstly, then you can fill color in those selected blank cells. Just do the following steps:

#1 select the range of cells that contain blank cells to be highlighted.

highlight blank cells1

#2 go to HOME tab, click Find & Select command under Editing group, and then select Go To Special from the popup menu list. And the Go To Special dialog will open.

highlight blank cells2

 

#3 select Blanks radio button  , and click OK button. You will see that all blank cells will be selected in the selected range of cells.

highlight blank cells3

highlight blank cells4

#4 go to HOME tab, click Fill Color icon under Font group, and choose one color as you need. All blank cells will be highlighted.

highlight blank cells5

highlight blank cells6

Highlight Blank Cells with Conditional Formatting


You can also use the conditional formatting to highlight blank cells in your selected range of cells in Excel, just do the following steps:

#1 select the range of cells where you want to highlight all blank cells

 highlight blank cells1

#2 go to HOME tab, click Conditional Formatting command under styles group. And select New Rules from the popup menu list. And the New Formatting Rule dialog will open.

highlight blank cells7

#3 select Use a formula to determine which cells to format in the Select a Rule Type list box, and type the following formula into the Format values where this formula is true: text box.

=B1=””

highlight blank cells8

Note: the Cell B1 is the first cell of your selected range of cells.

#4 click Format button to open Format Cells dialog, switch to Fill tab, and then choose one color as you like to fill. Click Ok button.

highlight blank cells9

#5 click OK button. All blank cells in selected range of cells have been highlighted.

highlight blank cells10

Video: Highlight Blank Cells

 

Related Posts
How to Sort Data but Keep Blank Cells in Excel

In daily work, if we sort data with blank cells included in the same column, these blank cells are listed at the bottom automatically after sorting. If we want to keep the positions of these blank cells unchanged and only ...

How to Copy and Paste Only Values and Ignore Formula

When we copy a cell applied with a formula, we copy the formula of the cell rather than copy the value showing in the cell. In this article we will introduce you the way to copy only value ignoring applied ...

How to Sort Date by Day of Week in Excel

Except sort data by “A to Z” (alphabetical order, for numbers from small to large), we can also sort data by date, month or year if these conditions are given. In this article, we will show you the way to ...

How to Select All Non-Blank Cells of a Range

In daily work, we may meet the cases that select all blank cells or non-blank cells of a range. You may know the way to select all blank cells as they are “blanks”. But for non-blank cells, they may contain ...

How to Highlight Cells Greater Than a Certain Length in Excel
highlight cells based on cell lenght6

This post will guide you how to highlight a cell that contains more than a certain length using Conditional Formatting function in Excel. How do I highlight cells greater than or less than a certain text length in Excel. Highlight ...

How to Highlight Dates Older Than 30 Days in Excel
highlight dates older than 30 days5

This post will guide you how to highlight dates older than 30 days in Excel. How do I use conditional formatting to highlight if date is greater than 30 days old in Excel. How to highlight a row or cell ...

How to Delete All Comments in Excel
delete all comments9

This post will guide you how to delete all comments in your current worksheet in Excel. How do I delete all comments from a worksheet with VBA code in Excel. Delete All Comments with Delete Command Delete All Comments with ...

How to Select Cells That Meet Certain Criteria in Excel
select cells that meet criteria10

This post will guide you how to select cells that meet certain criteria in Excel. How do I find and select cells based on specific types of data in your worksheet in Excel. Select Cells That Meet one Criteria Using ...

How to Replace # Errors with Zero or Any Specific Value in Excel
replace formula error5

This post will guide you how to replace #VALUE! Or #DIV/0! Errors with zero or any specific value in Excel. How do I replace all Formula Errors in your worksheet with Go to Special feature in Excel. Replace # Formula ...

How to Unmerge Cells and Fill Down Values in Excel
unmerge cells fill values11

This post will guide you how to unmerge cells and fill with duplicate values in Excel 2013/2016. How do I unmerge multiple cells and copy the content in each previously merged cell in Excel. How to unmerge cells and fill ...

Sidebar