How To Erase Blank Cells In Excel | Delete With One Shortcut

To erase blank cells in Excel, select the range, press F5, choose Special > Blanks, then right-click and delete with a shift up or left.

Blank cells sink data cleanup time faster than almost any other formatting headache. One stray gap in a column can ruin a sort, misalign a table, or break a formula range. The fastest fix lives inside a dialog most users skip. Here is how to erase blank cells (or entire blank rows) without scrambling the rest of your spreadsheet, plus the shortcut that makes the job take five seconds.

The Fastest Way To Erase Blank Cells In Excel

The quickest way to erase blank cells inside a selected data range is the Go To Special command. It selects every empty cell in the range at once so you can delete them in one move.

1. Select the range of cells that contains blanks.
2. Press F5 to open the Go To dialog, then click Special.
3. Choose Blanks and click OK. Excel highlights all empty cells inside the selection.
4. Right-click any one of the highlighted empty cells and choose Delete.
5. Select Shift cells left or Shift cells up and click OK.

The gaps disappear and the remaining data fills inward, compressing the range.

How To Delete Entire Blank Rows Without Breaking Your Data

Deleting blank cells shifts data around and can break formulas that reference specific addresses. When your goal is removing empty records rather than compressing a column, deleting the entire blank row is safer. Two methods work well for this.

Filter Method

1. Select the full data area.
2. Go to Data > Filter to enable filter arrows on every column header.
3. Click the drop-down arrow on any column and uncheck everything except Blanks.
4. Select all the visible blank rows, right-click, and choose Delete Row.
5. Clear the filter with Data > Clear.

The blank rows vanish and your remaining data stays contiguous.

Helper Column Method

This handles datasets where a blank row might have some text but is mostly empty. Add a new column and use the COUNTA formula.

1. Insert a helper column next to your data.
2. Enter =COUNTA(A2:C2)=0 (adjust the range to match your columns).
3. Fill the formula down. Rows that are completely empty return TRUE.
4. Filter the helper column for TRUE, select the visible rows, and delete them.

What Is The Shortcut To Delete Blank Cells In Excel?

The full keyboard shortcut skips the mouse entirely. After selecting your range, press Ctrl+G to open Go To, then Alt+S to jump into Special, arrow down or type B for Blanks, and press Enter. Once the blanks are selected, press Ctrl+minus (Ctrl+–) and use the arrow keys to choose Shift cells left or up. The whole sequence takes under three seconds once the keystrokes are familiar.

A note on the Mac version: Apple’s Excel doesn’t support the exact Ctrl+G path. To select blanks on a Mac, use Control+G or go to Home > Find & Select > Go To Special, then choose Blanks. The delete step works the same way.

Can You Erase Blank Rows With Power Query Or VBA?

Yes. Both Power Query and VBA remove blank rows cleanly, and each has a specific strength depending on how often you repeat the task. Ablebits’s detailed guide to removing blank rows includes the formula and VBA approaches side by side.

Power Query

1. Select the data and go to Data > From Table/Range. Excel converts the range into a table if it isn’t one already.
2. In the Power Query editor, go to Home > Remove Rows > Remove Blank Rows.
3. Go to Home > Close & Load.

A new worksheet appears with the blank rows already gone. The original data stays untouched, and you can refresh the query later for new data.

VBA Macro

For a reusable macro that works on any sheet with one click:

Sub DeleteBlankRows()
    Dim rng As Range
    Dim row As Range
    Set rng = ActiveSheet.UsedRange
    For Each row In rng.Rows
        If Application.WorksheetFunction.CountA(row) = 0 Then
            row.Delete
        End If
    Next row
End Sub

1. Press Alt+F11 to open the VBA editor.
2. Go to Insert > Module and paste the code above.
3. Close the editor, then press Alt+F8, select DeleteBlankRows, and run.

Every row that was completely empty is gone. Save the workbook as a macro-enabled file (.xlsm) to keep the code for future use.

Common Mistakes When Erasing Blank Cells

Using Go To Special to delete blank cells when you actually want to remove blank rows is the most frequent error. Deleting cells with shift left or up changes the row structure and misaligns columns that had different numbers of blanks. Formulas that point to specific cells also break because the deleted cells are gone and the references move.

Another mistake is selecting too narrow a range before opening Go To Special. If the range doesn’t cover all the columns that should be considered together, blanks outside the selection remain untouched and the data still looks messy.

A third gotcha involves cells that look blank but contain a formula that returns "". Go To Special treats those as non-blank, so the cells won’t be selected. Use Find & Select > Find and leave the “Find what” field empty when you need to deal with formula-returned blanks.

Final Verdict

If you only learn one method, make it the Go To Special shortcut. It handles the most common “erase blank cells” task faster than any menu hunt. Keep the filter and helper-column methods in your back pocket for blank-row cleanup, and use Power Query or VBA only when the task repeats weekly. The wrong approach will scramble your data; the right one finishes the job and leaves your formulas intact.