12-Jun-2013

In this article we are going to look producing totals in Excel Table.

Starting with our personal banking example, below is a partially filled out table.

Notice the total row has been added to the Excel table. If yours is missing, click on the table, go to Table Tools, Design, and make sure the Total Row is ticked in the Table Style Options.

To create a total of the Outgoings, click on the cell at the intersection of the Out column and Total Row – in the example above cell I11.

Click on the down arrow to the right of the cell and choose Sum from the drop down list.

Oddly - at first glance that is - it doesn’t put a SUM formula in the cell; instead it puts in a SUBTOTAL formula.

The SUBTOTAL formula consists of two parts, a function number – in this case 109, and a reference which in our example is the data part of the Out column.

If you use Excel’s help and look up SUBTOTAL, you can find out what all the function numbers are and mean. In our case 109, means SUM and ignore hidden values. Function numbers come in pairs separated by 100. So drop the 100 from 109 and you get function number 9, which again is a SUM function but includes hidden values.

*Now there are a couple of important points here.*

Firstly the meaning of hidden is when you hide a row by right clicking on it and using hide. It is not the same as filtering, when you use the drop down on the column header to filter the table.

Secondly, the SUBTOTAL function will give you different results if you filter the table, irrespective of if you use 109 or 9 - for example - as the function number.

Using Excel Tables with filtering and SUBTOTAL is a powerful analysis tool. So in our example it is easy to find out how spend there is on Utilities.

Firstly click on the down arrow at the top of the Category column

Then untick the (Select All), and then tick Utilities

Then click on OK and the result is as below. Notice that the down arrow has changed to a funnel symbol to indicate the column has been filtered.

You can apply further filtering by clicking on other columns, such as Date, which would narrow the data even more. Below we have filtered by the 6 Jan 2013, as a trivial example.

Having totals adjust as you filter the table is a really useful feature. But it comes with a strong warning. It is very easy to forget you have filtered the table, particularly when the table has lots of data in it, and then use a total value when it is really only a partial total. This is potentially a disaster waiting to happen.

There are a few solutions.

If you decide you never want to filter the data don’t use the SUBTOTAL function, just use the SUM function instead. You can enter the formula in yourself or from the drop down list in the total row, choose More Functions and then pick the SUM function.

If you do want to use the SUBTOTAL function, then in the row underneath enter the SUM function for and select all the data rows (but not the header or total row) for the particular column.

This is the result.

As an optional nicety.

Select the cell in the Table Total Row, and click on Conditional Formatting, and choose New Rule...

Then choose “Use a formula to determine which cells to format”

In the box under “Format values where this formula is true”, type in a “=” sign, click on the cell in the Table Total Row (the SUBTOTAL), then enter “ < >” which means not equal to, and then click on the cell under the Table Total Row (the SUM). In the formula remove the dollars preceding the column letter – this allows you to copy by dragging the formula to other columns in the Total Row. Finally click on Format... and pick a red Fill.

You should end up with something like this.

Then click ok.

You can drag copy the formula and conditional formatting to the In Column.

This is the result with a filter applied to just show Utilities

And the result with the Utilities filter removed, and a new filter applied to Salary on the Reason column.

This is a good visual indication when a SUBTOTAL is not showing the full total.

When you a building a bigger spreadsheet solution and want to incorporate the total from the table elsewhere think very carefully about linking to a SUBTOTAL formula. In most cases it will be better to either link to the SUM formula underneath the Table, or better still recreate the SUM formula where you need it, like below.

One last item, in Excel 2010 and upwards there is a new function called AGGREGATE. It works in a similar way to the SUBTOTAL function, except that there are more functions available within the AGGREGATE function and there are various calculation options, so for example get the AGGREGATE function to ignore nested SUBTOTAL and AGGREGATE functions.

In the next article we would look at how to create a running total when using an Excel Table which is a little trickier than in a plain Table.

Screen shots created in Excel 2007 and Excel 2010 where stated, all on Windows 7