Microsoft Excel - Function Slicers (slice ANYTHING)

Learn a great workaround for Excel Slicers' limitations and extend their operational use beyond Pivot Tables and Data Tables. Now, you can slice ANYTHING with any standard Excel function.

File Download Link:

00:17 History of Slicer Use
00:54 Overview of Features Used

01:29 Review of the GROUPBY Function
03:16 File Download Instructions

Project 1: Single Filter
03:28 GROUPBY - No Filters
06:19 Setting Up the Slicer Controls
06:41 Creating a Product Pivot Table
08:46 Understanding the COUNTIF Logic
10:31 Creating Custom Headings

Project2: Multiple Filters (AND)
12:05 Compound Filters Using AND Logic
15:20 Adding the 2nd Slicer Filter (AND Logic)

Project3: Multiple Filters (OR)
16:21 Adding the 2nd Slicer Filter (OR Logic)

Project4: Filtering Dynamic Array Functions
17:52 Filtering other Functions (FILTER Function)
18:47 Custom Sort Logic

Project5: Filtering Traditional Functions
20:16 Filtering Dashboard Functions
24:59 Calculate Filtered Average
25:25 Calculate Filtered MAX
25:36 Calculate Filtered MIN

26:00 Conclusions
26:24 Thinking about the Big Picture

[FsoOmar]

I used to bypass this kind of lessons, but since I bought Office 365 (finally) I'll be learning from your many helpful videos.
🎉👍✌

[tosornat]

I am immensely grateful for the cheat codes for the best game in the world

[jacobbusch]

As always, explained in detail. Thanks. I enjoy and learn from this channel.

[Luciano_mp]

Wow, brilliant. Your videos have excellent teachings. Thank you.

[tamersalem]

Thank you for your interest in spreading knowledge and science. My greetings to you, respected person. ❤

[brianxyz]

Very impressive! Microsoft should consider updating slicers to work with dynamic array functions.

[arnyanderson]

Amazing and wonderful
Perfect way to solve complex problems
Hats off

[tamersalem]

Thank you for your interest in spreading knowledge and science. My greetings to you, my esteemed person. I had a request to explain my sheet, the graph, how it works to benefit from the wonderful content. ❤

[michaeldingee]

As usual very well explained video I created another sheet and used the following formula. This allows me use slicers without the need for underlying pivot tables.

=LET(header, VSTACK({"State", "Product", "Total Sales", "Avg. Sale", "# of Sales"}),
rows,
CHOOSECOLS(FILTER(Data48, MAP(Data48[Product], Data48[State], Data48[Sales], LAMBDA(p, s, sv, SUBTOTAL(103, p, s, sv)))), 2, 1, 3),

cols, HSTACK("", "Grand Total", SUM(SORT(CHOOSECOLS(rows, 3), 1, 1)), AVERAGE(SORT(CHOOSECOLS(rows, 3), 1, 1)), COUNT(SORT(CHOOSECOLS(rows, 3), 1, 1))),

VSTACK(header, cols, rows))

[gandhisunil]

Filtering with slicer by puvot table is good mechanics 😊

[IvanCortinas_ES]

Great tutorial.
I've seen that it's also possible to use slicers with the GROUPBY or PIVOTBY function with the BYROW-LAMBDA-SUBTOTAL combination, instead of COUNTIF, although your method seems much more human to me. Thanks for sharing!!!

[michaeldingee]

I would also like to give shout out to you and your channel. I first came across your channel in regard to this video "Power Query - Faster & Easier Parameters". I recreated it in a new workbook. I also added 4 panels "FILTER BY USING CHECKBOX", "FILTER BY PIVOT SLICERS", "FILTER BY USING DROPDOWNS" this uses your parameters, "FILTER USING GROUP BY". I can send you a copy of the workbook if you wish.

[kndeepak]

Instead of the multiple pivot for the slicer, we could use a helper column in the table to check whether the row is visible of not (subtotal with 103 function). Then we can apply slicers on the data table and use the helper column in the filter parameter. When you do this, there would be no need of the complex AND OR etc

[sujanmdsiful]

Awesome video with proper explanation.

As a data scientist I hate groupby function. I don't see any use for it. Other than simple use case.

I use pivot table then i use take function combination of match and isblank so that I can still use the pivot table relationships with multiple slicer