How to Delete Blank Rows in Excel in 2 Easy Steps

In most spreadsheets, empty rows are undesirable. Microsoft Excel uses spaces to delimit data ranges, and a misplaced blank row will block many built-in features. Fortunately, Excel has an easy way to remove empty rows from a data range—but this technique can potentially destroy the data, so you should be careful.

In this Excel tutorial, I’ll show you a quick and easy way to delete blank cells, which you can think of as rows in the middle of a dataset, without accidentally deleting adjacent data. Throughout the article, I use the term “row” to describe contiguous empty cells within a dataset.

I use Microsoft 365 on a Windows 11 64-bit system, but you can use earlier versions. Excel for the web supports this simple technique.

Step 1: Select a blank cell in Excel.

Once you realize that there are empty rows (cells) in your dataset, you might want to delete them. The first step is to select empty rows, which is a tedious process if done manually. Fortunately, you don’t have to.

To select all empty rows in a dataset:

1. Select the data range A3:E14 — or your own data range if you’re using a different data set.
2. Press F5.
3. In the resulting Go To dialog box, click Special.
4. Click the Empty option, and then click OK. Doing so selects the empty cells in the selected range. In this case, that is A7:E7 and A10:E10.

Excel’s GoTo feature lets you quickly select all blank rows in a selection. Now, you are ready to delete these rows. Do nothing to deselect the selection.

See: Explore them. Excel tips every user should master..

Step 2: Delete blank rows in Excel.

Now, you are ready to delete the selected cells. So far, I’ve used the term rows, but deleting the actual rows will delete everything in that entire row, not just the selected range. This includes data that may be off screen.

To delete selected sales—not rows—click Delete (not the dropdown) in the Sales group on the Home tab. This should only delete the selected cells. But if you don’t get the results you want, press Ctrl + Z to undo the deletion, and then try the following:

1. With empty rows 7 and 10 still selected, click the Delete drop-down in the Sales group on the Home tab, and then select Delete Sales.
2. In the next dialog, click Shift Sales Up.

3. Click OK.

Excel will then delete the empty cells from the selected data range.

See: Learn how to do it Parse time values ​​in Microsoft Excel..

The Delete Cells option removes only the empty cells in the selected range. If you choose to delete the sheet rows in step two, you could potentially destroy the data on the right. Choose carefully when using this option to delete empty rows when you really only want to delete empty cells. It’s easy to think of rows and choose the wrong option.

In such a small dataset, you can easily delete each row individually, but imagine doing that if you have dozens or hundreds of records to evaluate for empty cells. This method will save you time and effort, eliminating the possibility of missing spaces.

How to Avoid Crashes When Deleting Rows in Excel

Deleting rows, whether empty or not, can be effective. For example, if you have data off-screen and out of sight, you won’t realize you’re deleting it when you move an empty cell or row in front of you. Also, watch out for hidden rows that might be inadvertently deleted.

Also, many built-in features do not work as expected when encountering empty queues. Suppose you want to convert the example dataset to a table object, so you click inside the dataset and press Ctrl + T. It happens that you click D5, expecting Excel to select the entire data set. However, this will not happen. Excel selects the rows adjacent to the clicked cell, D5, which is A3:E6 — a partial selection.

If you’re lucky, you’ll notice that Excel doesn’t define the entire range of data. If you fail to notice, you won’t get the results you expect when you use table properties. You must delete blank rows to use many of Excel’s built-in features.

Can I also use keyboard shortcuts to delete blank rows in Excel?

Fortunately, there is a way to delete empty rows using a combination of keyboard shortcuts. Instead of following the steps above, you can do some work with your keyboard. Here’s how to do it:

1. Use Ctrl + A to select all data to avoid manually highlighting a range containing empty rows.
2. Open the Go To dialog using Ctrl + G, then press Alt + S to select Special.
3. You must select the spaces and press Enter in the Go to Special dialog box.
4. Press Ctrl + – with blank cells highlighted to achieve desired deletion.

Is using filters another good way to delete empty rows in Excel?

Filters are another way to achieve the same goal in Excel. Here’s how to use filters to delete empty rows:

1. Click the Filter drop-down arrow in the column of your dataset where some rows are empty.
2. In the Filter menu, deselect all options by unchecking Select All, then check Only Blanks.
3. Click OK to apply the filter, show only rows with empty cells in this column.
4. Highlight rows by dragging your mouse down or pressing Shift and selecting the rows.
5. Right-click any selected row and select Delete Row, or press Ctrl + 1 and select the entire row.

Be sure to select a column where an empty cell means the entire row is irrelevant. As always, a good rule of thumb is to back up your data by saving a copy first before deleting rows.