An Excel slicer is a set of clickable buttons that filters a table or PivotTable by selected values. In Excel for Microsoft 365 on Windows, select a table and choose Table Design > Insert Slicer, or select a PivotTable and choose PivotTable Analyze > Insert Slicer.
What an Excel slicer is
A slicer is a visual alternative to choosing filter options from a column’s drop-down menu. If a sales table has columns for Region, Product, and Month, a Region slicer can show buttons such as East, West, and Central. Clicking West filters the table to matching rows; clicking East instead changes the selection. The records are not deleted or rewritten.
Slicers are useful when a workbook is meant to be explored by someone who may not know how to operate column filters. The available choices remain visible, and a reader can often understand the current filter just by looking at which buttons are selected. Slicers can also make dashboards easier to use, since they put common choices beside the report rather than hiding them in a menu.
Excel provides slicers for Excel tables and PivotTables. A slicer is not a general-purpose filter for any arbitrary cell range: for a regular range, first turn the data into a table or create a PivotTable. A slicer filters data based on the field it represents, such as Department or Status.
How slicers filter data
Each slicer is tied to a field in its source table or PivotTable. Its buttons represent the distinct values found in that field. Selecting a value filters the associated table or report, while selecting multiple values keeps records matching any of the selected values. Other slicers connected to the same report can further narrow the result.
For example, imagine a table of 500 orders with Region, Product, and Salesperson columns. Choosing West in a Region slicer and a particular product in a Product slicer displays orders that meet both conditions. Within the Product slicer, selecting two products displays records for either product, but those records still need to meet the Region selection. Excel updates the filtered display as selections change.
A slicer changes what is displayed, not the source values. With a table, filtered-out rows are hidden in the filtered view. This distinction matters for calculations: a regular SUM over a range can still include hidden rows, while functions designed to respond to filtered or hidden rows, such as SUBTOTAL, can be more appropriate for a visible-rows total. A PivotTable’s values update according to its filters.
Add a slicer to an Excel table
For a normal data list, converting the range to a table first makes the data easier to filter and expand. Select a cell in the list and choose Insert > Table, confirm the range, and indicate whether the table has headers. Then add a slicer:
- Click a cell inside the table. The Table Design tab appears on the ribbon.
- Choose Table Design > Insert Slicer.
- In the Insert Slicers dialog, select one or more column names, such as Region or Status, and choose OK.
- Move and resize the slicer so it does not cover the table, then click a button to filter the rows.
Each slicer corresponds to one column, so selecting both Region and Status in the dialog creates two separate controls. You can format and position them independently. If the table grows by gaining rows, Excel tables generally extend to include data entered immediately below them; check the table range if new records do not appear in the slicer’s choices.
Add a slicer to a PivotTable
PivotTable slicers filter a summarized report rather than simply hiding source-table rows. Select a cell in the PivotTable, choose PivotTable Analyze > Insert Slicer, select the fields to filter, and choose OK. For a date field, you can also use PivotTable Analyze > Insert Timeline when you want a visual date-range control. A timeline is a separate feature from an ordinary slicer and requires a suitable date field in the PivotTable.
After insertion, the slicer can be placed beside the PivotTable or on a dashboard sheet. Use its buttons to filter the report. A slicer may show values that currently have no matching records under the other report filters; Excel’s display and formatting settings determine how unavailable items appear. If the source data has changed, refresh the PivotTable so the report and its filter choices reflect the latest records. Use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh.
Use slicer controls and connect reports
Click a button to apply a filter. To select more than one item, use the slicer’s Multi-Select control if it is displayed, or hold Ctrl while clicking additional items on Windows. To remove the slicer’s selection, click the Clear Filter icon near the slicer’s upper-right corner; this clears that slicer’s filter, not filters applied by other controls.
For PivotTables, one slicer can control more than one report when those PivotTables are compatible with the same source and PivotTable cache or connection. Select the slicer, open the Slicer tab, and choose Report Connections. In some Excel versions the command is labelled PivotTable Connections. Tick the PivotTables to control and confirm. If a PivotTable is missing from the list, it may use a different source or cache; recreating reports from a shared source may be necessary.
Excel table slicers work with their associated table, rather than acting as universal controls for unrelated tables. If you need a coordinated dashboard, PivotTables with shared connections are usually a better fit than several independent tables. Slicer formatting options let you change the style and, where available, the number of columns of buttons. These are presentation choices; they do not change the filter logic.
When slicers are useful—and when they are not
Slicers are especially useful for dashboards, recurring status reports, and workbooks used by people who need a few clear filtering choices. A manager might click a Department button to view one team’s results, or a project coordinator might choose Open in a Status slicer to focus on unfinished work. Unlike a hidden filter menu, the slicer makes the available categories and current selection easy to see.
They are less suitable when there are hundreds or thousands of distinct values in a field. A slicer for a column of unique invoice numbers could become too large to navigate. A drop-down filter, a search-based approach, or a PivotTable may work better for high-cardinality fields. Slicers also occupy worksheet space, so adding one for every column can make a report crowded. Choose fields that answer useful questions rather than exposing every possible filter.
For time-based PivotTable exploration, a timeline may be easier to use than a slicer with many separate dates. For detailed filtering on an ordinary table, the built-in column filters may be quicker. These approaches are alternatives rather than replacements in every case: choose according to the number of values, the workbook layout, and how the reader needs to interact with the data.
Common slicer problems
- The Insert Slicer command is unavailable: Click inside a table or PivotTable first. A plain range does not expose the relevant contextual commands. Convert the range to a table or create a PivotTable before trying again.
- New values are missing: Check whether the new rows are included in the table or PivotTable source. For a PivotTable, refresh it after changing the source data. A new category will not appear in a slicer until its source is recognized.
- A slicer does not control a second PivotTable: Open Report Connections or PivotTable Connections. The reports must have a compatible shared source or connection. A similar-looking field name alone is not enough to make two PivotTables share a slicer.
- Every item looks unavailable: Another filter may leave no matching records, or the report may need refreshing. Clear filters one at a time to identify the cause, and confirm that the source contains the expected values.
- A table total does not change as expected: Filtering a table hides rows; it does not remove them from every formula. Review the formula and use a calculation designed to account for filtered rows if that is the intended result.
On Mac, current Excel versions include slicer features, but ribbon labels and available commands can vary by version. Excel for the web may not expose the full set of desktop authoring and connection controls. If a workbook’s slicer options are missing, open the file in an up-to-date desktop version of Excel and check the relevant Table Design, PivotTable Analyze, or Slicer tab.
Frequently Asked Questions
Can I use a slicer without a PivotTable?
Yes. Convert the data range to an Excel table, select a cell in it, and choose Table Design > Insert Slicer. Table slicers filter the table’s rows without requiring a PivotTable.
Can one slicer control multiple PivotTables?
Yes, if the PivotTables use a compatible shared source, cache, or connection. Select the slicer and use Slicer > Report Connections, or PivotTable Connections in versions with that label, to select the reports it should filter.
Does a slicer change or delete my source data?
No. It filters what is displayed in the table or PivotTable. The underlying records remain in the workbook, so clearing the filter restores the broader view.
Can I use a slicer to filter dates?
Yes. A date column can have a regular slicer with date values, though that may be awkward when there are many dates. For a PivotTable with a suitable date field, Insert Timeline provides a separate control for selecting date periods or ranges.
Why can’t I see a value in the slicer?
The value may not be in the table or PivotTable source, or a PivotTable may need refreshing. Check the source range and refresh the report. Also clear other filters to make sure they are not excluding the records associated with that value.
Related
- No Hyperlink in Excel? Remove or Prevent Links
- Excel File Corrupted? How to Check, Repair, and Recover
- Named Range in Excel: Definition, Uses, and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Remove Formatting in Word 365: A Step-by-Step GuideTo remove formatting in Word 365, select the text and choose Home > Clear All Formatting to strip…
- Office Contact List Template Excel: Build a Useful DirectoryAn office contact list template in Excel is a structured worksheet for storing coworkers’ names, roles, work contact…
- How to Exit Group Edit Mode in Excel and Ungroup SheetsTo exit group edit mode in Excel, click a worksheet tab that is not selected, or right-click a…
- Excel Split Cell Shortcut: The Fastest Ways to Separate DataIn Excel for Microsoft 365 on Windows, there is no single dedicated key that splits a cell; the…
- Google Sheets Merge Cells Shortcut and Menu OptionsGoogle Sheets does not have a dedicated keyboard shortcut that directly merges selected cells. In a browser, open…
- Microsoft Excel Goal Seek: How It Works and How to Use ItMicrosoft Excel Goal Seek works backward from a result you want: provide a formula cell, a target value,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.