Microsoft excel subtotal command

We will illustrate the Excel Subtotal command using the above spreadsheet on the right , which lists the sales figures for three teams during the first three months of the year.

Click on the Subtotal option which is located in the 'Outline' group, within the Data tab of the Excel ribbon. This will cause the Subtotal dialog box to open up see above right. Within this box:. These options tell Excel to display a subtotal every time the value in the Month column changes and that this subtotal should display the Sum of the data in the Sales column.

Clicking on the outlines, to the left of the table allows you to hide or display the details of each section of your data table. Formulas and functions. More functions. Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. To display or hide data rows for individual subtotals , use the and symbols.

As you see, using Subtotal in Excel is easy… until it comes to copying only subtotals to somewhere else. The most obvious way that comes to mind - display the desired subtotals, and then copy those rows to another location - won't work! Excel will copy and paste all of the rows, not only the visible rows included in the selection.

As the result, you have just the data summary copied to another worksheet. Please note, this method copies the subtotal values and not the formulas:. This will ungroup your data and delete all of the existing subtotals.

It provides even more versatility, and I will show you a couple of useful tricks in our next tutorial. For example, this is how your Excel subtotals can look like: How to insert subtotals in Excel To quickly add subtotals in Excel, perform the following steps.

Organize the source data The Excel Subtotal feature requires that the source data be arranged in a proper order and should not contain any blank rows. The easiest way to do this, is click the Filter button on the Data tab, then click the filter arrow, and select to sort either A to Z or Z to A: To remove blank cells without messing up your data, please follow these guidelines: How to remove all blank rows in Excel. If you want to add subtotals only for some part of your data, select the desired range before clicking the Subtotal button.

Only visible rows are subtotaled In essence, Excel Subtotal calculates values in visible cells and ignores filtered out rows. The Excel Subtotal feature inserts formulas with function number The image below illustrates the difference: To exclude manually hidden rows so that only visible cells are calculated, modify the Subtotal formula by replacing the function number with the corresponding number Grand totals are calculated from the original data The Excel Subtotal feature calculates grand totals from the original data, not from the subtotal values.

Just compare the following screenshots to see the difference: 3. Subtotals are not available in Excel tables If the Subtotal button is grayed out on your ribbon, then most likely you are working with an Excel table. Sort data by several columns When inserting nested subtotals in Excel, it is important that you sort the data in all the columns that you want to group your subtotals by.

As the result, the values in the first two columns are sorted in alphabetical order: 2. Insert the first level of subtotals Select any cell within your data list, and add the first, outer level, of subtotals as demonstrated in the previous example. I think you are missing our problem. When filtering, the subtotal, 9 does not change the sum to reflect what is being filtered as would be expected. Products 72 Special Topics 41 Video Hub Most Active Hubs Microsoft Teams.

Security, Compliance and Identity. Microsoft Edge Insider. Azure Databases. Autonomous Systems. Education Sector. Microsoft Localization. Microsoft PnP. Healthcare and Life Sciences. Internet of Things IoT.