How to Sort Subtotals in Excel
Last Updated :
17 May, 2024
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.
What are Subtotals in Excel
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.
How to Sort Subtotals in Excel
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:
.png)
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
.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 to Apply the Subtotals
Step 6: Preview the Result
.png)
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.
How to Add Subtotals in Excel
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
Also Read
Similar Reads
How to Sort by Number in Excel? Excel is the world where you can organise your data. Organising data is a very big task especially when you are doing it on a large scale. In Excel, one can have around 17 million cells in one worksheet. So at this point, arranging the data in a systematic order is very important. If you are curious
2 min read
How to Sort by Date in Excel? Excel provides various types of sorting formats e.g. âsort A to Zâ, âSort Z to Aâ, âSort by colorâ and âSort by dateâ etc. Sorting in Excel is nothing but arranging data into some meaningful order to make it easier to understand, analyze, or visualize. Here, we will be discussing how to sort by Date
6 min read
How to Create Relational Tables in Excel? Excel directly doesn't provide us ready to use a database, but we can create one using relationships between various tables. This type of relationship helps us identify the interconnections between the table and helps us whenever a large number of datasets are connected in multiple worksheets. We ca
4 min read
How to Sort Data by Color in Excel? Sorting Data By Color allows us to segregate the data cells of a specific color. There can be many ways to sort by color like sorting by Cell color, sorting by Font color, etc. We can also add multiple levels in sorting data by color. Sorting by Color makes analysis very easy and time-saving. Sort b
3 min read
How to Calculate Running Total in Excel? Excel is a tool widely used by professionals for financial data or trend analysis but can be used for different purposes. It is used for various data visualization purposes. It simplifies data management and data evaluation which uses spreadsheets for managing, storing, and visualizing large volumes
2 min read
How to find Top or Bottom N values in Excel? Excel provides powerful functions to find the top or bottom N values in a dataset, making data analysis much easier. The functions used to find the Nth largest and Nth smallest numbers are =LARGE(array, k) and =SMALL(array, k), respectively. In this article, we will explore how to use these function
6 min read
How to Sort a Pivot Table in Excel : A Complete Guide Sorting a Pivot Table in Excel is a powerful way to organize and analyze data effectively. Whether you want to sort alphabetically, numerically, or apply a custom sort in Excel, mastering this feature allows you to extract meaningful insights quickly. This guide walks you through various Pivot Table
7 min read
How to Sort Data in Excel: Easy Step by Step Process Sorting data in Excel is a simple yet powerful way to organize your information, making it easier to analyze and interpret. Whether you're working with a small dataset or a large table, knowing how to sort data in Excel can help you find the information you need quickly. In this guide, we will cover
5 min read
How to Sum a Column in Excel: 3 Methods Knowing how to sum a column in Excel is essential for managing budgets, financial reports, and large datasets. Whether youâre calculating sales totals, adding expenses, or analyzing business data, Excel provides multiple ways to add up a column efficiently. Many users wonder "how to add columns in E
5 min read
How to Create Slicers and Timelines in Excel? Slicers and Timelines in Excel are used to implement with the Excel tables and Pivot Tables to help in filtering out large amounts of data to get the high level of information from a collection of data. Finding Slicer and Timeline in Excel Navigate to the Insert menu ribbon, Select the Filters secti
3 min read