Custom filter pivot table google sheets

Slicers in Google Sheets are a powerful way to filter data in Pivot Tables.

They make it easy to change values in Pivot Tables and Charts with a single click. Slicers are extremely useful when building dashboards in Google Sheets.

Video: How Slicers Work And How To Add Them

What do Slicers in Google Sheets do?

Consider this basic dashboard in a Google Sheet. It consists of three small pivot tables and a chart, displaying (fictional) data about house sales.

You’ll notice the two black boxes above the chart labeled “Agent Test Slicer” and “Lead Source”. These are slicers in Google Sheets.

When you click on the drop-down arrow in the slicer, it brings up a filter menu:

In the Home Sales dashboard example above, we can click on the slicers to focus on a subset of agents and/or subset of lead sources.

For example, we might select “Emma Johnson” only, so that we can see data that is just relevant to this person.

The three pivot tables (labeled 1, 2 and 3 in the image below) and the chart (labeled 4) are all updated to just show the rows of data from our dataset that are associated with “Emma Johnson”.

You’ll also notice that the slicer has updated the drop-down to say “1 of 4” instead of “All”, to signify that we’ve filtered on one value (“Emma Johnson”) from a possible set of 4 names.

(Note: you can absolutely choose more than one value at a time in your filter.)

How do you add a Google Sheets slicer?

Slicer Template

Feel free to make your own copy of this file (File > Make a copy…)

Slicer Template including the Home Sales Dashboard

The Data

Start with this table of fictitious real estate data (from Sheet1 of the template above):

Create a Pivot Table

If you’re new to Pivot Tables, have a read of Pivot Tables in Google Sheets: A Beginner’s Guide

Back in the real estate dataset, insert a Pivot Table: Data > Pivot Table

Create a simple Pivot Table in a new Sheet, for example this one shows property types and total sales price for each category:

Add a Slicer Control

Back in the Data menu, choose: Data > Slicer

If your cursor was inside the Pivot Table when you added a Slicer, it’ll be added automatically and you’ll see the default slicer control:

If your cursor was outside the Pivot Table in another cell, you’ll be prompted to choose the data to use inside your slicer.

The first thing to do with a slicer is to select a column.

In this example, let’s choose the “Side” column, which is the column containing data about whether the agent acted as a Buyer or Seller in the transaction.

Our slicer will update to show the column name:

We now have a perfectly good, working slicer.

However, you can customize it under the “Customize” menu and change the heading and formatting.

Here we give it a more descriptive title and change it to have a blue background:

Slicer Settings Menu

When you’ve finished setting up your slicer, you can get back to the editing menu by clicking the 3 dot menu next to the drop-down. This brings up the slicer’s settings menu:

You can choose to copy the slicer (super helpful if you need to create several slicers), edit it, delete it or set the current filters as default.

Using Slicers

Clicking the drop-down arrow brings up the slicer filtering menu.

This will look familiar if you use Filters with datasets in Google Sheets. It has exactly the same functionality, but now exists in a standalone control that you can position next to Pivot Tables and Charts as part of a dashboard report.

The data in our Pivot Tables will update when we change the filter applied in our Slicer menu.

Look at the data in the Pivot Table changing in this example as I change between Buyer and Seller:

I can’t wait to explore slicers in more depth and start adding them to more complex dashboards.

More information

Announcement: Additional tools for enhanced reporting in Google Sheets on the G Suite Updates Blog

Filter charts and tables with Slicers in the Docs Editors Help page.