Excel tutorials

Removing or deleting multiple non-adjacent blank cells in a given range of data

In this Excel tutorial, I will show how you can remove or delete blank cells in some range of Excel data. This step-by-step tutorial describes how to remove blank cells, and with each step you have a screenshot that belongs to the description and information.

Look at the range of data in Excel

In the figure below, note the status and arrangement of the data in the 'A' column. Notice that we have five empty cells. We want to remove these cells from the data range. Once removed in the data range, we only need cells that are not empty.

View blank cells in the data range in Excel

 

 

Selection of data range in 'A' column

I believe you know all the options for how to select a specific range of data. Either way, move your mouse over the A1:A15 range. Now all the cells are highlighted / selected.

Selecting a Data Range in Excel

Running the "Go To Special" command

The next step is to run the "Go To Special" command to open the dialog box. In the 'HOME' tab of the 'Editing' group, click on the 'Find & Select' command and then the 'Go To Special' command.

The basic command to open the GoToSpecial dialog

If you want the previous action you can also do it via shortcuts. So you can use the "F5" key on your keyboard or the "CTRL + G" key combination. After opening the "Go To" dialog box, click on the "Special" button.

Starting the Go To Special dialog box using shortcuts

'Go To Special' dialog box in Excel

In the next step, you have the "Go To Special" dialog open. In this dialog, click on the "Blanks" Radio button and then click on the OK button.

Selection of Blank Cells Selection Option

Selected cells in the data range

Now Excel has selected all the blank / empty cells in the previously selected data range. Note that cells A1, A4, A8, A11, A12 and A14 are selected.

Selected non adjacent cells in the data range

Deleting or Removing Selected Cells

The next step is to run the command to delete the selected cells. Right-click on the first cell of the selected cells. Select the "Delete" command from the drop-down menu.

Delete command via Excel popup menu

 

The 'Delete' dialog box opens and the option to delete cells is active so that other cells in the same column are moved up. Of course it suits us, so we'll confirm by clicking the OK button. (for other options, experiment on your own)

Choosing to move the remaining cells after deleting blank cells in Excel

Layout of the data range after deleting non-adjacent blank cells

Finally, notice how the data range looks after blank cells are removed.

Layout of data after deleting blank cells

How to solve a problem if blank cells are the result of a formula?

In the situation where you have blank cells that result from the formula, the above steps will not work. In this case, we can use a different approach, to get empty cells in the 'A' column.

Consider the following situation. The 'A' column contains the formula below
=IF(G2="";"";G2)

We have a situation like the one below where the empty cells are the result of a formula. In this case the 'Go To Special' command, will not help us.

If the cells are the result of a formula, like blank then select them

In the example above, being able to use the 'Go To Special' command to select empty cells will not help us either
Copy -> Paste Special -> Values, to another column. The result of the 'Go To Special' command in case the blank cell is the result of the formula looks like the following.

The cells you were looking for were not found

How to select the blank cells that result from the formula?

To select all the blank cells that result from the formula, do the following.

  1. Select the range of cells (shown below A2:A15)
  2. Click on the 'HOME' tab on the 'Find & Select' command -> 'Find'
  3. Leave blank in the dialog box 'Find what:'
  4. In the 'Look in:' dialog box, select 'Values'
  5. Click on the 'Find All' button
  6. Select all the items in the bottom of the window dijaliog "Find and Replace"
  7. Notice that only blanks are selected in the 'A' column.
  8. Click on the 'Close' button

Blank cells selected if they result from a formula

Now you have the situation that all cells that are blank are selected even though those cells contain a formula.

Press the "Delete" (DEL) key on your keyboard. You have now deleted formulas in the data range and all the cells in question are empty

You can now use the 'Go To Special' command on cell range 'A2:A15' to remove empty cells from the data range.