							Contact Us! California (408) 835-8436 Rhode Island (401) 580-5289 Questions? Comments?
Home	Case Studies	MS Office Tips and Tricks	Sample Code	Helpful Videos	Custom Development	Custom Training	Join Us	About Us
Search Our Site:

Excel Subtotals Vs Summary Queries

We're going to compare the use of Excel built-in subtotals to the use of database (we'll use Access for our examples) summary queries.

Both functions take long list of data - like sales data or manufacturing quality control results - and quickly summarize them into a much shorter and easier-to-read format. Both functions can create subtotals on an arbitrary number of columns so that data can be consolidated into a single grand total or consolidated by month, month and product, month, product, and region, etc.

Bear in mind that the more columns you use in your subtotals (whether in Excel or Access), the less consolidation happens. If you subtotal on every column, you're like to retain every row - not really consolidating your data at all!

Let's start by examining Excel subtotals in Office 2007. Below we see raw data taken from our sales transaction database. We'd like to see our monthly sales amounts - a perfect application for subtotals.

We click inside of our data range, click the Data ribbon, and then click Subtotals at the far right. The dialog box below pops up, and asks us, when do you want to see a subtotal? We'd like a subtotal at each change in month (a March subtotal, an April subtotal, etc.) so we choose the Date column for the At each change in: box.

We want to see the Sum of the Amount column, and we'll replace any subtotals that might already be in our data region.

But look what happens! We get subtotals by day instead of month. We have to remember to create a new column with only the month in it, or format our data so that it only shows the month data. While the formatting option looks very attractive and works to a certain extent, let's take a look at its pitfalls as well.

Select the date column and use Ctrl-1 shortcut key to bring up the Format > Cells dialog box. Format the date column using a custom format as MMMM to get the fully-spelled-out month name.

Run the subtotals again, and look! We see our subtotals by month instead of day. That was simple!

Now that the format has changed, the subtotals have changed, but watch out! What happens when we decide to get subtotals by product within the month? We click back in our transactions, go to the Data ribbon, click Subtotals and in the dialog box below choose Product and clear the Replace current subtotals box.

Look closely at the result... line 3 has the subtotal for March EJ-501 sales, but so does line 15! Lines 11 and 17 have subtotals for March EJ-14 sales. This is not correct - we did not ask for subtotals by region or salesperson. What happened?

What's wrong is that the data is not sorted by date and product. Remember that Excel subtotals look for "each change in" a column. If the data is not sorted, then values may change every line, and change back on the next line giving incorrect subtotals.

Take a deep breath - this is where the problem with our formatting 'shortcut' rears its ugly head. We try to sort on Month and Product so that we can subtotal on Month and Product. Our spreadsheet LOOKS like that will work, but when we try...

our products don't line up! What's happening now?

Although we formatted the dates to show only the month, and even though subtotals honor the formatting change, sort does not! Our transactions are still sorting by the day, not the month, so it appears as though the sort by Product did nothing.

We have to add an additional column that has only the month information so that our sort will be able to arrange by month and then product.

One way to do this is with the TEXT function. Using the same type of format that we used to show only the month, however, would cause the months to sort alphabetically (April, August, December, February, etc.) We'll add the number of the month to our column so that we can both read the month, and use the number to sort.

Now we can sort our data by month using the new column, and by product. Once sorted, we need to run the subtotals again. First we subtotal by month, then we go back in and run subtotals again, being careful to uncheck the box "Replace current subtotals".

At last! We have our subtotals by month and by product.

That took quite a while - but mostly because we had a few new challenges we weren't expecting. The next time we go to subtotal in Excel, we'll know right up front to add columns for sorting and subtotalling, and we'll know to do a sort right up front before attempting the subtotals.

Having said that, be aware that every time you get a new Excel file you have to do the process again.

An Excel file is a result, not a process. Every time you get a result, you're not really any closer to streamlining your process. A query, on the other hand, is a process, not a result. Once you set up your database query, your results are always only a single mouse click away.

Our data is already in a table called SalesData in an Access 2007 database. We'd like to see how to create a summary query which will give us results similar to Excel subtotals. If you go to the Create ribbon and click the Query Design button, you'll the dialog box below asking what table contains the data you want to query.

Select the SalesData table, click Add and then Close.

Double-click on Date and on Amount. You'll see them appear in the columns at the bottom of the window as shown below.

Then click on the Σ symbol (Totals) at the top right of the window below. You'll notice a new row titled Total: appears in the query window.

Leave date alone: that is what we want to Group By. This is the equivalent of Excel's "At each change in" when we set up subtotals.

Also, just like Excel subtotals, we choose Sum for the Amount column.

Finally - just like Excel - our query would now create subtotals by the day, rather than the month! How do choose month instead of day?

We'll use the same technique that we used in Excel, but we don't have to modify the data, just the query. Format the date as "MMMM" again, and our summary query will operate on the month, not the date.

We'll save our work at this point with a name that denotes what data we're viewing, and how it's arranged.

Please note that what we are saving is a set of instructions, not a result! As we add to or edit our data, we simply re-run the instructions by opening the query.

We can make another summary query by going back into design mode and simply double-click the product field. It is immediately added to the summary query.

We save the query with a name that indicates the additional level of summary.

Now either query can run by simply double-clicking on its name, or by assigning it to a button on a form.

References

Journal of Accountancy Articles

Tweaking The Numbers

Block That Spreadsheet Error

Excel Security Issues

Clients

Do you need help with Microsoft Office? Contact us

See how we've helped companies like yours develop effective business processes

Client Success Stories

Consultants

I Get It! Consultants set their own hours, work with their own clients, and choose their own projects.

If you're a Microsoft Office guru and would like to explore being a full-time consultant, please see our consultant information pages.