How to Highlight Blank Cells in Excel: 4 Ways to Do It Better

Have you just got an Excel spreadsheet with hundreds or even thousands of cells containing data and now, you have to highlight the blank cells in it? This is a pretty common scenario when you work with Excel. Now, if you like pain, you can always try to find and highlight blank cells one by one.

But it is not the way to do it when you would like to work efficiently and have a huge dataset in your hand. So, you need to find a way to do it within seconds instead of spending hours doing this task. This is where we come in to help you take your game to the next level.

In this quick guide, you will find the best 4 methods you can follow to highlight the blank cells in Excel. Every method has some tips and tricks to make your work easier. They have been discussed as well.

Work smart, not hard. Find out your best method and add it to your arsenal right now!

How to Highlight Blank Cells in Excel: 4 Painless Methods

Now, when you are looking for an excellent way to highlight the blank cells in Excel, you are looking for an option that will deliver the intended results without much hassle. And you can pick what works best for you in a given situation when you are familiar with multiple methods.

Depending on what you are required to do and how big of a dataset you are working with, one method may work better than the other methods. This is the reason behind discussing four different methods in this article. Be sure to go through all of them and choose your preferred one.

Let’s explore the methods then!

Method 1: How to Highlight Blank Cells in Excel with Go To Special

This method utilizes the Go To Special option in Excel and selects the blank cells in your dataset. You will then need to highlight those selections with your preferred color.

It works in 5 easy steps.

1. Select your data by clicking and holding your mouse button and dragging it over the cells. Alternatively, you can use the shortcut CTRL + SHIFT + END to select all the cells that contain data. To do it even more specifically with rows and columns, press CTRL + SHIFT + ↓Down Arrow to select the vertical contents and CTRL + SHIFT + →Right Arrow (selecting from the left to right) to select the horizontal contents.

2. In the Editing group to the right and under the Home ribbon, click on Find & Select. It will open a drop-down menu. From there, click on Go To Special…

Method 1 - Go To Special 1

Alternatively, you can hit the F5 button on your keyboard and bring up the Go To box. Click on the Special button to open the selection box.

Method 1 - Go To Special 2

3. Now, click on the Blanks radio button, and then click the OK button.

Method 1 - Go To Special 3

4. This will select all the blank cells in your dataset.

Method 1 - Go To Special 4

5. Now, in the Font group under the Home ribbon, click on the Fill Color button. Choose the color you want to highlight your selected blank cells.

Method 1 - Go To Special 5

6. And there you have it! The blank cells are now highlighted with the color you have chosen.

Method 1 - Go To Special 6

Pros:

  • This method works best as a quick solution for one time.
  • It is not heavy on the system and will not slow down your computer.

Cons:

  • This is not a dynamic method. The blank cells will stay highlighted even after you put values in them. You will have to manually remove the highlight in this case. See the picture below.

Method 1 - Go To Special 7

  • This method works only on Blank cells that are actually empty. It will not select apparent blank cells with spaces, empty strings, etc. There are other methods discussed next that handle blank cells in a better way, like highlighting blank cells with Conditional Formatting.

Method 2: Highlighting Blank Cells in Excel with Conditional Formatting Using len Function

Using the length function len to find out and highlight the blank cells in an Excel dataset has been a popular method. It takes not more than five seconds to highlight the blank cells. Follow the steps below.

1. In this first step, make a selection of the area where you want to find out the blank cells and highlight them. Use the technique shown in the first step of Method 1. Make sure to begin selecting from the top left corner of your dataset, and go right and downward from there.

2. Click on the Conditional Formatting in the Styles group under the Home ribbon. Click on New Rule.

Conditional Formatting with len 1

3. A New Formatting Rule window will pop up. Select Use a formula to determine which cells to format. Then start writing down the formula in the Format values where this formula is true: box.

Conditional Formatting with len 2

4. Write =len( in the box and then click on the top-left cell in your selection. Your formula will look =len($B$2 at this point. The top-left cell that has been selected is an Absolute Reference right now. You need to turn it into a Relative Reference so that all the rows and columns you have selected in your dataset work with the formula. To do this, press the F4 button three times right after clicking the cell. It will change $B$2 to B2 .

Conditional Formatting with len 3

5. Your formula should look like =len(B2 right now. Close the parentheses to make it =len(B2) and then put =0 followed by those closed parentheses.

Conditional Formatting with len 4

After writing the whole formula =len(B2)=0 , click on the Format button.

6. Go to the Fill tab and pick a color to highlight your blank cells. Then click OK.

Conditional Formatting with len 5

7. You will be taken back to the previous window. It should look like the picture below. Click the OK button to complete the process.

Conditional Formatting with len 6

8. There you go! Your blank cells are now highlighted in your chosen color.

Conditional Formatting with len 7

Pros:

  • This is a dynamic method. It means that if you put values in a highlighted cell, the highlight will vanish for that cell. Similarly, deleting the value of a cell will get it highlighted automatically.
  • It is also a quick method to highlight the blank cells.
  • This method works with the length of the string. So it finds the absolute blank cells as the formula commands.
  • It works even if the cells have Empty strings in them.

Cons:

  • A cell containing space appears blank. But this method does not detect and highlight those cells.

Method 3: Conditional Formatting with Blanks Formatting Rule

This method is your friend when you want magic to happen right in front of your eyes. Conditional Formatting helps you with highlighting blank cells in many ways. We think that learning one technique to complete many tasks is one that is worth learning, and this is that one method. In the case that there is a need for something specific, we are covering those methods too.

For now, let’s focus on the King of highlighting blank cells. Do not forget to go through the Pros of this method to understand furthermore how powerful it is!

1. Select your cells by using your favorite method.

2. In the Styles group, click on Conditional Formatting first. When the menu opens up, click on New Rule…

Conditional Formatting with Blanks Formatting Rule 1

3. A mini New Formatting Rule window will open. Click on Format only cells that contain from the box. Under Edit the Rule Description, click on the “Cell Value” to open the drop-down menu and choose Blanks. Next, click on the Format button.

Conditional Formatting with Blanks Formatting Rule 2

4. A new window will pop open. Go to the Fill tab and then choose the background color you wish for your highlighted blank cells. Finally, click okay.

Conditional Formatting with Blanks Formatting Rule 3

5. You will return to the New Formatting Rule window. Click on the OK button.

Conditional Formatting with Blanks Formatting Rule 4

6. And voila! All the blank cells under the selection are now highlighted.

Conditional Formatting with Blanks Formatting Rule 5

7. You can even change the values in highlighted blank cells. The highlights will automatically get removed after putting values in them.

Conditional Formatting with Blanks Formatting Rule 6

Special Case: Highlight Blank Cells in a Row or Column with Conditional Formatting

Understandably, you may need to highlight blank cells in a specific row or column. Using the same technique utilizing the Conditional Formatting method shown above, you can do your task.

1. Select the row or column you want to find and highlight your blank cells.

2. Follow steps 2-5 shown in the method discussed above.

3. See the results for yourself!

Conditional Formatting with Blanks Formatting Rule 7

Highlighted blank cells in a row by using Conditional Formatting

Conditional Formatting with Blanks Formatting Rule 8

Highlighted blank cells in a column by using Conditional Formatting

Pros:

  • This method works dynamically. You can change the value of a blank cell and it will automatically get rid of the highlight. Deleting the value of a cell will also highlight it instantly.
  • Even if the apparent blank cells have Empty strings or Zero-length strings in them, this method still works to highlight the blanks.
  • This is one-method-works-in-many. In most cases, you will not be required to do anything else after following this method.

Cons:

  • This method is resource-hungry. Your computer may become slow when applying this technique.
  • Every single time you change a value in a cell, Excel will go through the whole dataset to check all the changes that have been made. For a few moments, Excel may become unresponsive. Don’t close the window if that happens, and wait a bit for it to work its magic.

Method 4: Highlighting with Visual Basic Editor (VBA)

If you can write a few lines of code to highlight your desired blank cells in a huge dataset, why not go for it? It also takes only a few seconds to do it. Although this requires the user to be almost on an advanced level, copying and pasting the code into the Visual Basic Editor for this Visual Basic Application (VBA) method is only a matter of a few seconds. Let’s begin then.

1. Make a selection of the cells. Follow the procedure shown in Method 1 Step 1.

2. Open the Visual Basic Editor from the Code group under the Developer ribbon. Alternatively, you can press ALT + F11 to open it instantly. If you can’t find the Developer ribbon in your Excel, the process to show it in your Excel is discussed right after this method.

Highlighting with Visual Basic Editor 1

3. Open the Insert drop-down list from the menu bar. Click on Module.

Highlighting with Visual Basic Editor 3

4. The Module window will pop up. Copy the code given below and paste it into the Module code editor.

Sub HighlightBlankCells()
Dim dtst As Range
Set dtst = Selection
dtst.SpecialCells(xlCellTypeBlanks).Interior.Color = vbGreen
End Sub

It now should look like the picture below.

Highlighting with Visual Basic Editor 4

5. Click on the Run button marked (2) in the picture above. Alternatively, you can press F5 to run it. Then close the windows.

6. There you have it! Your blank cells are highlighted.

Highlighting with Visual Basic Editor 2

Pros:

  • This method works best when you have an enormous dataset to work with. It works fast and smoothly.

Cons:

  • This method does not do the dynamic highlighting. Like Method 1, you will have to manually remove the color when you put value in a cell.

How to Show the Developer Ribbon

  • Go to the File menu in your Excel. Click on Options.

How to Show the Developer Ribbon 1

  • Click on the Customize Ribbon (1). In the box at the most right, tick the Developer (2) option. Click the OK button (3) to complete the process.

How to Show the Developer Ribbon 2

Final Thoughts

When you work with a huge amount of data in your everyday work with Excel, you have to be fast and efficient in what you do. Choosing the best method for anything you do in Excel is thus important for yourself.

You may try every method shown here and find out what is right for you. From our experience, we suggest using Method 3 for the best outcome. However, if your dataset is not big enough and fits within a page, there must not be too many blank cells to work with. In that case, going with Method 1 could be a good choice.

May the spirit of Excel be with you!

Leave a Comment