How to Fill Blank Cells with Color in Excel (5 Methods)

Spotting blank data or empty strings in a dataset is a very important step in any data analytical procedure. This information can be used to fill them out later with suitable data or remove particular rows or columns while cleaning the data for analysis. As a data analysis tool, Excel provides several methods to identify and format blank cells. For a small dataset, the blank cells may be easily identifiable. But for a large dataset, these automated procedures can be very helpful. Either way, this article will show you how to fill blank cells in Excel with color in 5 different methods.

How to Fill Blank Cells with Color in Excel: 5 Easy Ways

I will be going over five different methods on how you can fill blank cells with color in Excel. In the long run, these are just processes to find the blank cells of a dataset. Then fill them in with color using the formatting feature. For that, you can use some built-in functions in Excel or VBA codes to do all the work for you.

For demonstration, I will be using the following dataset.

As you can see from the figure, there are several blank cells in the dataset. I will be using different methods in each section to fill these blank cells with color in Excel.

1. Using Go to Special Command to Fill Blank Cells with Color

In Excel, you can use this method to find the blank cells using the Go to Special feature. And then format them by filling them with colors. Thus you can fill blank cells with color in Excel. Let’s follow the instructions below by using the Go To Special command to fill blank cells with a color.

Steps:

First, select the dataset with blank cells in it. If it is too big you can select a cell in it and press ‘Ctrl+A’ to select it easily.

Then go to the Home tab from your ribbon.
After that click on Find & Select from the Editing group.
Then from the drop-down menu, select Go to Special.

Consequently, the Go to Special box will appear. Select Blanks in it.

Then click on OK.
Owing to this all the blank values now will be selected automatically.

Now to fill up the blank cells with color go to the Home tab in your ribbon.
Then select the Fill option from the Font group.

You can explore and select the fill color from here. Select the fill color you prefer.

Once you are done with all the steps described, you will have the blank cells filled with colors.

This is one way you can fill blank cells in Excel with color.

2. Fill Blank Cells with Color Using Conditional Formatting in Excel

You need to find out and fill the blank cells with colors in Excel manually with the above method. However, there is another feature called Conditional Formatting to do both at the same time. This feature is useful not only to fill blank cells but also for cells with specific values or conditions too. Then again, this method can also be used to format certain cells in any way, not just filling. So, if we use the condition as blank cells and select the format as fill we can use this feature to fill blank cells in Excel with color.

To know more about how these steps work, follow these steps.

Steps:

First, select the dataset. If it is too big you can select a cell in it and press ‘Ctrl+A’ to select it easily.

Now, go to the Home tab from your ribbon.
Then from the Styles group, click on Conditional Formatting.
After that, select New Rule from the drop-down menu.

Next, a formatting rule box will open up named New Formatting Rule. In the Select a Rule Type field, select Format only cells that contain.
Eventually, select Blanks in the Format only cells with.
Then to fill up the cells, click on Format.

After that, in the Format Cells box, go to the Fill tab and select the color you want to fill it up with.

Finally, click on OK on both Format Cells and the New Formatting Rule. You will have your blank cells filled with color.

3. Fill Blank Cells with Color Using Formula

Besides using the blank cells as conditions in the Conditional Formatting feature, you can also use formulas as conditions to find the blank cells in a dataset. For formulas, you can use ISBLANK and LEN functions or simply use a reference blank cell to find the blank cells in a dataset. You can then follow the rest of the format procedure to fill in the blank cells with colors in Excel in a similar manner.

The ISBLANK function takes an argument, generally a cell, and returns a Boolean value of whether the value in it is blank or not. Then there is the LEN function which takes an argument to return the length of the string value in it. I am using the ISBLANK function for demonstration. You can use either one to achieve the same result. Follow these steps to use the formula for filling blank cells with color.

Steps:

As usual, select the dataset first. If the dataset is large, then select a cell in it and press ‘Ctrl+A’ on your keyboard.

After that go to the Home tab in your ribbon.
Then select Conditional Formatting from the Styles section.
Now, select New Rule from the drop-down menu.

Then in the New Formatting Rule box that popped up, select Use a formula to determine which cells to format in the Select a Rule Type field.
After that, in the Format values where this formula is true field, write down the following formula.

=ISBLANK(B4)

Or you can add the following formula instead of the upper one.

=LEN(B4)=0

Then click on Format.

Consequently, a Format Cells box will open up. Now, select the desired color you want from the Fill tab.

Finally, click on OK in both the Format Cells and New Formatting Rule box to save the changes. Your blank cells will be filled with colors.

These were the ways you can use the Conditional Formatting feature to fill blank cells in Excel with color.

4. Embedding VBA to Fill Blank Cells with Color in Excel

Until now the methods I have described use inbuilt Excel features and functions. Then again, you can use the helpful Microsoft Visual Basic for Applications (VBA) to use codes to fill bland cells in Excel with color easily. Henceforth I am going to show you how you can automate such a process with VBA. This section focuses on using VBA to fill blank cells only in Excel with color.

Of course, you need the Developer tab to show on your ribbon to use VBA codes in your spreadsheets. Once you have that shown, you can follow these steps to automatically fill blank cells with color in Excel.

Steps:

First, go to the Developer tab and select Visual Basic from the Code group.

As a result, the VBA window will open up. Select Insert and then Module from the drop-down menu in this new window.

To fill out blank cells, write down this simple one-line code or copy it from below.

Sub Fill_Blank_Cells()
Selection.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 4
End Sub

Here, I have selected the color index as 4. You can replace it with your own. Also, you can swap the ColorIndex portion of the code with Color and then write a color code of your choice on the right.

Then save and close the VBA window.
Now, to run the code to fill blank cells with color, select the dataset first.

Then go to the Developer tab and select Macros from the Code group.

After that, in the Macro box, select the code name that was at the first line of your code Fill_Blank_Cells in the Macro name field.
Then click on Run.

Eventually, this will fill blank cells in Excel with color.

5. Using VBA to Fill Blank but Not Empty Cells with Color

If the blank cells are seemingly blank but contain white spaces, you can fill them out with colors using Excel VBA codes too. In that case, you need an if-else loop in VBA to find out white spaces and use syntax to fill them out with your desired color.

Like the previous method, you need the Developer tab shown in your ribbon too. Once you have that, follow the steps below.

Steps:

First, go to the Developer tab in your ribbon and select Visual Basic from the code group.

Now a VBA window will pop up. Select Insert in it. And then select Module from the drop-down list.

Now in the module, write down the following code.

Sub Fill_Blank_Cells_2()

  Dim rnge As Range
  Set rnge = Selection

  For Each cell In rnge
    If cell.Text = "" Then
       cell.Interior.ColorIndex = 20
    Else
       cell.Interior.ColorIndex = xlNone
    End If
  Next

End Sub

After that save and close the VBA window.
Now select the dataset containing the blank cells in between.

After that, go to the Developer tab and select Macros from the Code group.

Now, select the code you have just created (the name was in the first line of the code) with the name Fill_Blank_Cells_2 in the Macro name.
Then click on Run.

As a result, you will have the blank cells and all the cells with white space filled with color using this VBA code.

Things to Remember

Select the whole dataset before running the steps, no matter the method you are using. Otherwise, it might fill blank cells outside of the dataset too.
The name of a VBA macro is the word following Sub and ends before the parentheses. Make sure to check the name before running the macros as these processes are irreversible. To be safe, always create a backup of the dataset first.

Download Practice Workbook

Download the workbook with the dataset and all the different methods contained in different spreadsheets below. The VBA codes are included in the workbook. Try to go through the workbook as you go along with the steps.

Fill Blank Cells with Color.xlsm

Conclusion

These were all the methods you can use to fill blank cells in Excel with color. Hope you have found this helpful and informative. If you have any questions or suggestions, let us know below.

<< Go Back to Fill Blank Cells | Blank Cells in Excel | Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Fill Blank Cells with Color in Excel (5 Methods)

How to Fill Blank Cells with Color in Excel: 5 Easy Ways

1. Using Go to Special Command to Fill Blank Cells with Color

2. Fill Blank Cells with Color Using Conditional Formatting in Excel

3. Fill Blank Cells with Color Using Formula

4. Embedding VBA to Fill Blank Cells with Color in Excel

5. Using VBA to Fill Blank but Not Empty Cells with Color

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply