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 in Excel, but this easy technique has the potential to destroy data, so you must be careful.
SEE: Explore these Excel tips every user should master.
In this Excel tutorial, I’ll show you a quick and easy way to delete blank cells, which you might think of as rows, in the middle of a dataset without deleting adjacent data accidentally. Throughout this Excel article, I use the term “row” to describe adjacent blank cells within the dataset.
I’m using Microsoft 365 on a Windows 11 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.
Jump to:
How to select blank cells in Excel
Once you realize that you have blank rows (cells) in your dataset, you’ll probably 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.
SEE: Learn how to create a drop-down list in Excel.
To select all the blank rows in a dataset, do the following:
1. Select the data range A3:E14 — or your own data range if you’re using a different dataset.
2. Press F5.
3. In the resulting Go To dialog box, click Special.
4. Click the Blanks option, and then click OK. Doing so selects the blank cells in the selected range. In this case, that’s A7:E7 and A10:E10 (Figure A).
Figure A
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.
SEE: Learn how to enter leading zeros in Excel.
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 actual rows will delete everything in that entire row, not only the selected range, and that includes data that might be off-screen.
To delete the selected cells — not rows — 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 B).
Figure B
3. Click OK.
Excel will delete the blank cells from the selected data range (Figure C).
Figure C
SEE: Learn how to parse time values in Microsoft Excel.
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 only the blank cells. It’s easy to think in terms of rows and choose the wrong option.
In such a small dataset, 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.
SEE: Here are some other ways to delete blank rows in Excel.
How to avoid disaster when deleting rows in Excel
Deleting rows, whether blank or not, can be hazardous. For instance, if you have data off screen and out of sight, you won’t realize you’re deleting it when you delete the empty cells or rows right in front of you.
In addition, many built-in features don’t work as expected when encountering blank rows. Let’s suppose you want to convert the example dataset to a Table object, so you click inside the dataset and press Ctrl + T. It just so happens that you click D5, expecting Excel to select the full dataset. However, it won’t. Excel selects the contiguous rows adjacent to the clicked cell, D5, which happens to be A3:E6 — a partial selection, as shown in Figure D.
Figure D
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 when trying to use Table features. You must delete blank rows to use many of Excel’s built-in features.
