Excel 2016 Tips and Tricks

Lesson – Working with Tables

Excel 2016

Creating a total row

NOTE Although the list of functions displays the names of common aggregate functions, Excel enters a special SUBTOTAL function when you select one of these options. The SUBTOTAL function uses two arguments. The first argument is known as the function_num argument, which determines the type of calculation applied to the column. When you select a function from the list, Excel enters a SUBTOTAL function with the appropriate function_num argument ( 101 for Average , 102 for Count , and so on). Because these numbers are in the range 101-111, the SUBTOTAL function ignores rows that are hidden by filters. If you filter the table, the results in the total row will change to calculate only the column data that is currently visible. Although this generally gives you the results you need, if you want the total row to calculate using all the data in the column, even when it is filtered, you can edit the formula and subtract 100 from the number ( 1 for Average , 2 for Count , and so on).

Page 30

OFFICEPRO, Inc.