{"id":2283,"date":"2015-02-17T12:11:33","date_gmt":"2015-02-17T12:11:33","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=2283"},"modified":"2023-12-31T00:05:09","modified_gmt":"2023-12-31T00:05:09","slug":"using-subtotal-in-excel","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/using-subtotal-in-excel\/","title":{"rendered":"Using Subtotal in Excel"},"content":{"rendered":"<h2><strong>Summarise data quickly by using\u00a0Subtotal<\/strong><\/h2>\n<p>Excel&#8217;s subtotal feature\u00a0provides\u00a0a quick and\u00a0easy way of summarising tabular data.\u00a0\u00a0A common use of\u00a0the feature\u00a0is to\u00a0display only the totals for different\u00a0categories.<\/p>\n<p>For example suppose you want to calculate and display\u00a0<em><span style=\"text-decoration: underline;\">only<\/span><\/em> the total units\u00a0for each product from data (partly shown below)<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/Data.jpg\"><img decoding=\"async\" class=\"alignnone wp-image-2286 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/Data.jpg\" alt=\"Data\" width=\"944\" height=\"768\" \/><\/a><\/p>\n<p><strong>Step 1\u00a0<\/strong>Sort the data by the Product field.<br \/>\nClick inside the Product column and click Data, AZ button.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/Data2.jpg\"><img decoding=\"async\" class=\"alignnone wp-image-2288 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/Data2.jpg\" alt=\"Data2\" width=\"940\" height=\"769\" \/><\/a><\/p>\n<p><strong>Step 2<\/strong> Then\u00a0click anywhere inside the table and select <strong>Data, Subtotal.<\/strong><\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/SubTotals.jpg\"><img decoding=\"async\" class=\"alignnone size-medium wp-image-2285\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/SubTotals-241x300.jpg\" alt=\"SubTotals\" width=\"241\" height=\"300\" \/><\/a><\/p>\n<p><strong>Step 3<\/strong> From\u00a0the Subtotal dialog select <strong>Product<\/strong> as the &#8216;At change in&#8217; field and <strong>Sum<\/strong> as the function. Use Count\u00a0if you want to display the number of transactions\u00a0of each\u00a0Product.<br \/>\nClick <strong>OK<\/strong>\u00a0to automatically inserts total rows below each change in Products.<\/p>\n<p><strong>Step 4<\/strong> Uncheck Summary below to add totals above each change.<\/p>\n<p>The subtotal feature\u00a0creates\u00a0small outline\u00a0buttons 1,2,3 at the top left of the screen.<br \/>\nClicking button<strong> 2<\/strong> shows only the Product totals and\u00a0hides the transaction details.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/SubTotalsResult.jpg\"><img decoding=\"async\" class=\"alignnone wp-image-2289 size-large\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/SubTotalsResult-1024x564.jpg\" alt=\"SubTotalsResult\" width=\"625\" height=\"344\" \/><\/a><\/p>\n<p>Clicking\u00a0a <strong>+<\/strong> symbol expands the details for a particular product.<br \/>\nClicking\u00a0button<strong>\u00a01<\/strong> shows only the grand total while\u00a0button<strong> 3<\/strong> shows all the data including the subtotals.<\/p>\n<h3><strong>Removing Subtotals<\/strong><\/h3>\n<p>To remove the\u00a0subtotals completely select any cell in the summary.<\/p>\n<ul>\n<li>Select <strong>Data, Subtotal<\/strong>, <strong>Remove All<\/strong>.<\/li>\n<\/ul>\n<p><!--more--><\/p>\n<h3><strong>Copying Subtotal data<\/strong><\/h3>\n<p>When subtotal summary data is displayed at level 1\u00a0or 2 the detail rows are hidden by Excel. However this\u00a0data doesn&#8217;t\u00a0remain hidden if you copy to another sheet or application\u00a0such as Word or Outlook.\u00a0 There is a way to copy only the &#8216;visible cells&#8217; as follows:<\/p>\n<ul>\n<li>First select the data (click a cell inside the subtotal summary then press<strong> ctrl+a<\/strong> to select all.<\/li>\n<li>Select <strong>Home, Find<\/strong> <strong>&amp;<\/strong> <strong>Select<\/strong><\/li>\n<li><strong>Go to Special<\/strong>, <strong>Visible cells only<\/strong><\/li>\n<li>Now select <strong>Copy<\/strong><\/li>\n<li>Click in the destination sheet or document<\/li>\n<li>Select <strong>Paste<\/strong><\/li>\n<\/ul>\n<p>This will ensure only\u00a0at only the summary data is\u00a0copied and pasted.<\/p>\n<h2>Resources<\/h2>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/blog\/excel-training\/insert-a-sub-total-in-excel-2010\/\">Insert a Sub-Total in excel 2010<\/a><\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/article-1346-how-use-subtotal-function-in-excel.html\">How To Use The Subtotal Function In Excel<\/a><\/p>\n<p><a href=\"https:\/\/support.office.microsoft.com\/en-us\/article\/Insert-subtotals-in-a-list-of-data-in-a-worksheet-7881d256-b4fa-4f81-b71e-b0a3d4a52b3a?CorrelationId=fc6f2b8f-f8be-44f8-9d8c-4bc9db8cd6e4&amp;ui=en-US&amp;rs=en-001&amp;ad=US\">Insert subtotals in a list of data in a worksheet<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Summarise data quickly by using\u00a0Subtotal Excel&#8217;s subtotal feature\u00a0provides\u00a0a quick and\u00a0easy way of summarising tabular data.\u00a0\u00a0A common use of\u00a0the feature\u00a0is to\u00a0display only the totals for different\u00a0categories. For example suppose you want to calculate and display\u00a0only the total units\u00a0for each product from data (partly shown below) Step 1\u00a0Sort the data by the Product field. Click inside the [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4,6],"tags":[114,217,301,317,386,459,519,520,522,566],"class_list":["post-2283","post","type-post","status-publish","format-standard","hentry","category-excel-training","category-hints-tips","tag-copying-subtotal-data","tag-excel-subtotal-resources","tag-how-to-subtotal-in-excel","tag-inserting-a-subtotal-in-excel","tag-nested-subtotal","tag-removing-subtotals","tag-sub-totals","tag-subtotals","tag-summary-below-totals","tag-using-subtotals"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2283","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=2283"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2283\/revisions"}],"predecessor-version":[{"id":6896,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2283\/revisions\/6896"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=2283"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=2283"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=2283"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}