To keep certain records and delete the rest in Excel, filter for the records you want to remove, select only the visible data rows, and delete those sheet rows. Then clear the filter to check what remains. Save a copy first: deleting rows changes the workbook, and the filter method is safest when your data has one header row and no unrelated information in the rows you plan to remove.
What You’ll Need
- An Excel workbook with the data you want to clean up, preferably saved as an Excel workbook (.xlsx).
- A clear rule for which records should be removed, such as a status of Inactive, a particular region, or blank entries in a required field.
- A backup or duplicate of the worksheet. Deleting rows is not a change to make casually, especially if the workbook contains formulas or linked reports.
The steps below are for Excel for Microsoft 365 on Windows. If you use Excel for Mac or Excel for the web, the filtering concepts are the same, but some keyboard shortcuts and ribbon layouts differ. Use the ribbon menu paths described below if a shortcut does not work.
Step-by-Step
-
Save a backup. Use File > Save As to make a separate copy, or duplicate the worksheet by right-clicking its tab and choosing Move or Copy, then selecting Create a copy. Work in the copy until you have checked the result. Excel’s Undo may reverse a deletion, but relying on it is risky if you perform other actions afterward.
-
Check the data before filtering. Your data should have a single row of column headings, with one record per row and no completely blank rows splitting the list. Each heading should be distinct and descriptive, such as Date, Status, and Region. If your list has a title above the headings, do not include that title as part of the filtered range. Also check whether other information sits in the same worksheet rows; deleting a sheet row removes cells across the entire worksheet, not just the columns in your list.
-
Turn on filters. Click a cell inside the data range, then choose Data > Filter. Excel adds a dropdown arrow to each heading in the detected range. If the arrows appear only on part of your data, undo or turn off the filter and select the full range, including the header row, before choosing Data > Filter again. For a well-formed Excel table, click inside the table and use its heading dropdowns; the table usually has filter buttons already.
-
Filter for the records you want to delete. This is the key to avoiding the wrong deletion: display the unwanted records, not the ones you intend to keep. Open the dropdown on the relevant column, clear Select All, select the value or values to remove, and choose OK. For more precise conditions, use the Text Filters, Number Filters, or Date Filters options in that dropdown. For example, to remove discontinued products, display the rows whose Status is Discontinued. If the removal rule involves more than one column, apply filters to each relevant column; Excel will show only rows that meet all active filter conditions.
-
Select visible data rows without the heading. Do not select the heading row. Select the data area that contains the displayed records, including every column in each record. One reliable Windows method is to enter the data-body range in the Name Box to the left of the formula bar, such as A2:F500, and press Enter. Adjust the example to match your actual columns and rows. Then choose Home > Find & Select > Go To Special > Visible cells only, and select OK. This excludes the filtered-out rows from the selection. Windows shortcut: with the range selected, press Alt+; (Alt and semicolon) to select visible cells only. On Mac, use Home > Find & Select > Go To Special and choose Visible cells only if available in your Excel version; menu options and shortcuts can vary. In Excel for the web, use the ribbon’s Go To Special or visible-cells option if available, or use the safer copy-to-new-sheet approach described in Troubleshooting.
-
Delete the displayed records. With only the visible data cells selected, choose Home > Delete > Delete Sheet Rows. This removes the worksheet rows containing the displayed unwanted records, including their data across other columns. If the range is an Excel table and you need to preserve unrelated cells beside it, use the table-specific row deletion command instead: right-click a selected table row and choose Delete > Table Rows, where that option is offered. Avoid pressing the Delete key by itself; that clears cell contents but leaves empty rows in place.
-
Clear the filters and verify the result. Choose Data > Clear to remove the active filter criteria, or open each filtered heading dropdown and choose Clear Filter. The rows that remain should be the records you meant to keep. Check the first and last records, the total row count, and any formulas or totals that depend on the data. Save the workbook only after this review.
Example: Suppose a list contains customer records and a Status column. You want to keep Active customers and remove Inactive ones. Filter Status to show only Inactive, select the visible data rows, choose visible cells only, and delete the sheet rows. Clear the filter. The Active records should now be visible. Filtering for Active and deleting those rows would do the opposite of what you want, so confirm the displayed status before you delete.
Common Mistakes
-
Filtering for the records to keep, then deleting them. The filter shows what is currently visible; it does not decide what should be retained. Before deleting, read the active filter criteria and confirm the visible rows are precisely the ones you intend to remove.
-
Selecting the heading row. If the header is part of the selection, you could remove it and leave the data without usable column labels. Select the data body only, starting below the headings.
-
Deleting cell contents instead of rows. The Delete key clears selected values but does not remove records as worksheet rows. Use Home > Delete > Delete Sheet Rows, or the table-specific Delete > Table Rows command when appropriate.
-
Forgetting hidden rows in the selection. A normal selection can include filtered-out rows even though they are not on screen. Use Go To Special > Visible cells only or the Windows Alt+; shortcut before deleting. Do not assume that selecting a visible block on screen automatically excludes every hidden row.
-
Removing rows that contain other worksheet data. Delete Sheet Rows affects all columns in those worksheet rows. If notes, calculations, or a second list occupy adjacent columns or the same rows, deleting sheet rows may damage that information. Move unrelated content, delete only the table rows, or copy the records to a separate sheet before cleaning them.
-
Using inconsistent values as a filter rule. Entries such as Inactive, inactive, and Inactive (with a trailing space) can behave like separate values. Inspect the dropdown list and review the filtered results instead of assuming that one selection catches every variation.
Tips
-
Use a table for regularly updated lists. Select the range and choose Insert > Table, making sure My table has headers is selected. Tables make it easier to filter and help new rows become part of the data range. They also make it clearer when you are deleting table records rather than unrelated worksheet rows.
-
Test the condition before deleting. Apply the filter and scan several displayed records, including records near the bottom of the list. If the filter is based on a date, number, or text condition, open the dropdown again and confirm the condition is the one you intended.
-
Use more than one filter when the rule needs it. For example, to remove orders that are both Cancelled and from a particular year, filter Status to Cancelled and Date to that year. Excel combines active filters, so the visible results must satisfy both conditions. If you intend to remove either of two different conditions, check carefully that your filters express that logic; filtering multiple columns usually narrows results rather than combining them as an either/or rule.
-
Keep a record of the removal rule. If the data is important or the deletion may need review, note the filter criteria and the number of rows removed. You can also copy the filtered unwanted records to a temporary worksheet before deleting them. This gives you a reference if someone later asks why a record is missing.
-
Use a new worksheet when preservation matters more than speed. Filter for the rows to keep, select visible cells only, copy them, and paste them into a new worksheet with the headings. Check the copied result before replacing or removing the original. This approach avoids deleting worksheet rows and is useful when the source sheet has other content, when you are unsure about the filter, or when Excel for the web does not provide a convenient visible-row deletion workflow.
-
Remember platform differences. In Windows Excel, Ctrl+Z undoes a recent action, and Alt+; selects visible cells only. On Mac, use the ribbon menus when those shortcuts differ or are unavailable. Excel for the web can have a smaller set of selection and deletion commands than desktop Excel, so copying the records to a new sheet can be more dependable than deleting filtered rows directly.
Troubleshooting
The filter arrows are missing or cover only some columns. Click inside the data and choose Data > Filter. If Excel still detects the wrong range, select the entire list, from the header row through the last record and across all columns, then apply the filter again. Remove blank separator rows if they split the list, and make sure every column has a heading.
The wrong records appear after filtering. Open the relevant dropdown and review the selected values or conditions. Check for blanks, inconsistent spelling, leading or trailing spaces, and dates stored as text. Clear the filter and apply it again with the intended values. Do not delete anything until the visible results match the removal rule.
Hidden records were deleted too. Stop immediately and press Ctrl+Z on Windows, or use Edit > Undo on Mac, if the deletion is still the most recent action. If Undo is no longer available, restore the backup or use the workbook’s version history if it is stored in OneDrive or SharePoint. Repeat the process and use Go To Special > Visible cells only before deletion.
Excel will not delete the selection, or it removes unrelated information. Check whether your selection includes non-adjacent visible cells or whether the data is inside a table. For a table, choose Delete > Table Rows rather than deleting whole worksheet rows. If selection commands are limited in your Excel version, copy the records you want to keep to a new worksheet and verify the copied list. If other information shares the worksheet rows, do not use Delete Sheet Rows until you have protected or moved that content.
The remaining row count seems wrong. Clear all filters and count the remaining data records, not the heading row. Check whether some unwanted values were omitted because they were blank, misspelled, or stored in a different format. If the workbook contains a total row or hidden rows that were not part of the filtered data range, account for those separately.
Frequently Asked Questions
Can I delete filtered rows without deleting hidden rows?
Yes. Filter to display the records you want removed, select the data body, choose Go To Special > Visible cells only, and then delete the selected rows. On Windows, Alt+; is the shortcut for selecting visible cells only.
Should I filter the rows I want to keep or the rows I want to delete?
For this deletion method, filter for the rows you want to delete. Those are the visible records you select and remove. If you prefer to filter for records to keep, copy the visible results to a new worksheet instead.
Does clearing a filter bring deleted rows back?
No. Clearing a filter only reveals rows that still exist but were hidden by the filter. Deleted rows require Undo, a backup, or a recoverable earlier version of the workbook.
Can I delete rows from an Excel table this way?
Yes, but use the table-row deletion command if you need to remove table records without affecting unrelated worksheet cells. Right-click a selected table row and look for Delete > Table Rows. The exact context menu can depend on what you selected.
Will filtering and deleting change formulas or totals?
It can. Formulas that refer to deleted cells may adjust or return different results, and totals will change when their source records are removed. Review formulas, charts, PivotTables, and linked reports after the deletion, then refresh reports if needed.
Can I do this in Excel for the web?
You can filter data in Excel for the web, but selection and deletion options can vary. If you cannot reliably select visible cells only, copy the rows you want to keep to a new worksheet, verify them, and use that copy rather than risking deletion of hidden records.
Related
- How to Do Superscript in Excel with a Shortcut
- How to Format Subscript Numbers in Microsoft Excel
- Quick Average in Excel: A Fast Way to Find the Mean
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUM: How to Add Numbers with a FormulaTo add numbers in Excel, select the cell where you want the total, type a SUM formula, and…
- Excel Year Quarter Formula: Get Year and Quarter from DatesWith a valid Excel date in A2, use =ROUNDUP(MONTH(A2)/3,0) to return its calendar quarter as a number from…
- Excel Formula for Percent Change: Examples and TipsTo calculate percent change in Excel, subtract the old value from the new value, then divide by the…
- Excel Scenario Manager Tutorial: Compare What-If PlansTo use Excel Scenario Manager, build a worksheet model, then select Data > What-If Analysis > Scenario Manager…
- Excel Data Validation for a Unique ListYes—but Excel’s Data Validation list does not automatically remove duplicates from its source. In Microsoft 365 for Windows,…
- Excel Formula Solver: Goal Seek, Solver, and How to Use ThemExcel does not have a worksheet function called “formula solver.” To make Excel find an input that produces…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.