Adding a Custom Formula in a Slicer For Chart in Google Sheets

Do you want to add a custom formula in a Slicer for filtering charts in Google Sheets? While it might sound intimidating, I’ve got you covered. However, there is one issue you might encounter when the source data and the chart are on different sheets in the same workbook. But worry not, I have a workaround for that too. Let’s dive in and learn how to add a custom formula in a Slicer for a chart in Google Sheets.

Custom Formula in a Slicer Filter by Condition to Filter a Chart – Example

In this example, we’ll create a chart using the Scorecard chart type as it’s sufficient for our purpose. Here are the steps:

1. Create a chart by going to Insert > Chart in “Sheet2.”
2. Select the Scorecard chart type.
3. Enter the data range as “Sheet1!C2:C19.”
4. Select “Aggregate” as the chart type.

To provide context, we have a sample dataset of diesel consumption for six vehicles from January to March in a year. You can even use the UNIQUE formula to find the unique vehicles in the dataset.

Now, let’s move on to adding a custom formula in the Slicer for filtering the chart based on vehicle numbers.

Sheet2 (Slicer)

In “Sheet2,” where the chart resides, follow these steps:

1. Go to Data > Add a Slicer.
2. In the “Select a data range” field, enter “Sheet1!A1:C19” as the Slicer source data range and select OK.
3. In the Column field, select “Vehicle #.”

Adding Custom Formula in Slicer for Chart

Now, let’s add a custom formula to the Slicer in order to control the Scorecard chart.

1. In cell “Sheet2!A1,” enter any vehicle number from the source data (e.g., 1111597).
2. However, we can’t use this cell directly in the Slicer’s custom formula for filtering the chart. We need to enter the criterion within “Sheet1.”
3. In “Sheet1!D1,” enter the formula “=Sheet2!A1.” This cell acts as a helper cell.
4. Now, we can add the custom formula to the Slicer using the formula =$A2=$D$1.
   • $A2 represents the first cell in the column to filter (Vehicle #).
   • $D$1 contains the criterion (freezes the helper cell).

To filter by a new vehicle, you don’t need to make any changes in “Sheet1!D1.” Instead, enter the criterion in “Sheet2!A1.” For example, enter “3011729” in “Sheet2!A1.” Then click on the down arrow in the Slicer and select OK to apply the filter.

Remember, each time you change the vehicle number (criterion in “Sheet2!A1”), you need to click on the down arrow in the Slicer and select OK again to update the filter.

And there you have it! You have successfully added a custom formula in the Slicer to filter a chart in Google Sheets.

Enjoy exploring the possibilities with custom formulas in your Google Sheets!

Note: You can access a sample sheet here.