« The Best Way to Update Third Party (Flash, Java, Reader, etc) Programs on EARTH! | Main | OverDrive How-To Guides »

Creating simple subtotals in Excel

Most people who work with Excel spreadsheets know you can total a group of numbers by using the Sum function.  But did you know Excel has a subtotal function that will create simple subtotals and a grand total for you? 

Open the spreadsheet and select the data you would like to subtotal.  Then go to the Data tab and look on the right hand side for the Subtotal option.  Click on the Subtotal icon and a pop-up window will appear asking how you want to subtotal the information. 

Subtotalbar

The “At each change in:” option, lets you define the groups of entries you wish to subtotal.  For example, if you have a report that is for all libraries, you can choose to have it subtotaled by library by choosing Library for the “At each change in:” option.  To know what’s available for options, click the down arrow on the right side of the in the “At each change in:” box, and choose from a list of possible selections. 

The “Use function” section allows you to choose what kind of subtotal you wish to see.  For example, you can go with a straight up sum of the numbers or you could choose to show the average of the numbers instead.  Again, to know what’s available, click on the down arrow at the end of the “Use function:” box.

With the “Add subtotal to:” selections, you decide which column is getting subtotaled.  Most of the time you’re going to want to choose a numeric column since subtotals of most non-numeric data don’t make much sense. 

There are also three checkboxes for subtotal options.  First is “Replace current subtotals”.  If you are wanting to do multiple subtotals, for example one for the sum and one for the average, deselect this option for the successive subtotals to keep them from replacing the original subtotals.  Next is “Page break between groups”.  This will cause each subtotaled group to print on a separate page when you print the spreadsheet.  The last option is “Summary below data”.  With this, you control whether the grand total and the subtotals are at the top of what they’re totaling or if the subtotals come after the list of entries being totaled. 

When you're done with your selection, click on OK.  You've now got a quick, simple subtotal of your data.

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

Post a comment