Have you every wanted to sum your data by category? I hope you didn’t do this manually. Excel has a subtotal feature that will do this for you.
Let’s suppose you have a worksheet that has product orders. The columns might be Product Category (Toys, Clothes, Bath & Body, etc.), Date, Number Sold, and Price. You want to see how much you’ve sold in each category.
First, you want to sort your spreadsheet by the column that contains the data you want to summarize. In this case, we want to see number sold by category, so we will sort the category column. It does not matter if you sort ascending or descending, as long as all like categories are together.
Next, you will select the subtotal tool from the Data tab. In the popup, select “Product Category” from the At each change in drop down. In the Use Function drop down, select sum. Lastly, pick the appropriate field you want to include the total in. In this example, we would select “Number Sold”. Click OK.
Now, you should see a break after each category and in the “Number Sold” column, the field in that break will show the sum of the product for that category.
Note: Try this out with different types of data. Play around with the count feature and the average feature. They can come in handy!
If you are an Advocate Capital customer and need help preparing or manipulating data in the Excel application, feel free to contact me at [email protected].
We love feedback from our customers and implement suggestions often. What shortcut do you use the most? If you have a question or idea for an Excel tip that you would like to see, let me know and I might write about it!
Training Specialist
Photo Credit: Ammentorp
