Weekly Feature:Excel Subtotals

I’m going to digress slightly with this week’s feature. This is actually an Excel feature, not a Great Plains feature per se but it has huge implications for Smartlist exports, so I’m going to cover it as a feature.

If you’ve never used Subtotals in Excel before, you need to know that I get more jaw dropping looks from people over this feature than for anything else I’ve shown them. Often I get slapped as well because they’ve just finished some long, tedious subtotalling by hand.

So what do subtotals do? When you export Smartlists that are detail lines such as GL transaction, Purchase Transaction Lines Lines, Sales Line Items, Inventory Transactions, etc., you get the details of transaction but often you also need the subtotal of all those lines to trace back to the transaction total. This is where Excel subtotals come in. Figuring out where all those transactions break and subtotaling with a formula is really slow.

I’ll use Purchase Transaction Lines in this example and start by exporting a large number of lines (1000) to Excel. Sort the list by PO Number either in Smartlist before exporting or in Excel after the export. THIS IS AN IMPORTANT STEP. You would actually sort by whatever you want to subtotal on. It could be open vs. closed PO’s, date created, etc. We’ll use PO Number for our example.

In Excel select Data->Subtotals. This pops up a box that you follow through like a wizard.

At each change in: Check PO Number
Use Function: Select SUM
Add Subtotal to: Check only Extended Cost
Click OK

In English: “At each change in PO number, sum the extended cost. Replace any current subtotals and put the summary below the data.”

Your PO’s now have the extended cost for each line subtotaled by PO number. There are little numbers 1,2 & 3 to the left of cell A1. Clicking on these expands and contracts the amount of detail you see. You can see a grand total, a subtotal by PO or the full detail.

Depending on how you sort and filter the Smartlist data you can get different results, for example separating open and closed PO lines from the PO. If you uncheck the Replace Current Subtotals box you can actually create nested heirarchies of subtotals. But that hurts my brain.

Excel’s subtotals overcome one of the few shortcomings of Smartlists. You can’t total stuff in Smartlists, but now you know more about totalling than you ever wanted.

4 Replies to “Weekly Feature:Excel Subtotals”

  1. PROPHIX also has a solution that I wanted to bring to everyone’s attention.It is a very good replacement for spreadsheets.

    PROPHIX is a feature-rich, software suite. Simple to use, easy to deploy, and tailored to meet your needs. You can find out more about PROPHIX at 
    http://www.prophix.com. A complete solution for budgeting, reforecasting, consolidation, data mining, reporting, personnel planning and scorecarding.

    RSS Feeds

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.