|
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.
Try applying subtotals that use formulas other than "Sum" to produce
averages or counts of items instead of totals and see how versatile this
little feature is. Notice the myriad of different types of reports you can
now quickly print off. For example, you could remove the Subtotals by simply
going back to Data>Subtotals and clicking on the Remove All button; then
try sorting your data by the “Raise?” column and choosing to Count the entries
in the Last Name column at each change in the Raise column. This would give
you a count of the employees who got a raise and the employees who didn’t.
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.
Linda Johnson
http://personal-computer-tutor.com
|
