June 20


Insert subtotal rows into sorted data

By Sharyn Baines

June 20, 2018


Excel Sort Levels

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.

Excel 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.

Excel Subtotal


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.

If you enjoyed this you may also enjoy...

How to freeze a row in Excel

[Watch on YouTube] /  [Subscribe to our YouTube Channel]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. 

Read More

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

Read More

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

Read More

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. For example, if I’m creating a personal cash flow worksheet and at the end of the month I

Read More

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. 

Read More

Sharyn Baines

About the author

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.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!