How to Quickly Delete All Blank Rows in Microsoft Excel

When you import data into Microsoft Excel from an external source—such as a PDF extraction tool or a poorly formatted CSV file from a legacy database—you will often find your dataset plagued by hundreds of entirely blank rows scattered randomly throughout the text. Manually right-clicking and deleting these blank rows one by one is an agonizing waste of time, especially if your spreadsheet contains thousands of lines of data. Fortunately, Excel includes a powerful “Go To Special” feature that can instantly identify every blank cell in your spreadsheet simultaneously, allowing you to delete them all with a single click.

Selecting Your Data Range

Before you run the tool, you must highlight the specific columns that contain your data. If you simply select the entire worksheet (by clicking the triangle in the top-left corner), Excel will select over a million empty rows at the bottom of the sheet, which will crash the program when it attempts to delete them.

1. Click and drag your mouse to highlight only the columns that actually contain your data (for example, click the letter ‘A’ and drag across to ‘E’).
2. Alternatively, click on cell A1, hold down the Shift key, and use the arrow keys to highlight the specific block of data.

Using the “Go To Special” Tool

Now that your data is highlighted, you need to tell Excel to isolate only the blank cells within that specific selection.

1. Ensure you are on the Home tab of the top ribbon.
2. Look to the far right side of the ribbon for the “Editing” group and click the Find & Select button (it has a magnifying glass icon).
3. Click on Go To Special… from the dropdown menu.
4. A small dialogue box will appear with various radio buttons. Select the button labelled Blanks.
5. Click OK.

You will notice that the solid block of highlighting has disappeared. Instead, only the completely empty cells within your original selection are now highlighted in grey. Every cell containing data has been ignored.

Deleting the Blank Rows

With all the blank cells actively selected by Excel, you can now issue the command to delete the rows they belong to.

1. Do not click anywhere on the spreadsheet, as this will clear your carefully selected blank cells.
2. On the Home tab, look for the “Cells” group near the right side.
3. Click the small downward arrow directly beneath the Delete button.
4. Select Delete Sheet Rows from the dropdown menu.

Excel will instantly delete every row that contained one of those highlighted blank cells, seamlessly shifting all your legitimate data upward into a clean, contiguous block.

Warning: This method assumes that the rows you want to delete are entirely blank across all columns. If you have a row where Column A has data, but Column B is blank, this method will delete the entire row, destroying the data in Column A. If your data has legitimate blank gaps, you should use the standard Filter tool to filter for blanks on a specific column instead.