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 Excel to show the filters and logic. (Includes Excel practice spreadsheet with VBA code.)
Update 9/28/2020: I received an email from Robert Phillips, the author of the Excel filter function referenced below. He indicated the function has an issue with the newer structured tables, and he is working on a fix.
Normal Excel Filter View
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, 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 Excel AutoFilter tutorial.
Excel 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.
Running Into Limitations
On the first pass with my large spreadsheet, I clicked each column and wrote the column’s filter criteria. However, 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.
This solution was elegant but static. For example, if I wanted to change the filter to include the 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.
But as often the case with software updates, what once worked in previous versions of Excel for me broke. However, I was able to find another solution from Bob Phillips on Microsoft TechNet.
The result was I could see the filter criteria above my columns. This even worked if I used a word filter.
Creating an Excel VBA Function to Show Filter Criteria
- Open the Excel spreadsheet where you would like to add this function.
- Press Alt + F11. The Microsoft Visual Basic Editor should appear.
- Highlight the entry ThisWorkbook. (Your entries will look different from mine based on your file name and Excel add-ons.)
- From the Insert menu, select Module.
- Copy Rob Phillip’s code below and paste it into the new module.
- Click the Excel icon to return to your spreadsheet.
You should now see a blank Code module on the right side.
'Code from Bob Phillips @WessexBob Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sOp As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Dim i As Long Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next lngOp = filt.Operator If lngOp = xlFilterValues Then For i = LBound(filt.Criteria1) To UBound(filt.Criteria1) sCrit1 = sCrit1 & filt.Criteria1(i) & " or " Next i sCrit1 = Left(sCrit1, Len(sCrit1) - 3) Else sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 If lngOp = xlAnd Then sOp = " And " ElseIf lngOp = xlOr Then sOp = " or " Else sOp = "" End If End If ShowFilter = sCrit1 & sOp & sCrit2 End If End If End Function
Notice how the new ShowFilter function shows in the top right.
Inserting the ShowFilter 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 blank 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.
- Place your cursor in cell A1.
- From the Formulas tab, select Insert Function
- In the Insert Function dialog, select the User Defined category. (Your list may look different)
- In the Select a function: section, highlight ShowFilter.
- Click OK.
- In the Function Arguments dialog, enter the cell range for your column. In my example the range is A4:A124.
- Click OK.
- At this point, you should test the code by selecting and deselecting various items in the filter box.
- Repeat the process for any other columns.
If you click cell A1, you should see your formula with the user-defined function. Because this column is not filtered, the cell displays No Active Filter.