Efficiently managing data in Excel often involves filtering and removing unnecessary rows. However, deleting filtered rows in Excel can be tricky if not done correctly, as unwanted data may get removed. This article provides a step-by-step guide to help you safely and accurately delete filtered rows without affecting unfiltered data. Whether you’re cleaning up a large dataset or organizing information for analysis, this guide is tailored to save you time and prevent errors while working in Excel.

Disclaimer: Double-check your data and backup your worksheet before applying bulk actions to avoid accidental loss of important information.

Delete Filtered Rows in Excel

What Are Filters in Excel

Filters in Excel help narrow down data so that you can view only the information you need, without permanently deleting the rows. Filters hide rows that don’t match your criteria. However, once your data is filtered, you may need to delete certain rows that are no longer required. This is particularly helpful in cleaning up large datasets or when preparing your data for analysis.

Why Delete Filtered Rows

Deleting filtered rows can help clean up your data and make it more relevant by removing unnecessary or irrelevant entries. For instance, if you’re analyzing sales data, you might want to filter out and delete rows where sales are below a certain threshold. However, deleting filtered rows incorrectly can accidentally remove unfiltered data, so it’s crucial to follow the right steps.

How to Filter and Delete Rows in Excel

Let’s consider you have the dataset with the names of some employees with their ages and their designations and you want to delete all rows in which the Designation is Operations.

Step 1: Select the Data Range

Select the Data range where you want to apply the Filters.

Step 2: Go to the Home Tab and Select Format as Table

Navigate to the Home tab, and then choose the Format as a Table option, selecting the type that suits your preference.

Step 3: Select the range and Click Ok

Select the range and click ok.

Step 4: Apply Filter to the Column

Click the arrow located in the column header where you wish to activate the filter. At this point, all the data in that column will be highlighted.

Step 5: Deselect Select All and check the box next to Operations

Step 6: Preview Result

Check the results after following and performing the steps given here.

Step 7: Delete the Visible Rows

To remove rows, either right-click and choose “Delete Row” from the context menu, or use the keyboard shortcut Ctrl and the minus (-) key.

Step 8: Clear the Filter

• Go back to the Data tab.
• Click on Clear to remove the filter and display all rows.

Step 9: Preview the Result

Check the results after following and performing the steps given here.

How to Delete Hidden Rows in Excel After Filtering

Hidden rows that are unrelated to your filters can still remain in your dataset, creating inconsistencies or clutter. Here’s a clear guide on how to delete hidden rows in Excel after applying filters:

Step 1: Open File and Select Info

• Click the File tab in the ribbon.
• From the menu, select Info.

Navigate to the File Tab

Step 2: Open Document Inspector

• Click on Check for Issues, located under the Info menu.
• Select Inspect Document from the dropdown.

Select Inspect Document

Step 3: Enable Hidden Rows and Columns Option

• In the Document Inspector window, scroll through the options.
• Ensure the checkbox for Hidden Rows and Columns is selected.

Put a Tick Mark on Hidden Rows and Columns Choice

Step 4: Inspect the Document

• Click the Inspect button at the bottom of the Document Inspector window.
• Wait for Excel to scan the document for hidden rows and columns.

Click the Inspect Button on the Base

Step 5: Remove Hidden Rows

• If hidden rows or columns are detected, the Remove All button will appear.
• Click Remove All to delete all hidden rows and columns from your worksheet.

Step 6: Close the Document Inspector

• Once all hidden rows are removed, click the Close button to exit the Document Inspector window.
• Return to your worksheet and verify that only visible rows related to your filters remain.

How to Delete Filtered Rows Using Go To Special

To delete only the visible rows after applying a filter, follow these steps using Excel’s Go To Special feature:

Step 1: Select Visible Cells

• Highlight the visible rows you want to delete in the filtered dataset.
• Press F5 to open the Go To dialog box.
• Click the Special button in the dialog box.

Step 2: Choose Visible Cells Only

• In the Go To Special dialog box, select the Visible cells only option.
• Click OK to confirm.

Step 3: Delete Rows

• Verify that only the visible cells are selected.
• Right-click within the selected range to open the context menu.
• Choose Delete Rows from the menu.

Step 4: Review the Results

• Verify that the visible rows have been deleted.
• Clear or expand the filter to confirm that only the hidden rows remain in the dataset.

How to Delete Filtered Rows in Excel Using VBA

Follow these steps to efficiently delete filtered rows in Excel using VBA:

Step 1: Open the VBA Window

• Right-click on the sheet tab at the bottom of your workbook and select View Code.
• Alternatively, press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Insert a Module

• In the VBA editor, click Insert > Module to create a new module.
• Copy and paste the following VBA code into the module:

Sub RemoveHiddenRows()

Dim rng As Range

Set rng = ActiveSheet.UsedRange

On Error Resume Next

rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete

On Error GoTo 0

End Sub

Step 3: Run the Macro

• Close the VBA editor and return to Excel.
• Click Developer > Macros, select RemoveHiddenRows, and click Run.
  (If you don’t see the Developer tab, enable it from Excel Options.)

To know how to run a macro click here

Step 4: Confirm Removal

• Once the macro runs, it will delete all visible rows in the filtered dataset.
• Check your worksheet to confirm that only the desired rows remain.

VBA – How to Delete AutoFiltered Rows Except for the Header

Follow these steps to delete rows in Excel with AutoFilter applied, while keeping the header intact:

Step 1: Apply AutoFilter

• Select the data range, including the header row.
• Navigate to the Data tab and click Filter to apply AutoFilter to your dataset.

Step 2: Filter Your Data

• Click the dropdown arrow in the column header where you want to filter.
• Select the criteria to filter the rows you want to delete.

Step 3: Select the Filtered Rows

• Once the filter is applied, highlight the visible rows (excluding the header).
• Click the row number of the first visible row.
• Hold the Shift key and click the row number of the last visible row.

Step 4: Delete the Selected Rows

• Right-click the selected row numbers and choose Delete Row from the context menu.
• Alternatively, press Ctrl + – (minus key) to delete the rows quickly.

Step 5: Remove the Filter

• Return to the Data tab and click Filter again to remove the AutoFilter.
• Verify that only the header row and unfiltered data remain.

How to Delete Filtered Rows in Excel Using Power Query – Advanced Method

Follow these steps to remove filtered rows effectively using Power Query:

Step 1: Load Data into Power Query

• Open your Excel workbook and select the data range or table you want to work with.
• Navigate to the Data tab and click From Table/Range.
• This action will open the Power Query Editor.

Step 2: Apply Filters

• In the Power Query Editor, locate the column header you want to filter.
• Click the dropdown arrow and select the criteria to filter the rows you wish to keep.

Step 3: Remove Filtered Rows

Once your data is filtered, go to the Home tab in the Power Query Editor.

Click Remove Rows in the toolbar.

• Select Remove Blank Rows to eliminate any empty rows.
• Choose Remove Other Rows to delete the filtered-out rows.

Step 4: Close & Load

After completing the changes, click Close & Load to return the modified data to your Excel worksheet.

The cleaned data will now appear in your workbook without the filtered rows.

Additional Tips

• Keep Original Data: Power Query works on a copy of your data. The original data remains intact, and changes are applied only to the data loaded back into Excel.
• Query Editing: You can always go back to the Power Query Editor to make further adjustments to your filters and data as needed.

This process allows you to efficiently delete filtered rows in Excel using Power Query while keeping your data organized and manageable.

Conclusion

Learning how to delete filtered rows in Excel is essential for maintaining a clean and efficient dataset. Whether you use basic filtering, advanced VBA techniques, or Power Query, each method provides a unique way to streamline your data. These steps ensure accuracy and prevent accidental deletion of important information. By mastering these techniques, you’ll enhance your productivity and Excel expertise, making data management more effective.

How to Delete Filtered Rows in Excel – FAQs

Why there is a need to delete rows not shown in a Filter?

Deleting rows not shown in a Filter can help you streamline and optimize your data by removing unnecessary or hidden information. This can improve data analysis, reduce file size, and enhance the clarity of your worksheet.

What are the benefits of using the Document Inspector Method?

The Document Inspector method ensures a thorough examination of your Excel file for hidden rows and columns. It offers a systematic way to identify and remove hidden content, enhancing data privacy and file integrity.

How to recover deleted rows using these methods?

You cannot recover the deleted rows using the above methods. You can only make a backup of your original data to avoid any accidental data loss.

How to delete only specific columns using these methods?

The methods described in the article focus on deleting rows based on the visibility in the filter. If you need to delete specific columns while retaining rows, you may need to modify the provided VBA code or explore alternative approaches.

What is the shortcut key for deleting filtered rows in Excel?

There is no direct shortcut key for deleting filtered rows in Excel, but you can use the following steps for efficiency:

Select the filtered rows.

• Use the keyboard shortcut Ctrl + – (minus key) to delete the rows.
• This method ensures only visible (filtered) rows are deleted.

How to select only filtered rows in Excel?

To select only the filtered rows in Excel:

• Apply a filter to your dataset.
• Press F5 to open the Go To dialog box.
• Click Special, select Visible cells only, and click OK.

Now only the visible (filtered) rows will be selected, allowing you to perform actions like deleting or copying them.