Slicers in Google Sheets

Slicers in Google Sheets are a powerful new 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.

Slicers in Google Sheets dashboard

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:

Slicer 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”.

Slicer in Google Sheets

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?

(Note 8/1/19: According to the G Suite blog, it may take up to 15 days for feature visibility.)

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):

Data in Google Sheets

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:

Pivot Table

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:

Slicer column choice

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:

Slicer column added

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:

Customize slicer in Google Sheets

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:

Slicer 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.

Slicer Filter in Google Sheets

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:

Google Sheets slicer

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.

13 thoughts on “Slicers in Google Sheets”

  1. Do you know whether slicers will work like Filter Views i.e. specific to the user? If I could build my dashboard and allow users of the same sheet to slice it for different areas/divisions of the company – WITHOUT affecting other users’ views – that would make a real difference.

  2. I wonder what the general opinion will be of google sheet slicer vs datastudio – I’ve been stepping away from data analysis in sheets and instead plugging into a datastudio, so will be interesting to compare the user functionality/friendliness!

    1. Wondering exactly the same thing myself. For my use case, sheets is normally enough in terms of rough dashboards for internal use, and this takes away a big difference between the two suites. I think I may be hanging around in sheets much more…

  3. I’m GSuite and we don’t have it yet (noted your 15-day warning about that) so can’t play with it yet. Will I find that I can add these to sheets/pivot tables that already existed before August, or will I have to “convert” my sheet somehow to get it on those?

  4. I’ve been waiting a very long time for this functionality! Can’t wait for it to be implemented for my account.

  5. Mr. Collins,

    Is there a way to save the condition setting for each slicer? I’m calculating information relevant to the the current month, specifically for a budget. Each time I reload the sheet, the slicers reset. This is the only drawback I have noticed about them.

    Thanks for your time and effort!

  6. In the animated gif for the spreadsheet illustrating Slicers, Scorecards, and Themes (http://shorturl.at/invBV), why do the sheet tabs look so different than my sheet tabs? Is it a browser issue? Also, the second sheet has a database icon in front of the name. New feature or one I’ve just overlooked all this time?

  7. Do you know if there is a way to exclude the Blank data rows in the Slicers? I have a sheet that grabs data from the entire column (since the column is constantly updating with new data), but in doing this it shows my options for the Slicer a (Blank), A, B, C, etc…

  8. Hi There,

    I am finding that it where I have dashboard charts on another tab it is not allowing me to create or copy across the original slicer so therefore not updating the pivots. This means I am having to filter on the pivot sheet then tab across to the dashboard to see the updates. Are you aware if this is intentional?

  9. Not sure if I’m just missing it.. but should the slicers work across the workbook? Or are the designed to only work within the SAME Sheet where the slicer resides? My gut tells me that given you define the Data Range of the Slicer, it should work – but doesn’t seem to? Am I missing something?

    1. Hi Tracy, Slicers are attached to a specific data range (i.e. table of data) but don’t have to be on the same sheet as that dataset. In addition you can choose to apply them to pivot tables as well.

      Hope that helps!

      Ben

Leave a Reply

Your email address will not be published. Required fields are marked *