Subtotals in Excel

While it’s helpful to have your data in a spreadsheet, it’s only the beginning of analyzing it. You need to use the analysis tools to subtotal and calculate so that you understand what’s inside your spreadsheet.

Instead of manually totaling up groups of data, let’s learn how to use the built-in feature for subtotals in Excel.

Step One: Sort Data in Excel

Before we add subtotals, it helps to organize and sort our data. Excel’s subtotal feature will sum up data each time it sees a change in the list, so we first need to group our data.

With your columns highlighted, go to the Data > Sort option on Excel’s ribbon. You’ll see a new menu pop up that helps you decide how to sort data. For my example data, I’m going to sort all of the brands together, then sort based on year. Basically, it will alphabetically sequence the brands, then sort the years. See the Sort menu screenshot below.

Make sure you have one rule for each column (except the numeric column.) In my example that means that there are two sort rules. Press OK, and your data is now sequenced.

Step Two: Add Subtotals in Excel

Once you’ve sorted your data, make sure your columns are highlighted  and choose Outline > Subtotal. 

Now, it’s time to tell Excel how to add your subtotals. There’s an option labeled At each change in. Basically, this tells Excel where to add subtotals. In my case, I’ll choose Brand so that each brand is subtotaled.

Make sure you leave Use function set to Sum, then tick the box for the column with your values. In my case, that’s Sales. Press OK when complete.

Now, your data is subtotaled by brand. You have an easy way to see the totals for each brand.

Don’t manually total your data by category, Instead, add subtotals and decide on your groups. Then, Excel handles the hard work for you.