How to Delete or Remove Blank Cells in Excel

This guide shows you how to remove blank cells from your Excel data, whether you want to shift the remaining data up, left, or keep it in place. It covers the quick filter method, the Go To Special command, and how to avoid the common pitfalls that can scramble your spreadsheet.

What You’ll Need

Microsoft Excel (2010 or later; the steps are the same for Excel for Microsoft 365, Excel 2021, 2019, and 2016).
A copy of your data or an undo plan. Removing cells is destructive, so it’s wise to save a backup or work on a duplicate sheet.
Your data should be in a contiguous block (no fully empty rows or columns between your data and the blanks you want to remove). If there are, select the specific range first.

Step-by-Step

Method 1: Using Go To Special (Recommended for Most Cases)

Select the data range. Click and drag to highlight all the cells that contain your data, including the blank cells you want to remove. If your data is a large table, click any cell inside it and press Ctrl+A (or Cmd+A on Mac) to select the current region.
Open Go To Special. Press F5 (or Ctrl+G) to open the Go To dialog box, then click the Special button in the bottom-left corner.
Choose Blanks. In the Go To Special dialog, select the Blanks radio button and click OK. Excel will now highlight every blank cell within your selected range.
Delete the blank cells. With the blank cells still selected, right-click on any one of the highlighted cells and choose Delete from the context menu.
Choose your shift direction. In the Delete dialog box, you have two main options:
- Shift cells up: Removes the blanks and moves all data below them upward. This is the most common choice.
- Shift cells left: Removes the blanks and moves data from the right to fill the gaps.
Select the option that matches how you want your data rearranged, then click OK.

Method 2: Using Filter (Best for Removing Entire Rows with Blanks)

Select your data. Click any cell within your data range.
Turn on the filter. Go to the Data tab on the ribbon and click the Filter button (it looks like a funnel). You’ll see drop-down arrows appear in the header row of your data.
Filter for blanks. Click the drop-down arrow on the column where you want to identify blank rows. Uncheck Select All, then scroll down and check the (Blanks) option at the bottom of the list. Click OK. Excel will now show only the rows that are blank in that column.
Delete the visible rows. Select all the visible (filtered) rows by clicking the row numbers on the left. Right-click on one of the selected row numbers and choose Delete Row.
Clear the filter. Click the Filter button on the Data tab again to turn it off. Your blank rows are gone, and the remaining data is intact.

Method 3: Using Find & Replace (For a Single Column)

Select the column. Click the letter at the top of the column that contains the blank cells.
Open Find & Replace. Press Ctrl+H (or Cmd+H on Mac) to open the Find and Replace dialog.
Leave fields blank. In the Find what field, leave it empty. In the Replace with field, type a unique placeholder text like DELETE_ME.
Replace all. Click Replace All. This fills every blank cell in the column with your placeholder.
Filter and delete. Now use the filter method (Method 2) to filter your column for DELETE_ME, select all visible rows, and delete them. Finally, clear the filter and use Find & Replace to replace any remaining DELETE_ME with nothing (leave Replace with empty) to clean up.

Common Mistakes

Deleting entire rows when you only want to shift cells. If you select a blank cell and press Ctrl+- (minus), Excel might delete the entire row, which will shift all your data up and potentially misalign columns. Always use the right-click > Delete option and choose Shift cells up or left unless you specifically want to remove whole rows.
Not selecting the full range. If you only select a few columns when your data spans more, Go To Special will only find blanks in those columns. This can leave you with misaligned data in the unselected columns.
Filtering on the wrong column. When using the filter method, filtering for blanks in a column that has no blanks won’t show you anything. Make sure you pick a column where you know blanks exist.
Forgetting to clear the filter. After deleting filtered rows, if you don’t clear the filter, you might think your data is gone. Always turn off the filter to see the full result.

Tips

Make a backup first. Before any deletion, copy your sheet (right-click the sheet tab > Move or Copy > check Create a copy) or save a separate version of the file. This gives you a safety net.
Use a helper column for complex data. If you have blanks scattered across multiple columns and want to remove entire rows that have any blank, add a new column at the end. Enter the formula =COUNTA(A2:C2) (adjust the range to your data). If the result is 0, the row is completely blank. Filter for 0 and delete those rows.
Sort before deleting. If you want to compact data without shifting cells, you can sort your data by a key column. This will group all blank cells together, making them easier to select and delete in one block.
Check for spaces. A cell that looks blank might contain a space character. Use =TRIM(A1) in a helper column to identify these, or use Find & Replace to replace a single space with nothing.

Troubleshooting

Go To Special doesn’t find any blanks. This usually means your selection doesn’t actually contain any truly empty cells. Check for cells with spaces or formulas that return "" (empty text). Use the TRIM function or Find & Replace to fix these.
Deleting cells shifts my data in the wrong direction. Undo with Ctrl+Z and repeat the process. In the Delete dialog, carefully choose Shift cells up or Shift cells left based on your layout.
My data is misaligned after deletion. This happens when you delete cells in one column but not in another. The fix is to select the entire data range (all columns) before using Go To Special, so all blanks are removed simultaneously.
The filter option for (Blanks) is grayed out. This occurs when your data has no blank cells in that column, or when the column contains merged cells. Unmerge any merged cells in the column, or choose a different column.
I accidentally deleted data that wasn’t blank. Immediately press Ctrl+Z to undo. If you’ve done other actions since, you may need to close the file without saving and reopen the backup you made.

Frequently Asked Questions

What is the difference between “Shift cells up” and “Shift cells left”?

“Shift cells up” moves the data from below the deleted blank cells upward, filling the gaps vertically. “Shift cells left” moves data from the right side of the deleted cells horizontally to fill the gaps. Choose based on whether you want to compact rows or columns.

Can I delete blank cells without affecting the rest of my data?

Yes, if you select the exact range where the blanks are and use the Shift cells up or Shift cells left option, only the cells within that selected range are affected. Data outside the selection remains untouched.

How do I remove blank rows that have no data in any column?

Use the helper column method with =COUNTA(A2:Z2) (adjust the range). Filter for rows where this formula returns 0, then delete those rows. This ensures you only remove completely empty rows.

What if my blank cells contain spaces or invisible characters?

These are not considered “blank” by Excel. Use =TRIM(A1) in a helper column to clean them, or use Find & Replace: put a single space in Find what and leave Replace with empty, then click Replace All.

Will deleting blank cells mess up my formulas?

Yes, it can. If your formulas reference cells that get deleted, they will return errors like #REF!. Always check formulas after deletion, or use absolute references ($A$1) if you want to keep pointing to the same cell.

Is there a way to automatically remove blank cells as I type?

No, Excel doesn’t have a built-in auto-remove feature. You can use a macro (VBA) to automate the process, but for most users, the manual methods above are safer and simpler.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →