How to Delete Blank Rows in Microsoft Excel

The Problem with Messy Data Exports

In a perfect world, every Excel spreadsheet would be meticulously organized from top to bottom. In reality, most of the data we work with is exported from secondary sources—accounting software, CRM databases, or online survey forms. These raw data exports are notorious for generating spreadsheets riddled with completely blank rows scattered randomly throughout the document.

Leaving these blank rows in place is not an option. Blank rows completely break Excel’s ability to sort data alphabetically, they ruin pivot tables, and they cause simple formulas (like AutoSum) to fail because the software assumes the empty row marks the end of the data set.

If you only have two or three blank rows, you can simply right-click the row number and click “Delete.” However, if you have a spreadsheet with 5,000 rows of data and 300 random blank rows scattered within it, doing this manually would take hours. You need an automated solution.

The Risk of “Sorting” to Remove Blanks

A common mistake beginners make is trying to fix this by highlighting all the data and applying a standard alphabetical sort (A to Z). The logic is that sorting will push all the empty rows to the very bottom of the document.

While this technically works to group the blanks together, it permanently destroys the original order of your data. If your rows were originally ordered chronologically by date or grouped logically by transaction number, that specific order is now gone forever. You must use a method that removes the blanks without rearranging the remaining information.

The Best Method: Using “Go To Special”

The safest and most efficient way to instantly remove hundreds of blank rows while preserving your data order is by using Excel’s hidden “Go To Special” tool.

Step 1: Highlight Your Data

1. Open your messy spreadsheet.
2. You must highlight the entire data set that contains the blank rows. Do not simply click the “Select All” triangle in the corner, as this selects the entire infinite spreadsheet.
3. Instead, click on the very first cell in your data (usually A1).
4. Press and hold the Shift key on your keyboard, then click on the very last cell of your data at the bottom right. The entire working block will turn grey.

Step 2: Isolate the Blank Cells

1. With the data highlighted, ensure you are on the Home tab on the top ribbon.
2. Look to the far right side of the ribbon for the magnifying glass icon labeled Find & Select.
3. Click it, and choose Go To Special… from the dropdown menu.
4. A small dialogue box will appear with various radio buttons. Select the button labeled Blanks.
5. Click OK.

Excel will instantly deselect everything except the completely empty cells. Every single blank cell in your spreadsheet will now be highlighted in grey.

Step 3: Delete the Entire Rows

1. Do not click anywhere on the spreadsheet, or you will lose your selection.
2. Look back at the Home tab ribbon. Find the Cells section.
3. Click the small downward arrow immediately below the Delete button.
4. Select Delete Sheet Rows from the dropdown menu.

Excel will instantly collapse the spreadsheet, deleting every single row that contained those blank cells, pulling all your legitimate data tightly together without changing its original order.

Warning: The Danger of Partial Blanks

The “Go To Special” method is incredibly powerful, but it has one major flaw you must be aware of: It deletes the entire row if any cell within that row was highlighted as blank.

Imagine a spreadsheet tracking customer orders. Row 15 contains a valid customer name, a valid purchase amount, but they forgot to provide a phone number, leaving the “Phone Number” cell blank. If you run the “Go To Special” tool on the entire spreadsheet, it will find that blank phone number cell and delete the entire customer row, destroying valid financial data.

To prevent this, you should only highlight a single column before using “Go To Special.” Choose a column that is absolutely mandatory (like “Customer ID” or “Invoice Number”). If the Invoice Number cell is blank, it guarantees the entire row is a useless formatting error. By highlighting only that specific column and running the tool, you ensure you are only deleting genuinely empty rows, protecting rows that simply have partial data missing.