Slicers are a great feature for interactive reports, but you need to know a trick to use them with formulas. That is where the function SUBTOTAL can help to unlock the full potential of this interactive feature. The slicers can control tables by filtering rows and the SUBTOTAL function can ignore hidden rows. Slicer selections can be used in formulas by performing calculations with visible table cells only. As a result, you can analyze multiple scenarios with one chart and perform selection to total analyses. In this video you will see how to use slicers with spilling formulas containing dynamic array functions with the help of the SUBTOTAL function. Also, you will see how to make interactive charts with changing number of categories that respond to slicer selections (in Microsoft 365 and Excel for the web).
This method can also be used as an alternative to pivot tables and pivot charts in files that already have a very large size.

00:00 Introduction
00:56 Preparing the table and slicers
01:50 Formula for categories (method 1)
02:13 Formula for total average price (method 1)
03:08 Helper column with function SUBTOTAL
04:18 Formula for average price of selection (method 1)
05:38 Adding a dynamic chart (method 1)
08:36 Formula for changing number of categories (method 1)
10:25 Formula for categories (method 2)
10:52 Formula for total average price (method 2)
11:31 Formula for average price of selection (method 2)
13:49 How to make the formula for average price of selection spill
15:37 Formula for changing number of categories (method 2)
16:54 Adding a dynamic chart (method 2)


For more contents like this, please subscribe to my channel.
#MsExcel #ExcelTips
Screenshots used with permission from Microsoft.