Did you know that, with just a few clicks, Excel can add subtotals and a grand total to your lists? In order for it to work you do have to start out with your data formatted correctly. Logically, you’ll need to have the data in your list sorted by field you want to subtotal by. And that field (column) needs to have an entry on every line. For example, if you have a list of sales that you want subtotaled by sales rep, first it must be sorted by sales rep and, second, the sales rep has to be entered on every line. You can’t just have the sales rep on the first line of that section, like a heading. This is because the automatic subtotal function looks for changes in the column that you want your subtotal based on in order to determine where to insert the subtotal lines.
Once you have your data formatted properly make sure your cursor is within the table you want subtotaled, or better yet select the entire table to make sure Excel knows what should be included. Then select the Data tab on the ribbon, and click the Subtotal button which you’ll find in the Outline group.
In the window that opens, use the “At each change in” box to choose which column it should look at to determine when and where to insert subtotals. Continuing the above example you would choose Sales Rep. The next option is “Use function”. We want Sum in our example because we just want to add up the sales, but choices include sum, count, average, min, max, and several others that you can explore. Step 2 is to choose which columns you want totaled. Just check the box next to each column name; you can choose one or several. Finally, set the three options at the bottom as desired:
- Replace current subtotal
- Page break between groups
- Summary below data (in other words, add a grand total)
Click OK and all of the totals will be added.
You’ll also see that some boxes and lines will have appeared In the left margin. This is a collapsible outline of your data. At the top left corner are three small boxes numbered 1, 2, and 3. If you click on 1,the outline will be rolled up completely so that you only see the grand total. If you click on 2, you will see just the subtotals. Click on 3 and you will see all of the details along with the subtotals. If you Click on 2 again to view just the subtotals you see that all of the boxes in the left margin now show a +. Click on one of them and just that section will be expanded to show detail. Click on the box now showing a – and it will be rolled back up.
You can even add multiple levels of subtotals. You just have to add them in the right order. Lets say that your spreadsheet was sorted by sales region first and then by sales rep within each region, and you want totals for both. You need to start with the largest grouping, or sales region in this case. Go through the process as described above choosing your first subtotal (sales region) at “At each change in” and click OK to insert your first subtotals. Then click the Subtotal button again but choose Sales Rep at “At each change in”. This time, make sure “Replace current subtotals” is not checked.Click OK and you’ll have both sets of subtotals as well as the grand total. The outline in the left margin will now show 4 levels instead of just 3.
