Special, Select and Highlight empty cells
This straightforward approach finds all blank cells in a range and allows us to fill them with any colour we choose.
To select the blank cells in Excel, we will do the following:
- Select the blank range you wish to draw attention to. To select all data-filled cells, click the upper-left cell and press Ctrl + Shift + End to extend the selection to the last cell utilised.
- Click Find & Select -> Go to Special on the Home tab in the Editing group. Alternatively, press ‘F5’ and choose ‘Special’.
3. Select ‘Blanks’ in the Go To Special dialogue box and click ‘OK’. This will select all of the range's empty cells.
4. Select the blank cells and click the ‘Fill Color’ icon in the Font group on the Home tab to choose the desired colour.
The Go To Special function selects only fully blank cells, i.e. cells that contain no data. Empty strings, spaces, carriage returns, non-printing characters, and other non-printing characters are not considered blank and are not picked. Use either Conditional Formatting or a VBA code to highlight cells containing formula that yield an empty string (" ”).
Since this technique is static, it should only be used once. Changes you make later will not be reflected automatically: new blanks will not be highlighted, and previously coloured blanks will remain coloured. If you're seeking a dynamic solution, conditional formatting is the way to go.
Fill in the blanks in a specified column
Excel Filter might be the correct answer if you don't care about empty cells elsewhere in the table and instead want to discover and highlight cells or entire rows with blanks in a specific column.
Follow these steps to get it done:
- Select any cell in your dataset and go to the Home tab > Sort & Filter > Filter. To activate auto-filters, use the CTRL + Shift + L shortcut.
- Filter blank values by selecting the target column from the drop-down arrow. Clear the Pick All box first, and then select (Blanks).
- Choose the Fill colour you wish to use for the filtered cells in the critical column or complete rows.
This is how we may select and then highlight the rows in our example database when the SKU (Stock Keeping Unit) cells are empty:
Formulas that yield empty strings ("") are treated as blank cells in this technique, unlike before.
Since we would have to tidy up and emphasise each change, this technique isn't suited for regularly changing data.
How to use Conditional Formatting
Both of the strategies outlined before are simple and concise, but they have one major flaw: neither method reacts to changes in the dataset. On the other hand, conditional formatting is a dynamic approach, which means you only have to put up the rule once. The colour will disappear when any value is entered into an empty cell. Similarly, when a new blank arises, it will be instantly highlighted.
Example: In a range of cells, highlight all blank cells
Configure the Excel conditional formatting rule in the following way to highlight all empty cells in a specific range:
- Choose the range where you'd want to highlight blank cells (A2:E6 in our case).
- Click New Rule > Use a formula to select which cells to format on the Home tab in the Styles group.
- Type one of the following formulae in the Format values when this formula is the correct box, where A2 is the upper-left cell of the specified range:
To draw attention to empty cells:
=ISBLANK(A2)
To draw attention to cells that appear to be blank but contain zero-length strings ("") returned by your formulae, use the following formula:
=LEN(A2)=0
or
=A2=""
4. Click the Format option, go to the Fill tab and select the background colour you desire.
5. To save the rule and exit the main dialogue box, click OK.
Example: Rows with blanks in a specific column are highlighted
To refer to the cell in that specific column, make a slight change to the equations described above. Be sure to lock the column coordinate with the $ sign if you want to highlight the complete rows containing empty cells in a particular column.
Select the whole table without column headings (A2:E6 in our example) and write a rule using one of these formulae to highlight rows with blanks in column B, for example:
To draw attention to entirely blank cells:
=ISBLANK($B2)
To draw attention to blanks and cells with empty strings, use the following formula:
=LEN($B2)=0
or
=$B2=""
As a consequence, only the rows with an empty SKU cell are highlighted:
VBA Scripts
The following VBA scripts to colour empty cells in Excel may be beneficial if you enjoy automating things.
Macro 1: Fill in the blank cells with colour
This macro can assist you in highlighting empty cells.
A single line of code is all that is required to colour all empty cells in a certain range:
Sub Highlight_Blank_Cells()
Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 181, 106)
End Sub
This is the code to use to highlight blanks in a preset worksheet and range (in the example below, range A2:E6 on Sheet 1):
Sub Highlight_Blank_Cells()
Dim rng As Range
Set rng = Sheet1.Range("A2:E6")
rng.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 181, 106)
End Sub
You can use one of the 8 major base colours instead of an RGB colour by entering "vb" before the colour name, for example:
Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = vbBlue
You can also give the colour index, for example:
Selection.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
Macro 2: Fill in the blanks and empty strings with colour
Check whether the Text property of each cell in the specified range = " ", and if TRUE, apply the colour to visually identify blank cells containing formulae that return empty strings as blanks.
To highlight all blanks and empty strings in a range, use the following code:
Sub Highlight_Blanks_Empty_Strings()
Dim rng As Range
Set rng = Selection
For Each cell In rng
If cell.Text = "" Then
cell.Interior.Color = RGB(255, 181, 106)
Else
cell.Interior.ColorIndex = xlNone
End If
Next
End Sub
How to create a macro and execute it
Follow these steps to add a macro to your workbook:
- To open the Visual Basic Editor, press Alt + F11.
- Right-click the target workbook in the Project Explorer on the left, then select Insert > Module.
- Paste the VBA code into the right-hand code window.
This is what you must do to execute the macro:
- In your worksheet, choose a range.
- To access the Macro dialogue, press Alt + F8.
- Select the macro and press the Run button.
FAQs
1. How do we pick all the range’s blank cells?
Select Blanks in the ‘Go To Special’ dialogue box and click ‘OK’. This will pick all of the range's empty cells.
2. What ISBLANK function returns when the cell is blank?
ISBLANK function will return TRUE when the cell is blank.
3. What uses the F5 key on behalf of the blank cell?
After pressing the F5 key, we will get a Go-To Dialog box.
4. What is conditional formatting in excel?
Conditional formatting alters the look of cells in a range based on the conditions you specify. The criteria are rules based on numerical values or language that matches. Visually highlighting relevant data points for study may be done by changing the look of cells.
5. What is VBA in excel?
Visual Basic for Applications is a Microsoft-developed and-owned programming language. You may use VBA to construct macros to automate repetitive word and data processing tasks and create custom forms, graphs, and reports. VBA is a component of Microsoft Office; it is not a standalone product.
Key Takeaways
In this article, we have discussed blank cells. We have discussed how we can find empty cells and how we can use them. We have also discussed four ways to recognise them visually. So basically, when a cell is blank, the ISBLANK function returns TRUE, and when a cell is not empty, it returns FALSE.
We hope that this blog has helped you enhance your knowledge regarding Chart Axes. If you want to learn more, check out our article on ABS Function, AND & OR and Contains Specific Text.
Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses and refer to the mock test and problems available; look at the interview experiences and interview bundle for placement preparations.
Do upvote our blog to help other ninjas grow.
Happy Learning!
519+ registered 
