A quick way to delete or remove blank rows in Excel

Deleting blank rows in an Excel data range is easy with this technique but watch out for unintended consequences.

In most spreadsheets, blank rows are undesirable. Microsoft Excel uses blanks to determine data ranges, and a blank row in the wrong place will inhibit many built-in features. Fortunately, there’s an easy way to remove blank rows from a data range, but this easy technique has the potential to destroy data, so you must be careful.

In this tutorial, I’ll show you a quick and easy way to delete blank rows in the middle of a data set without deleting adjacent data accidentally. Throughout this article, I use the term row to describe adjacent blank cells within the data set.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. Excel for the web supports this easy technique.

You can download the Microsoft Excel demo file for this tutorial.

How to avoid what could happen

Deleting rows, whether blank or not, can be hazardous. If you have data off screen and out of sight, you won’t realize that you’re deleting it when you delete the empty cells or rows right in front of you. Let’s look at a quick example using the simple data set in Figure A.

Figure A

Let’s suppose you want to convert the data set to a Table object, so you click inside the data set and press Ctrl + T. If you’re fortunate, you’ll notice that Excel doesn’t specify the entire data range. If you fail to notice, you won’t get the results you expect. You must delete blank rows to use many of Excel’s built-in features.

How to select blank rows in Excel

Once you realize that you have blank rows in your data set, you will want to delete them. The first step is to select the blank rows, which is a tedious process if done manually. Fortunately, you don’t have to.

To select all the blank rows in a data set, do the following:

1. Select the data range, A3:E14.

2. Press F5.

3. In the resulting Go To dialog box, click Special.

4. Click the Blanks option and click OK. Doing so selects the blank cells in the selected range. In this case, that’s A7:E7 and A10:E10 (Figure B).

Figure B

5. Click OK.

Excel’s Go To feature lets you quickly select all the blank rows in the selection. Now you’re ready to delete those rows. Don’t do anything to deselect the selection.

How to delete blank rows in Excel

Now you’re ready to delete the selected cells. Until now, I’ve used the term rows, but deleting the rows will delete everything in that row, including data that might be off screen.

To delete the selected cells, click Delete (not the dropdown) in the Cells group on the Home tab. This should delete only the selected cells, but if you don’t get the desired results, press Ctrl + Z to undo the delete and then try the following:

1. With the blank rows 7 and 10 still selected, click the Delete dropdown in the Cells group on the Home tab and then choose Delete Cells.

2. In the next dialog, click Delete Cells Up (Figure C).

Figure C

3. Click OK.

Excel will delete the blank cells from the selected data range, as shown in Figure D.

Figure D

Choosing Delete Cells deletes only the blank cells in the selected range. If you choose Delete Sheet Rows in step 2, you could potentially destroy data to the right. Choose carefully when using this option to delete blank rows when you really want to delete just the blank cells. It’s easy to think in terms of rows and choose the wrong option.

In such a small data set, you could easily delete the two rows one by one, but imagine doing so if you have dozens or even hundreds of records to review for blank cells. This method will save you time and effort, and it removes the possibility of missing blanks.