This post will show you everything you need to know about slicers in Google Sheets.

While working on Google Sheets, you have multiple options to apply filters to your data. One such filtering option are slicers!

You can apply filter through the Data menu, through the Filter Toolbar option, through a function, etc.

Slicers allow you to apply filters in an intuitive and visual way when using datasets or pivot tables.

Slicers are a visual filter. They allow you to slice your tables, Pivot tables, and Pivot Charts by providing a visual clickable way to filter data of a specific type.

In this article, you will learn everything you should know about slicers, including various ways of creating them, when to use them over other filters, the difference between slicers and other filters, and the customization options available.

This will be the ultimate guide for you regarding Slicers. After reading this article, I am sure that you will be well versed with this feature, and it is going to help you increase productivity in your day-to-day work life.

If you are equally excited as I am, download your copy of the example file through the link above to follow along.

Difference Between Slicers and Filters

Filters and slicers both allow you to filter data in your Google Sheets, but here are the main differences between them.

If you are new to filters, then check out the article on everything you need to know about filters in Google Sheets.

When to Use Slicers Instead of Filters?

These are a few cases in which Slicers have the upper hand over filters.

• If you have an audience that would like to check the effect of different item level filters on your data, you would ideally need to grant them edit access if you are using the filters. However, if you are using slicers, they can adjust the items by clicking on them with only view access. However, they can’t add a new slicer or edit the current one unless they have the edit access.
• When you want to apply multiple filters based on different criteria’s, it is useful to use slicers as they are visual filters and you can see which category filters are applied. In Filters you don’t have that luxury since they are fixed to the top cell and not visible to everyone.
• When you have a situation where the filters you apply need to be reused ,then slicers are the best option. Filters can’t be reused unless you save them as a Filter view in Google Sheets.

How to Create a Slicer for Your Data?

Here is how to create a slicer for your data.

The examples in this post will use a dataset of online sales with 1912 rows and 11 columns.

The data consists of different items that are being sold daily in different regions, with quantities, price, sales price, line price, etc. You can see an overview of this data, as shown in the screenshot above.

Now suppose you want to add a slicer to this data. Follow the steps below to add one.

1. Go to the Data menu from ribbon.
2. Click on the Add a slicer option. Clicking on this option will create a black slicer box in the middle of your sheet.

The slicer that you add is empty. You need to select a column to use in the slicer from the Slicer editor in order to use the slicer.

1. There are two sections in the Slicer editor. By default will be in the Data section.
2. You can see the data range for which slicer is added. You can alter this range by clicking on the square ison placed next to the range.
3. In the Column dropdown, select a column to use in the slicer. This example will select the City column to add to the slicer. This will now enable the slicer to visually filter the entire data based on the City column.

Note: In the demo shown above, the slicer is added in the middle of the data table. Next demo onwards, the slicer will be placed to the right of the data table.

How to Filter By Condition with a Slicer

Once you add a slicer into your sheet, you would like to filter it either by values or conditions.

The standard conditions are filter text, numbers, dates, and a custom formula.

Filter By Text Through Slicer

Now, suppose you want to filter the data based on the city named Regina. Follow these steps.

1. Click on the All dropdown from the slicer.
2. Click on the Filter by condition dropdown to expand it.
3. Select the Text Contains condition to add a text filter based on the City.
4. Type Regina inside the Value or formula section.
5. Click on the OK button.

Once you hit the OK button, you can now see that the entire data table is filtered, and you can now only see cells for Regina city.

• Note 1: You can also open the filtering window by clicking on the three horizontal lines (inverted pyramid) placed beside the column name City in the slicer.
• Note 2: There are various text conditions such as Text starts with, Text ends with, Text is exactly, and Text does not contain. You can explore all of them and see how the filtering output changes.

Filter By Date Through Slicer

There are three ways of filtering data through slicers based on dates.

You can filter by the Date is, Date is before, and Date is after. You can either filter based on an exact date, and dates less than or greater than a specific date.

1. First, you will need to select a date column for your slicer in order to use the date filtering options. You can change the column to the Order Date through Edit slicer menu. It can be accessed through three horizontal dots found in the slicer on the very right.
2. Select the Date is before options in the Filter by condition.
3. Select the exact date option. Other options suchs as before today, tomorrow, yesterday, one week before, one month before, and one year before are also available here.
4. Add a date value such as 5/31/2021 inside the Values or formula input.
5. Press the OK button to apply the filter.

When you apply this filter, you will see all the rows where the date is before May 31, 2020.

Note: Try out the other options to filter the data table with the exact date and date after conditions.

Filter By Number Through Slicer

Filtering number columns is also possible with slicers.

This example will use the Price column in the slicer to filter by number conditions. You can access the Slicer editor through the three vertical dots menu and change the column.

1. Click on the Filter by condition to expand the available options.
2. Select the Is between option. Other conditions such as Greater than, Greater than or equal to, Less than, Less than or equal to, Is equal to, Is not equal to, and Is not between are also available.
3. Add a lower bound number such as 50.
4. Add an upper bound number such as 100.
5. Press the OK button to apply the filter.

This will filter out all the rows for which the price is between 50 and 100 as lower and upper limits.

Filter By Custom Formula Through Slicer

Out of all the available conditions, filter by a custom formula is the most interesting.

The Custom formula is a condition that allows you to filter the data based on any formula!

This example will use a custom formula to return all the rows for which values in the Price column are odd. This is something you wouldn’t be able to achieve with the other Filter by condition options.

1. Click on the All dropdown to open the filtering pane and click on the Filter by condition dropdown.
2. Select the Custom Formula is from the available conditions. This will allow you to write a formula of your own to filter the data.
3. In the Value or formula box, type the formula = ISODD ( $I$2:$I$1913 ). This formula will check all rows of the Price column, return the output as TRUE if the cell value is odd else returns FALSE in case it is even.
4. Click on the OK button to apply this filter.

You will see that all the rows remaining from the dataset have odd values in the Price column. Similarly, you can use the ISEVEN function to filter on all the even rows.

How to Filter By Value With a Slicer

Filtering by value is a common approach while applying a filter. The option is also available for Slicers, and you can use it through them as well.

You need to create a slicer based on a numerical column for this.

1. Go to the All dropdown to open up the filtering pane.
2. Click on the Filter by values to see all the available values to select from.
3. You can see all the values associated with the column. All of the values have tick infront of them. It means they are selected.
4. Press the Clear button which is colored blue to clear all the values from selection. Then tick on any of the available values. In this example, choose the first value of 6. A tick will appear in front of the value to show it is selected in the filter.
5. Click on the OK button to apply this filter.

Once you press the OK button, you will see only the lines where the Price is 6 in the data table.

These are a few basic yet essential ways in which you can use the slicer in Google Sheets.

How to Apply a Slicer to Your Pivot Tables

Slicers also work great with Pivot tables! Using slicers with pivot tables means you can filter and summarize your datasets at the same time.

Here’s how to add a slicer to the Pivot table.

To create a Pivot table, follow these steps.

1. Click on the Insert menu.
2. Click on the Pivot table option to insert a pivot table.
3. The system will select the active Data range for the Pivot table by default. If not, you can choose it manually.
4. You can create the Pivot table either on an Existing sheet or a New sheet. Choose any of these options.
5. Click on the Create button to add a pivot table.

In this example, a Pivot table has been created where the Line Total is summed by province.

You can now add a slicer for this pivot table.

With the Pivot table created, go to the Data menu and choose Add a slicer from the options.

As soon as you click on Add a slicer button, you will see a slicer appear on the sheet and the Slicer editor will open up on the right-hand side of the sheet.

Within the Slicer editor, select a column of your choice for the slicer. In this case, we will choose the column City as a slicer.

Note: The column doesn’t have to be included in the pivot table. You will still be able to filter the pivot table based on a column not appearing in the pivot table fields.

Once the slicer is set up, you will see a dropdown with All items selected.

Click on All, and you will see all the available filtering options based on the City column. You can now filter this Pivot table and get a filtered view of the Line Total based on a specific City.

All of the Filter by condition and Filter by values methods will also work with a pivot table.

How to Apply Slicer to Your Charts

One of the best things about slicers is, you can even apply them on the charts you create based on your data table or a Pivot table.

It is an exciting aspect that isn’t available with the regular filters.

Since the inclusion of Slicers in Google Sheets, you now have the liberty to use them as a filtering criterion inside the charts you generate from your data.

You can set the filter for your graph by any column that is present in your data table with the slicer.

Slicers can work on charts created on both regular tables as well as Pivot tables.

This section will cover both ways of adding a slicer to your graphs.

Apply Slicer to the Chart Created From a Table

If you have a data table in your sheet, follow the steps below to create a chart and apply a slicer.

1. Select the columns in the dataset which are to be displayed in the chart. In this example, select the Category and Price columns together. You can select one column first and then hold the Ctrl key to select another column.
2. Go to the Insert menu.
3. Select the Chart option from the menu to insert the chart based on the two selected columns.

The chart will show the average Price plotted for each Category.

Note: The chart type is selected by Google Sheets based on the selected data. You can change the chart type if needed.

Now you can insert a slicer for the chart.

1. Go to the Data menu
2. Select the Add a slicer option from the menu. Make sure you keep a cell from your data table selected.
3. Select the City column for the slicer.

Select a specific city from the slicer’s filtering menu and you will see the chart updates. Select Regina and press the OK button to apply this slicer on the chart created.

You will see that the graph changes its values to show the average Price by Category for the selected City.

Apply Slicer to the Chart Created From a Pivot Table

You can apply slicers effectively on charts that are either created with a table or a pivot table.

The previous section showed how to add a slicer to a chart created based on a table. This section will apply the slicer to a chart created based on a pivot table.

To create a chart based on a Pivot table, follow these steps.

1. Click on Insert menu and select the Pivot table option to insert a pivot table. The example above shows the Sum of Line Total by Province.
2. Click on the Insert menu again and select the Chart option. This will add a chart based on pivot table created in the previous step. For this example, we have chosen the Donut as a chart type.
3. To add a slicer that works on this chart, click on the Data menu and select the Add a slicer option. The range for this slicer should be the same as the original data table range. In this example it is Data!A1:K1913.
4. Select a Category column for the slicer.

As soon as you select a specific Category in the slicer, the donut chart will update based on the selection.

Can You Apply Multiple Slicers?

Adding more than two slicers to your table doesn’t work.

If your data has a slicer already based on a specific column, and you try to add a new one, you will see an error saying Two slicers can either have zero or all common rows.

However, if you want to add more than one slicer into the Google Sheets it is possible.

There is a valuable hack that you can use to add more than one slicer to your table inside of Sheets.

You can copy the already added slicer and change the column within the copy. This way, you will have two slicers for your data table.

You can expand this method and add more than two slicers as well on the same source table.

You already have a slicer based on City for your data table in this example.

Click on the black slicer box to see the three vertical dots. They represent the options menu. Click on it to see all the available options.

You will see the Copy slicer options too. Click on it to make a copy of this slicer.

Paste the slicer on any other cell and change the column through the Slicer editor. This time, select the Category column to apply a second slicer based on this column.

You can add more than one slicer to your data table to filter it based on multiple categories by following the method above.

Note: Unlike with data tables, you can add more than two slicers to your pivot tables.

How to Edit and Delete a Slicer

Any tool that you use in Google Sheets comes with its own edit and delete menu.

You can edit as well as delete a slicer through the slicer settings/options menu. Click on the three vertical dots on a slicer to open the settings/options menu.

Edit a Slicer

1. Click on the slicer and then click on the three dots to open the settings/options menu.
2. Click on the Edit slicer option to open the Slicer editor at right of the sheet.

Through the Slicer editor, you can edit several options.

1. You can change the source of the data through the Data range option. Click on the square icon next to the Data range to select a new range.
2. You can change the Column that should appear in the slicer.
3. You can control whether the slicer created is applied to the pivot table with the same Data range using the Apply to pivot tables checkbox.
   • If you check the Apply to pivot tables option, the system will automatically apply the slicer to any pivot table that you create using the same data source.
   • If you want to keep the Pivot table and data slicers independent of each other, keep that option unchecked.

You can also increase the slicer size. When you select a slicer, blue lines will appear around the slicer box and you can click and drag this to increase or decrease the size.

You can also change the position of the slicer by dragging it anywhere on the sheet.

Delete a Slicer

To delete the slicer you created, click on the slicer settings/options menu and select Delete slicer from the options.

You can also click on the slicer and press Delete key from your Windows or iOS keyboard.

There are two other slicer options worth noting, Copy slicer and Set current filters as default.

The Copy slicer option allows you to create a copy of your slicer.

The Set current filters as default option allows you to set the currently filtered layout as default, which you and everyone else who has access to the sheet can use.

Double Click a Slicer to Edit

A handy tip for accessing the Slicer editor is you can double click on the slicer.

This will open up the Slicer editor where you can edit the Data range, change the slicer Column, and add a current slicer to Pivot tables.

How to Customize a Slicer

An interesting thing about the Google Sheets slicers is they are very customizable.

You can easily customize them to change the Title, font size, font appearance, alignment, and Slicer color.

You have the Customize tab available under the Slicer editor that appears on the right of the sheet once you click on the Edit slicer option.

1. You can change the Title of the slicer.
2. You can change the Title font. Just click on the dropdown and select a font of your own.
3. You can change the font size. Click on the Title font size dropdown and select any font size that suits your style.
4. You can add bold, italic, and change the alignment of the Title with options in the Title format.
5. You can change the text color of the title. Click the Title text color dropdown to change the color.
6. You can change the background color of the slicer. Click on the Background color dropdown to choose any color.

Conclusions

Slicers are visual filters and allow you to take control of the way you slice your data.

You can apply slicers to Data Tables, Pivots, and Charts to filter these layouts.

You can filter the data through a slicer based on Text, Numeric, Date, or a Custome Formula conditions.

This is very similar to regular Filters with one added advantage that the slicers show you how many items are selected in filter.

You can also filter the data based on Values by selecting values from a list.

Slicers work great with pivot tables and you can even add a column to the slicer which is part of your pivot table layout but is in the source data.

You can also use slicers with Charts created on Tabular data or Pivot Charts. It works great on both of these layouts.

Slicers are an extremely versatile and useful way to explore your data!

Are you using slicers in your Google Sheets yet? Let me know in the comments below!