How To Use The Subtotal Function In Excel

You may be surprised or even amazed at how useful the subtotal function in Excel can be. This article reveals how versatile this Excel function is and describes three really useful features. When in use, the function formula looks something like this =SUBTOTAL(9,D1:D4) showing you that within the brackets there's a code number, followed by a range of cells.

The first really useful feature is that this code number is used to tell Excel what kind of function to apply to the specified range of cells. For example a 9 means sum, so in this example the result would be a sum all the cells in the range D1 to D4. You can use the code numbers 1 to 11 to apply different numerical functions to the range of cells. The most frequently used code numbers are 1 for "AVERAGE", 2 for "COUNT" and 9 for "SUM".

To see the full code list, first add the subtotal function to a cell and use, for example, the 9 code to sum some cells. Ensure the cell containing the subtotal function is still selected and click on the fx symbol to the immediate left of the editing panel above the spreadsheet. In the Functions Arguments panel the subtotal function should be showing. Click on the blue link "Help on this function" in the lower left of the panel and you'll see the full list of codes and functions available. This code number lets you apply a wide range of different numerical functions just by changing this number.

The second really useful feature is that the subtotal function normally only works on visible cells. This means that, for example, if you use an Autofilter and select only certain records in a table, then any subtotal function which calculates a value from cells in the table only calculates on the currently displayed cells. To illustrate this, suppose you have a table of data consisting of several columns, with the last column containing numbers. Add a SUM function and a SUBTOTAL function in separate cells under the last column, with both functions adding the cells in this last column. Of course both should show the same value.

Now add an autofilter to the table, and apply a filter setting. Once done you should find that the SUM function still adds all the original cells, but the subtotal function now adds only the visible cells. So subtotal can be used to analyse data in situations such as filtering where some cells may be hidden.

The third really useful feature is that the subtotal function ignores any other subtotal functions within the specified range of cells. Have you ever needed to add lots of cells in a single column, and then noticed that there are several sum functions already in the column? In this situation adding a sum of all the cells adds all the cell values and all the other sum functions as well. The subtotal function allows for this situation. If you subtotal a range of cells containing existing subtotals, you will be pleasantly surprised to discover that the internal subtotals are ignored, so the function accurately sums all the cell values. You can see this at work if you apply the subtotal command to an subtotal functions.

In conclusion the versatile subtotal function has three key benefits. Firstly the code number from 1 to 11 within the function lets you choose from a wide range of numerical functions. Secondly the function only acts on visible cells, making it ideal to analyse filtered data. Thirdly the function ignores other subtotals within the specified range of cells, making it the perfect tool to create several subtotals and an overall total within a single column of numbers.

If you're interested in finding out more about Excel functions a really good way would be to attend a training course and really boost your Excel skills.