In Excel, a PivotTable slicer is a set of clickable buttons that filters a PivotTable, and often its associated PivotChart, by a field such as region, product, or year. To add one in Excel for Microsoft 365 on Windows, select the PivotTable, choose PivotTable Analyze > Insert Slicer, select the fields, and click OK.
What a PivotTable slicer is used for
A slicer gives people a visible way to filter summarized data without opening a field’s drop-down menu. Each button represents an item in that field. Selecting East, for example, filters the PivotTable to show records assigned to the East region; selecting a product can narrow the report further. The PivotTable recalculates its displayed summary to reflect the selected items.
Slicers are especially useful in reports and dashboards that other people need to explore. A viewer can see which filter choices are available and which are active, rather than guessing from a small filter icon. Slicers can also make a report easier to use on a screen or in a meeting, where clicking a large labeled button is more straightforward than navigating a long list of field names.
A slicer does not delete, edit, or rearrange the original source records. It filters what the connected PivotTable displays. Clearing the slicer selection restores the broader view, subject to any other filters that remain in place.
How slicers work with PivotTables
A slicer is connected to a PivotTable field. Excel uses the field’s available items as buttons, and the slicer sends the chosen filter to its connected report. For instance, a sales PivotTable might summarize revenue by month and salesperson. A Region slicer can limit the summary to selected regions while keeping the existing rows, columns, and value calculations intact.
Slicers show the state of the filter visually. Items that are included remain selectable; items excluded by the current selection appear dimmed or otherwise unavailable, depending on the report and Excel version. If the PivotTable has more than one filter, the slicer only represents its own field. A visible selection in one slicer does not tell you whether a separate report filter or another slicer is also limiting the results.
When you refresh a PivotTable, its source data is re-read and the report can reflect new or changed records. New field items may then appear in the slicer. If a source item has been removed, it may still be retained in the PivotTable field’s item history depending on the PivotTable settings; if an unexpected old button remains, check the field’s data-retention settings and refresh the report.
How to insert and use a slicer
- Click inside the PivotTable you want to filter. The PivotTable-specific ribbon tabs should appear.
- Choose PivotTable Analyze > Insert Slicer. In the dialog box, select one or more fields, such as Region, Category, or Salesperson, and choose OK.
- Move or resize the slicer so its buttons are easy to read. Click a button to filter the PivotTable to that item.
- To choose several items, use the slicer’s Multi-Select button, if shown, or hold Ctrl while clicking additional items in Windows. Click selected items again to remove them from the selection.
- To remove the slicer’s filter, click the Clear Filter icon at the top-right of the slicer. This clears that slicer’s selection, not necessarily other filters in the report.
Button layout depends on the slicer’s size and settings. If the field contains many items, enlarge the slicer or adjust the number of columns so labels are readable. You can also scroll within the slicer to reach items that do not currently fit.
Connecting one slicer to multiple reports
A single slicer can control more than one PivotTable when the reports are compatible. Typically, the PivotTables need to share the same underlying PivotTable cache, or be built from the same Data Model. This is useful for dashboards with separate summaries—such as revenue by month and order count by region—that should respond to the same Region selection.
To check connections, select the slicer and open the Slicer tab, then choose Report Connections. Select the PivotTables that should respond and confirm. If a PivotTable is missing from the list, it may not share the required source or cache with the slicer. In that case, create compatible reports from the same source, or use a shared Data Model where appropriate, rather than assuming every PivotTable in the workbook can be linked.
Before sharing a dashboard, test each slicer against every connected report. A field can have different meanings or item lists in different data sources, and an unconnected PivotTable will not change when a slicer is clicked. Clear the slicer and check that all intended reports return to their expected unfiltered state.
Choosing slicers, report filters, and timelines
Slicers are not the only way to filter a PivotTable. A Report Filter placed in the PivotTable layout takes less worksheet space and can be suitable when a user needs to choose one item occasionally. The trade-off is that its selection is less prominent than a slicer and may be easier to overlook.
- Use a slicer when people need a visible, quick way to explore one or more categories, or when filter state should be obvious on a dashboard.
- Use a Report Filter when space is limited or the filter is a secondary control that does not need a large on-sheet interface.
- Use a timeline for a date field when users need to filter by periods such as years, quarters, months, or days. Select the PivotTable and choose PivotTable Analyze > Insert Timeline. Timelines are designed for date fields, whereas ordinary slicers can represent categorical fields.
A slicer can consume considerable worksheet space if a field has many distinct values. For hundreds of customer names or transaction IDs, a report filter or a different report design may be more practical. Slicers work best with meaningful categories that users can recognize and select.
Formatting and dashboard design
Select a slicer to access its Slicer tab. Excel provides style choices and controls for its height, width, button dimensions, and column count. Use a consistent style for controls that belong to the same dashboard, but keep enough contrast to distinguish selected items from unselected ones. Long labels may need a wider slicer or more button columns.
Place slicers near the reports they control, and label the report or dashboard so users understand the numbers being filtered. If several slicers appear together, arrange them in a predictable order—for example, Year, Region, then Product. Avoid squeezing a large slicer beside a chart if doing so makes either difficult to read. A slicer is an interactive control, not a chart or a permanent display of the underlying records.
When the report is intended for printing, remember that slicers are worksheet objects and may affect the printed layout. Preview the report before printing, and consider whether a static summary or a separate print layout would be clearer than an interactive dashboard.
Preparing source data for reliable slicers
Slicer choices come from the PivotTable’s source field, so clean source data makes slicers easier to use. Use one clear header per column, keep each record on a single row, and avoid mixing different kinds of values in one field. For example, standardize region labels so East, EAST, and E. do not represent the same place as three separate choices.
Blank cells can create a blank item in a slicer, and inconsistent spelling can create near-duplicate buttons. Check the source data when a slicer contains unexpected items. If you add records outside the original source range, update the PivotTable’s data source or use a suitably expanding Excel Table as the source, then refresh the PivotTable. A refresh cannot include records that are outside the defined source range.
It is also worth distinguishing a slicer from a filter on the source worksheet. Filtering a source table does not necessarily filter a PivotTable built from that table; PivotTables have their own report filters and slicer connections. Use the slicer attached to the PivotTable when you want to control the summarized report.
Common slicer problems
- The slicer does not affect a PivotTable. Select the slicer and inspect Report Connections. Confirm the intended PivotTable is checked and compatible with the slicer’s source or cache.
- The slicer is missing a new category. Confirm the new record is inside the PivotTable source range, then refresh the PivotTable. If necessary, change the source range or convert the source data to an Excel Table that can expand.
- A category appears more than once. Look for extra spaces, inconsistent spelling, or different underlying values in the source column. Correct the source and refresh.
- There is a blank button. Check whether the source field contains blank cells or whether the PivotTable retains an old item. Correct the source or review the PivotTable’s retained-item setting, then refresh.
- Buttons are too small or crowded. Resize the slicer, adjust its columns and button dimensions on the Slicer tab, or choose a field with fewer distinct items.
Sometimes a slicer appears to produce an unexpected total because another filter remains active or because the PivotTable has not been refreshed after source changes. Clear the slicer, inspect the PivotTable’s field filters, and refresh before drawing conclusions from the displayed figures.
Excel on Mac and in a browser
The steps above describe Excel for Microsoft 365 on Windows. Excel for Mac also supports PivotTable slicers, though ribbon labels and the location of some controls can differ by release. Select the PivotTable and look for the PivotTable Analyze tab and its Insert Slicer command; select the slicer to find its formatting and connection controls. Windows Ctrl-click instructions do not apply to every Mac setup; use the slicer’s Multi-Select control when available, or use the Mac modifier indicated by the application.
Excel for the web can display and interact with many existing workbook slicers, but its authoring and formatting options may not match the desktop application. If a command is unavailable in the browser, open the workbook in the desktop version of Excel to create or configure the slicer. Workbook features and available controls can vary with the current app version and the type of PivotTable source.
Frequently Asked Questions
What is the main purpose of a slicer in Excel?
Its main purpose is to filter a PivotTable or compatible report using visible, clickable buttons. It makes selections easier to find and shows users which items are included.
Can one slicer control multiple PivotTables?
Yes, if the PivotTables are compatible, commonly because they share a PivotTable cache or use the same Data Model. Select the slicer, choose Slicer > Report Connections, and check the reports it should control.
How do I clear a PivotTable slicer?
Click the Clear Filter icon at the top-right of the slicer. This resets that slicer only; other slicers, report filters, or field filters may still limit the PivotTable.
What is the difference between a slicer and a timeline?
A slicer filters a field through buttons and is commonly used for categories such as product or region. A timeline is a date-oriented control for selecting date periods such as months, quarters, or years.
Why is my slicer not showing new data?
Check that the new records are within the PivotTable’s source range, then refresh the PivotTable. If the records are outside the source, expand or change the source before refreshing.
Related
- Excel Not Responding: How to Save an Unsaved Workbook
- Free 1099 Expense Tracker Spreadsheet: Build One in Excel
- Food Pantry Inventory Spreadsheet: Setup, Tracking & Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pie Chart With Other Category: How to Group SlicesTo make an Excel pie chart with an Other category, select the data and choose Insert > Insert…
- How to Build a Thermometer Chart in Excel: Step by StepTo build a thermometer chart in Excel, put the completed amount and the amount remaining in a two-series…
- Google Sheets Jira Dashboard: Setup, Sync, and ReportingYes. You can build a Jira dashboard in Google Sheets by connecting a spreadsheet to Jira with an…
- Excel Custom Combo Chart: How to Build and Format OneAn Excel custom combo chart combines two or more chart types—such as columns and a line—in one chart,…
- Pareto Chart Data in Excel: Examples, Setup, and TipsA Pareto chart in Excel uses categories and their counts or costs, sorted from largest to smallest, to…
- Excel Pivot Table Data Source Reference Error?If Excel reports that a PivotTable data source reference is not valid, check that the source is a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.