Sort Methods to Organize Data in MS-Excel [With Screenshots]

If you want to organize your data, the best way is to put all the data in a spreadsheet in Ms Excel. Once all the data has been entered, there are various ways in MS-Excel to organize the data efficiently and as per your requirements.

Arranging the unsorted data as per criteria is really important to make our data more presentable and easily understandable. It becomes a tedious job to search for a record if the data is not ordered properly.

For ordering our data, MS-Excel provides a Sort feature. Sorting can be done on numbers, alphabets, background color and much more.

In this article, we will cover the various ways of sorting data in MS-Excel i.e.

• Sorting based on One Column
• Sorting based on Multiple Columns
• Sorting Numbers
• Sorting Dates or Times
• Sorting by Background Color of cell

So let us start with the sorting of data and make our data well organized.

Where to find the Sort Menu?

Before we start sorting our data we need to first understand where this tool is present in Excel. The Sort option is present at two places in Excel.

Firstly, sort option is present in the Home tab itself in the Editing section.

Secondly, in the Data tab, we have the Sort & Filter section.

Using the Sort Menu

You can use either of the two options to sort your data in MS-Excel. Both the options will give you the same results. Let us show you how these options work.

Sort & Filter option in the Home tab

This option is used when we wish to sort data according to a particular column. Let’s learn it side by side with an example.

Take note of the following un-ordered data:

To sort this data follow the following steps:

1. We need to first select the cell of the column based on which we want to sort our data. Say cell D4 in this example.
2. Then, click on the Sort & Filter option in the Home tab then it will display the following options.
3. On selecting sort A to Z option the entire data will be sorted in alphabetical order based on the cell selected before this option was clicked.
4. In order to sort the data in reverse order i.e. z to a, click the option Sort Z to A. This will sort the data as follows:
5. If you want to refine your sorting further, click on a Custom sort… option. Clicking this will show you the same dialog box which will also open on clicking the sort option in the data tab. This will be discussed further in the article.

NOTE: No row or column should be left empty in between the data else data before the empty row or column will only be sorted.

For Example:

Data before using sorting

After sorting in increasing order i.e. a to z

Note that the data before the empty row is only sorted with respect to D4 cell in alphabetic order.

Sort & Filter option in the Data Tab

On clicking the Sort Option in the Data Tab, the following dialog box will be displayed:

It is here that we mention various criteria for sorting our data. The add Level, Delete Level and Copy Level buttons helps us sort data with multiple criteria.

Arranging Data Alphabetically

To perform simple sorting, you need to first select the data you want to sort. Then click on the sort option in the data tab. When the dialog box appears, select the column name by which you want to sort the data (Student Name in this example).

Then select the order in which you want to sort the data. Then click the OK button. Once clicked, the data will be sorted based on the column and order specified. Have a look at the output of the above example.

The data is sorted in decreasing order (i.e. from z to a) of the name of the student.

Alphabetically Sorting data with Multiple Criteria

Now say that you want to sort data according to the stream in which a person has studied but you also want to sort the names in alphabetic order, then how to achieve this? The answer is that we sort our data at two levels. Let us see how to do so.

Here is our unsorted data. Let us sort it by multiple criteria i.e. Stream and Student Name.

1. Once again open the Sort dialog box.
2. In the sort by option we will select the stream column and in the order option, we will select the A to Z option.
3. Now click on Add Level

    

4. This will enable us to add another criterion. Here we will select the column with which we want to further sort our data. In our example, it is Student Name and will specify the order in which data is to be sorted.
5. Click OK. And the data will be sorted with these two criterion. Have a look at the output.

Sorting Numbers

In MS-Excel, you can not only order the data alphabetically but also order them on the basis of the numeric values. Let us understand it with this example.

Have a look at the unsorted data.

For sorting values according to any numeric values (say Percentage in this case) you need to follow the following steps.

1. Open the Sort dialog box.
2. Under Sort by drop-down list select the column containing numeric values. So we have selected percentage column.
3. Note that the order drop down box options will change.
4. Select the option smallest to largest to arrange the data in increasing order. For decreasing order, select largest to smallest option.
5. Click OK. Your data will be sorted accordingly.
6. Have a look at the output. We have arranged the data in decreasing order of percentage.

Sorting Dates or Times

In MS-Excel, you can also order the data according to dates. Let us understand it with this example.

Have a look at the unsorted data.

For sorting values, according to any date (say DOB in this case), you need to follow the following steps.

1. Open the Sort dialog box.
2. Under Sort by drop-down list select the column containing dates. So we have selected DOB column.
3. Note that the order drop down box options will change to Oldest to Newest and Newest to Oldest.
4. Select the option Oldest to Newest to arrange the data in increasing order of dates. For decreasing order select Newest to Oldest
5. Click OK. Your data will be sorted accordingly.
6. Have a look at the output. We have arranged the data in increasing order of DOB i.e. oldest to newest.

Sorting by Background Color of Cells

If you are familiar with conditional formatting you would know that excel helps us work with cell background colors. These colors can be used by us to even sort our data according to the color they contain. Let us take an example.

Have a look at the data which we have already formatted using conditional formatting.

Here, we have formatted our data on the basis of the percentage, a person has scored. The colors range from: the person with the highest percentage has a red color background, the one with the least percentage has a peach colored background, and the others have a blue background.

In order to sort data according to the color of the cell, follow the following steps:

1. Open the sort dialog box.
2. Now, in the Sort on drop-down list, select Cell Color in place of values.
3. On selecting this, an additional drop-down list named Order will come. This will contain all the color options we have in our data. This will also ask us to set the sort order for the data.
4. Add levels and Click OK.
5. Now have a look at the sorted data. The data with red background comes first, followed by blue background data and the rest at last.

Final Words

Hope we could simplify the sorting data concept of Microsoft Excel for you. This feature will surely help you organize and analyze your data in an enhanced manner.