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.

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.

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.

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.

'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.

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.

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.

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)

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

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

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.

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.

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

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.