Excel’s ability to automatically insert subtotals in a list can be huge time saver. But the arrangement of your data is the key to making it work.
The first requirement is that you must have a column that contains the information by which you want to sort and subtotal. This could be region, or sales rep or anything else. But it must be in a column by itself. And the data must be on every line, not just the first line of the group.
The second requirement (you may find exceptions to this) is that your spreadsheet needs to be sorted by the column you want to subtotal on.
Once both of those conditions are met, you just go to the Data menu and choose Subtotals. In most cases, Excel can figure out what area of the spreadsheet you want to subtotal. If you get an error, or if Excel leaves some of it out, then manually select the area you want subtotaled before choosing the command from the menu. After clicking on Subtotal, a small window will open allowing you to tell Excel how to total your spreadsheet. Just make your selections and click OK. Excel will insert subtotals for the columns you selected, and even put a grand total at the bottom.
In the left margin, you will see some boxes and lines. That is a collapsible outline of your data. At the top left corner are three small boxes numbered 1, 2, and 3. If you click on 3,the outline will be rolled up completely so that you only see the grand total. If you click on 2, you will see just the subtotals. Click on 1 and you will see all of the details along with the subtotals. Click on 2 again to see just the subtotals. All of the boxes in left margin now display a +. Click on one of them and just that section will be expanded to show detail. Click on the box now showing a – and it will be rolled back up.
To give you a simple example, preview the Invoice Register in Peachtree. Click the Options button and change the sort order to Customer Name. Click OK to show the new report. Now click the Excel button to send the report to Excel (make sure the Raw Data Layout option is selected).
- In Excel, click on the Data menu and choose Subtotals.
- In the Subtotal Window, the first option is “At each change in”, set it to “Name”.
- The next setting is “Use Function”. Set it to “Sum” for this example.
- At “Add subtotal to” check the box next to “Amount”.
- You can leave the 3 remaining check boxes set the way they are for this example. The “Summary below data” option will put a grand total at the end of the list.
- Click OK and Excel will insert a subtotal every time the name changes in the list. That is why it is important to have your data sorted before using the subtotal command. Now experiment with expanding and collapsing the outline using the buttons in the left margin. If you want to remove the subtotals, click on Data – Subtotals, and click the “Remove All” button.
