Lesson 2.5 – Subtotal Feature in Excel

Lesson Overview

The Subtotal feature in Excel allows you to summarize grouped data by automatically calculating totals, averages, counts, or other functions for each group. It is very useful for reports and data analysis where categories or groups need quick aggregation.

1. Understanding Subtotals

• Definition: Subtotals automatically insert summary rows within sorted data to show aggregated values for each group.

• Purpose:

  • Quickly summarize data by category

  • Avoid manual calculation for each group

  • Enhance readability of large datasets

2. Preparing Data for Subtotals

1. Ensure your data has clear headers.

2. Sort your data by the column you want to group.

   • Example: If you want subtotals by “Department”, first sort by Department.

3. How to Add Subtotals

1. Select any cell in your dataset.

2. Go to Data → Outline → Subtotal.

3. In the Subtotal dialog box:

   • At each change in: Choose the column to group (e.g., Department)

   • Use function: Choose the calculation (SUM, COUNT, AVERAGE, MAX, MIN)

   • Add subtotal to: Select the column to summarize (e.g., Salary)

4. Click OK

Result: Subtotals are added automatically at the end of each group.

4. Adding Multiple Subtotal Levels

• You can apply a second level of subtotal for another column.

  • Example: First by Department, then by Region

• Steps: Repeat the Subtotal process after the first subtotal is added.

5. Replacing or Removing Subtotals

• To replace existing subtotals: Check “Replace current subtotals” in the Subtotal dialog.

• To remove subtotals:

  1. Go to Data → Outline → Subtotal

  2. Click Remove All

6. Tips for Using Subtotals

• Always sort the data before adding subtotals.

• Use grouping symbols (+ / -) to expand or collapse groups.

• You can view different levels of data using the Outline bar on the left.

• Combine with Conditional Formatting for better visibility.

7. Real-Life Examples

• Summarize total sales per region in a sales report.

• Count number of employees per department.

• Calculate average project cost per project type.

Practice Questions

1. What must you do before adding a subtotal in Excel?

2. How do you calculate the average of a group using Subtotal?

3. How can you remove all subtotals from a dataset?

4. Explain how multi-level subtotals work.

5. How can you collapse or expand groups after adding subtotals?