To create a slicer in Excel for Microsoft 365 on Windows, select a cell in an Excel table or PivotTable, choose the relevant Table Design > Insert Slicer or PivotTable Analyze > Insert Slicer command, select the fields to filter, and choose OK. Click the buttons in the slicer to filter the data; use the clear-filter icon to show everything again.
What an Excel slicer does
A slicer is a visual filter: it displays a field’s available values as clickable buttons. For example, a sales report might have buttons for North, South, East, and West. Selecting North filters the connected table or PivotTable to show records for that region. Unlike a drop-down filter tucked into a column heading, a slicer remains visible beside the data and makes the current selection easier to spot.
Slicers can filter Excel tables and PivotTables, but the setup and connections differ. A slicer added to a table filters that table. A slicer added to a PivotTable filters that PivotTable and may also control other compatible PivotTables. Slicers do not turn an ordinary range into a filterable table; first format the range as a table or create a PivotTable from it.
They are especially useful in summaries and dashboards, where someone needs to explore a report without opening filter menus or editing formulas. A slicer is not a chart or a formula: it changes which records a connected object displays.
Prepare the source data
Before adding a slicer, check the data you want to filter. Each column should have a descriptive header, and each row should represent one record. Avoid blank header cells, merged cells in the data area, and multiple header rows. For instance, a sales list might have columns named Date, Region, Product, and Amount, with one sale per row.
- For a regular range: click within the data and choose Insert > Table. Confirm the selected range and check My table has headers if the first row contains field names.
- For a PivotTable: click inside the source data, choose Insert > PivotTable, and build the summary. Slicers filter PivotTable fields, including fields that may not be displayed in the report itself.
- Check the field values: inconsistent entries such as North, north, and North Region can make filtering confusing. Correct the source data if those labels are meant to represent the same category.
If your source is already an Excel table, new rows added directly beneath it are generally included as the table expands. A PivotTable, however, may need refreshing after its source changes. Keeping the data structured makes later updates and filtering more predictable.
Create a slicer for an Excel table
For a table, start by clicking any cell inside it. The contextual Table Design tab appears on the ribbon. Choose Table Design > Insert Slicer, select one or more column names in the dialog box, and choose OK. Excel places a slicer on the worksheet for each selected field. Depending on the window size and ribbon layout, the command may appear in the Tools group.
For example, if a table contains Date, Region, Product, and Amount, choose Region to create a slicer with the region names as buttons. Selecting West filters the table to rows whose Region value is West. To narrow it further, add a Product slicer and select one or more products. The table then shows records matching both selections.
To remove a table slicer, select its border and press Delete. This removes the slicer object, not the table or its data. If you only want to stop filtering temporarily, use the clear-filter icon in the slicer instead.
Create a slicer for a PivotTable
Click inside the PivotTable to show its contextual tabs, then choose PivotTable Analyze > Insert Slicer. In the field list, check the fields you want to filter and select OK. Excel adds a separate slicer for each selected field. Click a button to filter the PivotTable; hold Ctrl while clicking to select multiple items, or use the slicer’s multi-select button when it is available.
Suppose a PivotTable summarizes Amount by Product and Region. A Region slicer can switch the summary to West, while a Product slicer can limit it to a selected product category. The slicer filters the underlying PivotTable data, even if Region is not currently in the Rows or Columns area of the PivotTable Fields pane.
If you do not see the insert command, first confirm that the active cell is inside the PivotTable rather than beside it. The PivotTable Analyze tab is contextual and will not appear for a normal worksheet cell. Also confirm that the data is actually a PivotTable, not a manually formatted summary.
Use slicers and read their controls
Click a button to apply a filter. To select several items, turn on the slicer’s multi-select control if shown, then click the items you want. In many Windows versions, holding Ctrl while clicking also lets you add or remove individual selections. A selected button indicates an included value; buttons that are unavailable under the current combination of filters may appear dimmed.
The icon near the top-right of a slicer clears its filter and restores all values for that field. Clearing one slicer does not necessarily clear the others. For instance, clearing Region while Product remains set still leaves the report filtered by Product. If the slicer is too small to show every item, scroll within it or resize it.
Several slicers can work together. Selecting West in Region and a particular product in Product shows only records meeting both conditions. This is an intersection, not an either/or selection across different slicers. To see a broader result, clear one or more filters.
Connect a slicer to other PivotTables
A PivotTable slicer can sometimes control more than one PivotTable, which is helpful for a dashboard with several summaries. Select the slicer, open the Slicer tab, and choose Report Connections (called PivotTable Connections in some Excel versions). Check the PivotTables the slicer should control and choose OK.
Only compatible PivotTables are listed. They generally need to share the same data source and PivotTable cache, or be built in a compatible way from the Data Model. If a report is missing from the connections list, it usually cannot be connected directly just by selecting it elsewhere on the sheet. Re-create the PivotTables from a shared source or cache where appropriate, and review how the workbook was built.
A table slicer does not use the PivotTable Report Connections command to control unrelated tables. If you need several dashboard elements to respond to one selection, decide at the outset whether those elements should be PivotTables sharing a source, or whether a different report design is more suitable.
Format and position a slicer
Select a slicer to reveal its contextual tab. Use the available slicer styles to change its appearance, and use the controls for height, width, and button layout to make it fit the worksheet. A taller slicer can show more items at once; multiple columns can make a long list more compact. Keep the field name visible so readers know what the buttons represent.
Drag the slicer by its outer border to move it. Drag a sizing handle to resize it. Avoid placing it over important cells or charts, and leave enough room for the longest button label. On a dashboard, align related slicers near the report they control and use consistent sizes and styles. Formatting changes appearance, not the underlying field values or filter logic.
Common problems and fixes
- Insert Slicer is unavailable: click inside the table or PivotTable first. For a plain range, create a table with Insert > Table before trying to add a table slicer.
- A value is missing: check whether it exists in the source data and whether another slicer has already filtered it out. In a PivotTable, refresh the report after changing the source. A blank source value may appear as a blank item.
- New records do not appear: check that the records are inside the table or PivotTable source. Expand the PivotTable’s source if necessary, then refresh it. A table and a PivotTable do not update in exactly the same way.
- A slicer will not control another report: select Report Connections and check whether that PivotTable is listed. An unrelated table or PivotTable based on a different cache may not be compatible.
- The report looks unexpectedly empty: clear filters one at a time using each slicer’s clear-filter icon. Several simultaneous selections can leave no records matching every condition.
- The slicer is hard to use: make it larger, reduce the number of columns, or improve the source labels. For a very long list, a standard field filter may be quicker than scrolling through slicer buttons.
Slicers compared with ordinary filters
Column drop-down filters are often the better choice for routine work on a detailed list. They take little worksheet space, work directly with table columns, and are convenient when users already know which column to filter. Slicers are more visible and easier to operate in a presentation or dashboard, particularly for fields with a manageable number of categories.
A slicer is also different from a timeline. A timeline is a specialized visual filter for date fields in PivotTables, with controls for selecting periods such as months or quarters. For non-date fields, use a slicer. Neither option changes the original source records; each controls what its connected report displays.
On Excel for Mac, slicer availability and ribbon labels can vary by edition. Select the table or PivotTable first and look for the corresponding Insert Slicer command on its contextual ribbon tab. Excel for the web may support interacting with existing slicers, but the commands available for creating or editing them can differ from the Windows desktop app. If a command is absent, use a current desktop version of Excel.
Frequently Asked Questions
Can I add a slicer to a normal range in Excel?
Not directly. Convert the range to an Excel table with Insert > Table, or create a PivotTable from the range, then insert a slicer for that object.
How do I select more than one slicer item?
Use the slicer’s multi-select control if it appears, or hold Ctrl while clicking items in Excel for Windows. Multiple selected values in one slicer are included together; selections in separate slicers are combined as filters.
How do I clear a slicer filter?
Click the clear-filter icon at the top-right of that slicer. This clears that field’s selection, but other slicers may continue to filter the report.
Can one slicer filter two PivotTables?
Yes, if the PivotTables are compatible. Select the slicer, choose Slicer > Report Connections, and check the reports shown. If a PivotTable is not listed, the reports may not share a compatible source or cache.
Why does my slicer show a blank button?
A blank item usually indicates blank values in the source field or missing values in the PivotTable data. Check the source column, fill or remove unintended blanks, and refresh the PivotTable if needed.
Related
- Excel Flash Fill: Automatically Fill Repeating Patterns
- Automatically Merge Duplicate Cells in Excel? What Works
- Automatically Remove Duplicates in Excel: Methods and Risks
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Conditional Formatting: Find Partial Text MatchesYes. In Excel for Microsoft 365, use Home > Conditional Formatting > Highlight Cells Rules > Text that…
- Where to Insert a Table of Contents in WordIn Word, insert a table of contents (TOC) where you want it to appear—usually near the beginning of…
- Excel MAC Address Format: Display, Clean, and ValidateIn Excel, store a MAC address as text, then display it in a consistent form such as 00:1A:2B:3C:4D:5E.…
- Excel Page Breaks Keep Appearing? How to Stop ThemExcel page-break lines keep appearing because Excel is showing where the worksheet will split across printed pages. To…
- Split a Google Sheets Column Into Two ColumnsTo split a column in Google Sheets, select the cells, choose Data > Split text to columns, then…
- Ctrl+E in Excel: What the Shortcut Does and How to Use ItIn Excel for Microsoft 365 on Windows, Ctrl+E runs Flash Fill. Flash Fill recognizes a pattern in your…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.