The easiest way to do this, is click the Filter button on the Data tab, then click the filter arrow, and select to sort either A to Z or Z to A: So, before adding subtotals, be sure to sort the column that you want to group your data by. The Excel Subtotal feature requires that the source data be arranged in a proper order and should not contain any blank rows. To quickly add subtotals in Excel, perform the following steps. Additionally, it creates a hierarchy of groups, known as an outline, which lets you display or hide the details for each subtotal, or view just a summary of the subtotals and grand totals.įor example, this is how your Excel subtotals can look like: It allows you to group and summarize your data using SUM, COUNT, AVERAGE, MIN, MAX and other functions. In Microsoft Excel, the Subtotal feature is not limited to only totaling subsets of values within a data set. Generally speaking, subtotal is the sum of a set of numbers, which is then added to another set(s) of numbers to make the grand total. Please click on the following links to learn the details. Luckily, Microsoft Excel provides a powerful Subtotal feature that lets you quickly summarize different groups of data and create an outline for your worksheets. Worksheets with a lot of data can often look cluttered and difficult to comprehend.
HOW TO HAVE MULTIPLE SUBTOTALS IN EXCEL HOW TO
You will also learn how to display or hide the subtotal details, copy only subtotal rows, and how to remove subtotals.
![how to have multiple subtotals in excel how to have multiple subtotals in excel](https://i.stack.imgur.com/QDk8D.png)
The outline in the left margin will now show 4 levels instead of just 3.The tutorial explains how to use the Excel Subtotal feature to automatically sum, count or average different groups of cells. 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. Then click the Subtotal button again but choose Sales Rep at “At each change in”. 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. You need to start with the largest grouping, or sales region in this case. 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 just have to add them in the right order. You can even add multiple levels of subtotals. Click on the box now showing a – and it will be rolled back up. Click on one of them and just that section will be expanded to show detail. 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 3 and you will see all of the details along with the subtotals. If you click on 2, you will see just the subtotals. If you click on 1,the outline will be rolled up completely so that you only see the grand total. At the top left corner are three small boxes numbered 1, 2, and 3. This is a collapsible outline of your data. You’ll also see that some boxes and lines will have appeared In the left margin. Summary below data (in other words, add a grand total)Ĭlick OK and all of the totals will be added.Finally, set the three options at the bottom as desired:
![how to have multiple subtotals in excel how to have multiple subtotals in excel](https://www.customguide.com/images/lessons/excel-2019/excel-2019--outline-and-subtotal--04.png)
Just check the box next to each column name you can choose one or several. Step 2 is to choose which columns you want totaled. 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. Continuing the above example you would choose Sales Rep. 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. Then select the Data tab on the ribbon, and click the Subtotal button which you’ll find in the Outline group.
![how to have multiple subtotals in excel how to have multiple subtotals in excel](https://www.avantixlearning.ca/wp-content/uploads/2018/09/Remove-all-button-in-Excel-Subtotal-dialog-box.png)
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. 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. You can’t just have the sales rep on the first line of that section, like a heading. 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. And that field (column) needs to have an entry on every line. Logically, you’ll need to have the data in your list sorted by field you want to subtotal by. 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.