A couple of weeks ago I published a video covering Excel’s new GROUPBY function which many people are saying is a replacement for Pivot Tables. Which of course it’s not.
There are benefits of using GROUPBY but if you’re trying to replicate all the functionality of a Pivot Table with nothing more than a single function then I’m afraid you’re going to be sadly disappointed.
In my quest to see how much Pivot Table functionality can be replaced using GROUPBY, in this video I cover how to combine data validation with the GROUPBY function to simulate a Pivot Table filter.
Create the Drop-down and GROUPBY Function
I used Data Validation to create a drop-down list in J2. I then created a GROUPBY function in the cell 2 rows below the drop-down (as that is where the Pivot Table would be positioned). I included a “Filter” argument in the GROUPBY function so that it returned only the values where Customer Location matched the location selected from the drop-down.
Formatting Issue
I formatted the Total row with a green background (see screenshot above). However, like a Pivot Table, the placement of the Total row changes depending on how many rows are returned by the GROUPBY function. This caused a problem with the formatting which I solved using Conditional Formatting.
Clearing the Filter
A drop-down created with Data Validation is really intended to be a “pick-list” rather than to be used as a filter. It doesn’t include a way to clear the filter (with a Pivot Table filter there is an ALL option).
To simulate the ALL option I combined the GROUPBY function with an IF function…if J2 (the cell with the drop-down) is empty (you have to manually delete the contents of J2 to clear the filter) use a version of GROUPBY without the filter argument otherwise use a version of GROUPBY with the filter argument.
Formulas
The formulas I use in the tutorial:
=GROUPBY(Sales[[#All],[Flavour]],Sales[[#All],[Revenue]],SUM,3)
=UNIQUE(SORT(Sales[Customer Location]))
=$M$1#
=GROUPBY(Sales[[#All],[Flavour]],Sales[[#All],[Revenue]],SUM,3,,,Sales[Customer Location]=J2)
=$I5 = "Total"
=IF(J2="",GROUPBY(Sales[[#All],[Flavour]],Sales[[#All],[Revenue]],SUM,3),GROUPBY(Sales[[#All],[Flavour]],Sales[[#All],[Revenue]],SUM,3,,,Sales[Customer Location]=J2))
Download a copy of the file(s) used in this video: https://share.zight.com/JruZeowL