Google Sheets Slicer: How They Work & How to Add

Slicers provide a quick and convenient way to analyze your data. Not only do they let you interactively filter all the elements on your worksheet at the same time, but they also look great and are much more intuitive than regular filters. This is why they’re often used to make interactive reports and dashboards.

This tutorial will serve as a complete guide on the Google Sheets slicer. We will explain what they are, how they work, and how to create and use them with the help of a sample database.

What is a Slicer in Google Sheets?

A slicer in Google Sheets (or a Data Slicer) is a sort of visual filter that lets you filter multiple worksheet entities in one go. They let you quickly select your filter. Since each slicer is color-coded and distinct, you can clearly see exactly which filters have been applied to your data.

In fact, they get their name from the fact that they let you cut into your data and grab a particular slice of it for you to view.

Slicers can be applied to tables, pivot tables as well as charts that are on the same worksheet. Thus, they let you focus all the elements in a worksheet to only a particular portion of your data.

How to Use Slicer in Google Sheets

Let us take an example to understand how to use a slicer in Google Sheets. We are going to base our slicers on the following sample dataset:

 

The above dataset contains 245 records of data relating to food sales by an imaginary company.

Note: In order to understand the next section, you need to have an understanding of pivot tables and charts. If you’re not familiar with these concepts, we suggest you go through the following articles to get at least a basic understanding of Charts and Pivot tables:

• Google Sheets Charts: An Easy Guide to Making Beautiful Charts
• How to Make a Google Sheet Pivot Table [Step-by-Step]

Let’s say we have two pivot tables created from the above dataset – one that displays the total units sold for each product and another that displays category-wise total sales.

 

We also have a bar chart that displays city-wise sales made by the company.

Note: All the above entities (the chart and the two pivot tables) are displayed in the same worksheet and are based on the same source data.

We want to insert a slicer that can manipulate all the above entities to filter their results at the same time.

How to Insert a Slicer in Google Sheets

When you want to insert a slicer, the first step is to connect the slicer to a dataset. We want to base our slicer on our main sample dataset. Let us see how to do that first.

• From the main menu, navigate to Data >Add a Slicer.

• This will display the ‘Select data range’ dialog box. Select the range corresponding to your source data. In our case, it is Sheet1!A1:G245

• You should now see your slicer created in the form of a rounded rectangle on top of your worksheet.

Note: The slicer is independent of any cell in your worksheet, so you can easily drag it around to move and position it.

Setting up the Data Slicer

Once your slicer is created, you can then set it up to filter all entities in the worksheet by a single column. Let’s say you want this slicer to filter all entities by City.

To select the column corresponding to a slicer, follow the steps below:

• Double-click your slicer.
• This should display the Slicer sidebar to the right side of the window.

• Make sure the Data tab of the sidebar is selected.
• Select the dropdown below ‘Column’.
• You should see a list of column names from your source dataset.

• Select the column that you want your slicer to correspond to. Since we want to filter by city, we will select ‘City’ from the list.

• You should now see the name of the City column as the title of the slicer.

Note: You can create as many slicers as you want for a single source dataset.

Customizing the Slicer

You can use the Customize tab of the Slicer sidebar to customize the look of your slicer.

For example, you can create multiple slicers in different colors to make it easier to differentiate between them. You can also change the default title of the slicer to something that is more intuitive or easier to understand.

In our example, the title ‘City’ is not really that intuitive. So let us change it to the title ‘Filter by City’. For this, simply select the Customize tab of the Slicer sidebar and type the new title in the input box as shown below:

Let us also change the color and font of the slicer to match the theme of our worksheet (which has different shades of orange and a Calibri font), as follows:

 

Our slicer is now ready to interact with our pivot tables and charts.

How to Adjust a Google Sheet Slicer

We can now start adjusting our slicer. In other words, we can start specifying which cities we want to see the data for in all our charts and pivot tables.

There are two ways to adjust the slicer:

• Clicking on the filter icon to the left of the slicer title

• Clicking on the dropdown arrow to the right of the slicer title

Both will display the same thing – a filter menu (similar to the menu that you see in regular Google Sheets filters) that lets you specify your slicer’s filters.

You will notice that you have two filtering options:

• Filter by Condition: This lets you specify a condition that needs to be satisfied for a city to be included in the filter results. For example ‘all cities that start or end with a particular letter’, etc.
• Filter by Values: This lets you select specific cities that you want included in the filter results. For example the results corresponding to only the cities Boston and New York.

Let us now adjust this slicer so that it makes all entities on our worksheet display only the results for Boston and New York. For this follow the steps shown below:

• Click on the filter icon (or dropdown arrow) of your slicer
• From the values displayed under ‘Filter by Values’, make sure to deselect all city names and select only the cities ‘Boston’ and ‘New York’, as shown below:

• Click OK

You will notice that all entities on the sheet (pivot tables and charts) got adjusted to display only results for the cities Boston and New York.

The slicer also shows that you’ve chosen to filter 2 out of 4 values (cities).

Creating Multiple Slicers based on a Single Data Source

It is important to understand that a slicer can have only one column associated with it. However, Google sheets does let you create multiple slicers.

So if you want to apply a second filter to your data, you can simply create a new slicer to filter by a different column. Here’s how to use slicers in Google Sheets if you want to cascade them.

For example, let’s say you created the following slicer to filter by OrderDate:

 

The above slicer was created in the same way as the ‘Filter by City’ slicer. Let’s give this new slicer the title ‘Filter by Order Date’:

 

You can now further refine your results by considering only those orders that were made after a particular date.

Let’s say you want to consider only the data relating to orders made after June 30, 2020 (in only Boston and New York).

You can accomplish this using the Filter by condition option, as follows:

• Click on the filter icon (or dropdown arrow) of your new slicer
• Click on Filter by condition to display an input box from where you can select your condition.

• Click on the input box to display a dropdown list of options. Select ‘Date is after’.

• You should now see a new input box just below, that lets you select the date for your condition. Click on this box.
• Select ‘exact date’ from the dropdown menu that appears.

• A third input box will now appear just below. You need to enter the date for your condition. We want to see data corresponding to orders made after June 30, 2020. So type this date in the correct format, as shown below:

• Click OK.

You should now find the new filter applied on top of the previous filter. So all your entities will now consider only those data records that satisfy both our created slicers.

Basically, all slicers that you create in a specific worksheet are connected to one another. So all of them apply filters to the entities on your worksheet simultaneously.

Google Sheets Slicers vs. Filters

Slicers	Filters
+ Easier to Manage	– Harder to manage
+ Cascading. For example, the first filter applies before the second filter from a new slicer, etc	– Not cascading
Users with view access only can interact	View only readers can’t view other data
Can be stored as a template	Can be copy pasted more easily

A common question we get is why use slicers when Google Sheets already has filters? After all, both do basically the same thing – filter data. However, as you saw in this tutorial, there are some basic differences that make slicers better than filters.

• Slicers used on sheets are more visual than basic filters. As such, they are more user-friendly. Using slicers you can apply any filter(s) to entities on the fly. The process is quicker and feels more intuitive.
• Since slicers are visual elements, you can move them around just like any other visualization element on Google Sheets. Thus, you can decide how you want them positioned, helping you keep your interface look more aesthetic and organized. Filters, on the other hand, are tied to their respective entities, so you can’t really ‘move’ them.
• When you apply a filter on an entity in Google Sheets, they will remain in place for all users until they are removed or changed. However, changes made by slicers are visible only to you, unless you set them as default.
• Slicers can be stored as templates for reuse, while it’s not possible to do that with basic filters.

For these reasons, slicers are often preferred over filters for making dashboards, reports and Google Sheets templates.

When to Use Slicers Instead of Filters

Slicers are better when you plan to share the spreadsheet with clients or coworkers that want to easily turn them on and off to view important data.

They’re also easier to use if you want to apply several filters separately to a single data set.

If you plan to use a similar filter across several sheets, a slicer may be better as it can be saved as a template.

More Actions for Slicers

When you click the three-dot menu for a slicer, you will get more actions you can interact with. You can edit, copy, delete, set defaults, or learn more about the data of the slicer.

You can also add more than one of the same slicer and set it to different data ranges from this menu:

1. Click the three-dot menu and select Copy slicer
2. Press Ctrl + V (Cmd +V on macOS) to paste the slicer and move it into an appropriate position
3. Click the three-dot menu on the new slicer and select Edit slicer
4. In the sidebar menu, change the data set

This makes it easier to make relevant graphs (after applying conditions).

How to Delete a Slicer

Click the three-dot menu on the slicer, then select Delete Slicer. You can also highlight and press Delete on your keyboard.

What to Do if Your Slicer Isn’t Working

If you’ve imported the slicer from another sheet, it’s likely that the data is missing or the cell references are wrong instead of just importing the slicer, try copying over everything from the other sheet into a new sheet, then using the sheet name in references to your existing data.

Sometimes you may have some filter settings wrong. To check on them, click the filter button on the left side of the slicer. Edit or remove the filters as neccessary.

There may also be two or more slicers on one set of data. One of them may be cutting out data that you don’t want to be sliced. Check, then edit or remove any extra slicers.

Frequently Asked Questions

How Do You Add Slicers to Sheets?

For Google Sheets to add a slicer to your sheet, simply navigate to Data >Add a slicer from the main menu. After that, select the range of cells in the data source that you want your slicer to be connected to and click OK.

How Does Slicer Work in Google Sheets?

A slicer in Google Sheets acts like a visual filtering element. It lets you interactively select which filters you want to apply or remove from your data. Once you’ve selected your filters, the slicers apply them to all entities (tables, charts, pivot tables) on the active worksheet.

How Do I Link a Slicer to Multiple Pivot Tables in Google Sheets?

A slicer is linked to all pivot tables on the same active sheet. So if you want to link a slicer to multiple pivot tables, all you need to do is make sure that they’re all on the active sheet.

Do Slicers Apply to All Charts and Pivot Tables in a Sheet That Use the Same Data Set?

Yes, a slicer manipulates all the charts and pivot tables that are based on the same dataset, provided that they are all on the active sheet.

How Do You Connect Slicers to Each Other?

Slicers are connected to all other slicers that are based on the same dataset by default. When you apply multiple slicers based on the same source data, they work together to affect all entities on the active worksheet that are also based on the same source data.

Can You Add a Slicer to a Chart in Google Sheets?

Yes, you can. Double click the chart, then head to Data > Add a slicer.

How Do I Make a Chart From a Slicer?

You don’t make the chart from the slicer itself, but slicers can be used as a Google Sheets chart filter, you create a chart from the data that the slicer leaves in the pivot table instead.

Can You Apply Multiple Slicers?

Yes, just select the data range again, and add a slicer in Google Sheets as you normally would.

What Do Slicers in Google Sheets Do?

Slicers filter data in Google Sheets and work best in pivot tables or from charts.

Is There a Way to Save the Condition Setting for Each Slicer?

Yes, right click the slicer and select Set current selection as default.

Can You Exclude the Blank Data Rows in Google Sheets Slicers?

Not from the slicer itself, you will have to do that at the pivot table level instead.

Conclusion

The Google Sheets Slicer can be thought of as a more advanced, higher-level version of the filter. They help you quickly slice and dice through your data and apply filters to all entities on your active sheet at the same time. They work like any other visual control element in Google Sheets, so you can move them around, stack them on top of each other or next to each other, and basically just transform your sheet into a more organized and interactive dashboard.