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: https://youtu.be/Hn-INQQAp7k 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.