Filter using Slicer in Google Sheets

Filter using Slicer in Google Sheets
Reading Time: 4 minutes
Slicer in Google Sheets
Slicer in Google Sheets

Learn how to Filter data using Slicer in Google Sheets.

Why use Slicer in Google Sheets?

Suppose you have a huge data table and it contains a lot of fields and entries. For example, you have a large table of books, it contains all the relevant information about them, author, publishing year, genre, etc. Now let’s say you want to filter the data such that you only see the data for the “fiction” genre. That’s where slicer in Google Sheets can come in very handy.

Slicer in Google Sheets is a tool that lets you slice your table conditionally. You can define conditions manually as well as prebuilt conditions. 

Slicer in Google Sheets: Basic use

Let’s take a table that contains a list of books along with all the relevant information such as title, author, genre, etc.

Suppose we want to only see the information for the genre “fiction”. To implement so, go to Data->Add a Slicer

Slicer Sidebar
Figure 1: Slicer Sidebar

A sidebar and a floating panel like such should open up. Let’s go through the fields one by one.

Data range: As the name suggests this is the data range across which you want Slicer to work. It contains the sheet name followed by the cells. For this example, the name of our sheet is books_new followed by the range of the table.

Column: This would be the column by which you would want to filter data. It is a dropdown that contains the header of the columns.

Figure 2: Column in Slicer Sidebar

Since we want to slice by Genre, we will choose the same option. After selecting the Columns you’ll see the floating toolbar would change per your choices. On expanding it would look something like this.

Floating toolbar options in Slicer
Figure 3: Floating Toolbar options

There are two options, either filter by conditions or by values. We will come to filter by condition later for now let’s filter by values. It’s pretty straightforward, select the values that you would want to see. To see the book for Genre “fiction”, uncheck all the values but “fiction” and then hit “OK”.

You’ll see the table would change to something like this.

Filtering books in the "fiction" genre
Figure 4: Books having genre “fiction”

Only those entries can be seen which have their genre as “fiction”.

Filter by Condition using Slicer

Let’s take the same table, and this time we only want the books to have a height of less than 200. To do so First we would need to set the column in Slicer as Height. After that Select Filter by the condition in the floating bar.

Filter by condition options in Slicer
Figure 5: Filter by condition options

Since we want the height less than 200, so choose “Less than”, then enter the value 200.

Floating toolbar settings to filter books having height less than 200
Figure 6: Floating toolbar settings to filter books having a height of less than 200

Hit “OK” and the table would only show the entries for the book with a height of less than 200.

Resultant Table
Figure 7: Resultant Table

Dynamic Charts using Slicer in Google Sheets

Another very interesting and useful feature you can implement is by making dynamic charts. 

Pie-chart of  genre distribution in books
Figure 8: Pie-chart of genre distribution in books

Herein, I made a piechart of the table by genre. Currently, no filter has been applied for Slicer. But imagine, if we want to see the distribution for books having a height less than 200, then we can use slicer and the chart would automatically get converted according to the filter applied.

Pie-chart of  genre distribution in books after applying filter
Figure 9: Pie-chart of genre distribution in books after applying filter

Since tech books saw a drastic decline in distribution, we can infer that generally, books in the “tech” genre are more thick compared to the other Genres.

Conclusion

We learn to use Slicer in Google Sheets and saw how we can filter our data using the same. We can also make use of it to make dynamic tables which can help us a lot in analysis. You can also check the official documentation for Slicer here.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

How to create Heat Maps: Understand how to create Heat Maps using Conditional Formatting in Google Sheets

Conditional Formatting in Google Sheets: Get started with Conditional Formatting in Google Sheets