Excel’s Missing Feature: Slicers for PIVOTBY

With the new Excel Slicer for PIVOTBY and GROUPBY, when you change the slicer, your data quickly reacts. This makes it super easy for you to analyze and explore without any hassle. PIVOTBY lets you pivot your data across multiple dimensions, while GROUPBY adds a whole new level of granularity to your analysis. Now, combine these with the sleek and powerful Excel Slicer, and you've got a data analysis powerhouse!

🔗LINKS

Related Content:

00:00 Introduction
00:43 Summary Report created by PIVOTBY Function
03:40 How to create slicers
05:30 Filter Values
09:18 Checkboxes
11:08 Bonus Feature

Thoughts/comments/suggestions/feedback? Leave them in the comments below ⬇️

[GeertDelmulle]

Very ingenious formulas, Jon!
I would solve this challenge like this:
• create a calculated column “vis” in the data table that indicates whether a row is visible or not.
• use vis in the filter condition.
• use any and all table slices I need right next to the PivotBy or GroupBy formula.
I can do complex filter conditions that way.

[edilpoulina]

Jon, this is awesome. I went twice through your video and now I'm going to study the file and the formulas. Thanks Jon!

[cboyda]

Really like how you maintained functionality, then added features to it!

[IvanCortinas_ES]

The whole process was very brilliant. Especially this formula...

Everything is very combinative. I love it. Thank you so much.

[KevinPGA]

What a wonderful concept and brilliant code. Thank you for sharing! 👍👍👍

[richardhay]

Great video. BTW Can you HSTACK multiple filter criteria in the Filter argument of PIVOTBY and GROUPBY?

[IvanCortinas_ES]

The effect by clicking the spacebar, you can check or uncheck all of the checkboxes in the list. That's dynamite!!!

[Excelambda]

Great video!! Check these out, 2 tiny lambdas to replace slicers, checkboxes, etc. This is for 1 "slicer" only, for multiple slicers more complicated to publish here.

SLICER(a, i, s, l)

a: array
i: clm index nr.
s: selection arg: if omitted -> all ( all -> sort(unique(index(a, , i))) ) ; if not omitted ex: {1, 3, 5}
l: list arg: interactive argument used to toggle between showing all selection when "l"=1 and "s" selection when "l" omitted

=LAMBDA(a, i, [s], [l],
LET(
u, SORT(UNIQUE(INDEX(a, , i))),
q, SEQUENCE(ROWS(u)),
t, HSTACK("nr\cl ix", i),
h, HSTACK(q, u),
v, VSTACK(t, h),



ex:
=SLICER(tblOrders, 7)

nr\cl ix 7
1 Q1
2 Q2
3 Q3
4 Q4

=SLICER(tblOrders, 7, {2, 3})

nr\cl ix 7
2 Q2
3 Q3

FLTSLC(a, slc) creates the filter pattern for a specific slicer;
a: array ; slc: slicer address ( ex: reference B15# )

=LAMBDA(a, slc, ISNUMBER(XMATCH(INDEX(a, , INDEX(slc, 1, 2)), DROP(TAKE(slc, , -1), 1))))

To get filtered results based on the "slicer" at B15 we call
=PIVOTBY(tblOrders[Color], tblOrders[Year], tblOrders[Ordered], SUM, , 1, , 0, , FLTSLC(tblOrders, B15#))

[IvanCortinas_ES]

I find it spectacular to see...
=FILTER(O6#, CHOOSECOLS(O6#:N6, 1);O6#)
O6#:N6 !! Woww. Amazing.
I have to cool off my face.