Intelligent filters in Excel let you connect different data sources and view existing and missing values. This helps you understand your data more clearly. A powerful tool for this is Slicers.
Filters Can Be Hard to Use with Many Columns
When working with many columns in Excel, regular filters can be difficult to use. Here’s why:
- Scrolling across many columns is time-consuming and tiring.
- Finding specific columns in a large sheet can be confusing.
- Filters may hide data you need to compare or analyze.
- Complex filters can slow down Excel, making it harder to work efficiently.
The solution for managing complex filters in Excel is to use Slicers.
Slicers make it easy to filter data with just a click. They work well in Pivot Tables or Tables because they show which filters are active. This helps you explore and analyze data without scrolling through many columns.
How to Insert Slicers
- Insert your data in a Table (Insert > Table)
- Select a cell in your table
- Open the Insert > Slicer menu
- Excel knows each column's header since we added data in a Table. The Slicer dialog shows these headers as slicer options.
- Select the slicers you want to insert (for this example, select all).
How to Use Slicers as Intelligent Filters
No extra steps are needed – just select items in the slicer 😮👍
- When you choose a value in a slicer, the table are filtered automatically.
- If a selection doesn’t include all values, those values will fade.
- For example, if we choose size 12.5 and color Yellow, only brands Puma and Reebok will appear.
- This shows that Adidas and Nike don’t have size 12.5 in Yellow.
More Examples
In the next examples, we use intelligent filters to highlight Brand, Size, Color, Shop
- When a value is missing in Size and Color, it fades instead of reordering. Learn more here.
Question #1: What are the missing sizes for the Black Reebok?
- Answer: 6, 8, 10
#2: What are the missing colors for the Reebok, size 8?
- Answer: Black, Blue, White
#3: What are the missing sizes for the Black Adidas in Shop A?
Here, we have 3 criteria: Brand, Color, and Shop
- Answer: 6, 7, 8, 8.5, 9.5, 12.5
