Google Sheets Slicer: How They Work & How to Add [Easy]

Slicers provide a quick and convenient way to analyze your data. Not only does it allow you to interactively filter all elements on your worksheet at the same time, it looks great and is much more intuitive than regular filters. For this reason, it is often used to create interactive reports and dashboards.

This tutorial serves as a complete guide to Google Sheets Slicers. We’ll explain what they are, how they work, and how to create and use them using a sample database.

What is a Slicer in Google Sheets?

A slicer (or data slicer) in Google Sheets is a type of visual filter that allows you to filter multiple worksheet entities at once. Filters can be selected quickly. Each slicer is color-coded to distinguish it, so you can see exactly which filters have been applied to your data.

In fact, it derives its name from the fact that it can cut into the data and retrieve specific slices of it for display.

Slicers can be applied to tables, pivot tables, and charts on the same worksheet. Therefore, all elements in the worksheet can be focused on only certain parts of the data.

How to Use Slicer in Google Sheets

Let’s look at an example to understand how to use slicers in Google Sheets. Create a slicer based on the following sample dataset:

  

The dataset above contains 245 records of data on food sales by a fictitious company.

Note: To understand the next section, you should be familiar with pivot tables and charts. If you are unfamiliar with these concepts, I recommend reading the following articles to at least understand the basics of charts and pivot tables:

• Charts in Google Sheets: A Simple Guide to Creating Beautiful Charts
• How to Create a Google Sheet Pivot Table [Step by Step]

Suppose we have two pivot tables created from the above dataset. One shows total sales for each product and the other shows total sales by category.

  

There is also a bar chart displaying sales by city by company.

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

I would like to insert a slicer that can operate on all the above entities and filter the results simultaneously.

How to Insert a Slicer in Google Sheets

When inserting a slicer, the first step is to connect the slicer to a dataset. I want to base my slicer on the main sample dataset. Let’s see how to do that.

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

• The Select Data Range dialog box is displayed. Select the range that corresponds to your source data. In this example, Sheet1!A1:G245

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

Note: Slicers are independent of any cell in the worksheet, so you can easily drag them to move and position them.

Setting up the Data Slicer

Once you’ve created your slicer, you can set it to filter all entities in your worksheet by a single column. I want this slicer to filter all entities by city.

To select columns for slicers:

• Double-click Slicer.
• This will bring up the Slicer sidebar on the right side of the window.

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

• Select the column you want the slicer to correspond to. We want to filter by city, so select “City” from the list.

• The name of the City column should now appear as the slicer title.

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

Customizing the Slicer

You can customize the look of your slicer using the Customize tab in the Slicer sidebar.

For example, you can create multiple slicers with different colors to make them easier to distinguish. You can also change the slicer’s default title to something more intuitive or easier to understand.

In this example, the title “City” is not very intuitive. So let’s change the title to “Filter by City”. To do this, simply select the Customize tab in the slicer sidebar and enter a new title in the input box as shown below:

Let’s also change the color and font of the slicer to match the theme of the worksheet (which includes shades of orange and the Calibri font) as follows:

  

Slicers can now work with pivot tables and charts.

How to Adjust a Google Sheet Slicer

You can start adjusting your slicer. In other words, you can start specifying which city’s data to display in all charts and pivot tables.

There are two ways to adjust slicers:

• Click the filter icon to the left of the slicer title

• Click the dropdown arrow to the right of the slicer title

They both show the same thing: a filter menu (similar to the menu you see in regular Google Sheets filters) where you can specify filters for the slicer.

You can see that there are two filtering options:

• Filter by condition: You can specify conditions that must be met for a city to be included in the filter results. For example, “all cities that start or end with a certain letter.
• Filter by value: This allows you to select specific cities to include in your filter results. For example, results that correspond only to the cities of Boston and New York.

Now let’s adjust this slicer so that all entities on the worksheet only show results for Boston and New York. To do this, follow the steps given below:

• Click the filter icon (or dropdown arrow) in the slicer
• Deselect all city names and select only the cities “Boston” and “New York” from the values ​​displayed in Filter by Value, as shown below:

• Click OK

You can see that all the entities on the sheet (pivot tables and charts) have been adjusted to show results only for the cities of Boston and New York.

The slicer also shows that you have chosen to filter 2 of the 4 values ​​(city).

Creating Multiple Slicers based on a Single Data Source

It’s important to understand that a slicer can only have one column associated with it. However, Google Sheets allows you to create multiple slicers.

So if you want to apply a second filter to your data, just create a new slicer to filter on another column. Here it is how to use slicers in Google Sheets if you want to cascade them.

For example, if you created the following slicer that filters by OrderDate:

  

The above slicer was created in the same way as the “Filter by City” slicer. Let’s title this new slicer ‘Filter by order date’:

  

You can now further narrow your results by considering only orders placed after a certain date.

We want to consider only data related to orders placed after June 30, 2020 (Boston and New York only).

This can be achieved using the Filter by Criteria option as follows:

• Click the filter icon (or dropdown arrow) for your new slicer
• If you click “Filter by condition”, an input box will appear where you can select the condition.

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

• A new input box will appear directly below, allowing you to select a date for the state. Click this box.
• Select Exact Date from the drop-down menu that appears.

• A third input box appears directly below. You must enter your state date. I would like to see data corresponding to orders placed after June 30, 2020. So enter this date in the correct format as shown below:

• Click OK.

You can see that the new filter is applied on top of the previous filter. Therefore, all entities will only consider data records that satisfy both created slicers.

Basically all slicers created in a particular worksheet are connected to each other. Therefore, they all apply filters to entities on the worksheet at the same time.

Google Sheets Slicers vs. Filters

slicer	filter
+ 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 is why use slicers when Google Sheets already has filters? After all, they both basically do the same thing: filter the data. However, there are some fundamental differences that make slicers better than filters, as discussed in this tutorial.

• Slicers used in sheets are more visual than basic filters. Therefore, it is more user-friendly. Slicers allow you to apply arbitrary filters to entities on the fly. The process feels faster and more intuitive.
• Slicers are visual elements, so you can move them like any other visualization on Google Sheets. Therefore, you can decide how to arrange them, thus keeping your interface looking nicer and more organized. Filters, on the other hand, are tied to their respective entities, so you can’t really “move” them.
• When you apply a filter to an entity in Google Sheets, the filter remains in place for all users until removed or changed. However, changes made by slicers are visible only to you unless you set them as default.
• Slicers can be saved as templates and reused, but basic filters cannot.

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

When to Use Slicers Instead of Filters

Slicers are great for sharing spreadsheets with clients or colleagues who want to easily turn them on and off to view important data.

It is also easier to use when applying multiple filters individually to a single data set.

If you plan to use similar filters on multiple sheets, a slicer that can be saved as a template might be a better choice.

More Actions for Slicers

Click the slicer’s three-dot menu to reveal more actions you can take. You can edit, copy, delete, set defaults, and learn more about slicer data.

You can also add multiple of the same slicer from this menu and set them to different data ranges:

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 to the appropriate position
3. Click the three-dot menu on the new slicer and select “Edit Slicer
4. Change the data set in the sidebar menu

This makes it easy to create relevant graphs (after applying conditions).

How to Delete a Slicer

Click the slicer three-dot menu and select Delete Slicer. You can also highlight it with your keyboard and press Delete.

What to Do if Your Slicer Isn’t Working

If you imported the slicer from another sheet, you may have missing data or incorrect cell references instead of simply importing the slicer. Copy everything from other sheets to a new sheet, then try using the following sheet names: A reference to existing data.

In some cases, the filter settings may be incorrect. To see them, click the filter button to the left of the slicer. Edit or remove filters as needed.

A data set can have more than one slicer. One might be cutting out data that you don’t want to slice. Check for extra slicers and edit or delete them.

Frequently Asked Questions

How Do You Add Slicers to Sheets?

To add a slicer to your sheet in Google Sheets, simply go to Data > Add Slicer from the main menu. Then select the range of cells in the data source to connect the slicer to and click OK.

How Does Slicer Work in Google Sheets?

Slicers in Google Sheets act like visual filtering elements. You can interactively select filters to apply to the data or filters to remove from the data. When you select a filter, Slicer applies the filter to all entities (tables, charts, pivot tables) on the active worksheet.

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

Slicers are linked to all pivot tables on the same active sheet. So if you link a slicer to multiple pivot tables, just make sure they are all on the active sheet.

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

Yes, slicers work with all charts and pivot tables that are based on the same dataset (if they are all on the active sheet.

How Do You Connect Slicers to Each Other?

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

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

Yes, I can. Double-click the chart and go to Data > Add Slicer.

How Do I Make a Chart From a Slicer?

Instead of creating charts from slicers themselves, slicers can be used as chart filters in Google Sheets, instead charting from the data slicers leave in pivot tables.

Can You Apply Multiple Slicers?

Yes, simply select the data range again and add the slicer to Google Sheets as usual.

What Do Slicers in Google Sheets Do?

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

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

Yes, right click on the slicer and select “Set Current Selection as Default.

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

You need to do this at the pivot table level, not from the slicer itself.

Conclusion

You can think of the Google Sheets Slicer as a more advanced, high-level version of Filters. They help you quickly slice your data and apply filters to all entities on the active sheet at the same time. They work like other visual control elements in Google Sheets, so they can be moved, stacked one above the other, and lined up, essentially turning the sheet into a more organized and interactive dashboard. Just convert.