Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Selecting and highlighting blank cells
3.
Special, Select and Highlight empty cells
4.
Fill in the blanks in a specified column 
5.
How to use Conditional Formatting
6.
VBA Scripts
6.1.
How to create a macro and execute it
7.
FAQs
8.
Key Takeaways
Last Updated: Mar 27, 2024

Blank Cell

Author Aditya kumar
1 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

With the use of conditional formatting and VBA(Visual Basic for Applications), this article demonstrates how to locate and highlight voids in Excel. You can colour just completely blank cells or those that contain zero-length strings, depending on your needs.

It's usually a good idea to double-check the data when you get an Excel file from someone or import it from an external database to ensure there are no gaps or missing data points. With your own eyes, you can readily notice all the voids in a tiny dataset. However, manually finding vacant cells in a large file with hundreds or even thousands of rows is very impossible.

This article will show you how to highlight blank cells in Excel in four different ways so that you can visually recognise them. Which strategy is the most effective? That depends on the data structure, your objectives, and how you define "blanks."

When a cell is empty, the ISBLANK function returns TRUE, and when a cell is not empty, it returns FALSE. For example, ISBLANK(A1) returns FALSE if A1 contains some word, for e.g., "apple". For checking if a cell is empty, we can use the ISBLANK function. ISBLANK accepts only one parameter, value, a cell reference such as A1.

The term "blank" might be deceptive in Excel because a cell containing just space will seem blank but will not be empty. ISBLANK should be considered "is empty" in general because it will return FALSE when a cell seems blank but is not empty.

Selecting and highlighting blank cells

Here are four ways mentioned below for selecting and highlighting the blank cells:

  • With Go To Special, we can select and highlight empty cells.
  • Fill in the blanks in a specified column by filtering and highlighting them.
  • Conditional Formatting in Excel highlights blank cells.
  • In a range, highlight all the empty cells.
  • Empty cell rows in a given column are highlighted.
  • If a field is blank, use VBA to highlight it.
  • To colour the truly blank cells, use a macro (a single operation or a sequence of activities that you may repeat as often as you wish).
  • Use macro to colour blanks and empty strings.
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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:

  1. 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.
  2. 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:

  1. 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.
  2. Filter blank values by selecting the target column from the drop-down arrow. Clear the Pick All box first, and then select (Blanks).
  3. 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:

  1. Choose the range where you'd want to highlight blank cells (A2:E6 in our case).
  2. Click New Rule > Use a formula to select which cells to format on the Home tab in the Styles group.
  3. 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:

  1. To open the Visual Basic Editor, press Alt + F11.
  2. Right-click the target workbook in the Project Explorer on the left, then select Insert > Module.
  3. Paste the VBA code into the right-hand code window.

 

This is what you must do to execute the macro:

  1. In your worksheet, choose a range.
  2. To access the Macro dialogue, press Alt + F8.
  3. 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 FunctionAND & 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!

Previous article
Contains Specific Text
Next article
ABS Function
Live masterclass