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
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.
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.
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.
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.
Since we want the height less than 200, so choose “Less than”, then enter the value 200.
Hit “OK” and the table would only show the entries for the book with a height of less than 200.
Dynamic Charts using Slicer in Google Sheets
Another very interesting and useful feature you can implement is by making dynamic charts.
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.
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.
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.
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