How to Use Slicer in Pivot Tables in Google Sheets

The slicer is a really handy feature of Google Sheets. It enhances the power of Pivot Tables and Pivot Charts in Google Sheets.

With Slicers, you can analyze your data much more interactively. You can create really amazing-looking and interactive reports and dashboards right within your Google Sheets worksheet!

In fact, once you’ve mastered the use of slicers, you’ll never want to go back to using regular filters!

In this tutorial, we will explain what Google Sheets slicers are. We will also demonstrate how to create and use Slicers in Google Sheets (with a practical example).

In order to understand this tutorial, you need to understand how Pivot tables and charts work. If you’re not familiar with these, it would be a good idea to first become familiar with the concepts and how they work.

What is Slicer?

A slicer is a Google Sheets tool that allows you to quickly and easily filter tables, pivot tables, and charts with just the click/ drag of a button. They float above your grid and are not tied to any cell, so you can easily move it around your window, align it, and position it however you like.

A slicer is called so, because it cuts through your data, similar to a filter, to give you customized data analytics. It is, however, better than a filter because it is much more visually appealing and user-friendly.

How to Create a Slicer in Google Sheets (with Example)

To better understand how slicers work, let us try to take some sample data and create a slicer for it from scratch. Let us assume we have the following dataset:

Dataset to use for Slicers in Google Sheets

This dataset has around 44 records of sales data for stores in three different regions – Central, East, and West.

From the above dataset, let us say we have two pivot tables – one that displays the sum of sales by each Sales rep and another that displays the number of units sold per product.

Two Pivot Tables in Google Sheets

Let us say we also have a chart that displays a percentage of sales made by each Sales Rep as shown below:

Chart inserted along with the Pivot Table

That means we have two pivot tables and a chart, all based on the same source data. Now we can create a slicer that can control all three entities at the same time.

Creating a Slicer based on Region:

Let us create a slicer to filter all three entities (the two pivot tables and the chart) based on Region. For this, follow the steps below:

  1. Click any of the three entities (chart or pivot table) that you want to filter.
  2. From the Data menu, select the Slicer option.Click on Data and then on Slicer
  3. This will display the Slicer sidebar to the right of the Google Sheets window:Slicer sidebar
  4. You should also see a new slicer button created on top of the worksheet:Select the column first
  5. Check if the data range (under the Data tab) displays the correct range for your source data. Remember this should contain the range for your original data (not the pivot table).Check the data range
  6. Under the Column section, click on the dropdown menu to select the column by which you want the slicer to filter. Since we want to filter by Region, we will select the ‘Region’ option.Click the Region column

You should now see the name of the Region column as the title of the slicer:

Slicer now have the Region title

Customizing your Slicer

You can customize how your slicer looks from the Customize tab of the Slicer sidebar:

Click the Customize tab in Slicer Pane

  1. Let us change the title of the slicer (the name that will be displayed on the slicer) to ‘Filter by Region’ to make it a little more descriptive for the user.Change the Title to Filter by Region
  2. You can also customize the font and background color of the slicer as you like. Let us change the background color of our slicer to orange.Change the color of the slicer

Once you are done, your slicer is ready for you to interact with your pivot tables and charts.

Filter by Region slicer is ready

How to Change Slicer Settings

If at any point you want to make changes to your slicer’s settings, you need to use the Slicer sidebar. Normally this is hidden from you. So if you want the Slicer sidebar to appear, follow the steps below:

  1. Click on the slicer that you want to edit.
  2. An ellipsis button (three vertical dots will appear on the right side of the slicer). Click on this icon/button.Ellipsis button in the slicer when you click on it
  3. From the menu that appears, select ‘Edit slicer’.Click on Edit slicer

This will make the Slicer sidebar appear once again, so you can make your required changes to the slicer.

Slicer Pane appears again

How to Interact with a Slicer

To interact with your slicer, click on the drop-down arrow of the slicer to the right of the slicer title.

click on the drop-down arrow of the slicer to the right of the slicer title

This should display a filtering menu quite similar to the menu you see on regular filters. You can use this to easily select which regions you want to include in your pivot table and chart summaries and which regions you want to exclude.

All Slicer options in the drop down

The results in the pivot tables and chart will update when you change the filter applied in the slicer.

When the slicer filtering menu appears, you will notice you have the option to filter by values or by the condition.

Filter by value or condition in slicer

Filter By Values

The Filter by values option lets you select the region values that will be included in the pivot table and chart results.

 

Let us say we want to see results for only the Central region stores. For this you will need to make sure to deselect all-region options and select only the Central region option, as shown below:

Select Only Central Option in the Slicer filters

You will notice all the pivot tables and charts will now include results for only those stores that are in the Central region.

Pivot Table and chart are filtered to only show Central Data

Similarly, if you include one more region, say the West region too, then your chart and pivot tables will now include results for only the West and Central regions:

Include West in the Slicer Filter

This is how the worksheet would look now:

Pivot Table and chart are filtered to show Central and West Data

Filter By Condition

The Filter by condition option lets you add a condition that needs to be satisfied for a Region to be included in the results.

We will explain how you can use a slicer to filter by the condition in the following section, where we will create a second slicer to filter by Date.

Creating a Slicer based on Date in Google Sheets

It is important to understand that one slicer can filter by only one column. If you want to filter by more than one column, then you need to add more slicers, one for each column.

Let us say you want to further filter your summaries to display the sum of only sales made after 26/06/2019. For this, you will need to add one more slicer, this time based on the ‘Date’ column of the original dataset.

Follow the same steps to create this slicer as you had created the ‘Filter by Region’ slicer. You can give the new slicer the title, ‘Filter by Date’.

Filter by Date

You can now filter your pivot tables and chart by condition with the following steps:

  1. Click on the drop-down arrow of the ‘Filter by Date’ slicer (to the right of the slicer title).Click on All for Filter by Date Slicer
  2. This should display a filtering menu which you can use to enter the condition you want the dates included in your results to satisfy.Filter Menu for Date Slicer
  3. Click on the ‘Filter by Condition’ option.Filter by Condition in Date Slicer
  4. This will display an input box that lets you select the kind of condition you want to enter.Click on the Drop Down
  5. Click on the input box to display a dropdown list of options. Scroll down and select the ‘Date is after’ option.Click on the Date is after filter
  6. This will cause a new input box to appear just below. The new input box lets you select the date for your condition.Click on Today
  7. Click on the new input box and select ‘exact date’.Click on Exact Date
  8. You should now see a third input box appear just below.Third input box appears
  9. Type in the date for your condition. Since we want to see the sum of sales made after 26/06/2019, type the date (in the appropriate date format):Enter the Date manually
  10. Scroll down the filter dropdown window and click OK.Click OK
  11. You will find that all the pivot tables and charts will now include results for only those stores that satisfy filters for both slicers.Both slicer filter conditions are satisfied

So as you can see, you can filter your tables and charts using multiple slicers, and you can filter both by value and condition.

Depending on the filters applied, Google Sheets will hide or unhide entries/ results in all the tables and charts displayed in the current sheet that is based on the same base dataset.

Points to Remember

As a closing note, here are a few important points to keep in mind when using slicers in Google Sheets:

  • You can connect a slicer to more than one table, pivot table, and/or chart.
  • You can have only one slicer to filter by one column.
  • You can apply multiple slicers to filter a single dataset by different columns.
  • A slicer applies to all tables and charts on a worksheet, provided they have the same underlying source data.
  • Slicers apply only to the active sheet
  • Slicers do not apply to formulas on a sheet.

In this tutorial, I covered how to create, customize, and use Slicers in Google Sheets.

I hope you found this tutorial useful!

Spreadsheet Expert at Productivity Spot | Website | + posts

Google Sheets and Microsoft Excel Expert.