Subtotals in Excel - Step-by-Step Guide

Introduction

Subtotals help you quickly add up numbers in groups within a list. This feature automatically inserts summary rows that show totals for each group, making it easier to see parts of your data without doing math yourself.

When you have a sales list sorted by region and want to see total sales per region.

When you track expenses by category and want to know the total spent in each category.

When you have a list of students with their scores and want to find the average score per class.

When you want to quickly add totals for each month in a date-sorted list of transactions.

When you want to insert summary rows without creating separate pivot tables.

Steps

Step 1: Sort your data by the column you want to group by

- Data tab > Sort

Your data is arranged so that all items in the same group are together

💡 Sorting first is important because subtotals work on consecutive groups

Step 2: Select any cell inside your data range

- Worksheet area

Excel knows which data to use for subtotals

Step 3: Click Subtotal

- Data tab > Outline group > Subtotal button

The Subtotal dialog box opens

Step 4: Choose the column to group by in 'At each change in' dropdown

- Subtotal dialog box

Excel knows where to insert subtotal rows

Step 5: Select the function to use (Sum, Count, Average, etc.)

- Subtotal dialog box > Use function dropdown

Excel knows how to calculate the subtotal

Step 6: Check the box for the column(s) to subtotal

- Subtotal dialog box > Add subtotal to list

Excel will calculate subtotals for these columns

Step 7: Click OK

- Subtotal dialog box

Subtotal rows appear in your data showing totals for each group

Before vs After

Before

A list of sales data sorted by region with no totals, e.g., Region: East, West, East, West with sales numbers in adjacent column

After

The same list now has subtotal rows after each region group showing total sales for East and West, plus a grand total at the bottom

Settings Reference

At each change in

📍 Subtotal dialog box

Defines the column where Excel inserts subtotal rows when the value changes

Default: First column header

Use function

📍 Subtotal dialog box

Chooses the calculation type for the subtotal

Default: Sum

Add subtotal to

📍 Subtotal dialog box

Selects which columns get subtotal calculations

Default: First numeric column

Replace current subtotals

📍 Subtotal dialog box

Decides whether to remove existing subtotals before adding new ones

Default: Checked

Summary below data

📍 Subtotal dialog box

Controls if subtotal rows appear below or above each group

Default: Checked

Common Mistakes

Not sorting data before applying subtotals

Subtotals only work correctly when data is grouped together; unsorted data causes incorrect subtotal placement

Always sort your data by the column you want to subtotal before using the Subtotal feature

Trying to subtotal non-numeric columns

Subtotal calculations like Sum or Average require numbers; text columns cannot be summed

Select only numeric columns for subtotal calculations

Leaving 'Replace current subtotals' unchecked when adding new subtotals

This can cause multiple subtotal layers that clutter the data and confuse results

Keep 'Replace current subtotals' checked unless you want to add multiple subtotal levels intentionally

Summary

Subtotals automatically add summary rows to grouped data for quick totals and calculations.

Always sort your data by the grouping column before applying subtotals.

Choose the right function and columns to subtotal for accurate results.

Practice

1. What does the SUBTOTAL function do in Excel?

easy

A. Calculates sums, averages, counts, and other operations on filtered or grouped data

B. Sorts data alphabetically or numerically

C. Removes duplicates from a list

D. Creates charts from selected data

Solution

1. Step 1: Understand the purpose of SUBTOTAL

   The SUBTOTAL function is designed to perform calculations like sum, average, or count on a range of data, especially useful when data is filtered or grouped.

2. Step 2: Compare with other options

   Sorting, removing duplicates, and creating charts are different Excel features unrelated to SUBTOTAL.

3. Final Answer:

   Calculates sums, averages, counts, and other operations on filtered or grouped data -> Option A

4. Quick Check:

   SUBTOTAL = Group calculations [OK]

Hint: SUBTOTAL works on filtered data for accurate group totals [OK]

Common Mistakes:

• Confusing SUBTOTAL with sorting or filtering
• Thinking SUBTOTAL removes duplicates
• Assuming SUBTOTAL creates charts

2. Which of the following is the correct syntax for the SUBTOTAL function to sum values in range A2:A10?

easy

A. =SUBTOTAL(1, A2:A10)

B. =SUBTOTAL(109, A2:A10)

C. =SUBTOTAL(9, A2:A10)

D. =SUBTOTAL(10, A2:A10)

Solution

1. Step 1: Identify function number for SUM

   In SUBTOTAL, function number 9 means SUM including hidden rows, while 109 means SUM ignoring hidden rows.

2. Step 2: Choose correct syntax for basic sum

   Since the question does not specify ignoring hidden rows, 9 is the standard function number for SUM.

3. Final Answer:

   =SUBTOTAL(9, A2:A10) -> Option C

4. Quick Check:

   SUM function number = 9 [OK]

Hint: Use 9 for SUM in SUBTOTAL unless ignoring hidden rows [OK]

Common Mistakes:

• Using 1 which is for AVERAGE
• Using 10 which is for COUNT
• Confusing 9 and 109 without context

3. Given the data in cells A2:A6 as {10, 20, 30, 40, 50} and rows 4 and 5 are manually hidden, what is the result of =SUBTOTAL(9, A2:A6)?

medium

A. 90

B. 60

C. 100

D. 150

Solution

1. Step 1: Understand SUBTOTAL with function number 9

   Function 9 sums visible and hidden rows, so it includes all values including manually hidden rows.

2. Step 2: Calculate sum of all values

   Sum of 10+20+30+40+50 = 150, but since rows 4 and 5 (values 40 and 50) are manually hidden, SUBTOTAL(9, ...) still includes them.

3. Step 3: Reconsider filtering effect

   Actually, SUBTOTAL with 9 includes manually hidden rows, so sum is 150. But question says rows 4 and 5 are manually hidden. SUBTOTAL(9, ...) includes hidden rows, so sum is 150.

4. Step 4: Check options

   150 is the total sum. 60 is sum of visible rows only (10+20+30=60).

5. Final Answer:

   150 -> Option D

6. Quick Check:

   SUBTOTAL(9) sums all rows including hidden [OK]

Hint: Function 9 sums all rows, even hidden ones [OK]

Common Mistakes:

• Assuming manually hidden rows are excluded with function 9
• Confusing function 9 with 109
• Adding only visible rows mistakenly

4. You want to sum only visible rows in range B2:B10, but your formula =SUBTOTAL(9, B2:B10) includes hidden rows. How do you fix it?

medium

A. Add a filter to the data

B. Change function number to 109: =SUBTOTAL(109, B2:B10)

C. Use =SUM(B2:B10) instead

D. Use =SUBTOTAL(1, B2:B10)

Solution

1. Step 1: Understand difference between function numbers 9 and 109

   Function 9 sums all rows including hidden, while 109 sums only visible rows, ignoring manually hidden rows.

2. Step 2: Fix formula to sum visible rows only

   Change function number from 9 to 109 to exclude hidden rows in the sum.

3. Final Answer:

   Change function number to 109: =SUBTOTAL(109, B2:B10) -> Option B

4. Quick Check:

   Use 109 to sum visible rows only [OK]

Hint: Use 109 to exclude hidden rows in SUBTOTAL sum [OK]

Common Mistakes:

• Using SUM which ignores filtering
• Using function number 1 which is AVERAGE
• Thinking adding a filter fixes formula automatically

5. You have sales data in column C with some rows filtered out and some manually hidden. You want to calculate the average sales of only visible rows and also count how many visible sales entries there are. Which formulas should you use?

hard

A. =SUBTOTAL(101, C2:C100) for average and =SUBTOTAL(103, C2:C100) for count

B. =SUBTOTAL(1, C2:C100) for average and =SUBTOTAL(2, C2:C100) for count

C. =AVERAGE(C2:C100) and =COUNT(C2:C100)

D. =SUBTOTAL(109, C2:C100) for average and =SUBTOTAL(110, C2:C100) for count

Solution

1. Step 1: Identify function numbers for average and count excluding hidden rows

   Function numbers 101 and 103 calculate average and count respectively, ignoring hidden rows.

2. Step 2: Confirm formulas for visible rows only

   Using =SUBTOTAL(101, C2:C100) gives average of visible rows; =SUBTOTAL(103, C2:C100) counts visible numeric entries.

3. Final Answer:

   =SUBTOTAL(101, C2:C100) for average and =SUBTOTAL(103, C2:C100) for count -> Option A

4. Quick Check:

   101 = average visible, 103 = count visible [OK]

Hint: Use 101 for average and 103 for count on visible rows [OK]

Common Mistakes:

• Using 1 and 2 which include hidden rows
• Using AVERAGE and COUNT ignoring filters
• Confusing function numbers 109 and 110 which are for SUM and COUNT ignoring hidden rows