How to use Slicer in Google Sheets

Let’s say you are a sales manager preparing a report for a meeting. You have a large dataset with sales figures from different regions, products, and salespeople. It would help if you showed your team how each region performs without changing the data everyone else is working on.

What is a Slicer in Google Sheets

Unlike a normal filter, a slicer is a filter that is not applied to the column header, but it’s detached from the data, which you can move anywhere in the worksheet to filter data. With a slicer, you can filter a single table, multiple tables, pivot tables, and charts, which makes it the perfect tool for filtering data in reports and dashboards.

To prepare your data for applying a slicer, start by organizing and structuring it cleanly. Ensure each column has a clear header, like “Date,” “Salesperson,” “Region,” “Product,” and “Sales Amount.” Ensure no blank rows or columns are within your dataset, as this can cause a problem when adding slicers.

How to Insert a Slicer

Once your data is ready, you can use the below steps to add a slicer to your data table.

1. First, select any cells from the data you have in your worksheet.
2. Now, go to the “Data” menu at the top of the Window and select “Add a Slicer”.
3. Then, from the pane on the right side, select the columns you want to add to the slicer for the filter.
4. In the end, click on the “All” button to open all the values you have in the slicer to filter the data.

When you select any of the values from the slicer to filter the data and click OK, it automatically filters the table using the selection you have made in the slicer.

Filter by Condition in a Slicer

Filter by condition in a slicer allows you to filter your data based on specific criteria. Instead of selecting items from a list, you can set conditions to show only the data that meets specific conditions.

For example, you can filter to show only sales for products that are not “Widget A”. See in the below example, I have used the “Text Does not contain” and entered the “Widget A” value in the input bar. This tells the slicer only to filter data for products, not “Widget A”.

Note – One of the best uses of a slicer is to create a custom dashboard in Google Sheets and control that entire dashboard with slicers.

Edit and Customize a Slicer

Once you insert a slicer in your worksheet, you can edit and customize it. Once you select the slicer, click on the three dots and click “Edit Slicer”.

This will open a new panel on the right side of the Window. In this panel, you can choose the column you want to filter by, such as “Region” or “Product”.

To customize the appearance of your slicer, click on the Customize tab. Here, you can change the slicer’s title to make it more descriptive. You can also change the font style, size, Format, text color, and background color.

Filter a Pivot Table with Slicer

There are two ways to filter a pivot table with a slicer. One is to apply a slicer to the data table and activate the “Apply to pivot tables” from the edit panel. The second is to apply the slicer to the pivot table. Both work similarly, and there is no difference between them.

In the below example, we have created a pivot table from our data, which has a slicer to control the filtration. When you filter the data, the pivot table is also filtered.

Read Also – Refresh a Pivot Table

Filter a Chart with a Slicer

If you apply a slicer to a data table and then create a chart from that data, you can filter that chart along with the data. This makes it easy to present complex data in a simple form as a chart.

In the above example, we have a bar chart from the data to which we have added a slicer. Now, when I filter only the “Widget A” from the slicer, I have a chart with only bars for the “Widget A” for January to June.

As I said, this allows you to present your data differently and in a less complex view. Because when you add data for all three products, it is hard for the viewer to read the chart.

Using Slicer with Convert to table

Now, you can create a table in Google Sheets, like Excel. When you go to Format > Convert to table, your normal data table is converted into a structured one.

You can also add a slicer to this table to filter data. This is one of the best ways to work with data and slicer, as with the “Convert to table,” you will have a dynamic table that will extend.

It will also extend the range for the slicer, and you don’t need to update the range in the slicer.

Delete a Slicer

When you click on the three dots on the slicer, you can delete it from the worksheet. Alternatively, you can use the Google App Script code below to delete all the slicers from a worksheet in a single go.

function deleteAllSlicers() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var slicers = sheet.getSlicers();

  // Loop through each slicer and remove it
  for (var i = 0; i < slicers.length; i++) {
    slicers[i].remove();
  }
  
  Logger.log('All slicers have been deleted.');
}

Go to Extension > App Script and add the code to the Google App Script Editor.

A few Points to Note Down

Using a Slicer in Google Sheets is simple, and you don’t need to take care of many things. But here I have some important points for you to know to use them with the full.

• You can add multiple slicers to a single data table to filter by different columns simultaneously. You might have one slicer for “Region” and another for “Product” to see sales data specific to a particular region and product.
• Adjust the placement and size of your slicers to ensure they are easily accessible, but do not clutter your sheet.
• If your sheet is protected and you have a slicer, the other users won’t be able to use the slicer to filter the data.
• When you convert an Excel Workbook to Google Sheets, and the workbook has a slicer, Google Sheets won’t convert that slicer from Excel to Google Sheets.
• Like a normal filter, the slicer doesn’t have a sort option to sort the data.
• When you download a Google Sheet as a PDF and filter the data with a slicer, you will get all the data in the PDF without any filter.