Subtotals help you analyze subsets of data. They help you see how specific items affect your overall data set. Let’s learn how to add subtotals in Microsoft Excel.
How to Add Subtotals in Excel
Imagine a list of data showing the most recent deals closed by your top salespeople. You could find a grand total by simply finding the sum of column B. But you might want to see the total for each salesperson. That’s where subtotals come in.
The first rule of thumb for subtotals is that you need to sort your data by category. In this case, that’s by name. On the Home tab, click Sort & Filter, then choose Filter from the dropdown.
Then, click the dropdown arrow that appears inside cell A1. Click Sort: Ascending to group by name.
Now, it’s time to add your subtotals. With a cell in the range selected, on Excel‘s Data tab, click the Subtotal button in the Outline group.
You’ll see the Subtotal menu appear. Here, you have a variety of options. At each change in: represents where subtotals will be inserted. Here, Excel automatically recognizes that you want to add subtotals by individual name.
- Use function: demonstrates that subtotals don’t have to be totals. You can also use this feature to find averages, counts, and more.
- Add subtotal to: lets you choose where you want the subtotal placed. This is most useful if you have multiple columns of numbers. Here, Sales is your value, so leave it set to the default.
Click OK, and you’ll see Excel insert subtotals for each salesperson’s total, along with a grand total at the bottom.
As you can see, Excel makes adding subtotals easy. With smart features, you can use these with any numerical data set. This is a quick and easy way to analyze and compare categories of data.