What Are Slicers In Google Sheets?
Slicers in Google Sheets are floating filters that we can move anywhere in the sheet. They enable us to filter data to present and manage the required information interactively.
Users can utilize the Slicer option in Google Sheets to extract specific data from complex tables and massive Pivot tables. They also help display the required information from complicated charts to make them easier to interpret.
For example, the given dataset shows employee IDs, their designations, and appraisal statuses.
The aim is to create an interactive filter to display the details of the last four employee IDs.
Then, we can use the Slicer feature, similar to Excel Slicers, to apply the required filter to the given dataset based on the meaning of the Slicers in Google Sheets explained earlier.
We shall see the steps for using Slicers in Google Sheets.
Firstly, we must click a cell in the given dataset. Next, select the Add a slicer option under the Data tab.
While we will see a floating filter as the slicer in the work area, the Slicer pane will appear on the right of the work area.
The pane has two tabs, Data and Customize. The Data tab shows two fields: Data range and Column. The first field shows the source dataset range.
Next, click the Column field dropdown button to choose the specific source dataset column based on which we must filter and display the required data from the given dataset. In this case, we must choose Employee ID as we must display the data for the last four IDs’ data.
After that, click the filter icon on the left end of the slicer or the dropdown button on the right end of the slicer. We shall see two options to filter: Filter by condition and Filter by values. When we expand the second option, we will see the list of unique employee IDs. Here, we can click on the IDs to select and unselect them. So, we can unselect the IDs whose data we aim to filter out and not display in the dataset. Finally, click OK.
So now we shall see the filtered data in the given dataset, which is the required data pertaining to the last four employee IDs.
Likewise, we can insert multiple Slicers in Google Sheets for the given dataset using the method explained above. However, we can have one slicer per column, with one slicer added at a time.
On the flip side, we can click the slicer to select it and then click Delete or Backspace to delete slicers in Google Sheets one at a time.
Table of contents
Key Takeaways
- Slicers in Google Sheetsare interactive filters that we can place anywhere in the work area. They enable us to showcase and handle the given data according to our requirements.
- We can add or delete one slicer at a time in Google Sheets. Also, each slicer can work for one column of the given dataset. Further, we can insert multiple slicers in one sheet.
- The Google Sheets Slicers are movable, editable and customizable.
- The Slicer feature in Google Sheets is helpful for filtering data in Pivot Tables and charts.
How To Add And Use Slicers In Google Sheets?
The steps to add slicers in a Sheet are as follows:
- Ensure the given dataset is accurate and in the correct data format.
- Click on a cell in the specified dataset and choose the Data tab – The Add a slicer option.
The above step will add the Slicer to the work area and open the Slicer pane on the right of the work area.
We can iterate step 2 to add multiple slicers in Google Sheets for the specified dataset, with one slicer added per column at a time.
The steps for using slicers in Google Sheets are as follows:
- The Slicer pane contains two tabs, Data and Customize. The Data tab shows two fields: Data range and Column. While the first field holds the source dataset range, we can choose the column cited in the source dataset in the second field, based on which we aim to apply the filter.
So, click the Column field dropdown button to choose the specific source dataset column according to which we must extract and showcase the required data from the given dataset.
2. Click the filter icon or the dropdown button in the slicer to view the filter options, Filter by condition and Filter by values. We can click one of the options to expand and use it.
3. The Filter by condition option includes one field where we can select the condition from the dropdown list.
After that, another field appears where we must update the required condition or value based on which we must apply the filter to the given dataset.
On the flip side, the Filter by values option shows two options: Select all and Clear. We can choose the Select all option to choose all the values of the chosen column. On the other hand, clicking Clear will remove all selections made in the list of values of the chosen column. Furthermore, there is a Search field where we can enter the value to search in the list of the values of the chosen column. On the other hand, we can manually click on the specific values of the chosen column from the list below the Search field.
4. Click OK to apply the required filter to the source dataset.
However, if we want to delete slicers in Google Sheets, we can do so by right-clicking the specific slicer or clicking the slicer to view and click the menu icon ( ) in the slicer. After that, we can select the Delete slicer option from the popup menu.
Examples
The following examples help us understand the definition of Slicers in Google Sheets explained earlier effectively.
Example #1
The dataset below lists the branch office-wise monthly sales figures of a firm.
We must filter the data by the branch office, displaying the data of all the branch offices except Houstonand Tampa. Then the steps are the following:
Step 1: Choose a cell in the given dataset. Next, choose Data – Add a slicer.
We can see the slicer in the workspace and the associated Slicer pane on the right of the work area.
Step 2: The Data range field in the Data tab in the pane contains the given dataset range. Next, we set the Column field as Branch Office using the field’s dropdown list.
Step 3: Click the filter icon in the Slicer to view the filter options.
Next, keep the branch offices, whose data we aim to show in the Sheet, selected. In contrast, unselect the branch offices we do not want to show in the Sheet.
Finally, once we click OK below the filter options, we shall see the dataset filtered based on the branch office.
Example #2
We have a list of orders with details about the products, categories, order costs, delivery dates, and delivery locations.
The requirement is to prepare a Pivot table, similar to Excel Pivot table, for displaying the delivery location-wise order costs of each product. Next, we must filter the Pivot table based on the product.
Step 1: Pick a cell in the source dataset and choose Insert – Pivot table.
The Create pivot table window appears, where the source dataset range is available in the Data range field. Next, we shall select the New sheet option to display the Pivot table in a new sheet. Next, click Create.
Step 2: We see a new sheet showing an empty Pivot table in the work area, with the Pivot table editor on the right of the window.
We shall drag the appropriate columns from the list below the Search field to the required sections in the editor.
Thus, we achieve the desired Pivot table.
Step 3: Click inside the Pivot table and choose Data – Add a slicer.
Step 4: We see the slicer inserted in the workspace and the Slicer pane on the right of the window.
The first field, Data range, holds the source dataset range. Next, since we must filter based on the product, set the Column field as Product by selecting the specific column from the field’s dropdown column.
Step 5: Assume that we must display the data for the products Banana, Beans, and Broccoli. So, click the All dropdown button to see the filter options. Unselect all the products listed in the Filter by values option, except Banana, Beans, and Broccoli.
Finally, click OK under the Slicer options to view the pivot table after applying the required filter.
Example #3
We have a dataset showing the annual product-wise inventory levels.
We need to create a Google Sheets Column chart, similar to Column chart in Excel, for the given data. Next, we must display the data for 2021-23 in the plot.
Step 1: Pick a cell in the source dataset and then Insert – Chart.
The above action will show a plot in the work area and the Chart editor on the right end of the window.
Since we need a Column chart, and it is the chart that is inserted by default, we are not required to change the chart type in the editor.
However, we shall double-click on the chart title to update it according to our requirements.
Step 2: Click on a cell in the source dataset and then Data – Add a slicer.
Step 3: The slicer is inserted in the workspace, with the Slicer pane on the right of the workspace.
Since we must filter by year, set the Column field in the Data tab in the Slicer pane as Year from the field’s dropdown list.
Step 4: Click the filter icon in the Slicer to view the filter options.
Next, click the Filter by condition section to expand it.
Set the first field in this section as the Greater than option from the options in the field’s dropdown list.
Another field will appear below the first field, where we enter the year 2020, indicating that the data of the years before 2021 should be filtered out from the chart.
Click OK.
Once we click OK, the plot will show the data for 2021-23, according to the requirements.
How To Edit Or Customize A Slicer In Google Sheets?
We can edit or customize a slicer in Google Sheets using the following steps:
- Right-click the specific slicer, or click slicer to view and select the menu icon ( ) in the slicer, and select Edit slicer from the popup menu.
2. The Slicer pane appears on the right end of the window, with the Data tab open. We can make the required changes using the appropriate options in the Data tab.
3. Click the Customize tab in the Slicer pane to open it. The tab shows a list of options we can use to customize the concerned slicer. We can change the slicer title, title font, size, format, text color, and background color.
Google Sheets Slicer Not Working
Google Sheets Slicer is not working, perhaps because of the following reasons:
- Importing a slicer from another worksheet may lead to the data missing issue or incorrect Google Sheets cell references, which are similar to Excel cell references.
- Incorrect filter settings may cause the slicer to appear as not working.
- When we have more than one slicer for a source dataset, chances are that one slicer might filter out data that we require in another slicer.
Important Things To Note
- When we want to copy Slicers in Google Sheetsfrom one sheet to another, it is best to copy the source data from the other worksheet into a new sheet. Next, we can utilize the sheet name for referencing the given data.
- Ensure to recheck the filters using the filter icon in the left end of the slicer to avoid scenarios of incorrect filter settings.
- The suggestion is to change or delete unwanted slicers so that we can avoid the situation of one slicer slicing off a section of the source data we require in another slicer.
Frequently Asked Questions (FAQs)
You can link a Slicer to multiple Pivot Tables in Google Sheets, provided all the Pivot tables and the slicer are in the same sheet.
The steps to link a slicer to multiple Pivot tables are as follows, explained with an example.
The current sheet contains two Pivot tables, created using one dataset, to show the monthly units sold data of a list of stationery items.
Step 1: Select a cell in one of the Pivot tables and choose Data – Add a slicer.
Step 2: The above step inserts a slicer in the workspace, with the Slicer pane open on the right end of the window.
Next, assume we must filter the Pivot tables based on the Stationery items. Then, set the Column field in the Data tab in the Slicer pane as Stationery Item by choosing the option from the field’s dropdown list.
Step 3: Assume we must present the data for all the stationery items in the two Pivot tables, except those of the A3 Sheets and A4 Sheets.
Then, click the filter icon in the slicer to view the filter options. Unselect the A3 Sheets and A4 Sheets items from the list in the Filter by values section.
Next, click OK.
We see that the filters applied via the slicer get reflected in the two Pivot tables, indicating that the slicer is linked to the two Pivot tables.
We can save the condition setting for a Slicer in Google Sheets by right-clicking the slicer and choosing the Set current filters as default option from the popup menu.
The difference between a Slicer and a Filter in Google Sheets is that a slicer can work similarly to a filter. However, it is a feature that we insert into our sheet, which makes the sheet more interactive. Furthermore, it provides a movable and customizable interface that we can utilize to filter data.
Download Template
This article must be helpful to understand the Slicers In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Are Slicers In Google Sheets. Here we learn how to use Slicers In Google Sheets with examples and points to remember. You can learn more from the following articles. –
Leave a Reply