Mastering Excel Filter, A Full Guide with 20+ Tips

The Excel Filter command can help to filter data in a range or table to only show the data you need and hide the rest. You can apply its built-in operators to easily filter numbers, texts or dates such as filter all numbers which is greater than or equal to a certain number, filter text begins, ends or contains specific character or word, or only show rows where the due date is before or after a certain date and so on. Once you filter data in a range or a table, if the data is changed, you can either reapply a filter to get the new data, or clear a filter to display all of the data.

In this tutorial, we will demonstrate how to add, use or remove filters in Excel. Beyond that, we will guide you how to enhance the filter feature to handle more complicated Excel problems.

---

1. How to add filter in Excel

To filter data in a range or table, firstly you need to add filter to your data. This section provides 3 ways to add filter in Excel.

1.1 Filter command on Data tab

Select any cells in a range or table you want to add filter, click Data > Filter.

1.2 Filter command on Data tab

Select any cells in a range or table you want to add filter, click Home > Sort & Filter > Filter.

1.3 Add filter with shortcut

Select any cells in a range or table you want to add filter, and then press Ctrl + Shift + L keys.

After applying one of the above operations, you can see drop down arrows are added in the column headers of selected cells.

---

2. How to apply filter in Excel (one or multiple criteria)

After adding the filter, you need to manually apply it. This section is going to show you how to apply filter in one or more columns in Excel.

2.1 Apply filter to one column (one criteria)

If you only want to apply the filter to one column, such as filter data in column C as the below screenshot shown. Please go to that column, and then do as follows.

1) Click on the drop down arrow in the column header.

2) Specify a comparison from the built-in operations. Or you can uncheck the Select All box to deselect all data, and then only check the boxes next to the data you want to show.

3) Click the OK button to start filtering. See screenshot:

Now the filter is applied to column C. All you specified data are displayed and the rest are hidden.

After applying filter, you can see the drop down arrow turns to a filter icon .

It is very considerate that when you hover the cursor over the filter icon, the filter criteria you have specified will be displayed as a screen tip as the below screenshot shown. So, if you forget the criteria you have specified to a filter, just hover the cursor over the filter icon.

2.2 Apply filter with multiple criteria to multiple columns (multiple criteria)

2.2.1 Apply filter with multiple criteria to multiple columns one by one

If you want to apply filter to multiple columns with multiple criteria, just repeat the above method to multiple columns one by one.

After applying filter to multiple columns, you can see the drop down arrows in filtered columns are turned to filter icons.

2.2.2 Simultaneously apply filter with multiple criteria to multiple columns

With the above method, you need to apply filter to columns one by one, and the most important point is that this method only supports AND criteria. Here introduce the methods for you not only apply filter to multiple columns simultaneously, but also apply both the AND and OR criteria.

Supposing you have a data table as the below screenshot shown, and want to filter data from multiple columns based on multiple criteria: Product = AAA-1 and Order > 80, or Total Price > 10000. Please try one of the following methods to get it done.

2.2.2.1 Apply filter to multiple columns with the Advanced Filter function

The Advanced Filter function may help you solve this problem, please do as follows step by step.

1. Firstly, create the criteria in the worksheet as the below screenshot shown.

Note: For the AND criteria, please place the criteria values in different cells of the same row. And place the OR criteria value on the other row.

2. Click Data > Advanced to turn on the Advanced Filter function.

3. In the Advanced Filter dialog box, please configure as follows.

Now the columns are filtered simultaneously based on the given criteria as the below screenshot shown.

2.2.2.2 Easily apply filter to multiple columns with an amazing tool

As the AND and OR filter criteria are not easy to manage in above method, here highly recommend the Super Filter feature of Kutools for Excel. With this feature, you can apply filter to multiple columns with both AND and OR criteria easily in Excel.

1. After installing Kutools for Excel, click Kutools Plus > Super Filter.

Then the Super Filter pane is displayed on the right side of the worksheet.

By default, two blank criteria groups are added with OR relationship between them in the Super Filter pane. And the relationship among the criteria in the same group is AND. You can change the relationship between different groups based on your needs.

2. In the Super Filter pane, please configure the filter criteria as follows.

Now only the matched data are displayed in the original data range, and the rest are hidden. See screenshot:

Tips: With this handy feature, you can add more criteria in a group, add more groups, save current filter settings as a scenario for future using and so on. It is an indispensable tool which can save a bunch of working time and enhance working efficiency.

Click to know more about this feature.

---

3. How to use filter in Excel

In this section, you will learn how to use filter command to filter different kinds of data types such as text, numbers, dates and formats.

3.1 Filter text values

3.1.1Filter text cells with specific criteria (begin with, end with, contain and so on)

Actually, the built-in filter operator – Text Filters supplies many useful criteria for you to easily filter text. Supposing you want to filter cells which begin with a specific character such as J, please do as follows to get it done.

1. Add filter to the column header of original data range. Click to know how.

2. Click the drop down arrow in the header cell to unfold the filter menu.

3. Click Text Filters > Begins With.

4. In the Custom AutoFilter dialog box, enter the specific character (here I type in a J) into the text box, and then click OK.

Tips: You can add another And or Or relationship criteria as you need.

Now all cells begin with character J are displayed in column D as the below screenshot shown.

3.1.2 Filter with case sensitive

It seems that it is easy to filter text cells based on specific criteria with the built-in filter operator. However, as the Filter function does not support filtering text with case sensitive, how can we do a case sensitive filter in Excel? This section will show you methods to achieve it.

3.1.2.1 Filter certain text with case sensitive by formula and the Filter command

Supposing you want to filter all uppercase of a certain text such as “TEXT TOOLS” in column B, please do as follows.

1. Create a helper column besides the original data range (here I choose the column D as the helper column). Enter the below formula into the second cell and then press the Enter key. Select the result cell, drag its AutoFill Handle down to get the other results.

=EXACT(B2,UPPER(B2))

Note: This formula helps to identify the uppercase and lowercase cells. If a cell contains all uppercase characters, the result will be TRUE, otherwise, you will get the result as FALSE.

2. Select column B and D (the column C will be selected, never mind), click Data > Filter to add filters to them.

3. Go to column B (the column contains the texts you will filter), and then configure as follows.

Now only uppercase and lowercase of “text tools” are displayed in column B.

4. Click the drop down arrow in column D, uncheck the Select All check box, check the check box next to the TRUE item, and then click the OK button.

Then all uppercase of text “text tools” in column B are filtered as the below screenshot shown.

3.1.2.2 Easily filter cells with case sensitive with an amazing tool

If you only want to filter all uppercase or lowercase text in a column, here recommend the Special Filter feature of Kutools for Excel. With this feature, all uppercase or lowercase text will be filtered easily with only several clicks.

1. Select the column range you will filter texts inside, and then click Kutools Plus > Special Filter > Special Filter.

2. In the Special Filter dialog box, please do the below settings.

3. Then a Kutools for Excel dialog box pops up to tell you how many cells are found and will be filtered, click the OK button.

Now all uppercase or lowercase cells are filtered immediately as the below screenshot shown.

Click to know more about this feature.

3.1.3 Filter by text length

If you want to filter cells by text length, for example, to filter cells with text lengths equal to 10 characters, what should you do? Here the below three methods can do you a favor.

3.1.3.1 Filter cells by text length with the Filter command

Actually, the Filter command has the built-in operator to solve this problem, please do as follows.

1. Select the range of cells that to be filtered (here I select B1:B27), and then add a filter to this column range by clicking Data > Filter.

2. Click the drop down arrow in the column header, and then click Text Filters > Custom Filter. See screenshot:

3. In the Custom AutoFilter dialog box, select criteria as equals, type 10 question marks (?) as pattern mode into the text box, and then click the OK button.

Tips: These 10 question marks indicate that it will match the text string that the length is 10.

Now all cells that the text string length is 10 (include spaces) are filtered immediately.

3.1.3.2 Filter cells by text length with formula and the Filter command

Besides, you can use the LEN function to calculate the text string length of each cell, and then apply the Filter command to filter the needed text length cells based on the calculated result.

1. Create a helper column adjacent to the original data range. Enter the below formula into it and then press the Enter key. Select the result cell and then drag its AutoFill Handle down to get the other results.

=LEN(B2)

Now you are getting the text length of each cell in specified column.

2. Select the helper column (include header), click Data > Filter to add a filter to it.

3. Click the drop-down arrow, uncheck the Select All box to deselect all items, and then only check the box next to number 10, and finally click the OK button.

Now all cells that the text string length is 10 (include spaces) are filtered immediately.

3.1.3.3 Easily filter cells by text length with an amazing tool

Here recommend the Special Filter utility of Kutools for Excel to help you easily filter cells by text length in Excel.

1. Select the column range you will filter cells based on certain text length, click Kutools Plus > Special Filter > Special Filter. See screenshot:

2. In the Special Filter dialog box, please configure as follows.

3. A Kutools for Excel dialog box pops up to tell you how many cells are found and will be filtered, click OK to go ahead.

Then all cells with the text string lengths equal to 10 are filtered as the below screenshot shown.

3.2 Filter numbers

In Excel, it is also very easy to filter numbers with the Number Filters command.

Supposing you want to filter cells with numbers between 15000 and 20000 in a column (such as column C as the below screenshot shown), you can do as follows to achieve it.

1. Select the column range contains the numbers you will filter, click Data > Filter to add a filter.

2. After adding filter, please configure as follows.

Tips: As I want to filter cells with numbers between 15000 and 20000, here I enter 15000 and 20000 separately into the text boxes.

Now cells with numbers between 15000 and 20000 are filtered as the below screenshot shown.

3.3 Filter dates

By default, the built-in feature Date Filters provides many common criteria for filtering dates. As you can see, there is no built-in option for filtering dates by day of week, weekends or workday. This section will teach you how to achieve these operations.

3.3.1 Filter dates by day of week or weekends

Supposing you have a data table as the below screenshot shown, if you want to filter dates by day of week or weekends, please apply one of the below methods.

3.3.1.1 Filter dates by day of week or weekends with formula and the Filter command

In this section, you will apply the WEEKDAY function to calculate the day of week of each date, and then apply the filter to filter the certain day of week or weekends as you need.

1. In a blank cell (D2 in this case), enter the below formula into it and press the Enter key. Select the result cell and then drag the AutoFill Handle over the below cells to apply this formula.

=WEEKDAY(A2)

Tips:

Note: As you can see, the formula returns numbers from 1 to 7, which indicates the day of week from Sunday to Saturday (1 is for Sunday, 7 is for Saturday).

2. Select the whole formula results (include the header cell), click Data > Filter.

3. Click the drop down arrow, and then uncheck the Select All checkbox.

Then all weekends or certain day of week cells are filtered. See screenshot:

3.3.1.2 Easily filter dates by day of week or weekends with an amazing tool

If the above method is not convenient for you, here recommend the Special Filter utility of Kutools for Excel. With this feature, you can easily filter cells containing any day of week with several clicks only.

1. Select the cells containing the dates you want to filter based on certain day of week.

2. Click Kutools Plus > Special Filter > Special Filter.

3. In the Special Filter dialog box, please configure as follows.

4. Then a Kutools for Excel dialog box pops up with how many cells are found and will be filtered, click OK to go ahead.

Now all weekends or any day of week cells are filtered.

3.3.2 Filter cells by workdays with an amazing tool

Besides filter cells by day of week or weekends, the Special Filter utility of Kutools for Excel can also help to filter cells by workdays.

1. Apply the same steps as above to enable the Special Filter utility.

2. In the Special Filter dialog box, do the below settings.

3. Then a Kutools for Excel dialog box pops up. Click OK to go ahead.

Now all workday cells are filtered.

3.4 Filter formats

Normally, Excel supports filtering data based on visual criteria such as font color, cell color or icon sets as the below screenshot shown.

However, if you want to filter data based on other visual criteria such as font style (bold, italics), font effects (strikethrough) or special cell (containing formulas), Excel does not help to achieve. This section provides methods to help you solve these problems.

3.4.1 Filter by bold/italics formatted text

Supposing you want to filter data by bold or italics formatting text as the below screenshot shown, the following methods can do you a favor. Please do as follows.

3.4.1.1 Filter bold/italics formatted text with formula and the Filter command

The combination of the Get.Cell formula and Filter command can help to filter bold formatted text in a column range.

1. Click Formulas > Define Name.

2. In the New Name dialog box, you need to:

Formula Syntax:

=GET.CELL(type_num, reference)

Formula Arguments

3. Select a blank cell in the same row of B2, type in the below formula into it and then press the Enter key. Select the result cell, drag its AutoFill Handle over the below cells to apply this formula.

=Filter_Bold_Cells

4. Select the whole result cells (include header), click Data > Filter.

5. Click the drop down arrow, only check the box next to the TRUE option, and then click OK.

Then all bold or italics text cells are filtered. See screenshot:

3.4.1.2 Filter bold or italics formatted text with the Find and Replace and Filter commands

You can also use the combination of Find and Replace and Filter commands to achieve.

1. Select the column range containing the bold or italics text cells you will filter, and then press Ctrl + F keys.

2. In the Find and Replace dialog box, you need to configure as follows.

3. Now all bold or italics text cells are selected in the original range, click Home > Fill Color, and then choose a fill color for the selected cells.

4. Select the whole column range again, click Data > Filter to add a filter to it.

5. Click the drop down arrow, select Filter by Color, and then click the fill color you have specified just now under Filter by Cell Color. See screenshot:

Then all bold or italics text cells are filtered.

3.4.1.3 Easily filter bold or italics formatted text with an amazing tool

As we mentioned above, the Special Filter utility of Kutools for Excel can help to easily filter with case sensitive, filter by text length, filter dates. Here, we will teach how to apply this feature to filter bold or italics formatted text cells in Excel.

1. Select the column range (include header) contains the bold or italics formatted cells you will filter.

2. Click Kutools Plus > Special Filter > Filter Bold / Filter Italic. See screenshot:

3. Click OK in the popping up Kutools for Excel dialog box to go ahead (this dialog box pops up to tell you how many cells meet the criteria).

Now all bold or italics formatted text cells are filtered.

3.4.2 Filter by strikethrough formatted text

Supposing you have received a list with people cutting by adding strikethrough to them, and need to find out all strikethrough cells by filtering, the below methods can do you a favor.

3.4.2.1 Filter strikethrough formatted text with User-defined function and the Filter command

You can apply the User-defined function to identify the strikethrough formatted text cells, and then use the Filter command to filter all strikethrough cells based on the results.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. And then copy the below VBA code into the Code window.

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Select a blank cell (this cell should be in the same row of the cell you want to calculate), enter the below formula and press the Enter key. Select the result cell, drag its AutoFill Handle over the below cell to apply this formula.

=HasStrike(B2)

Note: If the corresponding cell has strikethrough font effect, it returns TRUE, otherwise it returns FALSE.

5. Select the whole result cells (include the header cell), click Data > Filter.

6. Then click the drop down arrow > only check the box next to the TRUE option > click OK. See screenshot shown.

Now you can see all strikethrough formatted cells are filtered.

3.4.2.2 Easily filter strikethrough formatted text with an amazing tool

With the Special Filter utility of Kutools for Excel, all strikethrough formatted cells can be filtered directly with several clicks only.

1. Select the column range you will filter all strikethrough formatted cells, click Kutools Plus > Special Filter > Filter Strikethrough.

2. Then a Kutools for Excel dialog box pops up to tell you how many cells are qualified, click OK to go ahead.

Then all strikethrough formatted cells are filtered as the below screenshot shown.

3.4.3 Filter by font or background color

As we mentioned at the very beginning in this filter formats section, Excel supports filtering data based on visual criteria such as font color, cell color or icon sets with its built-in feature. This section demonstrates how to apply the Filter by Color feature to filter cells by font or background color in details. Meanwhile, we recommend a handy third-party function to help solve this problem.

3.4.3.1 Filter by one font or background color with the Filter command

You can directly apply the Filter by Color feature of Filter command to filter cells by specific font or background color in Excel.

1. Select the column range you will filter cells by font or background color, and then click Data > Filter.

2. Click the drop down arrow > Filter by Color. Then you can see all cell colors and font colors of current column range are listed out. Click on any cell color or font color will filter all cells based on it.

3.4.3.2 Filter by multiple background colors with User-defined function and the Filter command

If you want to filter cells by multiple background colors, please apply the below method.

Supposing you want to filter all cells with orange and blue background colors in column B as the below screenshot shown. Firstly, you need to calculate the color index of these cells.

1. Press the Alt + F11 keys simultaneously.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy the below VBA code into the code window.

VBA code: Get cell background color index

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. In a new column, type a header into the first cell (this cell should be on the same row of the header of the original range).

5. Select a blank cell next to the header cell (here I select E2), enter the below formula into it and press the Enter key. Select the result cell, and then drag its AutoFill Handle over the below cells to apply this formula.

=GetColor(B2)

Note: If the cell has no fill color, it returns -4142.

6. Select the helper column cells (include header), click Data > Filter to add a filter to the column.

7. Click the drop-down arrow to open the drop-down list, and then configure as follows.

Now cells are filtered by specified background colors as the below screenshot shown.

3.4.3.3 Easily filter by font or background color with an amazing tool

Undoubtedly, it is easy to apply the Filter by Color built-in feature to filter cells by font or background color. However, the disadvantage is that the drop-down list box covers the content of the original data so that we cannot view the data at any time for properly choosing a font or background color. To avoid this problem, here recommend the Special Filter utility of Kutools for Excel.

1. Select the column range you will filter cells by font or background color, and then click Kutools Plus > Special Filter > Special Filter.

2. In the Special Filter dialog box, please configure as follows.

Then all cells with specified font color or background color in selected range are filtered.

3.4.4 Filter cells containing formulas

If you have a long list of data which contains both actual values and formulas, and only need to filter the formula cells, what can you do? This section provides two methods to achieve it.

3.4.4.1 Filter formula cells with User-defined function and the Filter command

Firstly, you need to find out all formula cells in the list with the User-defined function, then apply the Filter command to filter the formula cells based on the results.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, then copy the below VBA code into the Code window.

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Select a blank cell, this cell should be in the same row of the cell you want to check if it is a formula cell, enter the below formula into it and then press the Enter key. Select the result cell, drag its AutoFill Handle over the below cells to apply this formula.

=HasFormula(C2)

As the above screenshot shown, the results are FALSE and TRUE, which indicate that if the corresponding cell is a formula cell, it returns TRUE, otherwise returns FALSE.

5. Select the result cells (include the header cell), click Data > Filter to add a filter to it.

6. Click the drop down arrow, only check the box next to the TRUE box, and then click OK.

Then you can see all formula cells are filtered.

3.4.4.2 Easily filter formula cells with an amazing tool

Here demonstrate the Special Filter utility of Kutools for Excel to help you easily filter formula cells in a list with several clicks only.

1. Select the list you want to filter all formula cells, then click Kutools Plus > Special Filter > Filter Formula.

2. A Kutools for Excel dialog box pops up to tell you how many cells are qualified, click OK to go ahead.

Then all formula cells are filtered as the below screenshot shown.

In addition, you can use the Special Filter feature of Kutools for Excel to easily filter cells with other formatting, such as:

Filter all cells with comments, click to know more...

Filter all merged cells based on certain value, click to know more...

3.5 Filter with wildcard

Sometimes, you may forget the exact search criteria when filtering. In this case, we recommend you to use the wildcard characters.

There are only 3 wildcard characters in Excel:

Wildcard character	Description	Example
* (asterisk)	Represents any number of characters	For example, *berry finds “Blackberry”, “Strawberry”, “Blueberry” and so on
? (question mark)	Represents any single character	For example, l?ck finds “lick”, “lock”, “lack” and so on
~ (tide) followed by *, ?, or ~	Represent a real *, ? or ~ character	For example, Face~*Shop finds “Face*Shop”

Let’s see how to use wildcard in filtering.

Supposing you need to filter all cells that end with Market in column B as the below screenshot shot, please do as follows.

1. Firstly, create a criteria range. Enter a header the same as the original column header, and then type the filter criteria in the below cell. See screenshot:

2. Click Data > Advanced.

3. In the opening Advanced Filter dialog box, configure as follows.

Then all cells end with Market are filtered. See screenshot:

The usage of * and ~ wildcard characters in filtering are the same as above operations.

3.6 Filter with build-in search box

If you are using Excel 2010 or the later versions, you may notice that there is a build-in search box in Excel Filter. This section will demonstrate how to use this search box to filter data in Excel.

As the below screenshot shown, you want to filter all cells that contain “Market”, the search box can help you to easily get it done.

1. Select the column range you will filter data, click Data > Filter to add a filter to it.

2. Click the drop down arrow, enter “Market” into the search box, and then click OK.

You can see all qualified texts are listed in real time while entering data in the search box.

Then all cells contain “Market” are filtered as the below screenshot shown.

---

4. Copy visible data only (ignore hidden or filtered data)

By default, Excel copies both visible and hidden cells. If you want to only copy the visible cells after filtering, you can try one of the below methods.

Copy visible data only with short cut keys

You can use short cut keys to only select the visible cells, and then manually copy and paste them to a needed place.

1. Select the range you want to copy the visible cells only. Both visible and hidden cells are selected in this step.

2. Press the Alt and ; keys at the same time. Now only the visible cells are selected.

3. Press the Ctrl + C keys to copy the selected cells, and then press Ctrl + V keys to paste them.

Easily copy visible data only with an amazing tool

Here introduce the Paste to Visible feature of Kutools for Excel for you to easily copy visible data only in Excel. Besides, with this feature, you can copy and paste values to only visible cells in a range that has been filtered.

1. Select the filtered range you want to copy visible cells only, then click Kutools > Range > Paste to Visible > All / Only Paste Values.

For formula cells, choose All copies both the result and formulas, choose Only Paste Values only copies the actual values.

2. In the popping up Paste to Visible Range dialog box, choose a blank cell to output the copied cells, and then click OK.

Then only visible cells in selected filtered range are copied and pasted to a new place.

Note: If the destination range has been filtered, the selected values will paste to the visible cells only.

---

5. Delete hidden or visible rows after filtering

For a filtered list, you may need to delete the hidden rows in order to keep only the visible data. Here in this section, you will learn three methods to delete hidden or visible rows in a filtered list in Excel.

Delete all hidden rows from current worksheet with VBA code

The below VBA code can help to delete all hidden rows from current worksheet in Excel.

Note: This VBA remove not only the hidden row in filtered list, but also remove the rows you have manually hidden.

1. In the worksheet contains hidden rows you want to remove, press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Module window.

VBA code: Delete all hidden rows from current worksheet

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3. Press the F5 key to run the code.

4. Then a Kutools for Excel dialog box pops up to tell you how many hidden rows have been removed, click OK to close it.

Now all hidden rows (include auto hidden rows and manually hidden rows) are removed.

Delete visible rows after filtering with Go To feature

If you only want to remove visible rows in a certain range, the Go To feature can help you.

1. Select the filtered range you want to remove the visible rows, press the F5 key to open the Go To dialog box.

2. In the Go To dialog box, click the Special button.

3. In the Go To Special dialog box, select the Visible cells only option, and then click OK button.

4. Now all visible cells are selected. Right click the selected range and click Delete Row in the context menu.

Then all visible cells are deleted.

Easily delete hidden or visible rows after filtering with an amazing tool

The above methods are tedious and time-consuming. Here recommend the Delete Hidden (Visible) Rows & Columns feature of Kutools for Excel. With this feature, you can easily delete hidden or visible rows not only in a selected range, but also in current worksheet, multiple selected worksheets or the whole workbook. Please do as follows.

1. Select a range you want to remove all hidden or visible rows from it.

Notes:

2. Click Kutools > Delete > Delete Hidden (Visible) Rows & Columns.

3. In the Delete Hidden (Visible) Rows & Columns dialog box, you need to configure as follows.

4. Then all visible or hidden rows are deleted at once. Meanwhile, a dialog box pops up to tell you the number of deleted rows, click OK to finish the whole operation.

---

6. Filter across multiple sheets

Normally, it is easy to filter data in a worksheet. In this section, you will learn how to filter data with same criteria across multiple worksheets that have common data structure.

Supposing a workbook contains three worksheets as the below screenshot shown, now you want to filter data across these three worksheets at the same time with the same criteria “Product = KTE”, the below VBA code can do you a favor.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the module window.

VBA code: Filter data across multiple worksheets at the same time

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

Note: The line “Xws.Range(“A1”.AutoFilter 1, “=KTE”)” in the code indicates that you will filter data in column A with the criteria =KTE, and the number 1 is the column number of column A. You can change them to your need. For example, if you want to filter all numbers that are greater than 500 in column B, you can change this line to “Xws.Range(“B1”.AutoFilter 2, “>500”)”.

3. Press the F5 key to run the code.

Then the specified columns are filtered at the same time in all worksheets of current workbook. See the below results.

---

7. Re-apply filter after changing data

Sometimes you may have made changes for a filtered range. However, the filter result remains the same no matter what changes you made to the range (see screenshot below). In this section, we are going to show you two methods to manually or automatically reapply the filter on the current range so that changes you’ve made are included.

Manually reapply filter with the Reapply command

Excel has a built-in Reapply feature to help manually reapply filter. You can apply as follows.

Click Data > Reapply to reapply the filter in current sheet.

Then you can see the filtered range are reapplied to include the changes you have made.

Automatically reapply filter with VBA code

If the filtered list needs to be changed frequently, you have to repeatedly click to apply this Reapply feature. Here provide a VBA code to help automatically reapply a filter in a real-time when data changes.

1. In the worksheet contains the filter you want to reapply automatically, right click the sheet tab and select View Code.

2. In the opening Microsoft Visual Basic for Applications window, copy the below VBA code into the Code window.

VBA code: Automatically reapply filter when changing data

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

Note: In the code, “Sheet2” is the name of current worksheet. You can change it to your needs.

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

From now on, when changing data in the filtered list, the filtered result will be dynamically adjusted. See the below gif picture.

---

8. Clear or remove filter

We have learned how to add, apply and use filter in the above content. Here we are going to learn how to clear or remove filter in Excel.

8.1 Clear a filter from a column

After applying filter in a column, if you want to clear it, click the filtered icon, and then click Clear Filter From “Header name” from the drop-down menu.

8.2 Clear all the filters in a worksheet

If you have applied filter to multiple columns, and want to clear all of them at the same time, click Data > Clear.

Then all filters are cleared as the below screenshot shown.

8.3 Clear filters from all worksheets in current workbook

Supposing you have applied filters across multiple worksheets in a workbook and want to clear these filters at once. The below VBA code can do you a favor.

1. Open the workbook you will clear all filters from it, and then press the Alt + F11 keys simultaneously.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the module window.

VBA code: Clear filters from all worksheets in current workbook

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3. Press the F5 key to run the code. Then all filters are cleared from all worksheets in current workbook.

8.4 Remove all filters in a worksheet

The above methods only help to clear the filtered status, and the filters still remain in the worksheet. If you want to remove all filters from a worksheet, try the below methods.

Remove all filters in a worksheet by turning off the Filter

Click Data > Filter to turn off the feature (the Filter button is not in the highlight status).

Remove all filters in a worksheet by shortcut key

Besides, you can apply shortcut key to remove all filters in a worksheet.

In the worksheet contains filters you want to remove, press Ctrl + Shift + L keys simultaneously.

Then all filters in current worksheet are removed immediately.