From Chaos to Clarity: Mastering the Art of Excel Filtering

Overview

Filtering is a common and crucial tool in Excel for finding, analyzing, verifying, and updating your data. Even the most novice Excel users generally know how to use filters.  But if that’s all you’re doing, you’re really missing out!  In this article, we’ll discuss several different ways you can filter in Excel, as well as some gotchas, tips, tricks, and macros that make filters much quicker and easier to use.

How do you use filtering?

• Filter – aka AutoFilter, this is the most basic and common way to use filters.  Use it to search for text (contains, begins, or ends with), number/date ranges and other operators, fill colors (fixed or conditional formatting), and use multiple criteria with the custom filter. It is accessible from the Home tab 🖼️ and the Data tab 🖼️.
• Advanced Filter 🖼️ – this dialog box (located in the Data tab drop-down) offers more filtering flexibility by being able to handle more criteria conditions. It can copy the filtered data to a separate location, and because the criteria is referenced in cells, it can be based on formulas for powerful dynamic and conditional results.
• Slicers 🖼️ – these are a visual representation of the filter values in place. They provide quick one-click filtering and work very well in dashboards.
• FILTER Function 🖼️ – this is a dynamic array function that can filter data within your formulas and spill across multiple cells, and dynamically update as the referenced data changes. It can handle complex filtering logic, and is often combined with other functions, such as UNIQUE, SORT, SUM, COUNT, and others.
• PivotTables/PivotCharts 🖼️ – filters are available for the fields referenced in your PivotTables and PivotCharts. This helps you look at precise data when aggregated, identify trends and patterns, and make your PivotTables and PivotCharts more interactive and useful.
• Power Query 🖼️ – filtering is often an essential part of your query steps. It increases performance, reduces the data and complexity brought into Excel, and filter steps can be reused with other query logic.

Tips & tricks

Here are several tips to help you use filters more easily and creatively:

• You can also use wildcards in the search terms: * (asterisk) for multiple characters and ? (question mark) for single characters.
• Use the “Add current selection to filter” option in the filter drop-down box to accumulate multiple filter searches.
• Use NOT operators (does not contain, begin with, or end with, equal) to exclude data from your filtered data set.
• Use the status bar at the bottom-left of the Excel window to determine how many rows are displayed when one or more filters are in place.  This can be a great completeness check.
• Filter quickly to your selected value or fill color by using Right-click > Filter (the menu key also mimics the right-click action).

Gotchas

Be aware of several gotchas related to filters:

• Extensive calculations can cause filters to be slow to update (among other things). Pasting values can help. If you go that route, make sure to save as a separate file so you retain all your hard work in building formulas!
• Ensure your entire data set is selected when applying filter criteria. Setting a table for your data set is a great way to ensure the filter range updates automatically.
• Numbers formatted as text, or inconsistent date formats may not filter correctly. For numbers formatted as text, a quick workaround is to multiply by 1 in a separate column.
• Merge cells will cause filters not to work properly (as well as create all kinds of other issues). Press practice is not to use them in a data set, or anything that will be filtered, sorted, or referenced.
• You can’t use filters with multiple sheets selected (filter options become greyed out). See the macros below for two great workarounds!

Macros That Help

The XLEV8 Excel Add-in contains several macros that help you work more efficiently with filters, offering shortcuts and bulk automation:

• Filter Picker – this provides several shortcuts for applying filters, clearing them, and even saving and reusing your favorite filter/sort criteria.
• Bulk Filter – this lets you apply different filters to multiple sheets/columns in one bulk, reusable step.
• Filter All Sheets – this lets you apply the same filter criteria for one or more columns across multiple identically-structured sheets.
• Show/Hide Filter Arrows – this allows you to hide/re-show filter arrows for one or more columns, which can help clean up screenshots of your data.

Summary

If you often work in Excel files and use filtering, it’s worth knowing all the ins and outs, well beyond the standard filter function.  While the standard filter is good for quick and dirty analysis, it’s not as flexible, capable, visual, or dynamic as other options.  With the amount of data we consume continuing to grow, filtering effectively and efficiently is a huge benefit.  Make sure to take advantage of the tips, tricks, gotchas, and macros above to filter like a pro!

How have you leveraged filters in your daily Excel work?  Any additional tips or tricks to share?  Let us know in the comments below!