Md. Abdur Rahim Rasel
Excel is widely used to store important numeric information of students or employees. So it is often needed to extract a group of subtotal values from a large dataset to understand the actual scenario. Today, in this article, we’ll learn two quick and suitable steps to sort subtotals in Excel effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to the Subtotal in Excel
Before diving into the calculation part, let’s know the meaning of subtotal.
In generic terms, subtotal refers to the total of one set of a larger group of sets. For example, suppose you got 100 marks in your last semester, where the marks of the math course were obtained from the three-class tests that you had. In the first class test you got 10, in the second you got 15, and in the last class test, you got 20. So now you want to know only your math score out of the total 100 marks. To get that easily, you can use subtotal. Similarly, in Excel, you can use the subtotal function to break down a larger set of data into a smaller set and then perform various other Excel functions such as SUM, AVERAGE, MAX, MIN, COUNT, PRODUCT, etc. to achieve the desired result.
2 Easy Steps to Sort Subtotals in Excel
Let’s assume we have an Excel large worksheet that contains the information about several students of Armani School. From our dataset, we will sort subtotals by using the Subtotal command. Here’s an overview of the dataset for today’s task.
Step 1: Use Sort Command to Sort Subtotals in Excel
In Microsoft Excel, a Sort command is a powerful tool to filter data. From our dataset, we will filter Austin’s information using the Sort command. This is an easy and time-saving way also. Let’s follow the instructions below to filter multiple values in one cell!
- First of all, select cells array B4 to D14.
- After selecting the cells array, from your Data tab, go to,
Data → Sort & Filter → Sort
- As a result, a dialog box named Sort will appear in front of you. From the Sort dialog box, firstly, select Name from the Sort by drop-down box. Secondly, select Cell Values from the drop-down box named Sort On. Thirdly, select A to Z from the Order drop-down box. At last, press OK.
- After pressing OK, you will be able to sort your data from A to Z order which has been given in the below screenshot.
Read More: How to Use SUBTOTAL in Excel with Filters (With Quick Steps)
Step 2: Apply Subtotal Command to Sort Subtotals in Excel
After sorting our dataset, we will apply the Subtotal command to Sort Subtotals. This is an easy and time-saving way also. Let’s follow the instructions below to Sort Subtotals!
- First of all, select the range of data that you want as the subtotal category. From our dataset, we will select cells array B4 to D14.
- After selecting the cells array, from your Data tab, go to,
Data → Outline → Subtotal
- Hence, a Microsoft Excel warning box pops up. From that warning box, press Yes.
- As a result, a Subtotal dialog box will appear in front of you. From the Subtotal dialog box, firstly, check the CGPA box under the Add subtotal to the drop-down box.
- If you already have an existing subtotal result and you want to remove that, then select the check box beside Replace current subtotals, otherwise, clear the check box.
- Wherever you want to insert an automatic page break for each subtotal, then select the Page break between groups check box, otherwise uncheck it.
- If you want your subtotal results at the bottom of each category, then select the Summary below data check box, otherwise, uncheck the box.
- At last, press OK.
- After completing the above process, you will be able to sort subtotals of your dataset which has been given in the below screenshot.
Things to Remember
➜ While a value can not found in the referenced cell, the #N/A error happens in Excel.
➜ Remove Subtotal
If you no longer need the subtotals, simply follow the steps below to remove subtotals,
Steps:
- First, select cells array B4 to D19.
- After selecting the cells array, from your Data tab, go to,
Data → Outline → Subtotal
- Select the Remove All from the bottom-left side of the Subtotal pop-up box.
- While completing the above process, you will be able to remove subtotals of your dataset which has been given in the below screenshot.
Conclusion
I hope all of the suitable methods mentioned above to sort subtotals will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
