How to Remove Empty Cells Between Data in Excel

Nothing brings data work to a screeching halt faster than a spreadsheet filled with random empty cells and blank rows. Those pesky gaps can mess up your formulas, break your pivot tables, and make your data incredibly difficult to sort or analyze. This guide will walk you through several effective methods to clean up those empty cells in Excel, from quick and easy tricks to more powerful, automated solutions.

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

First, Identify the Type of "Empty" Cell

Before you start deleting, it's helpful to know what you're dealing with. An "empty" cell in Excel isn't always what it seems. You might have:

• Truly Blank Cells: These are cells that contain absolutely no data. They are the easiest to deal with.
• Cells with Spaces: Sometimes a cell looks empty but actually contains one or more space characters. These won't be identified as "blanks" by some Excel features and can be tricky if you don't know to look for them.
• Cells with non-printing characters or hidden formulas: A cell might look empty but contain a formula that returns an empty string ("") or a hard-to-see character.

Let's focus on dealing with truly blank cells and rows first, as this is the most common issue. Most of these methods will effectively remove entire rows that are empty, closing the gaps in your dataset.

Method 1: Use the "Go To Special" Feature

The "Go To Special" command is one of the quickest and most popular ways to find and delete blank cells in a dataset. It selects all empty cells within your chosen range at once, allowing you to delete them in a single step.

Best For: Quick, one-time removals of empty rows without disrupting the order of your remaining data.

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-by-Step Instructions:

1. Select Your Data Range: Click and drag to highlight the range of cells where you want to remove blanks. It's often helpful to select the entire column(s) by clicking the column headers (e.g., A, B, C) if you want to clean up everything.
2. Open "Go To Special":
3. Select Blanks: In the "Go To Special" dialog box, select the radio button next to Blanks and click OK. Excel will now highlight every single empty cell within your selected range.
4. Delete the Blank Cells:
5. Choose How to Shift Cells: A "Delete" dialog box will appear. You have a few choices here, but the most common one is Shift cells up. This will delete the blank cells and move the data below them up to fill the gaps. If you've selected entire blank rows, you can also select Entire row. Click OK.

After clicking OK, all your selected blank cells will vanish, and the remaining data will be nicely consolidated. This method is incredibly effective for tidying up intermittent empty rows in a data set.

Method 2: Use the Sort Feature

If you have a simple list and don't care about the original order of your data, the Sort feature is probably the easiest way to remove blank cells. Sorting your data automatically groups all the non-blank cells together, pushing the empty ones to the bottom where they can be easily deleted.

Best For: Single-column lists or datasets where preserving the original row order isn't important.

Step-by-Step Instructions:

1. Select Your Column: Click on the column header you want to clean up.
2. Sort the Data:
3. Remove the Blanks: After sorting, Excel will move all cells with data to the top of the column and all the blank cells to the bottom. You can now simply highlight the rows containing the blank cells and delete them.

Pro Tip: Preserving the Original Order

What if you need to keep the original order? You can use a helper column. Before sorting, insert a new column next to your data and fill it with a sequence of numbers (1, 2, 3, etc.). Now, sort your data by the target column to group the blanks as described above and delete them. Afterward, sort your data again using the helper column to return the remaining data to its original relative order.

Method 3: Use the Filter Feature

Filters are another excellent way to handle blank rows, especially in larger tables. By filtering your data, you can temporarily hide the non-blank rows, leaving only the empty ones visible for easy deletion.

Best For: Datasets formatted as tables or when you want to review the blank rows before deleting them.

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-by-Step Instructions:

1. Select Your Data Header: Click on any cell within your data table.
2. Apply a Filter: Go to the Data tab and click the large Filter icon. A small dropdown arrow will appear in each of your header cells.
3. Filter for Blanks:
4. Delete the Filtered Rows: Your table will now only display the rows that are blank in that specific column. Highlight these visible rows (they might be non-consecutive, which is fine), right-click, and choose Delete Row. Excel will warn you that this will delete entire rows, proceed if that's what you intend.
5. Clear the Filter: Go back to the Data tab and click the Filter icon again to remove the filter. Your data will reappear, now without any of the blank rows.

Method 4: Use a Formula (with a Helper Column)

If you prefer a non-destructive method that keeps your original data intact, a formula can pull out all the non-empty cells into a brand-new list.

Best For: Situations where you need to preserve the source data, or for creating a dynamic, cleaned-up list that updates automatically if the source data changes.

For Newer Excel Versions (Microsoft 365 or Excel 2021)

If you have access to dynamic array functions, the FILTER function is a dream come true for this task. Let's say your messy data is in column A, from A2 to A100.

1. Click on an empty cell where you want your new, clean list to begin.
2. Type the following formula and press Enter:

=FILTER(A2:A100, A2:A100<>"")

That's it! Excel will "spill" a list of all the values from A2:A100 that are not empty (<>""). This new list will automatically adjust if you add or remove entries from the source column.

For Older Excel Versions

If you're using an older version without the FILTER function, you'll need a more complex array formula. For data in A2:A100, you can use this formula:

=IFERROR(INDEX($A$2:$A$100, SMALL(IF($A$2:$A$100<>"", ROW($A$2:$A$100)-ROW($A$2)+1), ROWS($B$2:B2))), "")

This looks intimidating, but you just need to do two things:

1. After typing it, press Ctrl + Shift + Enter to enter it as an array formula. You'll see curly braces {} appear around it.
2. Click the corner of the cell and drag it down to fill as many rows as you need.

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 5: Power Query (Get & Transform)

For those who handle data from external sources regularly or need to perform this cleaning task repeatedly, Power Query is the ultimate solution. It allows you to build a reusable, automated process for cleaning your data.

Best For: Repeatable data cleaning tasks, creating automated workflows, and handling large datasets that you import from other sources.

Step-by-Step Instructions:

1. Load Data into Power Query:
2. Remove Blank Rows:
3. Close & Load:

Final Thoughts

Dealing with messy data is a universal pain point, but cleaning up empty cells in Excel doesn't have to ruin your afternoon. By using built-in features like Go To Special, Filter, and Sort, or by using more advanced tools like dynamic array formulas and Power Query, you can quickly consolidate your data and prepare it for serious analysis.

Manually cleaning spreadsheets is often just the initial hurdle in a longer, more repetitive reporting cycle. Many teams spend Monday mornings downloading CSVs from platforms like Google Analytics, Shopify, and Facebook Ads, then wrangle those empty cells, just to build the same reports week after week. We built Graphed to break that cycle. It connects directly to your data sources and automates the entire process, letting you build real-time dashboards and get answers from your combined data just by asking questions - all without ever touching another blank cell or manually refreshing a spreadsheet again.