In this article, let’s look at the Subtotal feature in Excel. This allows you to subtotal one column of numbers, based on a change in another column.  For this exercise, enter the following data into a new blank workbook.

Let's say, looking at our data, that we want to get totals for each of our three departments.  This is quick and easy using the Subtotals feature.  It's exactly what it's made for.  No need to write formulas to do this.  Here's what you do:

First, make sure your data is sorted by the Dept. column.  In order for subtotals to work, you must ALWAYS sort by the column that will reflect the categories of your subtotals.  Don’t forget this step or you will get multiple entries for each department. In this case, these department names will be what we are showing subtotals for, so our data must be sorted on these names.

Now, click anywhere inside your data and go to the Data menu and choose "Subtotals".  Select to sum the Totals column at every change in the Dept column.  Make sure your choices look like mine below and click OK.

Now your data looks like mine does below, with all of your Subtotals in bold and a grand total at the end.  Notice the - and + signs to the left of your data where you can expand and collapse your subtotals to reveal just the subtotals for all or any one department.  Try expanding and collapsing the different rows to see all the different types of reports you could now print for this data.

TIP: Let’s say you want to create a new spreadsheet or new workbook that shows just the totals for each department. Click on all of the minus signs to collapse everything so you only see the department subtotals. Then go to the Edit menu and choose "Go To" (or simply hit the F5 key). In the "Go To" box, click on the button at the bottom that says "Special…", then put a tick in the option button that says "Visible Cells Only", then click OK. Now all of the visible cells are selected (and none of the hidden ones in the collapsed rows). Simply copy that (ctrl+C) and paste it into a new worksheet or workbook and you have a separate totals report which no longer includes the individual totals, but only the subtotals for the departments and the grand total. This is handy when you want to send a totals report to someone and not include the individual amounts, which might include confidential information.

Hope you enjoyed this article. Excel has many automatic features like this that eliminate the need for elaborate formulas.