Excel Tip of the Week: Subtotals

Today’s topic is subtotals.

Subtotal is a function in Excel (like SUM or VLOOKUP), but it is also a feature in excel (like goal seek, or inserting a row). When you use the subtotal feature in Excel, you are essentially allowing Excel to automatically execute a series of actions to help summarize your data. As always, this will make 100% more sense once an example is shown. Here are the steps:

1. Download the example spreadsheet here
2. Notice how all the months are already sorted. ***THIS IS SUPER IMPORTANT*** You have to sort your data properly before activating the subtotal feature. Otherwise you will end up with a nonsensical mess.
3. Place your cursor anywhere in the data set.
4. PC: From the “Data” menu in the “Outline” section select “Subtotal” (Keyboard Shortcut:Alt →A→B)
5. Mac: From the menu bar, select “Data” then “Subtotals”
6. Notice how Excel recognized your data range and highlighted it for you. Cool, huh?
7. You should see a box that looks like this:
   
8. This box is very straightforward and it makes most sense if you assess each option from top to bottom. I encourage you to try different things with these options to see how it works, but for now just click “OK”

Let’s assess what Excel just did for you. There were three main things:

1. Each time the month changed, Excel inserted a row
2. Inside the new row, in the “Amount” column, Excel inserted a formula to sum the total for each month (more on this later) as well as a formula to calculate the “Grand Total” at the bottom of the data set.
3. Each month was then “grouped” by excel. (See the last post)

Now, a quick explanation of the function Excel inserted for you. The SUBTOTAL function takes two inputs:

function_num: The number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use. The most common value you will use is “9” which indicates SUM. Here is the table which breaks down which number equals which function:

ref1: The range which you want subtotaled

The important thing to note is that if there are other SUBTOTAL formulas within “ref1” it will be ignored. This allows for nested subtotals and a more flexible way to calculate a grand total.

Finally, if you want to get rid of the subtotals, follow the steps above until you get to the “Subtotal” box, and then click “Remove All.”