Getting Subtotals in Microsoft Excel

First...if your Subtotals don't seem to work right in Excel 2002 or 2003, get the hotfix from Microsoft. Thanks to Dave Peterson for pointing this out.

I often see people asking how they can split their records up onto different worksheets. This is almost certainly the wrong thing to do. Like data is so much more manageable when it's all on one worksheet. One of the reasons people want to separate them is to get totals of individual groups of records.

This particular article was prompted by someone wanting to see totals of how much he paid church employees, so he wanted to put everybody on separate worksheets. Why? Likely because he didn't know about the Subtotals feature. So here it is.

We show the weekly payroll for a few employees. We could have other "Purposes" listed, too. No problem.

First, we sort by name. Then, as long as our cursor is on any of the cells that contain data or headings, and we click on the menu Data Subtotals, Excel automatically selects our range for us, and makes an assumption on which column we want to subtotal, too.

We could change the At each change in box to read Date, and see how much we spent each payday, if we like. For our demonstration, we're seeing how much we paid each employee. If we had a purpose called Expenses, we could check the Purpose column, too, and get subtotals for both the payroll and the expenses.

When we choose the options as shown in the above graphic, here's what we get. This may seem even worse to the new Subtotals user.

Now the cool part. Click the 2 in the upper-left corner of the worksheet.

This changes the level of detail, and here's what we get.

If you click on the 1, you get just the grand total. It may seem silly, until you use the Subtotals feature for hundreds of records. Then it's a whole lot easier to click the 1 than to scroll all the way down to the bottom.

Once you're done using your Subtotals, you just go to Data Subtotals again and hit Remove All.

Learn the different behaviors of choosing different options in the Subtotals dialog box, such as how unchecking the Replace Current Subtotals allows you to add another layer of subtotals.

So, how do you copy just the subtotals? In our example above, select from cell A1 down to D29. Then hit Edit Go to Special and choose Visible Cells Only. Then you can copy and paste somewhere else. If some of the outline numbers come along with it into the new worksheet, just click Data Group and Outline Clear Outline.