To filter Pivot Table reports, Charts and other tables in Google Sheets, you can now use the Slicer Data menu tool. This is a detailed tutorial on how to add and use Slicer in Google Sheets.

The purpose of Slicer in Google Sheets is to filter;

• Tables.
• Pivot Tables.
• Charts.

Now you can make use of the Slicer tool to enhance your Google Sheets Dashboard reports.

I am only recommending you to use Sheets Slicers for the last two purposes, i.e., in Pivot Tables and Charts. Why? Just read on to get your answer.

Want to filter multiple columns using Slicer? Then you should use multiple Slicers. Let’s create a simple Dashboard report that contains Slicers as below.

Learn the Use of Google Sheets Slicer Tool

Sample Data: This is a mockup data. It contains the diesel consumption of 6 vehicles (1111597, 1711069, 2411905, 2711451, 3011729, and 3011731) for the month of Jan, Feb, and Mar.

Before start using the Slicer to filter a Pivot Table or a Chart, learn how to use the Slicer to filter a table (here the data in the range A1:C19). That’s the best way to learn and understand how to use Slicer in Google Sheets.

Steps to Add a Slicer to a Google Sheets Table

1. Select the range A1:C19 (as per my sample data).
2. Then go to the Data menu and click on “Slicer” (It’s a relatively new tool. I hope, you have already got it).
3. Drag the Slicer to the required area in your Sheet.

Adding a Column to Filter in a Google Sheets Slicer

In the above step, we have added a Slicer to the table. Now to control the table, I mean slice the table in the range A1:C19, you must add columns to the Slicer. How?

Steps:

Double click on the Slicer to open the Slicer settings panel on the right-hand side of your screen. Alternatively, you can click on the three vertical dot menu on the Slicer and choose ‘Edit slicer’.

On the editor panel, you can see the “Choose a column” option. Click on that and choose the column that you want to add to the Slicer tool.

You can only add one column. If you want multiple columns, you should add one more Slicer. I will come to that later. Here I am adding the column ‘Month’ to the Slicer.

Now, using this first Slicer, I can filter the column B, i.e., the month column in the dataset.

Use Multiple Slicers to Add Multiple Columns to Filter

To add one more filter column to the slicer in Google Sheets, do as follows.

Click on the Slicer menu (three vertical dots) on the first Slicer and select “Copy slicer”. Right-click anywhere on the Sheet and select “Paste”

Double click on the newly added Slicer to open the settings and select the column to filter. Here I am selecting the “Vehicle #” column.

Now you can filter the table using the two Slicers. Based on the filter condition, Google Sheets will hide and unhide rows in the table.

Using the Slicers I have filtered the column A and B. In column B I have omitted the month “March”. In column A, I have only selected vehicle # 1711069.

Here actually there is no need to use the Slicers as above. You can directly use the Filter menu Filter command. I have detailed the above steps only to let you know how to add and use Slicer in Google Sheets.

The Slicer is more useful when using it with Pivot Tables as well as Charts. Let’s create a simple dashboard report that contains a Pivot Table, Chart and two Slicers.

How to Filter Charts and Pivot Table in Google Sheets

Let me use the above same data to create a Dashboard report that contains a Chart, Pivot Table and Slicer filters.

The above data is in “Sheet1”. First, delete the above two Slicers in that Sheet. Add a new tab and name it as “Dashboard”.

First I am going to create a Pivot Table then adding a Slicer to it.

Adding Slicer to a Pivot Table Report

My sample data is about diesel consumption of vehicles during the first quarter of the year 2019.

If you total column C in that data, you would get the total, i.e., 11501 gallons. I just want that total in the ‘Dashboard’ tab.

Instead of simply using the Sum function as below, I am going to use a Pivot Table report, so that I can use slicers to control the total.

Recommended: ✖

=sum(Sheet1!C2:C)

Recommended: ✓

In cell D5 I am inserting a Pivot Table report as below.

Steps:

Click on cell D5, then go to the menu Data > Pivot table. On the Pivot Table editor, add the ‘Qty. In Gallon’ column as below.

Now time to add a Slicer to this Pivot Table data. For that, click on cell D5 and go to Data > Slicer.

I will add one more slicer to this Pivot Table later. Here are the settings for the first Slicer.

Please pay your attention to the range used to control the Pivot Table report. It’s not the Pivot Table range, i.e. Dashboard!D5:D6.

As explained earlier, I mean by making a duplicate of the first Slicer, add one more Slicer to this Google Sheets Pivot Table. This time select the column “Vehicle #”

Now you can filter/control the Pivot Table total using the above two slicers added to the Sheet.

Points to Be Noted

What’s important here is; your data in “Sheet1” is unaffected by the filtering. I mean no rows in “Sheet1” will be hidden due to the filtering.

In my first example, I mean the Slicers in a normal table, the rows got hidden. Because the table and the Slicers were in the same “Sheet1”.

If you want to filter a Pivot Table report using a Slicer, use the Pivot Table source range (Sheet1!A:C), not Pivot Table range (Dashboard!D5:D6), as the range in the Slicer editor panel. Then make sure that the Slicer is added within the Pivot Table Sheet.

A Slicer hides rows only in the active Sheet

Adding Slicer to a Chart/Graph

Keep the above points in mind. Now insert a Chart in the “Dashboard” tab. For example purpose, I am inserting a Column Chart. You can follow my chart settings here.

Without adding any extra Slicers, we can filter this Chart. Do you know how?

The existing two Slicers use the source Sheet1!A1:C19. Both the Chart and Pivot table share the same source. So both of the reports (Chart and Pivot Table) can be controlled at a time using these Slicers.

I hope you could understand how to add and use Slicer in Google Sheets. One more thing. Don’t forget to save the Slicer filtering. Didn’t get?

Click the three vertical dot menu on the Slicer and click ‘Save current filter as default’. Otherwise, you will lose the filtering once you close the Sheet.

Also, check the customization option on the Slicer panel to change the font color, size, background color, etc. of the Slicer tool.

Example Sheet 22819