Learn how to insert subtotal rows into sorted data without having to spend time doing it manually.
Recently I ran a training session for an Accounts Manager and her staff.
They spent a lot of time pulling data out of their in-house computer system, sorting it by customer and then inserting a new row at every change in the customer name.
This was so that they could place a SUM into the row to total what the customer had spent with them.
Using the Subtotals feature I showed them a quick and effective way in which to summarise their data. And yep! They were pretty impressed!
Insert Subtotals into your data with a couple of clicks
Before you insert automatic subtotals, sort your list so that the data to be sub-totaled is grouped together.
Inserting automatic subtotals
1. First make sure your data is compatible for subtotaling:
- Turn Filtering off
- Ensure that each column has a title row.
- Check that there are no blank columns or rows.
2. Sort the column that contains the data you want to base your subtotals on. For example, if you wanted to insert a subtotal row after every change in the Product Type column you would sort the data first to ensure all products are grouped together.
3. Select the range to be subtotaled and then select Subtotal from the Outline group on the Data tab.
4. From the At each change in box, select the field that you want Excel to Subtotal by when it identifies a change in the data within that field.
5. Select a function from the Use function box to calculate your Subtotals with. The Sum function is the default option for fields containing numbers and the Count function is the default for fields containing text.
6. From the Add subtotal to list, select each field that you want Excel to Subtotal.
7. Click OK. Excel will now calculate the subtotals, insert a sub-total row at each change in the selected fields and insert a Grand Total at the bottom of the list.
A sub-totaled list is displayed with Outlining symbols attached so that you can show/hide different levels of data according to your requirements. Click the outlining level number or collapse and expand buttons to control how you view the data.
Removing Subtotals
1. Select any single cell in your list.
2. From the Data tab select Subtotal.
3. From the Subtotals dialog box select Remove All. Excel will display your list in its original form.
Multiple Subtotals
Multiple subtotals allows you to display several subtotals within the one data table.
To create another subtotal:
1. From the Data tab select Subtotal.
2. In the At each change in box select the new field to subtotal by.
3. Clear the selection from the Replace current subtotals option.
4. Click OK.
Excel adds another outlining level to the list and inserts subtotals within your already sub-totaled list. Nested subtotals are removed in the same manner as normal subtotals.
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.
She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.
If you enjoyed this you may also enjoy...
How to freeze a row in Excel
Let’s look at how to freeze a row in Excel. It can be a real pain when you don’t have freeze panes on, and when you start to scroll down the screen you lose the headings at the top of your columns.
Use Conditional Formatting to identify cells that are of interest
Learn how to use Conditional Formatting to identify cells that are of interest. For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using
Find, modify and break links to an Excel workbook
Learn how to find, modify and break links to an Excel workbook. Linking is great, until you no longer need it. Then it can be a bit of a pain. A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data
Link cells in the same or different Excel worksheets
Learn how to link Link cells in the same or different Excel worksheets. Linking saves a huge amount of time (and a huge amount of mistakes) in that it allows you to create connections from one cell to another.
Use the SUMIF function to total only the cells that match your requirement
Use the SUMIF function to total only the cells that match your requirement. For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a
Insert an Excel function into your workbook
Learn how to insert an Excel function into your workbook. Excel comes equipped with a large number of pre-defined formulas. These are known as functions. There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few.