In Google Sheets, we can use slicers to filter data in charts, tables or even pivot tables! Slicers allows users to filter data and find specific information using the slicers easily, without affecting other users view - we'll explain more about this in this article later.
What do slicers in Google Sheets do?
Slicers is commonly used in reports and dashboards built in Google Sheets. Not only slicers make it easy for users to interact with the report, it's also extremely easy to add a slicer in Google Sheets!
Well, if you want to know exactly how a slicer looks like in Google Sheets, here's a photo of a customised slicer in Google Sheets.
Still wondering how to insert a slicer in Google Sheets report, this article is perfect for you!
Advantages of using slicers over filters
Slicers is a graphic object that stays above the spreadsheets grid. When users clicked on the slicer, they'll see all the available options and able to select the desired value from the pop-up window. Hence, data can be sliced-and-diced to provide a custom view to the users.
In this article, we'll discuss mainly on using slicers to filter data in Google Sheets tables and charts. However, it is important to highlight why most of the time we prefer to use slicers instead of direct filters, which is a commonly known method to filter data in Google Sheets.
In Google Sheets, there are multiple ways you can filter data.
- Using Direct Filters
- Using Slicers
Difference between Google Sheets Slicers, Filters and Filter Views
Slicers vs Filters vs Filter Views
The main difference between a slicer and a filter is the reusability. For instance, we have a Sales report that shared across the entire team. Person A may want to filter the dashboard by the date to "last week only". Whereas Person B prefers to filter the table by the field: salesperson.
If the filter is developed using slicers, they'll get their own view based on the values selected in the slicers. After they have used the slicers, the values would be reset to default after they close the window.
However, if the report filter is configured using direct filters, there are a few limitations here:
1. Both of them can't be using the report at the same time, as the filter would applied to the entire report and affects all the report users.
2. They will have to reset the filters back to default manually after using the report so that the next person would get the default view. Otherwise, the next person who opens the spreadsheet would see the previous filters.
This is very troublesome and would impact the user friendliness when using the report. Hence, this is why we opt for slicers instead of filters for report and dashboard in Google Sheets.
There are other differences between the filtering options in Google Sheets. I recommend you to check out this Filters, Filter Views & Slicers documentation if you wish to find out more.
How to add a Google sheets slicer?
To insert a slicer in Google Sheets,
1. Open your spreadsheet at sheets.google.com
2. At the top menu, click Data > Add a slicer.
3. At the right, choose a column for the slicer.
How to use the slicer ?
Now that you have inserted the slicer. You can start using the slicer by clicking on it. By default, the slicers will filter by values. For example, to extract the content ideas that needs to be shoot, you can click "Clear" and then tick the value "#2 Video Shooting" in the dropdown. Then, the rules would be applied to the entire report under the same tab.
To deselect a value, simply untick the values in the window to have the conditions removed.
You can also choose to filter by condition for more advanced filtering rules.
How to customise Google Sheets slicer colour?
As you can see above, my slicers has a background colour of white, whereas yours would probably have a black background colour and a white coloured text.
If you wish to change the style format of the slicer, follow the steps below:
- Right click the slicer and click Edit.
- At the right, switch to Customise tab. Then, change the background colour and title text colour. You can also customise the title font and the font size from here.
How to rename the slicer in Google Spreadsheets?
By default, the slicers would use the field name in the dataset. However, when the dataset field name is not very reader-friendly, we would want to rename the slicer display text so that it's easy to use and understand.
To rename the slicer, follow the steps below.
- Right click the slicer and clickEdit.
- At the right, switch to Customisetab. Then, under Title, replace the default title with the new slicer name.
How to filter data without affecting other users in Google Sheets ?
If you already creating slicers to allow data filtering in your spreadsheet report, it would not affect other users even you use the same spreadsheet concurrently, unless you set the filters as default.
Continue reading to find out more on setting default filters in Google Sheets.
How to set default slicer values in Google Sheets? (Save the condition setting for each slicer)
Default value is the pre-defined filters applied to the report. When the report editors decided to set a specific value(s) as the default values, the same filters would be kept even after the spreadsheet is closed.
This feature can comes handy if there's a specific condition sets that every spreadsheet users would need to applied before they start using the report. This can be a huge time-saver as the filters is applied automatically every time they open the spreadsheets.
Some applications may include: excluding NULL or empty values from the table, setting status to "to-be-completed", and many more! The application is endless with your creativity in approaching the problem :)
So, how do we save the slicer filtering selections?
1. Click the slicer to select conditions to be set as default. Refer to how to use the slicer section above to learn how to select filters.
2. Right click and select Set current filters as default.
I hope this guide is useful for you! For more tips like this subscribe to my email list so you get the most updated tips for your report development.