Excel forulas are delayed updating totals
The Total Row can be used in a formula outside the Table.
Notice the Avg Hours Per Setup formula below uses #Totals to refer to the Total Row in the Table.
If you recall from my last post on Tables, I used the following formula for Average Hours Per Setup =SUM(Table1[Setup Hours])/SUM(Table1[Number Setups]) which has a different flaw. We can replace SUM in this last formula with the SUBTOTAL function =SUBTOTAL(109, Table1[Setup Hours])/SUBTOTAL(109, Table1[Number Setups]).
Now when we Filter the Table the calculation is correct and we don’t have to worry about having the Total Row toggled on for the formula to work.
Several functions are available when the Total Row is turned on.
In Excel 20 (Windows) select a cell inside the Table, then choose Table Tools and click the check box for Total Row.
Using SUBTOTAL in the Total Row means that when the Table is Filtered, the function will give the correct answer.
If you toggle the Total Row off, Excel remembers the function and it will appear again when you toggle the Total Row back on.
Average, Count, Count Numbers, Max, Min, Sum, Std Dev, and Var are options on the drop-down list for each cell in the Total Row. When you choose a function from the list, what you really get is the SUBTOTAL function, where the first argument is the function, and the second argument is the Table column data.9 out of 10 times, an Excel user would complain about the slow Excel spreadsheets. However, there are many speed-up tricks you can use to improve the performance of a slow Excel spreadsheet.Here are 10 tips to give your slow Excel spreadsheet a little speed boost, and save you some time and frustration (click to jump to that specific section).It’s much easier to use the SUBTOTAL Function with Windows versions of Excel (2007, 2010) because autocomplete gives you the different function numbers for the first argument.The Mac version (2011) doesn’t have autocomplete but you can access Help, which brings you to another window with some information that helps you decide which function number to use for SUBTOTAL.
If you have a file with Tables and open it with an earlier version having only Lists then you get a warning.