How to Sort Subtotals in Excel (with Quick Steps)

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.

Apply Subtotal Command to Sort Subtotals in Excel


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.

sort subtotals in excel

  • After selecting the cells array, from your Data tab, go to,

Data → Sort & Filter → Sort

Using Sort Command to Sort Subtotals in Excel

  • 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.

Using Sort Command to Sort Subtotals in Excel

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.

Apply Subtotal Command to Sort Subtotals in Excel

  • 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.

Apply Subtotal Command to Sort Subtotals in Excel

  • 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.

Apply Subtotal Command to Sort Subtotals in Excel


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.

sort subtotals in excel

  • 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.

Apply Subtotal Command to Sort Subtotals in Excel

  • 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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo