Nov
17

Applying multiple Subtotals to your Excel table

Using Subtotals is much more flexible than I put in my previous post about Subtotals. I kept it compact because that is what I need to suit my needs in the work that I perform. Here I would like to expand – literally – the Subtotal function to show some other options that might help you do the work that you need to do.

I will start with a basic file; here it is before I do anything to it:

Basic file before subtotals

Now as before, go to the Data tab on the ribbon, and select the Subtotal Button:

Open Subtotals by clicking on this button

Once you have that open, I will select At each change in Shipment Number, the Sum Function, and select subtotals for Discount, Net Amount, and Gross Amounts.

Choose subtotal settings

That way I will know the total cost of each shipment in my file. See the results below.

See subtotal results

Now let’s add some other functions to the list to show you what can be done. Again select the Subtotal button, now, to add some other functions don’t forget to “Unclick” the check box beside Replace current subtotals. If you forget to uncheck this box, your new subtotals will show up and all your previous subtotals will disappear. So make sure you uncheck that box! While you are unchecking boxes, uncheck the previous Add subtotal to selections, in our case the Discount, Net Amount, and Gross Amount have all been unchecked.

Make sure you unselect previous columns and Replace current subtotals box

Once that is unchecked you can add functions, I want to add count of Package Quantity.

Add functions to existing subtotals

Here are the results of that Subtotal:

Highlight result after adding new functions

I highlighted the first Subtotal results in green; the Count Subtotal I just completed in yellow. I now have the totals of the costs and the number of Packages in each shipment.

Now let’s add a subtotal for another column without deleting our existing subtotals. We will repeat our steps for setting up a subtotal, I am adding in the average of the weight, destination code and discount. Here is what we get (the newest is highlighted in blue.)

We add a new column and highlight new results

As you can see all of my subtotals are here for easy access and use. By clicking the numbers in the top left I can reduce my file so that all I am looking at is my subtotals, I have highlighted all of my subtotals to make it easy to see what I have done.

Select subtotal level number and highlight the results

There are other functions that you can use within the Subtotal Function such as: Max, Min, Product, Count Numbers, Standard Deviation, Standard Deviation for the Population, Variance, and Variance for the Population. I am not going into these in this blog, but this should give you some ideas to start experimenting with. Play around with some different data and functions to see what you come up with. That is after all the best way to learn new things, experiment with your data.

Thanks
Troy

See also

7 Responses to "Applying multiple Subtotals to your Excel table"

  1. Tatiana says:

    Exactly what I needed. Thank you!!!

  2. Gautam says:

    Very helpful, took care of my work need handily. THANKS,

  3. Scott Eaton says:

    Is there a way to check off all columns in one shot, other than each column one at a time. I have a YTD report that have 36 columns I have to check off one at a time when I do the subtotal and it would be great to just “check all”, then take out about 5 columns, rather than check off 36. Any one know how to do that or if possible? Thank you.

    Scott Eaton

  4. Melissa says:

    Thank you so much! It was the tips I was looking for!!

  5. Jay says:

    Very helpful

  6. Ravi Lele says:

    Very helpful.

  7. Anonymous says:

    thanks.

Post a comment