How to Insert a Slicer in Excel: Filter Tables and PivotTables With Buttons

shape
shape
shape
shape
shape
shape
shape
shape
How to Insert a Slicer in Excel: Filter Tables and PivotTables With Buttons - featured image

A slicer is a set of buttons that filters your data with one click. Instead of opening a drop-down and ticking boxes, you click a button such as North, Q2 or Laptop, and the table or PivotTable shows only matching rows. Slicers also show the current filter at a glance, which makes them popular in dashboards and in reports that other people will use. This guide shows how to insert a slicer for an Excel table and for a PivotTable, how to select several items, how to connect one slicer to many PivotTables, how to style and size it, and what to do when the option is grayed out. Each step uses a small sales example so you can follow along.

Updated November 2026

In this guide

  1. What a slicer does
  2. Example data
  3. Step 1: turn the range into a table
  4. Step 2: insert a slicer for a table
  5. Step 3: select more than one item
  6. Step 4: insert a slicer for a PivotTable
  7. Step 5: connect one slicer to several PivotTables
  8. Step 6: change the look and the layout
  9. Step 7: use slicers with formulas
  10. Fix common slicer problems
  11. Slicer or drop-down filter?
  12. A simple dashboard example
  13. Practice problems
  14. Quick recap

What a slicer does

A slicer is a visual filter. It lists the unique values of one column as buttons. A highlighted button is selected, and a plain button is not. When you click a button, the connected table or PivotTable updates at once. A slicer works only with an Excel table or a PivotTable, not with a plain range, so the first step is to convert your range to a table. Slicers are available in Excel 2013 and later for tables and in Excel 2010 and later for PivotTables, so most people can use them today.

Example data

Imagine a small sales list with the columns Date, Region, Product and Amount. It has 200 rows, and Region has four values: North, South, East and West. Suppose the North region sold 12,500 in total and the other regions sold 9,800, 11,200 and 10,500. The grand total is 12,500 plus 9,800 plus 11,200 plus 10,500, which is 44,000. After you add a slicer and click North, the total at the bottom of the table should show 12,500. That check tells you the slicer works.

Step 1: turn the range into a table

Click any cell in the data and press Ctrl+T, or go to Insert, Table. Make sure My table has headers is ticked and click OK. Excel formats the range as a table and adds a Table Design tab. Give the table a clear name in the Table Name box, such as SalesData. Our remove duplicates guide is a good way to clean the list before you build the table, since duplicates would inflate the totals.

Step 2: insert a slicer for a table

Click any cell in the table. Go to Table Design, then choose Insert Slicer. You can also go to the Insert tab and choose Slicer. A dialog lists the column headers. Tick Region and click OK. A box with four buttons appears on the sheet. Click North and the table shows only the North rows. Click it again, or use the clear filter icon at the top right of the slicer, to show all rows. The row numbers on the left turn blue to show that the table is filtered.

Step 3: select more than one item

To choose several items, click the multi-select button at the top of the slicer, which looks like a small list with a check mark, and then click each item you want. You can also hold Ctrl while you click the buttons. To select a block of neighboring items, click the first one, hold Shift and click the last. For the example, selecting North and East would show 12,500 plus 11,200, which is 23,700 in total. The slicer highlights both buttons so you can see the current choice.

Step 4: insert a slicer for a PivotTable

Click inside a PivotTable. Go to PivotTable Analyze, then Insert Slicer, tick the fields you want, such as Product, and click OK. The slicer filters the PivotTable in the same way. You can add several slicers, for example Region and Product, and use them together. If North and Laptop are both selected, the PivotTable shows only the laptop sales of the North region. A PivotTable also lets you use a Timeline, which is a slicer for dates that filters by months, quarters or years.

Step 5: connect one slicer to several PivotTables

Right-click the slicer and choose Report Connections, which is called PivotTable Connections in some versions. A list of the PivotTables in the workbook appears. Tick each one that the slicer should control and click OK. All the connected PivotTables must share the same source data, or the same data model, so make them from the same table. This is useful in a dashboard, where one Region slicer can control a sales table, a chart and a count at the same time. Our pie chart guide shows how to build a chart that will update when the slicer is used.

Step 6: change the look and the layout

Select the slicer to see the Slicer tab. Use the Styles gallery to choose a color. Use Columns to show the buttons in two or more columns, which saves space when a slicer has many items. Change Height and Width to resize the box, and use Button settings to change the button size. In Slicer Settings you can change the caption, hide the header, and choose whether to sort items from A to Z. Keep the slicer near the data and give it a short caption, so that anyone who opens the file knows what it filters.

Step 7: use slicers with formulas

A slicer filters rows, so formulas that sum the whole column are not affected. To get totals that follow the slicer, use SUBTOTAL, which ignores filtered-out rows: =SUBTOTAL(109,SalesData[Amount]). The number 109 means sum, and it skips the hidden rows. With North selected, the result is 12,500. Our SUBTOTAL guide explains the function numbers. In a table, the Total Row uses SUBTOTAL automatically, so you can switch it on in Table Design and get a total that follows the slicer.

Fix common slicer problems

If Insert Slicer is grayed out, the active cell is not inside a table or PivotTable, or the file is in an old format such as .xls. Convert the range to a table or save the file as .xlsx. If the slicer does not filter a second PivotTable, it is not connected, so use Report Connections. If old items remain in the slicer after you delete data, refresh the PivotTable and set Retain items deleted from the data source to None in the PivotTable options. If the slicer covers cells you need, move it by dragging, or lock its position in Format Slicer, Properties.

Slicer or drop-down filter?

A drop-down filter on the header row works well for one person who wants a quick filter. A slicer is better when you share the file, when you build a dashboard, or when you want everyone to see the current filter at once. Slicers take more space and cannot filter by a condition such as greater than 500, so use the filter menu for that. You can use both in the same sheet. See our sorting guide for how to order the data before you filter it.

A simple dashboard example

Build a PivotTable that sums Amount by Product and a second PivotTable that sums Amount by month. Insert one Region slicer and connect it to both, then add a chart to each PivotTable. Click North and both tables and both charts change together, and the total in each stays at 12,500. Place the slicer at the top left, the tables below it and the charts beside them. Name the sheet Dashboard, hide the gridlines from the View tab, and keep the slicer captions short so the page is easy to read on a laptop screen or a projector in a meeting.

Practice problems

Try these with the example data. Problem 1: create a table named SalesData and insert a Region slicer. Click South, and check that the total is 9,800. Problem 2: select South and West together and check that the total is 9,800 plus 10,500, which is 20,300. Problem 3: add a Product slicer and use it with the Region slicer. Problem 4: make a PivotTable that sums Amount by Product, add a Region slicer, and connect it to a second PivotTable. Problem 5: change the slicer to show two columns and give it a caption.

Quick recap

Convert the range to a table with Ctrl+T. Click inside the table and choose Insert, Slicer, then tick the column. Click buttons to filter, use Ctrl or the multi-select button for several items, and clear the filter with the icon at the top right. For a PivotTable use PivotTable Analyze, Insert Slicer, and use Report Connections to control more than one PivotTable. Use SUBTOTAL with 109 to build totals that follow the slicer. Keep the source clean, with one header row and no blank rows, and the slicers will work reliably.

Frequently asked questions

How do I insert a slicer in Excel?

Click inside a table or PivotTable, then choose Insert, Slicer, tick the field and click OK.

Why is Insert Slicer grayed out?

The cell is not in a table or PivotTable, or the file is in an old format.

Can one slicer control several PivotTables?

Yes. Right-click it, choose Report Connections and tick each PivotTable.

How do I select multiple items in a slicer?

Hold Ctrl while clicking, or use the multi-select button.

How do I clear a slicer?

Click the clear filter icon at the top right of the slicer.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *