Study smart | Excel super-stars
How to use Excel Filters
Filters are an ACE function that can be applied to a spreadsheet to ‘temporarily’ hide data that you don’t want to see
by Traci Williams | excelace.co.uk
Key stats
row limit
Excel supports filtering on datasets with up to 1,048,576 rows in a single worksheet. This high row limit allows users to analyse and manage large amounts of data efficiently.
unique items
AutoFilter in Excel can display up to 10,000 unique items in the filter dropdown list. This is particularly important when working with extensive datasets, as it affects the user's ability to select and filter data based on unique values.
characters limit
Each cell in an Excel worksheet has a limit of 32,767 characters, and this limit also applies to text used in filter criteria. This means when setting up filters, especially when using text filters, the criteria must not exceed this character count.
Introduction
Filters are an ACE function that can be applied to a spreadsheet to ‘temporarily’ hide data that you don’t want to see, enabling you to drill down into and focus on specific data.
The absolute beauty of filters is that they are temporary, and your data can be returned to its original state when required.
Filters are super simple to apply and are so helpful to use with any size of data set.
There are two types of Filter: Autofilter (simpler & quicker) & Advanced Filter (used for more complex or sophisticated data manipulation)
Using Autofilter
With the data set below, we can add Filters so that we can just look at Sales for an individual Sales Region, Product Type or Month.
To switch Filters on:
1) Highlight header row
2) Select Data ribbon
3) Select Filter
A filter arrow will appear at the top of every column:
Clicking on any arrow will display a list of ALL of the items available in that column in alphabetical (or numerical, or date) order.
This is the list that appears for the ‘Product Type’ column:
Unticking the ‘Select All’ box, will unselect all items, then we can tick the specific box(es), for the items we want to see i.e. Groceries.
There are 4 ways to see that a Filter has been applied to this data:
1) The arrow at the top of the ‘‘Product Type’ column has changed to be a funnel
2) The row numbers are no longer in consecutive order, there are gaps where the rows are hidden.
3) The colour of the row numbers has changed to BLUE (this only ever happens when a filter has been applied)
4) At the very bottom of the sheet, you can see ‘7 of 20 records found’. This is an actual count of the total number of records (20) and the number that has been filtered (7), without the need for using any formulas.
Instead of ticking individual items in a filter list (this can be cumbersome if you have lots of unique items in a column), the Search box can be used. Here, you can enter part (or all) of what you are searching for, and the list of items will reduce to only include the items that contain the search term. This feature is most effectively used with text as opposed to numbers or dates.
On cells containing text, above the list you will see ‘Text Options’, and the sub list will appear alongside it, providing alternative options for searching text:
These are the options available on a column containing numbers:
These are the options available on a column containing dates:
Excel automatically groups individual Dates into Months & Years, for ease of selection. Each Month & Year (above) has a + or – sign to the left of it, enabling them to be Expanded or Collapsed as required. This provides the ability to select a specific date, but also (un)select an entire Month by ticking one box (instead of each individual date).
Unapply Filters
To remove an applied filter, you can simply click back onto the funnel (at the top of a column), and tick ‘Select All’, and your data will be back to its original format.
Bonus Tip: You can also go to the ‘Data’ ribbon and select ‘Clear’ (next to the filter funnel), and this will unapply filters from ALL columns.
Advanced Filtering Techniques
If you have more complex filtering requirements, or want to extract data from the source data, you can use Advanced Filtering.
Here you can set up your filter criteria in separate cells:
In the yellow cells, I have included the column header (row 1) and the criteria I want to filter by (row 2).
Go to Data Ribbon and select ‘Advanced’:
This screen will appear:
Here you have the option to filter data in-place, or copy it to another sheet and also filter Unique records only. These are all options that are not available with autofilter.
Simply enter the details of the List & Criteria range (as below), then click OK:
The data will be filtered and copied as below:
If the Criteria in cell F2 is amended, unfortunately the Advanced Filter will NOT automatically update, it will need to be re-run. The data in cell H1 will also need to be manually removed first too.
Common Problems & Solutions
Conclusion
AutoFilter is a really ACE tool, due to its simplicity and flexibility. It simplifies data analysis by allowing quick access to relevant information, making it an essential feature for efficient data management and decision-making.
Advanced Filter is not used as frequently as AutoFilter due to its higher complexity and manual setup requirements, but it does still have it’s place.
Quiz Time
Test Your Knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
What is the maximum number of unique items that can be displayed in the AutoFilter dropdown list in Excel?
2
Which of the following is NOT a feature of Excel’s Advanced Filter?
3
How can you apply a filter to multiple columns in Excel?
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).