Sorting a table rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort your sales staff by the total sales made. Excel’s Sort feature helps organize the data in a list. Column fields can be quickly sorted in ascending or descending order. To sort the data in a column, select any cell in the column by which you want to sort.

In this blog, I will show you how to Sort the data in Excel and how to do Subtotaling of the data.

Table of Contents: –

Sorting A Table

We can Sort the table in four ways:

• Ascending Order in Alphabetic
• Descending Order in Alphabetic
• Smallest to Largest in numbers
• Largest to Smallest in numbers

Below is a data set where I have the agent name in column A, date listed in column B, area name in column C, list price in column D and sq. ft in column E.

Now, we want to sort the data Ascending Order. We will select the data which we want to sort in Ascending Order. After selection of data we will click on Data-> Sort -> Order “A-Z”

I have sorted “A-Z” based on Agent Name. So you get the data set as below

And if you do Descending Order, data set will be:

Now, we want to sort the data from “Largest to Smallest”. We will click on Data-> Sort -> “Largest to Smallest

I have sorted “Largest to Smallest” based on List Price. So you get the data set as below

And if you do “Smallest to Largest”, data set will be:

You can use custom filter to sort the data in more effective way by adding the levels in sort. Basically, we are saying excel, to sort the data by first level, after that second level, after that third level… and so on.

I have selected the data and add the custom sort, please see the below

As you can see, I have three levels, first level sort by Agent Name, second level sort by Area Name and third level sort by List Price (Smallest to Largest).

Result:

Note: You can sort on any number of columns. The trick is to sort the most significant column first and then proceed until the least significant column is sorted. For example, in the real estate table, you may want the list to be sorted by agent, And within each agent’s group, the rows should be sorted by area. And within each area, the rows should be sorted by list price. For this type of sort, first sort by the List Agent column, then sort by the Area column, and then sort by the List Price column. The figure above shows the table sorted in this manner.

Subtotaling of Data

Data in a list can be summarized using subtotal. The subtotal is invariably based on a field of a list of data that you must initially sort in order to have the list subtotaled.

Below is a data set where I have the agent name in column A, month in column B, area name in column C, list price in column D, achieved in column E and result in column F

Now, we will get the total based on Area wise.

Step 1: – Select the data, then click on Data Tab -> Subtotals. As we want the subtotal area wise.

So we will select the “Area” in first field and Use Function will be “SUM” and subtotal field will be “List Price”, as want the subtotal of list price.

Now click on the OK button to get the result:

If the database has any existing subtotal, then before clicking on OK, choose to Replace current subtotals to make it refresh. 

Page break between groups option is required if your database contains huge records and you want to get each group’s data in separate pages. The summary above data shows the Grand Total at the below of the database.