Whenever we receive data from someone or an external source, it is important to check the data for accuracy.
One common challenge many of us face is dealing with blank cells. Users may keep blank cells to add information later. However, this can create confusion and potential errors.
In a small dataset, the task of identifying blank cells may seem minor, but as the dataset grows in size, so does the challenge!
Thus, in this article, we will explore various methods for highlighting blank cells in Excel. By doing so, you can reduce the risk of data-related mishaps by visually identifying them.
Method 1: Highlighting blank cells using ‘Go To Special’ method
Go To command is used to quickly find and select all cells containing specific types of data. In this case, we will use it for highlighting blanks.
We are going to use the below-shown data as a reference to learn the techniques.
Here are the steps to follow:
Step 1: Select the range of cells where you want to highlight blank cells
Step 2: Under the Home tab. Click on ‘Find and Select. Click on the drop-down option
Step 3: Select ‘Go to Special’ from the drop-down as highlighted
Step 4: Select ‘Blanks from the options and click ‘OK’ as shown in the screenshot below
Step 5: As an output, all the blank cells will be greyed
Step 6: You can choose any colour of your liking or as per the organisation’s policy
Things to Note while using the ‘Go to Special’ option
- The Go To Special feature is safe to use as it does not change data, only allows selection
- It is not dynamic, any changes made to the data require manual formatting of that cell. Eg: If the data is added in place of Blank, you will need to change the colour for that cell manually.
Method 2: Highlighting blank cells using ‘Sort and Filter’ method
Like the previous method, The FILTER function allows you to filter a range of data based on criteria you define. However, before we apply the filter, the function needs to be applied to the data. Let’s see how it can be done in a step-by-step method.
Step 1: Select all the cells in the table as shown below
Step 2: From the ‘Home’ tab, select ‘Data’ and click on the ‘Filter’ option under ‘Data’. Refer to the screenshot for guidance.
Step 3: With that selection, an inverted triangle will appear on the header of each column. They are called ‘Filter’.
Step 4: Click on the Filter or the inverted triangle and it will open the list of drop-down numbers under column C.
Step 5: By default, all numbers and blanks are selected. Click on the tick against ‘Select All’
Step 6: Now manually check the box against ‘Blanks’ as shown in the screenshot below. Click ‘OK’ once you’ve made the selection.
Step 7: All the blanks in column C will be automatically selected
Step 8: You can colour the blanks so it is easy to identify it in the future. Please note this is an optional step.
You can colour them as per your choice or as per the organisation’s policy.
Add-On Method: As an additional step, we can combine all consecutive blank rows for the ease of identification and filling of the blanks in the future.
For this, we will use the ‘SORT’ function. The SORT function sorts the contents of a range based on the command.
Step 1: Select the entire table where we want to apply the SORT function.
Step 2: From the ‘Home’ tab in the Excel Ribbon select ‘Data’ and click on the ‘Sort’ feature. Refer to the screenshot below.
Step 3: Once we click the ‘SORT’ button, a new window will appear, asking us which column we want to use to sort the data. In this case, since the blank cells are in column C, which is labelled as ‘Contact’, we will choose ‘Contact’ from the drop-down menu under the ‘Sort By’ option.
Click ‘OK’ to confirm the selection.
Step 4: The data will be sorted based on column C. To check if the data is sorted, look for an arrow near the filter (encircled). Now, all blank cells are sorted together.
Things to Note while using the ‘Sort and Filter’ option
Many times, cells appear blank but are not really blank. It happens in cases where a cell contains a formula and returns empty text – this is denoted by double-double quotes “” in formulas.
or cells that contain a space character.
In such cases, the cells appear blank but are not really blank.
The advantage of using the method of ‘Sort and Filter’ is that it can identify the cells that are blank and also ‘appear’ blank.
Not to mention, this is the most simple, easy and quick method to differentiate blanks.
It is important to exercise caution when using this feature. Make sure to check that the data in all other columns are also rearranged or sorted accordingly.
In the last picture, for example, if you check column A, you will notice that the serial numbers have been shifted according to the sorting of column C.
Method 3: Highlighting blank cells using ‘Conditional Formatting’ method
Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify. This changes the appearance of a cell range based on a condition (or criteria). In this case, we will use it to highlight blanks.
Step 1: Select the column that contains ‘Blanks’. In the below data set, we are selecting, Column ‘C’.
Step 2: From the ‘Home’ tab, select ‘Conditional Formatting’.
Step 3: A list of options will appear in the drop-down. Select the first option ‘Highlight Cell Rule’. This will further open into array of options as shown in the screenshot below.
Step 4: Select ‘More Rule’
Step 5: With that selection, a new window will appear. Select the second option from that window ‘Format only cells that contain’
Step 6: Go to the ‘Edit the Rule Description’ and select ‘Blanks’ from the drop-down under the header Cell value.
Step 7: Click on ‘Format’ post that.
Step 8: A new window will appear. Although the default option will be ‘Fill’. If not, manually click on the Fill tab.
- Select any colour of your choice or as per the organisation’s policy.
- Click on ‘OK’ to confirm the choice.
Step 9: The previous window will appear. Click ‘OK’ to confirm the choice once again.
The cells with Blanks will be filled with the desired colour in the column ‘C’
Things to Note while using Conditional Formatting
- It will automatically change the data whenever there is a new entry in Excel which means, that any data if is added in a blank cell, will no longer be highlighted.
- Conditional Formatting reads entire data with every change. This may work in the smaller set of data, however, can potentially slow down the system in case of large data sets.
- Conditional formatting is relatively easy to set up, and you can quickly apply it to a specific range or column
Method 4: Highlight blank cells using ‘VBA’ method
However, if you don’t know how to use VBA or write code, Don’t Fret!
Using the same data set as before, we will now explore the VBA option. VBA is useful in the case of a large dataset and/ or repetitive task of highlighting blank cells.
Simply follow the step and copy the code mentioned here- and it’s done.
Step 1: From the ‘Home’ ribbon, go to ‘Developer’ and select ‘Visual Basic’. Refer to the screenshot below
Step 2: Selection of the Visual Basic function will open another window. Click on ‘Insert’ and select ‘Module’ from the options provided.
Refer to the screenshot below
Step 3: This selection will open the VBA editor.
You will now just need to paste the below code into the module.
Sub SelectBlankCells() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as needed ws.UsedRange.SpecialCells(xlCellTypeBlanks).Select End Sub
Take a look at the example shown below:
Please note: 1) You will need to update the ‘Sheet number’ in the code (encircled). In this example, it is updated to sheet no. 5- screenshot for reference.
2) Delete the sentence ‘Change the sheet as needed’ upon updating the sheet number.
This is how the final code looks like
Step 3: Run the VBA code by pressing F5 OR click ‘Run’ Or click ‘play’ icon
And close the window.
Step 4: All the blanks will be highlighted
Step 5: You can colour the blanks so it is easy to identify them in the future. Please note this is an optional step. Choose any colour of your choice or as per the organization’s policy
The blanks will be highlighted in the chosen colour.
So, now you know that VBA can automate these repetitive tasks for you? It’s really powerful when you’re dealing with empty cells with huge datasets.
And you do not need to know coding or VBA scripting, just copy the code and follow the steps mentioned above.
We learned numerous methods to tackle the challenge of blank cells in a spreadsheet in this article. Choosing the right method depends on your specific needs and familiarity with Excel’s functions.