MS Office 101

MS Excel Sorting Fun

Often in MS Excel we have a database of information that needs to be ordered in a certain way. (Whether it is alphabetical, by date or whatever—we need to get it in order.)

Since today's tip requires you to know how to sort, I thought it might be a good idea to cover the basics first.

The first step to sorting data is to tell Excel what database it's sorting. So, to accomplish this, select a cell in the database.

If you're sorting on only one field then you can use the Sort Ascending and Sort Descending buttons on the Standard toolbar.

If you have more than one criterion (such as by person's name then in date order) you'll need to go to the Data menu, Sort choice. (Alt then D then S)

When the Sort window opens you're given space to set three sorting criteria (in the order they should take precedence.)

Select each sort criterion from the drop down lists as well as ascending or descending sort order.

When you've made your choices click OK.

Voila!

Database is nice, neat and orderly—just the way I wish the rest of my life could be.

If only it were all that easy.

Office Tip of the Day

And The Subtotal Is...

Have you ever found yourself working on an Excel worksheet, desperately trying to group similar data, insert a row, then 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—no more inserting lines, writing endless formulas, making sure you get the right cells into each formula...

Well, welcome to the "best of worlds" because that's exactly what we're going to get MS Excel to do for us today.

Yeah!

Ready to get started?

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 sort criteria but be sure the primary sort field is the one that defines the groups.

In my example data I've got 4 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 to 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. (Alt then D then B will also work.)

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 then I'd pick that from the drop down list. If I want to know daily totals then I would have sorted by date and selected 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 formulae. (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.

When you've made all your choices click OK.

Presto Change-o!

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

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

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

Should you decide later that they must be removed, then 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.

Ahh... 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