How to Do Subtotals in Excel: A Step-by-Step Guide

Subtotals are an incredibly useful feature in Excel that allow you to summarize and analyze large datasets. With just a few clicks, you can add subtotals to group and total figures by categories, saving tons of time and giving you the ability to view high-level summaries as well as drill down into the details.

In this comprehensive guide, you’ll learn everything you need to know about using subtotals in Excel, including:

What are Subtotals in Excel?

Subtotals in Excel refer to sums or other summary statistics calculated for subgroups within a larger dataset. For example in a sales dataset you may want to insert subtotals by region to quickly see totals per region.

The Excel Subtotal feature allows you to automatically:

• Group data by categories like region, product, date, etc.
• Calculate sums, averages, counts, etc. for each group.
• Show different layer views like overall summary, group subtotals, or detailed rows.

This provides a powerful way to segment and summarize large datasets for reporting and analysis

How to Insert Subtotals in Excel

Adding subtotals in Excel is very easy thanks to the Subtotal command. Here are the steps:

1. Organize your data – Sort your data by the column you want to subtotal. Remove any blank rows.

2. Select your data – Select one or more cells within your dataset.

3. Go to Data > Subtotal. The Subtotal dialog box appears.

4. In the At each change in box, select the column to subtotal by.

5. In the Use function box, pick the calculation type (Sum, Count, Average, etc).

6. In the Add subtotal to box, check the columns to be subtotaled.

7. Click OK. Excel adds the subtotals and grand total rows.

That’s it! Excel will now recalculate the subtotals automatically when the source data changes.

Adding Multiple Subtotal Levels

One very useful trick is to insert nested subtotals to group your data by multiple fields.

For example, you can subtotal first by Region, then by Product within each Region. Here’s how:

1. Sort your data by the outer grouping column (Region).

2. Insert the top level subtotals (by Region).

3. Go to Data > Subtotal again and add a second-level (by Product).

4. Be sure to uncheck Replace current subtotals so you don’t overwrite the first level.

Repeat steps 3-4 to add more levels as desired. This builds a hierarchy with outer and inner groups summarized independently.

Showing and Hiding Subtotal Details

Once you’ve inserted subtotals, you can collapse and expand the different grouping levels as needed.

• Click the 1-2-3 buttons in the top left to show summaries or details.

• Use the + and – buttons to expand/collapse individual groups.

This lets you view high-level overviews or drill into the underlying rows very easily.

Copying Only the Subtotal Values

To copy only the subtotal values to another place:

1. Show the subtotal rows you want to copy.

2. Press Ctrl+A to select the entire dataset.

3. Go to Home > Find & Select > Go To Special and pick Visible cells only.

4. Press Ctrl+C to copy, then paste where needed.

This copies only the visible subtotal cells, leaving out the hidden details.

Changing Existing Subtotals

To modify existing subtotals:

1. Select any subtotal cell.

2. Go to Data > Subtotal.

3. In the dialog box, change the grouping column, calculation, etc.

4. Ensure Replace current subtotals is checked.

5. Click OK.

This will replace your existing subtotals with the new settings.

Removing Subtotals

To delete subtotals:

1. Select any subtotal cell.

2. Go to Data > Subtotal.

3. Click the Remove All button.

This will remove all subtotals, undoing the grouping while keeping the original data intact.

Subtotal Tips and Tricks

Here are some additional pointers for working with Excel subtotals:

• Subtotals ignore hidden or filtered rows. Use structured references or SUMIFS to subtotal filtered data.

• Grand totals calculate from the original data, not the subtotals.

• You can’t use Subtotals within Excel tables. Convert the table to a range first.

• Use the GETPIVOTDATA function to pull summarized values from PivotTables.

• Add custom subtotal labels using the Subtotal Name Manager under Data > Outline.

• Turn automatic subtotals on/off for future PivotTables under PivotTable Options.

• The SUBTOTAL function gives you more flexibility to control hidden rows, nested subtotals, etc.

Subtotals in PivotTables

PivotTables have their own Subtotals and Grand Totals features that provide similar grouping and summarizing functionality.

To add subtotals to a PivotTable:

1. Click anywhere in the PivotTable.

2. Go to PivotTable Analyze > PivotTable and choose Subtotals.

3. Check the boxes for the fields you want subtotals for.

4. Pick a calculation type – Sum, Count, Average, etc.

You can also turn on grand totals for entire PivotTables and enable or remove subtotals for future PivotTables using the PivotTable Options dialog.

The functionality works the same way – you get automatic grouping and summarizing, collapsible details, and multiple subtotal layers. The main difference is PivotTables let you summarize multidimensional data instead of flat ranges.

Summary

Subtotals are an extremely helpful reporting tool in Excel. In just a few clicks, you can:

• Group datasets by categories.

• Calculate sums, averages, counts and other statistics for those groups.

• Collapse and expand to view summaries or details.

• Build multiple subtotal layers for multidimensional analysis.

Using Excel’s Subtotal or PivotTable subtotal features, you can gain new perspective and extract insights from your business data.

So next time you’re faced with a large dataset, don’t spend hours manually summarizing – use Subtotals to automate it instantly. With this guide, you should have all the knowledge needed to master this time-saving tool.