{"id":115,"date":"2010-02-24T15:00:46","date_gmt":"2010-02-24T21:00:46","guid":{"rendered":"http:\/\/www.iqaccountingsolutions.com\/blog\/?p=115"},"modified":"2012-09-24T14:10:06","modified_gmt":"2012-09-24T19:10:06","slug":"automating-subtotals","status":"publish","type":"post","link":"https:\/\/www.iqaccountingsolutions.com\/blog\/automating-subtotals\/","title":{"rendered":"Automating Subtotals"},"content":{"rendered":"<p>Excel&#8217;s ability to automatically insert subtotals in a list can be huge time saver.\u00a0 But the arrangement of your data is the key to making it work.<\/p>\n<p>The first requirement is that you must have a column that contains the information by which you want to sort and subtotal.\u00a0 This could be region, or sales rep or anything else.\u00a0 But it must be in a column by itself.\u00a0 And the data must be on every line, not just the first line of the group.<\/p>\n<p>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.<\/p>\n<p>Once both of those conditions are met, you just go to the Data menu and choose Subtotals.\u00a0 In most cases, Excel can figure out what area of the spreadsheet you want to subtotal.\u00a0 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.\u00a0 After clicking on Subtotal, a small window will open allowing you to tell Excel how to total your spreadsheet.\u00a0 Just make your selections and click OK.\u00a0 Excel will insert subtotals for the columns you selected, and even put a grand total at the bottom.<\/p>\n<p>In the left margin, you will see some boxes and lines.\u00a0 That is a collapsible outline of your data.\u00a0 At the top left corner are three small boxes numbered 1, 2, and 3.\u00a0 If you click on 3,the outline will be rolled up completely so that you only see the grand total.\u00a0 If you click on 2, you will see just the subtotals.\u00a0 Click on 1 and you will see all of the details along with the subtotals.\u00a0 Click on 2 again to see just the subtotals.\u00a0 All of the boxes in left margin now display a +.\u00a0 Click on one of them and just that section will be expanded to show detail.\u00a0 Click on the box now showing a &#8211; and it will be rolled back up.<\/p>\n<p>To give you a simple example, preview the Invoice Register in Peachtree.\u00a0\u00a0 \u00a0\u00a0 Click the Options button and change the sort order to Customer Name. Click OK to show the new report.\u00a0 Now click the Excel button to send the report to Excel (make sure the Raw Data Layout option is selected).<\/p>\n<ul>\n<li>In Excel, click on the Data menu and choose Subtotals. \u00a0<\/li>\n<li>In the Subtotal Window, the first option is &#8220;At each change in&#8221;, set it to &#8220;Name&#8221;.<\/li>\n<li>The next setting is &#8220;Use Function&#8221;.\u00a0 Set it to &#8220;Sum&#8221; for this example.<\/li>\n<li>At &#8220;Add subtotal to&#8221; check the box next to &#8220;Amount&#8221;.<\/li>\n<li>You can leave the 3 remaining check boxes set the way they are for this example.\u00a0 The &#8220;Summary below data&#8221; option will put a grand total at the end of the list.<\/li>\n<li>Click OK and Excel will insert a subtotal every time the name changes in the list.\u00a0 That is why it is important to have your data sorted before using the subtotal command.\u00a0 Now experiment with expanding and collapsing the outline using the buttons in the left margin.\u00a0 If you want to remove the subtotals, click on Data &#8211; Subtotals, and click the &#8220;Remove All&#8221; button.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Last month I talked about the benefits of using the SUBTOTAL function.  This month I&#8217;ll show you how, in some cases, Excel can insert subtotals for you and give you a collapsible outline of your data that lets you choose between seeing full detail with subtotals, just subtotals, or just the grand total.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5],"tags":[],"_links":{"self":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/115"}],"collection":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=115"}],"version-history":[{"count":4,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/115\/revisions"}],"predecessor-version":[{"id":118,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/115\/revisions\/118"}],"wp:attachment":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=115"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=115"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=115"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}