Tech Tips Home
The Best Tech Tips And Daily Deals

WorldStart Tech Tip And Store Search

Like what you see here? Subscribe to the Tech Tips newsletter!   Email: Subscribe

# And the Subtotal Is…

Friday, April 28th, 2006 by | Filed Under: MS Excel, MS Office Help

And the Subtotal Is…

Have you ever found yourself working on an Excel worksheet desperately trying to group similar data, insert a row or write a formula for the group?

How was it?

What was that you said?

Did I hear you mutter under your breath something about it being a complete pain?

Yep, that’s what I figured I’d hear. (I thought the same thing too).

So, the question becomes (after you’re done muttering about it, of course) what can we do to make it better?

In the best of worlds, there would be a way to get Excel to do all the work. You know, no more inserting lines, writing endless formulas, making sure you get the right cells into each formula, etc.

Well, welcome to the “best of worlds,” because that’s exactly what we’re going to get Excel to do for us today.

Yeah!

Yes?

Good, then let’s get on our way.

The first thing you need to do is sort the data on the field that defines the groups. (Maybe a person’s name, a date, a part number. Basically, whatever defines a set of items as belonging together). You can use secondary sorting criteria, but be sure the primary sort field is the one that defines the groups.

In my example data, I’ve got four different sales reps and some sales data.

It’s obviously listed by date, but what if I wanted to know how much each rep has sold?

To get a total from this data, as is, would be a nasty mess, so the first thing I would do is sort the data by sales rep.

This is better, but I still don’t have the sales subtotal for the reps.

Here’s where we could do it all by hand (insert a row, write a formula for each rep), but the goal here is to avoid all that manual and repetitive work.

So, let’s get Excel to do the real work for us.

With our data sorted, select any cell within the list. Now, go to the Data menu, Subtotals choice.

The Subtotals window will open giving you a wide variety of options.

In the first field, “At each change in,” select the data that defines the groups. This tells Excel which data changing should trigger a new subtotal. (If I were working on sales rep totals, I’d pick that from the drop down list. If I wanted to know daily totals, I would have sorted by date and selected a date from the list. You get the idea).

The “Use function” field is a list of functions you can have Excel display for each group. Pick whatever subtotal data you were looking for so Excel knows how to write its formula. (Take a look at the list. You’ll find that you have many more options than just a sum in the subtotal window).

The third field, “Add subtotal to” tells Excel which data to perform the function on. (My example would be for “Sale Amount” to get the total sales for each rep).

At the bottom, you’re given three more choices. Check any box you want Excel to apply. These are pretty self explanatory, so I’ll save you the long winded description, but you might want to note that you can get Excel to give a grand total, as well as, subtotals by ensuring that the “Summary below data” box is checked.

Presto Change-o!

Your data is now beautifully separated into groups with subtotals automatically inserted between them.

(Yeah, I know! That was way faster than the do-it-by-hand, one group at a time method).

If you change your mind about the subtotals, just immediately hit the Undo button.

Should you decide later that they must be removed, return to the Subtotal window and click the Remove All button.

Also, if you decide that you want a different type of subtotal (maybe an average instead of a sum), you can return to the Subtotal window and change the “Use function” field. Make sure the “Replace current subtotals” box is checked to make the change.

Ah, did you catch that?

You have to check the “Replace current subtotals” box to change the type of subtotal, but what if you want a sum and an average subtotal?

No problem.

Create the first subtotal, then go back in to create the second. Before you click OK on the second subtotal, uncheck the “Replace current subtotals” box.

Take a look.

Now you’ve got two different pieces of data on each group and two different grand totals at the bottom.

Cool!

Now you’re ready to go subtotal crazy!

Enjoy!

~ April

• Tech Tips Daily - Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

• WorldStart's Daily Deals - Every week, we send out great deals in our Daily Deals newsletter. Many of these deals are exclusively for our Daily Deals newsletter subscribers and can't be found with our regular specials.

• Just For Grins - Each issue includes a couple clean jokes, some funny quotes, and a hilarious reader's story. Newsletter is sent five days a week.

Subscribe

We only use it to send you the newsletters you request. It is NEVER disclosed to a third party for any reason, ever! Plus, if you decided you don't like our newsletters (don't worry, you'll love them), unsubscribing is fast and easy.

Tech Tips Daily

Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

Tech Tips Weekly

The week's best in tech and computer help. Get your issue sent to your email every Friday!

WorldStart's Daily Deals

The very best deals on the Internet! Get a new set of incredible sales every day of the week!

Just For Grins

Clean jokes, funny quotes, and hilarious comics. Sent 5 times a week straight to your email.

Subscribe

Categories:
Archives: