Скачать или смотреть Use table slicers to their full potential with the SUBTOTAL function in Microsoft Excel

Tables in Microsoft Excel can be used with slicers, just like pivot tables. Slicers are a great feature for interactive reports. The function SUBTOTAL can help you to unlock the full potential of slicers for formulas. 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 test multiple scenarios with one set up, perform selection to total analyses, or make functions like the FILTER function interactive. But the SUBTOTAL function has to be used with a trick. If you supply a data range to it directly, then it will only perform the calculation on the whole range and return one number as a result. Watch this video to find out what the trick is and what helper functions you can use depending on your Office version.

To see how to make formulas with the SUBTOTAL function spill in Microsoft 365, take a look at this video:
   • Using table slicers with spilling formulas...  

00:00 Introduction
00:56 Setting up the table and slicers
01:49 Formulas in most versions
02:14 SUBTOTAL and its list of functions
03:16 Introducing a condition to the formulas
04:03 Using the SUBTOTAL function with a trick
08:32 Formulas in Microsoft 365
10:01 Using BYROW and LAMBDA functions with SUBTOTAL
13:23 Selection vs. total price comparison
14:36 Apply slicers to the FORMAT function to make it interactive

For more contents like this, please subscribe to my channel.
#MsExcel #ExcelTips

Screenshots used with permission from Microsoft.