How to Sort Subtotals in Excel

Sorting and adding subtotals is one of the features of Excel which is important for organizing and summarizing data in a structured manner. In this article, you will see the overview of Sorting and Adding Subtotals to Excel and then will discuss all the steps to perform, allowing you to take control of your datasets and derive valuable insights.

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.

To arrange data based on subtotals, begin by inserting subtotals into the dataset. After you’ve added subtotals, you can sort the data while preserving its structure and ensuring that related data remains grouped together.

For Example, You’ve the Following data:

To group students and calculate subtotals for each student in Column A, allowing you to sort their marks while retaining all the individual details, make sure your data is sorted based on the column you intend to group (in this case, Column A). Here’s how to do it:

Step 1: Select the Data

Step 2: Go to the Data tab in the Ribbon

Step 3: Click on Subtotal to access the Subtotal window

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.

Step 5: Click OK to Apply the Subtotals

Step 6: Preview the Result

Following these steps, you’ll see subtotals generated for each student in Column A. This allows you to sort the marks of each student while preserving the detailed data for each individual.

Follow the below steps to Add subtotals in Excel:

Step 1: Make sure your data in Sorted

Before adding subtotals, make sure your data is sorted correctly according to your 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 you can select the subtotal setting according to your need.

Step 3: Set the Subtotal options

In the Subtotal dialog box, first, choose the column you want to use for subtotals from the “At each change in” drop-down list. Now select the function you 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 you 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 your dataset.

Step 6: Apply the subtotals

After selecting the options, click “ok” in the Subtotal dialog box.

Step 7: Expand and collapse Subtotals

• How to Sort data in Excel

What is Sorting in Excel?

Sorting is the process of arranging the strings or integers so that they can be placed in ascending or descending order. You can sort a text column alphabetically(A-Z or Z-A) and the number column can be sorted either from largest to smallest or smallest to largest.

What is the purpose of Sorting in Excel?

Sorting helps to organize your data in a specified order. It helps you to easily find, analyze, and present information in a structured manner.

How to Sort Data?

Follow the below steps to sort your data:

Step 1: Select the range you want to sort.

Step 2: Go to the “Data” tab on the Excel ribbon.

Step 3: Click on the “Sort button” in the “Sort & Filter” group.

Step 4: Now you can specify your sorting criteria in the Sort Dialog box.

How to sort data based on Multiple Columns?

Adding additional sorting levels in the Sort Dialog box to arrange data by multiple criteria, provides a more comprehensive organization of your dataset.

How to add subtotals in Excel

Follow the below steps to add subtotals in Excel:

Step 1: Make sure your data is sorted by grouping column.

Step 2: Go to the “Data” tab, click on the “Subtotal” button in the “Outline” group, and Specify the column to use for subtotals.