#234 Easy Excel Subtotals

The Excel Subtotals command provides you with a quick and easy way to summarize your data.

Note:  This is different from the =subtotal() function that we’ve seen before, here & here in conjunction with AutoFilter

How To Use It

The first thing you’ll need to do is sort the column containing the data you want to group by. For example, if I want subtotal by vendor, I’ll need to sort the vendor column.

1. Once it’s sorted correctly you can turn on the subtotals by going to the Data menu and clicking on Subtotals.

2.. Indicate which column to group by using the “At each change in:” drop down.

3. Choose which function to apply to the groupings. (Sum, Count, Average, Max, Min, etc)

4. Check the field that you want to be calculated in the “Add to subtotal” section. This is what gets calculated in the subtotals.

In our example, this results in the following;

Notice the 1,2 & 3 buttons that appear on the left. These control how much detail is displayed. 1 only shows the overall grand total, 2 displays the subtotals and 3 displays everything as shown above.

Usually what you want to see are the subtotals shown by the level 2 display.

Turning Them Off

If you want to remove the subtotals after your done, all you need to do is do the same thing you did to turn them on except when you get to that dialog box just click the Remove All button.

Copy Just the Subtotals

This is really cool, unless you know this next trick, you’ll probably have trouble when they try to copy these subtotals to a new worksheet. It looks like there are just five rows of data, but when you copy and paste them, Excel will include the hidden rows as well.

The trick that will let you just copy the subtotal data is the GoTo Special function.

Here are the steps:

1. Click the #2 button in the left margin of your spreadsheet. This hides the detail rows.

2. Highlight the range you want to copy.

3. From the Edit menu, select Goto.

4. Click “Special” to see the Go To Special dialog box.

5. Select the “Visible Cells Only” option and click OK. This changes the selection from all 14 rows to just the five visible rows.

6. Now that you have just the 5 visible rows selected, you can copy only the subtotals and none of the detail rows.

If you’d like to see how it works and maybe play around with it yourself, feel free to download a copy of this spreadsheet.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s