How to Automatically Remove Blank Rows in Microsoft Excel

When you import raw data into Microsoft Excel from a web form, a CRM export, or an old database, the resulting spreadsheet is often riddled with completely blank rows. If you are preparing this data for a PivotTable or sorting algorithms, those empty rows will break your formulas and ruin your charts. While you could right-click and delete each empty row manually, this is impossible when dealing with tens of thousands of records. Instead, you can use Excel’s built-in “Go To Special” feature to automatically highlight and delete every blank row simultaneously.

How to Automatically Remove All Blank Rows

Before you begin, it is critical that you select only the specific columns containing your data. Do not select the entire worksheet, or you will accidentally delete rows that are supposed to be empty at the bottom of your sheet.

1. Click on the letter of the first column in your dataset, hold down your mouse button, and drag across to the last column to highlight all your data columns (e.g., Columns A through E).
2. Look at the main ribbon menu at the top of the screen and click on the Home tab.
3. Navigate to the far-right side of the ribbon and click on the Find & Select button (it usually has a magnifying glass icon).
4. From the drop-down menu, click on Go To Special….
5. A new dialog box will appear. Select the radio button next to Blanks.
6. Click OK.

Excel will instantly scan your selected columns and highlight every single blank cell in gray. Do not click anywhere on the spreadsheet, or you will lose this selection.

Deleting the Highlighted Rows

Now that all the blank cells are actively selected, you can delete the corresponding rows in one sweep.

1. With the blank cells still highlighted, go back to the Home tab on the ribbon menu.
2. Locate the “Cells” group on the right side.
3. Click the small downward-pointing arrow under the Delete button.
4. Select Delete Sheet Rows from the menu.

Every highlighted empty row will instantly vanish, and all of your actual data will cleanly shift upward, leaving you with a perfectly contiguous dataset ready for analysis. Note: If you only want to delete a row if the entire row is blank across all columns, you should apply an AutoFilter to your data, filter for “Blanks” in a mandatory column (like an ID column), and delete the visible rows instead.