Have you ever received an Excel file and wondered how it was filtered without checking each column’s data filter? Or, maybe you needed to send a file and show your filters. That was a problem I faced recently when I started with a list of 3200 non-profits for the county. I needed a way for the recipients to see my autofilter criteria at a glance. (Includes Excel lesson and sample code.)

The screen snap below shows a filtered product list from an example file. Each column heading shows the drop-down arrow on the right. In Excel 2003, columns that have filters have the down-control arrow show as blue. If you’re not familiar with this feature, you can review an earlier AutoFilter tutorial.

Excel 2007 makes it easier to identify a filtered column because it adds a small filter graphic to the drop-down arrow. If you place your mouse over the column heading you can see the filter criteria.

On the first pass with my large spreadsheet, I clicked each column and wrote the column’s filter criteria. I soon learned I didn’t want to do that again and started looking for a better way.

Like many problems, I thought someone smarter than me had solved this issue. The first person is a Microsoft Excel MVP named Stephen Bullen. Mr. Bullen has written several books on VBA for Excel and has a website called Office Automation Ltd.

His solution was to add a user-defined function that reads the filter criteria for any cell. This allowed me to insert a function reference above each column so I could see my filter. If the column didn’t have a filter, nothing showed for that cell.

The first solution was elegant, but static. For example, if I wanted to change the filter to include years 2003-2005, the top cell wouldn’t reflect this change. It could mislead the user. Fortunately, I found a workaround by Rob Markbram on his blog called Rob on Programming. He borrowed from Stephen Bullen’s code and appended the cell formula to force an update.

In other words the formula,

=FilterCriteria(A3) becomes

=FilterCriteria(A3)&LEFT(SUBTOTAL(9,A4:A200),0)

Adding the User Defined Function to Your Excel Spreadsheet

For instructions, I’ll use Excel 2003 but the process is similar for Excel 2007

1. Open the Excel spreadsheet where you would like to add this function.

2. Press ALT+F11. The Microsoft Visual Basic Editor should appear.

3. Highlight the entry ThisWorkbook. (Your entries will look different from mine based on your file name and Excel add-ons.)

4. The editor should open a blank page and display (General).

5. From the Insert menu, select Module.

6. Copy Stephen Bullen’s code below and paste into the editor.

Function FilterCriteria(Rng As Range) As String
    'By Stephen Bullen
    Dim Filter As String
    Filter = ""
    On Error GoTo Finish
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Finish
            Filter = .Criteria1
            Select Case .Operator
                Case xlAnd
                    Filter = Filter & " AND " & .Criteria2
                Case xlOr
                    Filter = Filter & " OR " & .Criteria2
            End Select
        End With
    End With
Finish:
    FilterCriteria = Filter
End Function

Notice how the new FilterCritera function shows in the top right.

6. Click the Disk icon to save the code to your workbook.

7. Click the Excel icon to return to your spreadsheet.

Inserting the FilterCriteria Function

The next part is to insert the function into the cells on your worksheet. My preference is to show the filter criteria at the top so I inserted two rows. I also prefer to insert the function for all the columns even if I don’t plan to filter them. You can adjust your spreadsheet accordingly.

1. Place your cursor in cell A1.

2. From the Insert menu, select Function…

3. In the Insert Function dialog, select the User Defined category. (Your list may look different)

4. In the Select a function: section, highlight FilterCriteria.

5. Click OK.

6. In the Function Arguments dialog, enter the cell range for the filtered column. An easy way is to click the cell such as A3.

7. Click OK.

If you click in cell A1 you should see your formula with the user-defined function. Because this column is not filtered, the cell is blank.

Applying Robert’s Tweak to Allow Filter Updates

The problem I found with the original formula was if I changed my filter criteria, the display values wouldn’t update. In the screen snap below, you can see I’ve filtered for just wines from Ridge Vineyards and yet cell A1 is blank.

1. Go to the cell that has your user-defined function. In my example, it’s A1.

2. Append &LEFT(SUBTOTAL(9,A5:A200),0) to the formula value.

3. Press Enter.

4. Edit your filter criteria for Column A and test cell A1 changes.

5. If the value looks correct, grab the cell’s sizing handle in the lower right corner and drag it over your other columns to copy the formula.

Related Excel Resources

• Sample Excel File with Filter Criteria code