How to Delete Empty Rows Between Data in Excel

Staring at an Excel sheet filled with annoying blank rows between your data can be frustrating. Not only do they make your spreadsheet look messy, but they can also break formulas, mess up sorting, and prevent features like PivotTables from working correctly. This guide will walk you through four easy-to-follow methods to quickly delete those empty rows and clean up your data for good.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Bother Deleting Blank Rows?

You might be tempted to just ignore them, but those extra blank rows are more than just a cosmetic issue. They can introduce serious problems that affect the accuracy and usability of your data.

• Broken Formulas & Functions: Many Excel functions, like SUM or COUNT, stop when they hit a blank row. If you're trying to analyze an entire column, these gaps can give you incorrect totals and throw off your analysis.
• Inaccurate Sorting & Filtering: When you try to sort or filter data, Excel often sees a blank row as the end of your data range. This means it might only sort the data above the first blank row, leaving the rest of your information untouched and creating a jumbled mess.
• PivotTable & Charting Nightmares: Tools like PivotTables and charts need a continuous block of data to work properly. Blank rows can cause them to read your data range incorrectly, leading to incomplete or error-filled reports and visualizations.
• Professionalism & Readability: A clean, well-organized spreadsheet is easier for you and your colleagues to read, understand, and trust. Scattered blank rows make your work look untidy and can reduce confidence in the data.

Method 1: The Go To Special Technique

This is one of the fastest manual methods for getting rid of empty rows. It involves telling Excel to find all the blank cells at once and then deleting their corresponding rows in a single action.

Step-by-Step Instructions:

1. Select Your Key Column: First, click and drag to highlight the entire column you'll use to identify an empty row. It's best to choose a column that should always have data, like an ID number, name, or email address. This prevents you from accidentally deleting a row that just happens to be missing data in one cell but is otherwise valid. If your goal is to delete rows that are 100% empty across all columns, you can highlight your entire data range.
2. Open the "Go To Special" Menu: Press Ctrl + G (or Cmd + G on a Mac) or the F5 key to open the "Go To" dialog box. From there, click the "Special..." button in the bottom left corner. This will bring up the "Go To Special" window.
3. Select "Blanks": In the "Go To Special" window, choose the "Blanks" option and click "OK."
4. Excel Finds the Blanks: Instantly, Excel will highlight all the empty cells within the column or range you selected in the first step.
5. Delete the Rows: Go to the "Home" tab on the ribbon. In the "Cells" group, click the dropdown arrow on the "Delete" button and select "Delete Sheet Rows." Alternatively, you can right-click any of the highlighted blank cells and choose "Delete..." followed by "Entire row."

That's it! Excel will remove all rows containing the selected blank cells, leaving you with a clean and compact dataset. This method works perfectly for deleting rows that are completely empty or have a blank in a critical column.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 2: Using Filters to Isolate and Remove Blanks

If you're a bit more cautious and want to see exactly what you’re deleting before you commit, the filter method is an excellent and safe choice. This approach lets you temporarily hide your valid data, showing only the blank rows, so you can inspect and delete them with confidence.

Step-by-Step Instructions:

1. Apply Filters to Your Data: Click anywhere inside your data set. Then, go to the Data tab in the Excel ribbon and click the "Filter" button. You'll see small dropdown arrows appear in each of your header cells.
2. Filter for Blanks: Click the filter arrow on a column header that should always contain data. In the dropdown menu that appears, uncheck the box for "(Select All)" and then scroll to the very bottom and check the box next to (Blanks). Click "OK."
3. View and Select the Blank Rows: Excel will now hide all rows with data, leaving only the empty ones visible. You can now clearly see all the rows you intend to delete. Click and drag on the row numbers on the left-hand side of the sheet to select all of these filtered blank rows.
4. Delete the Selected Rows: Right-click on any of the selected row numbers and choose "Delete Row" from the context menu. Since you've only filtered the blank rows, your data is safe and sound.
5. Remove the Filter: Go back to the Data tab and click the "Filter" button again to turn it off. All your data will reappear, now without any empty rows.

Method 3: Sorting Data to Group Blank Rows

This method works by reordering your data to push all the blank rows to the bottom of your sheet, where they can be deleted in one large group. It's a great option if the original order of your data isn't critical or if you planned on sorting it anyway.

Step-by-Step Instructions:

1. Select Your Entire Dataset: Click on your header row and press Ctrl + Shift + Down Arrow to select all your data down to the last row (including the pesky blanks).
2. Open the Sort Menu: Navigate to the Data tab and click the "Sort" button.
3. Choose a Column to Sort By: In the Sort dialog box, pick a column under "Sort by" that contains data in most or all of your valid rows. The order (A to Z or Z to A) doesn't really matter. Make sure the "My data has headers" box is checked if your selection includes your column titles.
4. Execute the Sort: Click "OK." Excel will shuffle your data according to the selected column's values, automatically grouping all the entirely blank rows at the very bottom of your dataset.
5. Select and Delete: Scroll down to the bottom of your data. You'll find all the blank rows neatly collected together. Click on the row number of the first blank row, hold down the Shift key, and click the row number of the last blank row to select them all. Right-click the selection and choose "Delete."

For the Power Users: Deleting Blank Rows with a VBA Macro

If you're dealing with routine reports and find yourself deleting blank rows repeatedly, a simple macro can automate the entire process down to a single click. A VBA (Visual Basic for Applications) macro is a small script that performs a set of instructions for you. Don't worry if you've never used one - it's easier than it sounds.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

How to Set Up the Macro:

1. Open the VBA Editor: Press Alt + F11 (or Fn + Option + F11 on a Mac) to open the VBA code editor.
2. Insert a New Module: In the menu bar of the editor, go to Insert > Module. A blank white window will appear on the right.
3. Copy and Paste the Code: Copy the code below and paste it directly into the new module window.

Sub DeleteBlankRows()
    'Selects the active range of cells and deletes entire rows if they are empty
    Dim i As Long

    'Loop backwards to avoid skipping rows after a deletion
    For i = Selection.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
            Selection.Rows(i).Delete
        End If
    Next i
End Sub

1. Close and Run the Macro: Close the VBA editor to return to your Excel sheet. Now, select the entire range of data where you want to remove blank rows. Press Alt + F8 to open the macro list. Select DeleteBlankRows from the list and click "Run."

The macro will instantly loop through your selected data, check each row, and delete any row that is completely empty. If you plan to reuse this macro, remember to save your file as a macro-enabled workbook (.xlsm format).

Watch Out for "Fake" Blank Rows

Sometimes a row can look empty but isn't actually blank. These "fake" blanks can contain hidden characters like spaces, non-printing line breaks, or formulas that return an empty text string (""). Methods like "Go To Special > Blanks" won't find these.

If you suspect this, you can investigate by clicking into a "blank" cell and looking at the formula bar. Is there a space you can't see? A quick way to test is to use the LEN formula in an empty column next to your data. LEN calculates the number of characters in a cell. For a cell in row 2, you could use =LEN(A2). If the cell is truly empty, LEN will return 0. If it returns a number greater than 0, there's something hiding in that cell.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Final Thoughts

Cleaning unwanted blank rows is a fundamental step in good spreadsheet management. Whether you prefer the speed of "Go To Special," the safety of filtering, the simplicity of sorting, or the automation of a VBA macro, there is a method that fits your needs and comfort level. Getting this task done quickly allows you to move on to the important work of analyzing your data and finding insights.

We know that manually cleaning data from different sources is often tiresome, especially just to get your reports and dashboards ready. That's why we created Graphed. It connects directly to your marketing and sales platforms - like Google Analytics, Shopify, or Facebook Ads - and pulls your data into live, automated dashboards. There's no need to export CSVs and delete blank rows because Graphed handles the data pipeline for you. Simply ask a question in plain English, and you'll get the real-time insights you need without the cleanup hassle.