Excel: How to quick filter a column by the text within a highlighted cell4 min read

I was looking for a keyboard shortcut within Excel that allowed me to quickly Filter a column by the contents of a cell. Unfortunately it didn't exist as a native Excel feature, but I was able to build a VBA macro that could accomplish that task, as well as a second macro that clears all filters.

This was the old way:

1. Enter data into a cell. For example, in the Expense column I would write “Cloudways” and then fill in the rest of the row with the amount and expense category.
2. I wanted to see how much my last monthly bill was for Cloudways, so I had to go to the top of the column, click the arrow, and type “Cloudways” again to filter the column.
3. Then when I was finished referencing my previous line items that included “Cloudways”, I'd have to go to Data –> Clear Filter in order to get rid of all filters.

Sure it's not too terrible, but every click counts, and I perform the action above many times per day when adding line items to my spreadsheet and wanting to reference previous ones that include the same data.

Here's the new way:

1. Create a VBA macro that instantly filters the column based on the contents of the cell. Now I can type “Cloudways” into the cell and hit “Ctrl + Shift + J” and Excel will auto-filter the column with “Cloudways”.
2. Create a VBA macro that clears all filters. Now when I hit “Ctrl + Shift + K” Excel will clear all filters and bring me back to where I was.

NOTE: The instructions above require that your Excel spreadsheet is saved as a “Excel Macro-Enabled Workbook (*.xlsm). You can create the macros in a normal Excel spreadsheet, but they won't save if you close the workbook. So best to “Save As” an *xlsm before you begin.

Instructions #1: Creating the instant filter macro

Open the VBA Editor:

• Press Alt + F11.

Insert a new module:

• In the VBA editor, click on Insert > Module.

Copy and paste the following code into the module:

Sub FilterByActiveCell()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cellValue As Variant
    
    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet
    
    ' Set the range to the active cell
    Set rng = ActiveCell
    
    ' Get the value of the active cell
    cellValue = rng.Value
    
    ' Check if filters are already applied, if not apply them
    If ws.AutoFilterMode = False Then
        ws.Range("A1").AutoFilter
    End If
    
    ' Get the column number of the active cell
    Dim colNum As Long
    colNum = rng.Column
    
    ' Apply filter to the column based on the active cell value
    ws.Range(ws.Cells(1, colNum), ws.Cells(ws.Rows.Count, colNum)).AutoFilter Field:=colNum, Criteria1:=cellValue
End Sub

Close the VBA editor:

• Press Alt + Q to close the VBA editor and return to Excel.

Assign the macro to a keyboard shortcut:

• Press Alt + F8 to open the Macro dialog box.
• Select the macro FilterByActiveCell and click on Options.
• Assign a shortcut key (e.g., Ctrl + Shift + J) and click OK. (You can use whatever letter you want for the shortcut.)

Now, you can use the assigned shortcut key to filter the column by the contents of the active cell.

• To use the macro, simply select the cell containing the value you want to filter by and press the keyboard shortcut you assigned. The column will be filtered based on the value in the active cell.

Instructions #2: Creating the clear all filters macro

Open the VBA Editor:

• Press Alt + F11.

Insert a new module:

• In the VBA editor, click on Insert > Module.

Copy and paste the following code into the module:

Sub ClearFilters()
    Dim ws As Worksheet
    
    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet
    
    ' Check if filters are applied, if so, clear them
    If ws.AutoFilterMode Then
        ws.ShowAllData
    End If
End Sub

Close the VBA editor:

• Press Alt + Q to close the VBA editor and return to Excel.

Assign the macro to a keyboard shortcut:

• Press Alt + F8 to open the Macro dialog box.
• Select the macro ClearFilters and click on Options.
• Assign a shortcut key (e.g., Ctrl + Shift + K) and click OK. (You can use whatever letter you want.)

Now, you can use the assigned shortcut key to clear filters in your Excel worksheet.

• To use the macro, simply press the keyboard shortcut you assigned, and the filters on the active sheet will be cleared.

Good luck! I hope this saves you many seconds of each day.