We can add a custom formula in a Slicer for filtering charts in Google Sheets. But there is a chance for you to face one issue.
If the source data and the chart are on two different sheets in the same workbook, then it won’t work.
This post explains how to solve that.
I am not a big fan of using a custom formula in a Slicer for charts in Google Sheets.
There are two reasons.
- When we enter the criterion within a cell and refer to that within the custom formula, the changes won’t reflect immediately. We may require to click the down arrow icon each time on the Slicer to get the criterion change reflected.
- If the Slicer and the chart are on one sheet and the source data is on another Sheet (but in the same workbook/file), then it won’t work. Though, I have got a workaround to solve it.
In this post, let’s learn 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 the first step, we will create a chart. I am preferring the Scorecard chart as it’s enough for our purpose. Here we go!
Important:- The sample data is in “Sheet1,” and both the chart and Slicer are in “Sheet2.”
Sheet1 (Sample Data)
In a Google Sheet, I have the following (diesel consumption) sample data in “Sheet1.”
It’s the diesel consumption of 6 vehicles from January to March in a year.
As a side note, we can use the below UNIQUE formula to find the unique vehicles in A2:A19.
=unique(A2:A19)Sheet2 (Scorecard Chart)
For testing purposes, I’m creating a Scorecard chart in “Sheet2” that shows the total of column C in “Sheet1,” i.e., the total of “Qty. In Gallon.”
Steps:-
- In “Sheet2,” go to Insert > Chart.
- Select Scorecard chart.
- Enter “Sheet1!C2:C19” in the Data range.
- Select “Aggregate.”
Here are the recommended settings within the chart editor to get the chart.
In the next step, let’s learn to add a custom formula in Slicer for filtering the above chart.
The purpose is to use a Slicer to filter the chart based on vehicle numbers.
Sheet2 (Slicer)
In Sheet2, where the chart resides, go to Data > Add a Slicer.
Then in the “Select a data range” field, enter “Sheet1!A1:C19,” the Slicer source data range, and select OK.
In the Column field, select “Vehicle #.”
Adding Custom Formula in Slicer for Chart
Now time to add a custom formula in this Slicer for controlling the above Scorecard chart.
In cell “Sheet2!A1,” enter any vehicle number from our source data, for example, 1111597.
We won’t be able to use this cell (criterion reference) directly in the custom formula in Slicer for filtering the above Scorecard chart.
We require to enter the criterion within “Sheet1”.
So in “Sheet1!D1,” enter the below formula.
=Sheet2!A1When you change the vehicle number in “Sheet2!A1,” the same will be reflected in “Sheet1!D1.”
In other words, “Sheet1!D1” acts as a helper cell.
Now we can add the below custom formula in the Slicer.
Custom Formula:
=$A2=$D$1
Where $A2 is the first cell in the column to filter, i.e., Vehicle #, and $D$1 contains the criterion.
$A2 – row is relative, and the column is absolute (so the formula tests each row in the Vehicle # columnn)
$D$1 – row and column are absolute (freezes the criterion cell)
To filter by a new vehicle, we don’t require 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.
Note:- Once you have applied a filter, you will see a funnel icon instead of the down arrow.
This action is required each time you change the vehicle number, i.e., the criterion in “Sheet2!A1.”
This way, we can add a custom formula in the Slicer to filter a chart in Google Sheets.
Thanks for the stay. Enjoy!
