Apply a Pivot Table Slicer in Google Sheets (An Easy Guide)

Slicers provide a simple way to filter out data in a spreadsheet. That combined with pivot tables can provide users with a powerful data analysis tool.

So, in this article, we will look at how to add a slicer to a Google Sheets pivot table.

Let’s get started.

What is a Slicer in Google Sheets?

A Slicer (or a Data Slicer) is simply a filter in Google Sheets. That said, it is quite different from the usual filter and has its own set of advantages and uses.

A slicer is a visual filter that can help users manipulate data at a glance with buttons and functions.

When using a filter, the changes are conditionally permanent and cannot be saved for reuse. However, with a slicer, a user can see the data already filtered when they open the worksheet and use the tool anytime to filter data, given that they have access to it.

Another advantage is that slices can be applied to any table or chart, that includes pivot tables.

It is the perfect tool to create a custom dashboard for your team to use at any time.

How to Add a Slicer in a Google Sheets Pivot Table

Here we have a sample worksheet that we will use as the source dataset to create the pivot table:

sample dataset for google sheets pivot table slicer

The resultant pivot table shows the Region-wise revenue:

pivot table showing the revenue by region

Step 1: To add a slicer to this pivot table, simply navigate to the Data tab and select the ‘Add a slicer’ option.

Data > Add a slicer

navigating to add a slicer option from the data tab in a google sheets pivot table

Step 2: The newly generated slicer will then prompt you to select the column option to filter the Google Sheets pivot table by:

the new slicer prompts the user to select the column condition

We have selected ‘Product’ to be our filter condition.

The slicer automatically recognizes all the headers from the data range used by the pivot table.

Step 3: The slicer is independent of cell positioning and can be placed anywhere on the worksheet (click and drag).

positioning the slicer beside the pivot table in google sheets

Place the slicer anywhere suitable.

Step 4: Clicking on ‘All’ will give us the menu. As you can see, the menu is the same as Filters in Google Sheets.

the slicer menu is the same as filter in google sheets

This means that anything you could’ve done with Filters can also be done here.

Setting the Revenue by Region to show the revenues earned by Bricks only:

setting a slicer in a google sheets pivot table animated

Example Dashboard Presentation with a Slicer

A more common way to use a slicer is to easily customize the data in a dashboard.

Let’s show that by creating a chart using the pivot table we have now.

Click on any cell of the pivot table and navigate to the Chart option from the Insert menu to insert a chart.

inserting a chart for the pivot table

Remove the grand total calculation by updating the range of the chart.

sample dashboard with a google sheets pivot table

What you see in the above image is a sample dashboard created with a Google Sheets pivot table that includes a slicer filter.

Using the slicer to change the filter conditions will also affect any charts in the pivot table:

the slicer will update both pivot table and chart in google sheets animated

You can add Multiple Slicers for a Pivot Table (Multiple Conditions)

It is possible to have more than one slicer for a single Google Sheets pivot table. This means that you can filter your pivot table with multiple conditions.

To add another slicer, just follow the steps we discussed when inserting a slicer in the first place.

Data > Add a slicer

Note: Make sure the data range is correct.

adding a second slicer in a google sheets pivot table

We have chosen the number of Units Sold for the second condition.

Let’s say we want to filter the revenues of the regions where the number of units sold is greater than 300.

Step 1: Select the ‘All’ drop-down on the second slicer. Click the drop-down ‘Filter by condition’ and select the ‘Greater than’ option.

setting the filter by condition option for the second slicer

Step 2: Set the number to 300 and click OK.

setting the greater than 300 units condition in the slicer for the google sheets pivot table

The result:

google sheets pivot table filtered with two slicers

We have successfully filtered a Google Sheets pivot table with multiple conditions using slicers.

Moreover, you can apply custom conditions to filter in the Google Sheets pivot table slicer.

Final Words

Slicers are the most user-friendly way to set up filters in a Google Sheets pivot table. They follow the same methodology as a normal filter in the application. It even takes the range from the source dataset so applying custom formulas is easy.

On top of that, you can apply multiple slicers to satisfy multiple filter conditions.

Feel free to leave any queries or advice you might have in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply