How to Insert a Slicer in Excel Without a Pivot Table

You can insert a slicer in Excel without a pivot table by formatting your data as an Excel table first. Click any cell in the data, press Ctrl + T, then go to Table Design > Insert Slicer, tick the columns you want to filter by and click OK. Each slicer is a set of buttons that filters the table with one click. This works in Excel for Microsoft 365, Excel 2013 and later on Windows, Excel 2016 and later on Mac, and Excel for the web.

Step 1: Convert the data to a table

1. Click any cell inside your data. It should have one header row and no blank rows or columns.
2. Press Ctrl + T, or go to Insert > Table.
3. Check that the range is right and that My table has headers is ticked.
4. Click OK.

The data gets banded rows and filter arrows. If you’d rather keep the plain look, choose Table Design > Table Styles > None. The slicer still works. More on tables in how to create a table in Excel.

Step 2: Insert the slicer

1. Click any cell in the table.
2. Go to Table Design > Insert Slicer. You can also use Insert > Slicer.
3. Tick one or more columns, such as Region and Product.
4. Click OK. One slicer box appears for each column.

Step 3: Filter with the slicer

• Click a button to show only matching rows.
• Hold Ctrl and click to select several items, or click the Multi-Select button in the slicer header and click items to toggle them.
• Click and drag across buttons to select a run of items.
• Click the Clear Filter button (funnel with a red x) in the slicer header, or press Alt + C with the slicer selected, to show everything again.

Several slicers on the same table work together. Choosing “East” in Region and “Laptops” in Product shows only East laptop rows.

Format and arrange slicers

Click a slicer to open the Slicer tab (called Slicer Options in some versions):

Setting	Where	What it does
Slicer Styles	Slicer tab	Colors for selected and unselected buttons
Columns	Slicer tab > Buttons	Lays buttons out in 2 or more columns to save space
Height and Width	Slicer tab > Size	Resizes the slicer box
Caption	Slicer Settings	Changes the title shown at the top
Hide items with no data	Slicer Settings	Removes buttons that match no rows
Sort order	Slicer Settings	A to Z, Z to A or a custom list

Use Slicer > Align to line up several slicers neatly. To stop slicers moving when you insert columns, right-click, choose Size and Properties, and set Don’t move or size with cells.

Show totals that follow the slicer

Normal SUM formulas include hidden rows. To total only the rows the slicer shows, use SUBTOTAL or the table’s Total Row:

• Turn on Table Design > Total Row, then pick Sum, Count or Average from the drop-down in the total cell.
• Or use =SUBTOTAL(109,Table1[Sales]) anywhere on the sheet. 109 sums visible rows only. Use 103 to count them.

Slicers vs filter arrows vs pivot tables

	Slicer on a table	Filter arrows	Pivot table + slicer
Shows which filter is active	Yes, at a glance	Only an icon on the arrow	Yes
Summarizes data	No, filters rows	No	Yes
One slicer controls several reports	No	No	Yes, with Report Connections
Timeline for dates	No	No	Yes

If you need summaries, or one slicer that controls several reports, use a pivot table instead. See how to make a pivot table in Excel and how to edit a pivot table.

Troubleshooting

Insert Slicer is greyed out

The data isn’t a table yet, the selected cell is outside the table, or the file is in Compatibility Mode (.xls). Click inside the table, or save the file as .xlsx.

The slicer stopped filtering

The table may have been converted back to a range, which disconnects the slicer. Delete the slicer, convert the data to a table again and insert a new one.

Slicers don’t work on a protected sheet

When you protect the sheet with Review > Protect Sheet, tick Use AutoFilter and Edit objects so people can still click the slicers.

Frequently asked questions

Can a slicer filter two tables at once?

No. A table slicer only connects to its own table. For several tables, load them into the Data Model and build pivot tables, which can share a slicer.

How do I delete a slicer?

Click its border and press Delete. The filter it applied is cleared.

Summary

1. Click in your data and press Ctrl + T.
2. Go to Table Design > Insert Slicer and tick columns.
3. Click buttons to filter; Ctrl + click for more than one.
4. Use SUBTOTAL or the Total Row for totals that follow the filter.

Related: How to Find Pivot Table in Excel.