Slicers in Google Sheets allow you to filter your source data dynamically, reflecting changes in associated charts and pivot tables.

Benefits of Using Slicers Over Traditional Filters

Imagine you have a dataset and have created both a chart and a pivot table from it. If you apply Data > Create a filter to the source data, it won’t reflect in the chart or pivot table.

For a pivot table, you can filter it using the pivot table settings. However, for a chart, no such direct option exists. You would typically have to use a filter formula to extract the data you need and then create the chart from that subset.

Slicers simplify this process. By connecting a slicer to your source data, you can filter it directly, and both the chart and pivot table will reflect the changes. Slicers are also user-friendly, allowing you to place them conveniently within your sheet.

Slicers to Filter Charts and Pivot Tables, Not the Source Data

Before using a slicer in Google Sheets, it’s essential to understand how it interacts with data filtering and where it should be placed within the workbook:

• Filtering a Data Range (Hiding Filtered Rows): If you want the slicer to hide specific rows based on filter criteria, insert the slicer in the same sheet as your source data. Placing it on a different sheet will not affect row visibility in the source data.
• Filtering Charts or Pivot Tables Without Affecting Source Data: If your goal is to filter charts or pivot tables without changing the visibility of source data rows, create these charts or pivot tables on a different sheet. Then, insert the slicer in the same sheet as the pivot table or chart. This ensures the slicer filters only those elements, leaving the source data intact.
• Filtering Source Data, Charts, and Pivot Tables Simultaneously: To apply a filter across all elements—source data, charts, and pivot tables—place them all on the same sheet. This way, the slicer’s filtering will be reflected across all components at once.

In summary, slicers only affect the sheet they are placed in. Therefore, positioning them strategically based on your filtering needs is crucial.

How to Create a Slicer for a Data Range in Google Sheets

Consider the following dataset, which tracks the monthly fuel consumption of several trucks. Column A contains truck numbers, column B lists months, and column C shows fuel quantities.

Steps to Add a Slicer to a Dataset

1. Select the data range A1:C19.
2. Go to the Data menu and click on Add a Slicer. The slicer will appear on your sheet.
3. Double-click the slicer or click on it, then select the three-dot menu and choose Edit Slicer.
4. In the sidebar panel, select the column to filter. Here, we select Month.

Applying a Filter Using the Slicer

To filter the Month column:

• Click the drop-down menu on the slicer and apply the filter as needed. The source data will be filtered accordingly.

Duplicating a Slicer to Add Additional Column Filters

If you need to filter multiple columns (e.g., the Vehicle # column in addition to the Month column), follow these steps:

1. Click the three-dot menu on the first slicer and select Copy slicer.
2. Right-click anywhere on the sheet and select Paste.
3. Double-click the newly added slicer, open the settings, and choose a different column to filter (e.g., Vehicle #).

Now, you can filter the table using two slicers. Based on the filter conditions, Google Sheets will hide or unhide rows accordingly.

Filtering a Pivot Table Using a Slicer

Let’s use the same vehicle fuel consumption dataset from Sheet1 to create a pivot table in a sheet named Dashboard. This setup ensures that filtering the pivot table does not modify the source data.

Creating a Pivot Table

1. Select cell D5 in the Dashboard sheet.
2. Click Insert > Pivot Table.
3. Under Data range, select Sheet1!A1:C19.
4. Choose Existing Sheet, enter D5, and click Create.
5. In the sidebar panel, drag Qty in Gallons under Values.

Now, your pivot table will sum the total fuel consumption.

Adding a Slicer to a Pivot Table

1. Click Data > Add a slicer.
2. Select the range Sheet1!A1:C19 and click OK.
3. In the sidebar panel, select the Month column.
4. Ensure that Apply to pivot tables is checked.

Adding Multiple Slicers to a Pivot Table

To filter the pivot table by vehicle number as well:

1. Duplicate the slicer as described earlier.
2. Set the new slicer to filter the Vehicle # column.

Now, both slicers can control the pivot table dynamically.

Adding a Slicer to a Chart in Google Sheets

Using the same Dashboard sheet, insert a column chart:

1. Click Insert > Chart.
2. Under Data range, select A1:C19.
3. Add Month under the X-axis.
4. Select Aggregate to summarize by month.

Since both the chart and pivot table share the same source data (Sheet1!A1:C19), they can be filtered simultaneously using the existing slicers.

Saving Filter Settings in Slicers

By default, when you close and reopen the sheet, any filters applied to the slicers will be lost. To save your filtering preferences:

1. Click the three-dot menu on the slicer.
2. Select Save current filter as default.

Customizing Slicers in Google Sheets

You can customize your slicers using the Customization tab:

• Title: By default, the slicer displays the column name. You can edit it to something more descriptive, like “Filter by Month”.
• Background Color: Change the slicer’s appearance to match your theme.
• Text Customization: Modify the title font, size, color, and format.

Conclusion

Now you know how to use slicers in Google Sheets to filter data dynamically. Whether you’re adding slicers to charts in Google Sheets, adding slicers to pivot tables in Google Sheets, or using slicers for a dataset, they provide an efficient and user-friendly way to refine data without modifying the original source.

Sample Sheet

Additional Resources

• Adding a Custom Formula in a Slicer for Charts in Google Sheets
• How to Include Filtered Rows in a Chart in Google Sheets
• How to Get Dynamic Range in Charts in Google Sheets