Getting Subtotals in Microsoft Excel
First...if your Subtotals don't seem to work right in Excel 2002 or 2003, get
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
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
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.
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved