In Excel, the "Subtotal" is a function that calculates subtotals for a range of data based on a specified column. It's commonly used in large datasets to summarize data at different levels, such as groups or categories. The SUBTOTAL function can perform various aggregate functions like SUM, AVERAGE, COUNT, MAX, MIN, etc., and it automatically updates when the data changes. This function is helpful for creating organized reports or analyses, especially when dealing with extensive datasets.
1. Sorting Data in Excel
To arrange data based on subtotals, begin by inserting subtotals into the dataset. After we've added subtotals, we can sort the data while preserving its structure and ensuring that related data remains grouped together.
For Example, we've the Following data:
.png)
To group students and calculate subtotals for each student in Column A, allowing we to sort their marks while retaining all the individual details, make sure our data is sorted based on the column we intend to group (in this case, Column A). Here's how to do it:
Step 1: Select the Data Range
Select the range we want to sort (e.g., A1:C20 containing “Student,” “Subject,” and “Marks”).
Step 2: Go to the Data tab in the Ribbon
Go to the Data tab on the Excel ribbon and click the Sort button in the Sort & Filter group.
Step 3: Click on Subtotal
Click on the Subtotal to access the Subtotal window.
.png)
Step 4: Fill the details
In the Subtotal window, select "Student" as the criterion in the "At each change in" drop-down menu.
Check the box next to "Marks" under the "Add subtotal to" section.
.png)
Step 5: Click OK
Click OK to Apply the Subtotals.
Step 6: Preview the Result
.png)
Following these steps, we'll see subtotals generated for each student in Column A. This allows we to sort the marks of each student while preserving the detailed data for each individual.
2. Adding Subtotals in Excel
Subtotals summarize data for groups (e.g., total marks per student) after sorting by the grouping column.
Step 1: Make sure our data in Sorted
Before adding subtotals, make sure our data is sorted correctly according to our need. Subtotals are typically added to a sorted dataset for better organization.
Step 2: Access the subtotal Dialog Box
Select the "data" tab on the Excel Ribbon and click on the "Subtotal" button in the "Outline" group. Now we can select the subtotal setting according to our need.
Step 3: Set the Subtotal options
In the Subtotal dialog box, first, choose the column we want to use for subtotals from the "At each change in" drop-down list. Now select the function we want to apply from the "User Function" drop-down list.
Step 4: Choose the column for Subtotal
In the "Add Subtotal to " Section, check the boxes of the columns for which we want subtotals to be calculated.
Step 5: Select the Summary Row option
Check the "Replace current subtotals" box, This will add a row with the grand total at the bottom of our dataset.
Step 6: Apply the subtotals
After selecting the options, click "ok" in the Subtotal dialog box.
Step 7: Expand and collapse Subtotals
Use the outline buttons (1, 2, 3) on the left to show or hide details. This allows you to toggle between viewing summary totals and detailed data.
Example: For a dataset with “Student,” “Subject,” and “Marks,” sort by “Student,” then subtotal “Marks” to show each student’s total (e.g., Alice: 250, Bob: 180).
Shortcut: Alt + A + B (Windows) to open the Subtotal dialog.