Slicer vs Filter in Excel: Which Is Better for Reports?

Slicer vs filter comes down to who uses the report. A slicer is a panel of buttons that anyone can click, and it always shows what is selected. A filter is the drop-down arrow in a header, and it can do more, such as "greater than 500", on any range. This comparison is for people who build reports for a manager, a team or a job test and need to choose the right control.

Slicer vs filter at a glance#

Slicers win on ease of use and visibility. Filters win on flexibility.

What a filter does#

A filter hides the rows that don't match what you pick, on any block of data with headers. Click a cell in the data and press Ctrl+Shift+L on Windows (Cmd+Shift+F on a Mac), or go to Data > Filter. A drop-down arrow appears in every header.

From that arrow you can tick items, or use Number Filters, Text Filters and Date Filters for conditions: greater than 500, begins with "Lon", this month, top 10. The complete guide to filtering data in Excel covers each type.

The weakness is visibility. A filtered column shows only a small funnel icon, and the row numbers turn blue. A reader can easily miss that rows are hidden and think the totals are wrong.

What a slicer does#

A slicer is a box of buttons, one for each item in a field, that filters a table or a pivot table when you click. Selected items are coloured, so anyone can see the current view without opening a menu.

Slicers need a structure under them: a pivot table or an Excel table created with Ctrl+T. They can't connect to a plain range. One slicer can also filter several pivot tables at once, which is why they are the standard control on dashboards. The step-by-step guide to inserting and setting up slicers shows how to add one, select several items and connect it to other pivots, so this article focuses on the choice.

The same task, solved both ways#

Here is a small sales table in A1:D7:

The task: show only North sales made Online.

With filters: turn on the filter, open the Region arrow and tick only North, then open the Channel arrow and tick only Online. Rows 2 and 6 stay visible.

With slicers: press Ctrl+T to make the range a table, go to Table Design > Insert Slicer, and tick Region and Channel. Click North in one slicer and Online in the other. The same two rows stay visible.

Either way, a normal SUM still adds every row, hidden or not. Use SUBTOTAL to total only the visible rows:

=SUBTOTAL(9,D2:D7)

With the filter or slicers on, this returns 8,800 (4,200 + 4,600). With nothing selected, it returns 21,900. The 9 means "sum". In a pivot table you don't need this, because the pivot's own totals already follow the slicer.

Now try a condition: "show months with sales over 3,500". The filter does it in one step with Number Filters > Greater Than. A slicer can't, because it only selects items. You would need a helper column, such as =IF(D2>3500,"Over 3,500","3,500 or less"), and a slicer on that column.

When to use a slicer#

Use a slicer when the person clicking is not the person who built the file:

• Dashboards and shared reports. A manager can click "North" without knowing where the filter arrow is.
• Several pivot tables from the same data. One slicer keeps them all in step.
• Presentations and screenshots. The selected buttons document the view, so nobody asks "is this all regions?"
• Short lists of items. Regions, product lines, years and statuses fit well. A field with 500 customer names makes a slicer that is hard to scroll.

The beginner's guide to pivot tables shows how to build the pivot a slicer usually sits on.

When to use a filter#

Use a filter for your own analysis and for anything that needs a rule:

• Conditions. Greater than, between two dates, contains a word, top 10, above average.
• Any range. No need to create a table or a pivot first.
• Cleaning data. Filter blanks, find odd spellings, then fix or delete the visible rows.
• Long lists. The search box in the filter drop-down finds one name among thousands.
• No room on the sheet. Filters take no space.

Inside a pivot table, the choice is between a slicer and the pivot's own filters: the Filters area at the top, and the drop-downs on the row and column labels. Those built-in pivot filters also accept conditions, such as a Value Filter that keeps only regions with sales over 10,000, or Top 10 to show the five best products. A slicer can't do either. A common pattern is a slicer for the reader's main choice, such as Year, plus a Top 10 value filter set by the builder.

If you want the filtered rows as a live copy somewhere else, the FILTER function is a third option. The comparison of the FILTER function vs AutoFilter explains that choice.

Common mistakes with slicers and filters#

These cause most "the numbers are wrong" moments:

1. Forgetting a filter is on. A hidden filter makes totals look too small. Check for funnel icons and blue row numbers, or use Data > Clear.
2. Using SUM on filtered data. SUM includes hidden rows. Use SUBTOTAL or AGGREGATE for visible-only totals.
3. Expecting a slicer on a plain range. Insert Slicer is greyed out until the data is a table or pivot.
4. Mixing slicers and filters without noticing. They combine as AND. Clearing the slicer doesn't clear the filter on another column.
5. Leaving items unselected before sharing. Click Clear Filter on each slicer (Alt+C on Windows when the slicer is selected) if the report should open showing everything.

Our recommendation#

Use slicers for any report another person will open and click: dashboards, monthly packs, anything built on pivot tables. They are clearer, they can't hide silently, and one slicer can drive several pivots.

Use filters for your own day-to-day work: checking data, cleaning it, and any question with a condition like "over 500" or "contains". They work on any range and cost no space.

Many good reports use both: slicers for the two or three main choices a reader makes, and filters left for the analyst.

Practise it#

The best way to feel the difference is to filter a table yourself. Practise filtering a table in a live spreadsheet: you'll filter by items and conditions and check totals of visible rows. Then practise pivot tables step by step to build the kind of report slicers are made for. Or try the exercise on this page first.