If I need a list of unique items from a long list, I usually use an Advanced Filter, because it leaves the original list alone, and extracts a list of unique records. The filter can be done in place, or sent to another range, on the same sheet, or a different sheet. It’s easier in newer versions of Excel though – here’s how to quickly remove duplicates in Excel 2013.
Poor Advanced Filter! It’s hidden in a dark corner of the Ribbon’s Data tab, overshadowed by its better known, and more popular sibling, AutoFilter. Sometimes, though, it’s worth the extra effort to use an Advanced Filter. See the benefits, and learn how to set up an Excel Advanced Filter.
Click on a cell in an Excel table, and the column is automatically filtered for that item. In the worksheet shown below, columns B and C have already been filtered, and when I click on Pen Set, column D will only show that item.
To see all the items in a column again, just click on the heading cell, and the filter for that field is cleared.
FilterClick Sample File
This filtering trick is accomplished with a bit of programming. There is an event procedure – Worksheet_SelectionChange – that automatically runs when you select a cell on the worksheet . You can turn the FilterClick feature on or off, by clicking on a cell that’s named FilterStatus. When you click on that cell, its value automatically toggles between On and Off.
The SelectionChange procedure checks the value in that cell, and only filters the column if the FilterStatus cell says “On”.
Update for Working With Excel Tables
I created the sample file a few years ago, and have just updated it to include a worksheet that has the data in a formatted Excel table. The code is almost identical – only one line is different if you’re using a table.
In the sample code shown below, there are two versions of the line that sets the range rngF. For formatted tables, use the first line, and for non-table AutoFilters, use the second line.
- Set rngF = ActiveSheet.ListObjects(1).Range ‘for tables
- ‘Set rngF = ActiveSheet.AutoFilter.Range ‘for AutoFilter ranges
Type an apostrophe at the start of the line that you don’t need, and remove the apostrophe at the start of the line that you do need.
You can see more examples of List AutoFilter VBA here.
The FilterClick Code
Here is the code, and you can copy it to the worksheet module for the sheet that your list is on. Remember to fix the Set rngF lines, so one has an apostrophe, and one doesn’t – or delete the line that you don’t need.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngF As Range Dim rngFS As Range Dim lRow As Long Dim lCol As Long Set rngF = ActiveSheet.ListObjects(1).Range 'for tables 'Set rngF = ActiveSheet.AutoFilter.Range 'for AutoFilter ranges Set rngFS = ActiveSheet.Range("FilterStatus") lCol = rngF.Columns(1).Column - 1 lRow = rngF.Columns(1).Row If Target.Count > 1 Then GoTo exitHandler If Target.Address = rngFS.Address Then If rngFS.Value = "On" Then rngFS.Value = "Off" Else rngFS.Value = "On" End If End If If UCase(rngFS.Value) = "ON" Then If Not Intersect(Target, rngF) Is Nothing Then If Target.Row > lRow Then rngF.AutoFilter Field:=Target.Column - lCol, _ Criteria1:=Target.Value ElseIf Target.Row = lRow Then rngF.AutoFilter Field:=Target.Column - lCol End If End If End If exitHandler: Exit Sub End Sub
Download the Sample File
You can download the sample file, with the code for both versions – table and non-table – on my Contextures website. Go to the Excel Sample Files page, and in the Filters section, look for FL0021 – FilterClick.
Pivot table filtering was improved in Excel 2010, when Slicers were introduced. Instead of using the drop down lists in the pivot table headings, you can click on a Slicer, to quickly filter the pivot table.
Slicers take up some room on the worksheet, but you can quickly see what filters have been applied. And, unlike Report Filters, Slicers show you what is available in the other fields, after you have applied a filter.
Slicers for Excel Tables
In Excel 2013, Slicers were enabled for named tables too, so you can filter your data with a single click. They work just like Pivot Table Slicers, and are especially handy if you’re doing a presentation. You can click one of the big Slicer buttons, instead of fumbling through the filter drop downs.
Workaround for Excel 2010 Tables
Slicers don’t work on Excel 2010 tables, but if you’re using that version, there’s good news – AlexJ has developed a workaround.
There are a couple of limitations:
- You need a unique identifier in each table row.
- Changes made manually to the field filters on the table are not reflected on the slicers (you might want to hide the table filters)
Add a Pivot Table and Slicers
From the Excel table’s data, AlexJ built a pivot table, with the ID field in the Row Labels area. Next, he added two Slicers for the pivot table, using the Size and Colour fields.
Then, copy or move those Slicers to the worksheet where the Excel Table is located.
Check for the ID
A named range – DD.Filter – is created, based on column A on the pivot table worksheet. In the Excel Table, a new column is added – xFilter – and a formula in that column checks for the row’s ID in the DD.Filter range.
The formula result is TRUE or FALSE, and only the TRUE rows will show after a Slicer is clicked.
Add Some Event Code
The final step is to add some event code to the pivot table, so it filters the table after a pivot table update. The pivot table update event is fired by the user action of changing a slicer selection.
Here is the code from the SalesPivot worksheet module.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim tbl As ListObject Dim lCol As Long Application.ScreenUpdating = False Set tbl = Worksheets("SalesData").ListObjects("Table1") lCol = tbl.ListColumns("xFilter").Index With tbl If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData .Range.AutoFilter Field:=lCol, Criteria1:="TRUE" End With Application.ScreenUpdating = True Set tbl = Nothing End Sub
Download the Sample File
To download the AlexJ’s sample file, you can visit his page on the Contextures website. In the Filters section, look for
The file is designed for Excel 2010 only, and you’ll have to enable macros to test the file.
You know that it’s time to update a web page, when all of the dates in the examples are 2002.
Yes, that’s what I found when I took a peek at my Advanced Filter – Complex Criteria page. Yikes! Fortunately, the Advanced Filter steps haven’t changed since then, but I added some new screen shots, and updated the sample file.
Find Unmatched Amounts
I’ve also created a video to show the steps for one of the examples – finding unmatched amounts in two fields of a record.
With a simple formula in the criteria range, you can filter all the records where the invoice amount does not equal the amount paid. It’s time to find those customers who have had outstanding amounts since 2002!
Watch the Video
In this video you’ll see how to set up a small criteria range, and enter a formula to check for unmatched amounts. Then, run the Advanced Filter to see the results.
There’s sample data on my Contextures website, and it’s useful for testing formulas and pivot tables in Excel.
On that page, there’s a link to a downloadable Excel file, with the same sample data. Or, you could just copy and paste the data into a workbook.
Now With Interaction
Now, thanks to Microsoft’s Excel Services, you can play with the data in Excel Interactive View. Just click the button above the sample data table, and the interactive view will open up.
Note: I’ve added the same feature to my Excel Book List page.
At the left, the Filters tab is selected, and you can click on any field name, to see its items. Then, click on an item to filter the table.
Sort the Data
In the data table, you can click any of the headings, to sort that column.
To sort it in the opposite order, click the heading again.
Create a Chart
You can also create a quick chart from the data. In the View panel at the right, click one of the chart views. On my Sample Data page, there is a chart view for each of the Value fields – Units, Cost and Total.
Here is the chart for Total values.
Table View With Data Bars
To return to Table view, click Table in the View panel.
The value fields in the table also show Data Bars, like the ones you can add in Excel, with Conditional Formatting.
Save and Print the Data
In the interactive view, you can also save and print the data. To print it, click the Options tab (gear icon), and click Print.
To download the data, click the Do More tab (X icon), and click Download.
Open in Excel Web App
To work with the data online, click on the Do More tab, then click Open in Excel Web App. In there, you can add formulas, and change the data, and use the other features available.
Add an Excel Interactive View on Your Site
To add an Excel interactive view to a table on your site, go to the Excel Mashup website. Click the Learn How link, to go to the page with instructions.
It’s easy to do:
- Select a button style
- Enter a title
- Add your website name
- Click the Generate Code button.
Follow the instructions in the two steps, to copy the code, and paste it into your website source code.
Then click Close, to go back to the web page.
Try the Interactive Excel View
To see how the interactive view works, go to the sample data page on my Contextures website, click the green button to get started.
Thanks Microsoft – this is an awesome new feature!
It seemed simple enough, but counting the AutoFilters on an Excel sheet is a tough job! The answer to "How many worksheet AutoFilters are there?" is "It depends!"
You can read the fascinating (to me!) results below.
The Good Old Days
In the old days, before Excel 2003, you could only have one AutoFilter on a sheet. That’s pretty easy to count – either 1 or 0.
If you point to one of the headings, where the hidden arrow is, the tooltip for that filter appears, so that shows us the AutoFilter is still active.
Count the Worksheet AutoFilters
To count the worksheet AutoFilters, I usually use AutoFilterMode to check if one exists. Recently, Pascal (forum name: p45cal) emailed me, to suggest that checking for a worksheet AutoFilter would be more reliable. Thanks, Pascal, for inspiring this test!
This code tests for a worksheet AutoFilter, by using either AutoFilterMode or AutoFilter:
Sub CountSheetAutoFilters() Dim iARM As Long Dim iAR As Long 'counts all worksheet autofilters 'even if all arrows are hidden If ActiveSheet.AutoFilterMode = True Then iARM = 1 Debug.Print "AutoFilterMode: " & iARM If Not ActiveSheet.AutoFilter Is Nothing Then iAR = 1 Debug.Print "AutoFilter: " & iAR End Sub
When I test that code in the Immediate window, both counting methods show 1 AutoFilter.
Named Table on the Worksheet
What happens if there is a named table on the worksheet, and it has its own AutoFilter? I ran the code again, on the worksheet shown below. It has a named table, and it has an AutoFilter applied, with all the arrows hidden.
When I test the code in the Immediate window, both counting methods show zero AutoFilters. I consider that count correct, because there is a ListObject with an AutoFilter, but no worksheet AutoFilter.
In the screen shot above, you can see that cell A1 is selected – outside of the named table. When I selected cell B1, inside the table, and ran the code, the results were different. AutoFilterMode was still zero, but AutoFilter detected one.
Apparently, Excel is counting the active cell’s table as a worksheet AutoFilter, with the AutoFilter counting method. I’d rather go with the AutoFilterMode’s zero, and count the ListObject AutoFilters separately.
Test With Visible AutoFilter Arrows
Maybe the hidden arrows are affecting the results. To check, I ran code to show all the list AutoFilter arrows, and tested again.
The results were the same as in the previous tests, so visible arrows don’t make a difference.
Multiple Tables on Worksheet
For some final tests, I created a sheet with 3 lists:
- Named Table – no AutoFilter – no arrows
- Named Table – AutoFilter – visible arrows
- Worksheet table – AutoFilter – visible arrows
With a cell in Named Table 1 selected, AutoFilterMode counted one, and AutoFilter counted zero. As in the previous test, the AutoFilter counting method is based on the active cell’s table AutoFilter. It doesn’t detect the AutoFilter in Worksheet Table 3.
With any other cell in the worksheet selected, the results were different – both AutoFilter and AutoFilterMode counted one – the correct count of worksheet AutoFilters.
Counting Worksheet AutoFilters Conclusion
Because ActiveSheet.AutoFilter detects the AutoFilter in the active cell, it could cause a miscount of worksheet AutoFilters.
AutoFilters in Other Excel Versions
After running these tests in Excel 2010, I tested the AutoFilter counting code in Excel 2003, and got the same results.
If you find different results in other versions of Excel, please let me know.
When you turn on the filter in an Excel worksheet list, or if you create a named Excel table, each cell in the heading row automatically shows a drop down arrow. If you don’t need them, here’s how you can hide arrows in Excel AutoFilter.
There is a sample Excel file on the Contextures website that has a list of orders, and sales rep names. You can click a button, and a sheet is created for each sales rep, with that person’s orders.
There is another version of the file, and it creates a sheet for each sales rep name that is visible, after an AutoFilter has been applied.
How It Works
When you click the worksheet button, a macro runs. It sets up a list of sales rep names, then loops through that list, creating a worksheet for each name.
To get the orders onto each sheet, the macro uses an Advanced Filter, setting up a criteria range in cells L1:L2 on the worksheet.
In the screen shot below, the first name from the list, Gill, has been entered into the criteria range.
Advanced Filter Criteria Problems
The filter works well, unless there is another name that begins with the same characters as one of the full names. In the screen shot below, the orders for Giller have been included on the Gill orders sheet.
A Giller sheet was also created, and it has the correct list of orders for Giller.
Why did this happen? The Advanced Filter assumes that we are looking for anything that BEGINS with "Gill", so it includes Giller. It’s as if there is an invisible "*" wildcard character at the end of the Sales Rep name.
To get an exact match only, we can change the criteria cell, so it uses an equal sign before the name. To do this manually, we would enter this formula in cell L2:
="=" & "Gill"
Fix the Problem in the VBA Code
In the original Excel VBA code, the sales rep’s name is entered into cell L2.
.Range("L2").Value = c.Value
I revised the code, so it includes the equal sign and quote marks, just as the manually-entered formula would have.
.Range("L2").Value = "=""="" & " & Chr(34) & c.Value & Chr(34)
Now, when you run the macro, only the exact matches for a sales rep name will appear on the list (upper and lower case will not affect the results).
Download the Sample File
To test the Advance Filter macros, you can download the sample files from the Contextures website.
On the Sample Excel Files page, go to the Filters section. Then, look for these files:
- FL0013 – Create New Sheets from Filtered List
- FL0004 – Create New Sheets from List
Advanced Filter Video
To see the steps for a manual Advanced Filter to a different worksheet, you can watch this short Excel video tutorial.
Or watch on YouTube: Automatically Copy Excel Data to Other Sheet
Someone emailed me for help with an Excel AutoFilter last week. He wanted to type the criteria onto a worksheet, and have the filtered results shown automatically. There are some built-in options for filtering by text, and keep reading to see a worksheet version that Roger Govier designed.
AutoFilter Search in Excel 2010
There is a new feature in Excel 2010 that provides easy searching, though not on the worksheet. You can see an example here, for the Excel 2010 AutoFilter search feature.
AutoFilter Search in Earlier Versions
In earlier versions of Excel, you can filter for text, but it’s a bit more work. In Excel 2007 you can use a text filter, which opens the Custom AutoFilter dialog box
In Excel 2003, use the Custom option on the AutoFilter drop down.
Roger Govier’s FastFilter
If you’d like to enter the AutoFilter criteria on the worksheet, instead of a search box or dialog box, you can use Roger Govier’s FastFilter sample Excel file.
He has set up a table on the worksheet, with an empty row above the table. In that row, you can type one or more criteria, and when you press the Enter key, the table is automatically filtered.
For a simple filter, type an exact match for a value, and press Enter. In the screen shot below, the table is showing only the items from category 2.
You can also use operators, and in the next screen shot I’ve added a ">20" criterion in the Unit Price column.
Use WildCard Characters
If you’re trying to find a specific string of characters in a column, you can use the * and ? wildcard characters. In the next screen shot, I used *b* in the product name column, to find any products that have a "b" somewhere in the name.
Use Multiple Criteria in a Column
You can use special characters for OR (^^) and AND (^), to combine multiple criteria in a single heading cell. In the Category ID column, I used the ^^ characters to find category 2 OR 4. In the Unit Price column, the ^ character limits the price to >20 AND <35.
Remove the Criteria
To clear the filter from a column, just click on the criteria cell, and press the Delete key on your keyboard. If you want to clear all the filters, select all the criteria cells, and press Delete.
Download the Sample File
To download the sample file, you can visit Roger’s Sample Files page on the Contextures website. In the Filters section, look for FL0001 – Fast Filter. There is a download link for the FastFilter zipped file.
The file is in Excel 2003 format, and will work in later versions too. After you open the file, enable macros, so you can test the automatic filter feature.