How to Delete Blank Rows, Columns, and Cells in Excel

This guide shows you how to remove blank rows, columns, and cells in Microsoft Excel, whether you have a few stray empties or a large dataset full of gaps. You’ll learn quick manual methods for small jobs and faster techniques like Go To Special and filters for bigger cleanups, so your data is tidy, formulas work correctly, and tables look professional.

What You’ll Need

Microsoft Excel (2016, 2019, 2021, or Microsoft 365; the steps are similar in older versions)
A worksheet containing the blank rows, columns, or cells you want to remove
A backup copy of your file (recommended before deleting anything)

Step-by-Step

Delete a Single Blank Row or Column

Click the row number on the left edge of the sheet to select the entire blank row. To select multiple adjacent blank rows, click and drag over their numbers.
Right-click the selected row number and choose Delete from the context menu. The row is removed and the rows below shift up.
For a blank column, click the column letter at the top of the sheet, right-click, and choose Delete. Columns to the right shift left.

Delete Multiple Blank Rows or Columns at Once (Using Go To Special)

Select the range that contains the blank rows or columns. For example, if your data is in A1:F100, select that entire block.
Press F5 (or Ctrl+G) to open the Go To dialog box.
Click the Special… button in the bottom-left corner.
In the Go To Special window, choose Blanks and click OK. Excel now selects every blank cell in your range.
If you want to delete entire rows that are blank, do not click anything yet. Instead, press Ctrl+9 to hide the rows (temporarily), then select the visible rows, right-click, and choose Delete. Alternatively, with the blank cells selected, go to the Home tab, click the Delete dropdown arrow, and choose Delete Sheet Rows. This removes all rows that contain any selected blank cell.
To delete columns that are blank, with the blank cells still selected, go to Home → Delete → Delete Sheet Columns.

Delete Blank Rows Using a Filter

Select your data range (including headers).
Go to the Data tab and click Filter (or press Ctrl+Shift+L). Small dropdown arrows appear in the header row.
Click the dropdown arrow in the column that you expect to be blank in the rows you want to remove (for example, a column that is only filled in for rows you want to keep).
Uncheck Select All, then check (Blanks) at the bottom of the list. Click OK. Now only the blank rows in that column are visible.
Select those visible rows by clicking the row numbers, right-click, and choose Delete Row.
Clear the filter by clicking the filter icon and choosing Clear Filter From [Column Name], or press Ctrl+Shift+L to turn off filtering.

Delete Blank Cells (and Shift Data Up or Left)

Select the range where you want to remove blank cells.
Press F5 → Special… → Blanks → OK to select all empty cells in that range.
Right-click any of the selected blank cells and choose Delete….
In the Delete dialog, choose Shift cells up or Shift cells left depending on how you want the remaining data to move.
Click OK. The blanks are removed and the surrounding data shifts accordingly.

Delete Entire Blank Rows with a Helper Column (for Large Datasets)

Insert a new column at the right edge of your data (e.g., column G if your data ends at F).
In the first data row of that helper column, enter the formula =COUNTA(A2:F2) (adjust the range to match your columns). This counts how many non-empty cells are in that row.
Copy the formula down for all rows in your data.
Filter the helper column to show only rows where the count is 0 (i.e., completely blank rows).
Select those rows and delete them as described in the filter method above.
Remove the filter and delete the helper column.

Common Mistakes

Deleting rows with hidden data: A row may look blank but contain spaces, apostrophes, or invisible characters. Use =ISBLANK(A1) or check with Go To Special to confirm truly empty cells.
Selecting the wrong range: If you select the entire sheet and use Go To Special → Blanks, you might delete legitimate empty cells that are part of your data structure (like blank separator rows). Always limit the range to your actual data area.
Using Delete Sheet Rows when you only want to delete cells: This removes entire rows, which can shift your data unexpectedly. Choose the correct option in the Delete dialog.
Forgetting to clear filters: After deleting filtered rows, if you don’t clear the filter, you may think data is missing when it’s just hidden.

Tips

Always save a backup copy before deleting anything, especially with large datasets.
Use Ctrl+Z immediately if you delete something by mistake.
If you have formulas that reference the deleted rows, they may return errors. Check and update those formulas after cleanup.
For regularly updated data, consider using an Excel Table (Ctrl+T) – tables handle blank rows more gracefully and auto-expand.
To quickly identify truly blank rows, use the Go To Special method and then look at the status bar – it shows “Count” if any cells are selected.

Troubleshooting

Go To Special selects more blanks than expected: This usually means there are non-printing characters (like spaces) in those cells. Select the range, use Find & Select → Replace, enter a space in “Find what” and leave “Replace with” empty, then replace all. Repeat for other invisible characters if needed.
Delete Sheet Rows is greyed out or does nothing: Make sure you have selected at least one cell in the rows you want to delete. If you only selected blank cells, try pressing Ctrl+9 to hide them, then select the visible rows and delete.
Filter doesn’t show “(Blanks)” option: This happens when there are no truly blank cells in that column – they may contain spaces or line breaks. Clean the column first with the Replace method above.
After deletion, data shifts incorrectly: If you used “Shift cells left” but wanted rows removed, undo and use “Delete Sheet Rows” instead. Always double-check the direction of shift before clicking OK.
Excel freezes or is slow on large files: Try using the helper column method with filters, which is more efficient than selecting thousands of blank cells at once. Also, close other programs to free memory.

Frequently Asked Questions

Why does my blank row still appear after I delete it?

It may contain invisible characters like spaces or line breaks. Use Find & Replace to remove spaces (replace a space with nothing) and check for other non-printing characters.

Can I delete blank rows without affecting my formulas?

Yes, but formulas that reference deleted cells will return errors. If your formulas use relative references, they will adjust automatically; absolute references may break. Review your formulas after deletion.

How do I delete blank rows in Excel on a Mac?

The steps are the same: select your range, press Fn+F5 (or Control+G) to open Go To, choose Special, select Blanks, then use the Delete dropdown on the Home tab. The keyboard shortcuts differ slightly (Cmd+9 for hide rows).

What is the fastest way to delete all blank rows in a large spreadsheet?

Use the helper column method with COUNTA and filter. It handles thousands of rows without freezing and is more reliable than selecting blanks manually.

Will deleting blank cells shift my data incorrectly?

If you choose “Shift cells up” or “Shift cells left,” it will move data in the direction you specify. To avoid shifting, delete entire rows or columns instead.

Can I undo a deletion if I make a mistake?

Yes, press Ctrl+Z (or Cmd+Z on Mac) immediately after the deletion. If you close and reopen the file, the undo history is lost.

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 →