How to delete filtered rows in excel

Answer

To delete filtered rows in Excel, apply a filter to show only the rows you want removed, select the visible rows only, then delete the entire rows. Follow the numbered steps below for reliable results.

Step-by-step (quick method — works in Excel for Windows and Mac)

1. Apply a filter to your data.

   1. Select any cell in your data range or table.

   2. On the Data tab click Filter (or use the table filter arrows). 

2. Filter so only the rows you want deleted are visible.

   1. Use the column filter drop-downs to choose the criteria that shows only the rows to remove. 

3. Select the visible rows. Use one of these methods:

   1. Click the first visible row number, then hold Shift and click the last visible row number.

   2. Select the visible area (click and drag). Press Alt + ; on Windows or Command + Shift + Z on Mac to select Visible cells only.

   3. Or use the ribbon: Home → Find & Select → Go To Special → Visible cells only → OK. 

4. Delete the selected visible rows (entire rows):

   1. Right-click any selected row number and choose Delete Row (or Delete → Table Rows if inside an Excel Table).

   2. Or press Ctrl + - (minus) on Windows (use the Delete/Grid menu on Mac). 

5. Clear the filter to verify the remaining data is intact: Data → Clear or click the filter icon and choose Clear Filter. 

Alternate safe method (use when you prefer a non-destructive check first)

1. Add a helper column and mark rows to delete (for example, mark with X or 1).

2. Filter that helper column to show only marked rows.

3. Select visible cells only (use Alt + ; or Go To Special).

4. Delete the entire row.

5. Remove the helper column and clear filters. 

Notes and important caveats

1. Deleting rows inside an Excel Table vs a plain range: delete behaves as Table Rows for structured tables; use the table commands or right-click row number and choose Delete → Table Rows to avoid breaking table structure. Merged cells can prevent row deletion or produce unexpected results. Unmerge cells before deleting rows if you encounter errors.

2. Copy/paste behavior: by default, Excel copies hidden cells as well. Use Go To Special → Visible cells only when copying before deleting or moving data. 

3. Always double-check which rows are visible before deletion. Reapply the filter or temporarily sort to confirm your selection. 

Windows vs Mac differences

1. Shortcut to select visible cells only: Windows = Alt + ;, Mac = Command + Shift + Z. Use Home → Find & Select → Go To Special → Visible cells only if a shortcut does not work. 

2. Keyboard shortcuts for deleting rows vary slightly on Mac; use the right-click menu (Delete → Table Rows or Delete Row) when unsure. 

Troubleshooting (common problems and fixes)

1. Problem: Hidden rows outside your filtered selection get deleted.
   Fix: Use Select Visible Cells before deleting so hidden rows are not included. Confirm selection shows only marching-ants around visible cells. 

2. Problem: Cannot delete because of merged cells or a protected sheet.
   Fix: Unmerge affected cells and unprotect the sheet before deleting. 

3. Problem: Using an Excel Table and rows seem not to be deleted.
   Fix: Use Delete → Table Rows to remove rows inside a ListObject/table. 

Best-practice checklist (quick)

1. Confirm filter criteria show exactly the rows to remove.

2. Use Select Visible Cells (Alt+; or Cmd+Shift+Z) or Go To Special.

3. Delete entire rows, not just contents, when you want to remove rows from the dataset.

4. Clear or reapply filter and scan remaining data to ensure no unintended deletions. 

Yes · 0 No · 0 Add Comment · 0

Add ·

Report