Filtering data in Excel – easy and comprehensive

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 a filter, click "Data" > "Filter".

1.2 Filter command on Home tab

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

1.3 Add filter with shortcut

Select any cells in a range or table you want to add a 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. 1) Click on the drop down arrow in the column header.
2. 2) Specify a filter condition as you need.
3. 3) Click the "OK" button to start filtering. See screenshot:
   

Now the filter is applied to column C. All data that meets the filter criteria will be displayed and the rest will be 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 we introduce the methods for you not only apply filter to multiple columns simultaneously, but also apply both the "AND" and "OR" criteria.

Suppose 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 we 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.

2.1) Check the "Specified" box, click the button to select the original range or table you will filter;

2.2) In the "Relationship" drop-down list, choose "Or";

3.3) Click on the first blank line in the first group, and then specify the criteria based on your need;

Tips: The first drop-down list is for column headers, the second is for filter types (you can choose "Text", "Number", "Date", "Year", "Text format" and so on from this drop-down), the third is for criteria types, and the last text box is for criteria value.

As the example we mentioned above, here I choose "Product" > "Text" > "Equals" separately from the three drop-down lists, and then type in "AAA-1" into the text box. See screenshot:

2.4) Keep creating the rest criteria, and the "Or" criteria needs to create in a new group. As the below screenshot shown, all the criteria are created. You can delete the blank criterion from the groups.

2.5) Click the "Filter" button to start filtering.

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 use 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. Suppose you want to filter cells which begin with a specific character such as J, please do as follows to get it done.

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

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

Suppose 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 we 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 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 a pattern 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 we 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 numeric values

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

Suppose 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 the week, weekends or workday. This section will teach you how to achieve these operations.

3.3.1 Filter dates by day of the week or weekends

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

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

In this section, you will apply the WEEKDAY function to calculate the day of the week of each date, and then apply the filter to filter the certain day of the 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 the 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 the week cells are filtered. See screenshot:

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

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

1. Select the cells containing the dates you want to filter based on certain day of the 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 the week cells are filtered.

3.3.2 Filter cells by workdays with an amazing tool

Besides filter cells by day of the 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

Suppose 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:

=GET.CELL(20,$B2)

=GET.CELL(21,$B2)

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

Suppose 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. Clicking on any cell color or font color filters all cells based on the selected color.

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.

Suppose 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 we 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 Plu"s > "Special Filter" > "Special Filter".

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

2.1) The cell address of selected column range displays in the "Range" box;

2.2) In the "Filter Rules" section, select the "Format" option;

2.3) Choose "Background Color" or "Font Color" from the drop down list;

2.4) Click the eyedropper icon ;

2.5) In the opening "Filter special cells" dialog box, select a cell containing the font color or background color you will filter based on, and then click "OK".

2.6) When it returns to the "Special Filter" dialog box, the font color or the background color of selected cell is displaying in the text box (you can modify the color as you need), click "OK" button to start filtering cells.

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

3.4.4 Filter formula cells

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:

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 containing “Market” are filtered as the below screenshot shown.

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

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 the desired location.

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 we 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" to copy both the result and formulas, choose "Only Paste Values" to only copy 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 (including auto-hidden 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 we 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 worksheets

Normally, it is easy to filter data in a worksheet. In this section, you will learn how to filter data using the 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.

The specified columns are now filtered simultaneously across all worksheets in the 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). This section shows 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

Suppose 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 the current workbook.

8.4 Remove all filters in a worksheet

The above methods only help to clear the filtered status, but the filter feature remains active 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.

All filters in the current worksheet are then removed immediately.

Best Office Productivity Tools

🤖	Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
	Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
	Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
	Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
	Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
	Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
	Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

More details of Kutools for Excel... Free Download...

---

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

More details of Office Tab... Free Download...

---

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

• All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
• One installer, one license — set up in minutes (MSI-ready)
• Works better together — streamlined productivity across Office apps
• 30-day full-featured trial — no registration, no credit card
• Best value — save vs buying individual add-in

More details of Kutools for Office... Free Download...