Ravindra BagaleCourses & study guides मराठी Track your progress

6. Tables, Sorting and Filtering

6.13 Slicers on an Excel Table (Not a Pivot)

By Last updated:

Example first. Slicers are not only for PivotTables. Put a City slicer and a Platform slicer next to tblMini, click Pune, and the Table and its Total Row show Pune only. A manager who has never used a filter button can do this.

Slicer on a Table — in 30 seconds

What: A slicer is a panel of buttons that filters a Table. Each button is one value of a column, like Pune or Nashik.

Why: Filter buttons are small and hidden in the header. Slicers are big, visible and show which filter is on, so they are perfect for a sheet you hand to a manager.

Tiny example: Click Pune on the City slicer: 4 rows stay and the Total Row shows ₹544.

First look at the data (before → after):

Slicer on a Table: before and after tables, fictional Blinkit Maharashtra data

Expected result — After clicking Pune on the City slicer

Order ID City Platform Amount (₹)
BLK-1001 Pune Blinkit 64
AMN-1003 Pune Amazon Now 110
BLK-1006 Pune Blinkit 150
BLK-1009 Pune Blinkit 220
Total — — 544

Expected result — Slicer clicks and the Total Row

Slicer selection Rows visible Total Row (₹)
City = Pune 4 544
City = Nashik 2 360
City = Pune and Nashik (Multi-Select) 6 904
Platform = Amazon Now 3 537
City = Pune + Platform = Blinkit 3 434
Before table and formula as text

Before — tblMini with a Total Row (no slicer yet)

Order ID City Platform Amount (₹)
BLK-1001 Pune Blinkit 64
BLK-1002 Nashik Blinkit 270
AMN-1003 Pune Amazon Now 110
BLK-1004 Nagpur Blinkit 240
AMN-1005 Kolhapur Amazon Now 232
BLK-1006 Pune Blinkit 150
BLK-1007 Solapur Blinkit 1299
AMN-1008 Sambhaji Nagar Amazon Now 195
BLK-1009 Pune Blinkit 220
BLK-1010 Nashik Blinkit 90
Total — — 2870

Formula / click path

Click in the Table › Table Design › Insert Slicer › tick City and Platform › OK
Click Pune on the City slicer

Do it step by step:

  1. Click any cell in tblMini.
  2. Click Table Design › Insert Slicer.
  3. Tick City and Platform and click OK.
  4. Click Pune on the City slicer and read the Total Row.
  5. Click the Multi-Select button at the top of the slicer, then click Nashik to add it.
  6. Click the Clear Filter button (funnel with a red cross) to show all rows again.

Good to know about slicers on a Table

Question Answer
Which Excel versions? Slicers on Tables work in Excel 2013 and later, including Microsoft 365.
Two slicers at once They combine with AND: City = Pune and Platform = Blinkit.
Several values in one slicer Click Multi-Select (or hold Ctrl and click).
Style and size Select the slicer › Slicer tab › Slicer Styles, Columns, Height and Width.
Slicer and formulas Formulas outside the Table such as =SUM(tblMini[Amount]) ignore the slicer. Use the Total Row or =SUBTOTAL(109,tblMini[Amount]) for "what I see".
One slicer for two Tables Not possible. A slicer belongs to one Table. To drive several reports with one slicer, use a PivotTable and Report Connections (Module 7).
Remove a slicer Click it and press Delete. The filter it applied is removed too.

Practice task (try it before you open the answer):

  1. Insert City and Platform slicers on tblMini.
  2. Try each selection in the table above.
  3. Put =SUBTOTAL(109,tblMini[Amount]) and =SUM(tblMini[Amount]) in two cells beside the Table and compare them while you click.
Answer

The Total Row and the SUBTOTAL cell follow the slicer (for Pune + Blinkit both show ₹434). The SUM cell stays at ₹2,870 whatever you click.

Ravindra Bagale's Tip

Put slicers above the Table, not on top of it. When the Table grows, rows slide under a slicer that sits on the data and nobody sees them.