Sorting in Excel

In this post we are going to discuss about sorting in Microsoft Excel. Sorting is used to order data in a specific manner. As an example, one might need to sort set of people from shortest to tallest to based on their height. Or maybe based on weight from heaviest to the lightest. Or maybe people names alphabetically. There are lot of ways you can sort (order) data based on. 

In Excel it is much easier to sort using the sort and custom sort. Let's dive in and see how to sort (order) data in an Excel sheet.

Let's take the below data set to demonstrate the sorting operations. Create a new Excel Workbook and prepare the data set as it is.

 

First Name	Last Name	Maths	English	ICT	Total
Nimal	Silva	72	85	76	233
Kamala	Fernando	50	72	64	186
Anura	Perera	82	78	88	248
Priya	Gunaratne	67	62	77	206
Indika	Ranasinghe	55	83	70	208
Sujith	Bandara	73	45	60	178
Damayanthi	Liyanage	88	76	84	248
Sunil	Jayawardena	62	90	68	220
Malini	Rajapaksa	80	88	75	243
Pradeep	Wijesinghe	68	55	67	190

 

Now let's try to do some Sorting operations to order data in different ways.

Before we start sorting operations let's get 5 copies of the above sheet. To do that right click on sheet name (Sheet 1) and select Move or Copy. From there tick the Create a Copy and click OK. Do this 4 more times.

Let's start sorting these data based on the First Name alphabetically. Change the sheet name to Alphabetically. Now let's do the Sorting. 

Select all the data cells. Then click "Sort & Filter". Select "Custom Sort". 

This will open the custom sort dialog. Change the Sort by value to the "First Name" as we are going to sort by First Name alphabetically.   Then for Sort On "Cell Values". Finally for Order select "A to Z". Click OK to apply the sorting.

Now the Table is sorted alphabetically based on the first name. In the table the first row is now "Anura Perera" and his marks. Also the last one is "Sunil Jayawardena". 

Notice that both Sunil & Sujith have the same first letter and same second letter. But because of the third letter Sujith will come before Sunil.

When it comes to sorting a text column, there we have two options, either "A to Z" (alphabetically)  or "Z to A" reverse alphabetically.

Let's try sorting a Number column. We'll sort this table by Maths marks from lowest to highest. For this we'll select the next sheet. Rename the sheet to "Maths L to H"

Select all the data cells. Then click "Sort & Filter". Select "Custom Sort". 

Now for the sort by column select "Maths" column. Then for sort on select "Cell Values". And for the Order "Smallest to Largest". Then click OK to apply the sorting.

For number filters, there are two options. They are Smallest to Largest (Ascending order) and Largest to Smallest (Descending order).

Try applying different sorting options listed below to table data.

Sort the table data Last Name reverse alphabetically.

Sort the table data Total Largest to Smallest.

Note: Perform these sorting operations on copied sheets.