When importing data into Microsoft Excel from a web database or a poorly formatted CSV file, you often end up with hundreds of empty, blank rows scattered throughout your spreadsheet. Manually right-clicking and deleting each blank row is incredibly tedious. Fortunately, Excel has a built-in feature that can locate and delete every single blank row in your entire dataset simultaneously.
How to Delete All Blank Rows at Once
Before you begin, it is highly recommended that you save a backup copy of your spreadsheet. Deleting rows shifts data upward, and if your data is not properly structured, it could cause misalignment.
- Click and drag your mouse to highlight your entire dataset. (Alternatively, click on the top-left cell of your data and press Ctrl + Shift + End to select everything down to the bottom right corner).
- With your data highlighted, make sure you are on the Home tab in the top ribbon menu.
- Look for the “Editing” group on the far right side of the ribbon and click Find & Select (represented by a magnifying glass icon).
- From the dropdown menu, choose Go To Special…
- A new dialogue box will appear with various options. Select the radio button next to Blanks and click OK.
Deleting the Selected Rows
Once you click OK, Excel will instantly highlight every single empty cell within the range you initially selected. The cells containing data will become un-highlighted.
- Do not click anywhere on the spreadsheet, as this will deselect the blank cells.
- While the blank cells are still highlighted gray, navigate back to the “Cells” group on the Home tab.
- Click the small downward arrow under the Delete button.
- Select Delete Sheet Rows from the dropdown menu.
Excel will instantly delete all the rows that contained those blank cells, shifting your remaining data seamlessly upward and leaving you with a perfectly clean, continuous dataset.