How to Delete Rows in Between Data in Excel

Blank rows scattered throughout your Excel spreadsheet can be more than just a visual annoyance, they can break formulas, mess up sorting, and prevent charts from working correctly. This article will show you several easy ways to delete those empty rows, from a quick manual fix to more powerful methods for large datasets.

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

Manually Deleting a Few Blank Rows

If you only have a handful of blank rows to deal with, the simplest solution is often the best. There's no need for fancy functions or filters if you can get the job done in a few clicks.

How to Delete One by One

To delete a single blank row, simply right-click the row number on the far left side of the screen and choose Delete from the context menu. The entire row will disappear, and the rows below it will shift up.

How to Delete Multiple Rows at Once

For a few separate blank rows, you can select them all at once before deleting. This saves you from having to repeat the process for each row.

1. Hold down the Ctrl key (or Cmd key on a Mac).
2. While holding the key, click on the row number for each blank row you want to remove. You'll see each one highlight as you click.
3. Once you've selected all the blank rows, let go of the Ctrl key.
4. Right-click on any of the selected row numbers and choose Delete.

This method is great for small, manageable datasets where you can easily spot the empty rows. If you're dealing with hundreds or thousands of rows, you'll want to use one of the more automated methods below.

The "Go To Special" Method to Find and Delete All Blank Rows

For larger spreadsheets, the "Go To Special" feature is your best friend. This is arguably the fastest and most popular way to eliminate all blank rows from a dataset in one swoop. It tells Excel to find and select every blank cell in your range, allowing you to delete their corresponding rows instantly.

Here’s how to do it step-by-step:

Step 1: Select Your Data Range

Click and drag to highlight the entire range of data where you want to remove blank rows. A quick way to do this is to click on the first cell of your data (e.g., A1) and press Ctrl + Shift + End. This shortcut selects all the cells down to the last cell containing data.

It's important to select your entire data range. If you only select a single column, you might accidentally delete rows that have data in other columns, causing your dataset to become misaligned.

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

Step 2: Open the "Go To Special" Dialog Box

With your data highlighted, find the "Go To Special" menu. You have a couple of options:

• Press the F5 key on your keyboard to open the "Go To" box, then click the Special... button at the bottom left.
• Alternatively, navigate to the Home tab on the Ribbon, find the Find & Select dropdown (usually on the far right), and choose Go To Special... from the list.

Step 3: Select Blanks

In the "Go To Special" window, select the radio button next to Blanks and click OK.

Excel will instantly highlight every blank cell within the data range you selected. If a row is completely empty, all of its cells in your selection will be highlighted.

Step 4: Delete the Selected Rows

Now that all the blank cells are selected, you can delete their parent rows.

• Go to the Home tab.
• In the "Cells" group, click the Delete dropdown menu.
• Choose Delete Sheet Rows.

You can also right-click on any of the selected blank cells and choose Delete... from the menu. From the pop-up, select Entire row and click OK. All your blank rows will instantly vanish.

Using a Filter to Isolate and Delete Blank Rows

Filters are another excellent tool for removing blank rows safely. This method allows you to visually isolate the blank rows before you delete them, giving you a chance to double-check that you're only removing rows that are truly empty. This can be especially useful in large or complex datasets.

Step 1: Apply a Filter

First, click anywhere inside your dataset. Then, go to the Data tab on the Ribbon and click the Filter button (it looks like a funnel). Alternatively, you can use the keyboard shortcut Ctrl + Shift + L.

You’ll see small dropdown arrows appear in the header of each column.

Step 2: Filter for Blanks

Click the filter arrow in one of your columns — ideally, a column that is essential and should always contain data (like an ID, name, or date). This ensures you're identifying rows that are completely irrelevant.

In the filter dropdown menu, first uncheck the (Select All) box. Then, scroll down to the very bottom of the list and check the box next to (Blanks). Click OK.

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

Step 3: Delete the Visible Rows

Your spreadsheet is now only displaying the blank rows. All your data rows are temporarily hidden.

Now you can easily select all the visible blank rows by clicking and dragging down the row numbers on the left. Once they are all highlighted, right-click on one of the selected row numbers and choose Delete Row.

Step 4: Remove the Filter

Once the rows are deleted, go back to the Data tab and click the Filter button again (or use Ctrl + Shift + L) to remove the filter. Your data will reappear, now clean and free of blank rows.

Grouping Blank Rows with the Sort Feature

If you don't mind rearranging your data, sorting is a simple way to group all the blank rows together for easy deletion. This method gathers all the empty rows in one place, so you can delete them in a single action.

Warning: This method will permanently change the order of your data. If the original order is important, be sure to create a backup copy of your sheet or add a temporary index column so you can sort it back later.

Step 1: Select Your Data

Highlight your entire table, including headers and the blank rows mixed within it.

Step 2: Open the Sort Menu

Go to the Data tab on the Ribbon and click the large Sort button. This will open the Sort dialog box.

Step 3: Choose a Column to Sort By

In the Sort dialog box:

• Make sure the My data has headers box is checked if your data does.
• Under Column, choose a key column from the dropdown.
• Under Sort On, keep Cell Values.
• Under Order, you can choose either A to Z or Z to A. In either case, Excel will group the blank cells for that column together, either at the top or the bottom.

Then, click OK. Excel will reorder your data, and you'll find all the rows that were blank in your chosen column neatly stacked together.

Step 4: Delete the Grouped Rows

Now that all the blank rows are consolidated, click and drag to select their row numbers, right-click, and hit Delete.

Automating Deletion with a Simple VBA Macro

If deleting blank rows is a task you perform regularly, creating a simple macro can save you a lot of time. Don't worry if you've never used VBA before — this is a very straightforward "copy and paste" operation.

Heads Up: Macros perform actions that cannot be undone with the "Undo" button (Ctrl+Z). Always save a backup of your workbook before running a new macro.

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

Step 1: Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Step 2: Insert a New Module

In the VBA editor, go to the top menu and click Insert > Module. A blank white window will appear on the right side of the screen. This is where you'll paste the code.

Step 3: Paste the Code

Copy and paste the following code into the blank module window:

Sub DeleteAllBlankRows()
    On Error Resume Next
    Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

This simple macro looks at column A, finds all the blank cells, and then deletes the entire row for each of those blank cells. You can change Columns("A") to a different column if needed.

Step 4: Run the Macro

You can run it directly from the editor by clicking inside the text and pressing F5, or you can close the VBA editor (Alt+Q), select your data range in Excel, press Alt + F8 to open the macro list, select DeleteAllBlankRows, and click Run.

All blank rows will be gone in an instant.

Final Thoughts

Cleaning your data by removing blank rows is a fundamental step toward accurate analysis in Excel. Whether you prefer the straightforward "Go To Special" method, the safety of filtering, or the efficiency of a VBA macro, there's a solution to fit your needs and keep your spreadsheets tidy and functional.

While these tricks make Excel management easier, they still sit on top of a manual process of exporting, cleaning, and formatting data. With tools like Graphed (target="_blank" rel="noopener"), we automate the tedious parts. Instead of downloading CSVs and cleaning them in Excel, you can connect your data sources directly to Graphed and create real-time, self-updating dashboards by just describing what you want to see in plain English. This eliminates the need for manual row deletion and puts hours back into your week.