How to Sort Data by Value in Excel (5 Easy Methods)

In this article, I will discuss how you can sort data by value in Microsoft Excel. While working with a large amount of data in Excel, often we need to organize them. In such a case, the Excel Sort option allows us to customize the order of worksheet data. For example, you can organize the ages of some people according to their names. We can sort data alphabetically, numerically, date-wise, etc. Let’s go through the article to learn how to sort data by value.

1. Using Ascending Order to Sort Excel Data by Value in a Column

Suppose we have the below dataset, containing several people’s names and their ages.

Now I will sort the ages of the above people first. Then I will sort their names too.

Steps:

• To sort the ages of the people, select the age values of column C first.

• Then, to sort in ascending order, go to the Data > A to Z icon from the Sort and Filter section (see screenshot).

• As a result, the below Sort Warning dialog will appear, select Expand the selection to organize the names of the people according to sorted age.
• After that,  press SORT.
• If you do not want sorting data in the selected column to affect the rest of the data then select Continue with the current selection.

• Finally, you will see the below result. The age values of the people are sorted in ascending order.

• Similarly, you can organize the above age data in descending order by going to Data > Z to A icon.

⏩ Note:

You can sort the names of column B in alphabetical descending/ascending order by following the below steps.

Steps:

• Select the names first.
• Then go to Data > A to Z/ Z to A icon.

• Consequently, here is the result we will get. You can apply this process to organize date values too.

Read More: How to Sort Data in Alphabetical Order in Excel

2. Sorting Data in Excel Range or Table by Value

Let’s say, we have the below data range containing several people’s names, ages, occupations, residing city, etc.

Now, I will organize the data in ascending order based on the people’s names.

Steps:

• Firstly, select any of the cells in the column that you want to sort (here Cell B7).
• Then, go to Data > A to Z icon (see screenshot).

• As a consequence, Excel will return the following result. All the name data in column B are sorted, hence updating the order of the data in the rest of the columns.

Read More: How to Add Sort Button in Excel

3. Using Custom Sort to Sort Data by Value in Excel

We can use the Custom Sort option to organize data in multiple columns. Besides, we can make a custom list, and thus sort data according to the list.

3.1. Sorting Data in Multiple Columns

First, I will sort data by values in multilevel using the Custom Sort option. Using this option we can sort data in multiple columns.

Steps:

• Initial, select any of the cells in the below dataset and go to Data > Sort.

• As a result, the Sort dialog shows up.
• Next, for the first level, select the column name from the Sort by drop-down.
• Then click on the Add Level. As I want to add two more levels, I will click Add Level twice.

• Consequently, you will see the below levels are added in the Sort dialog. Now, select the columns you want to sort, then check Sort On and Order and press OK.

• Finally, we will get the below result.

Read More: How to Remove Sort by Color in Excel

3.2. Sorting Data Using a Custom List

Sometimes we need to organize data based on a custom list. In this method, I will show you how you can make a custom list. Later I will sort values according to the custom list.

Let’s assume, in the below dataset, that 3 different occupations are listed. I will sort the Occupation column according to the list: Lawyer, Engineer, Journalist.

Steps:

• First, select any of the cells from your dataset and go to Data > Sort to bring the Sort dialog box.
• Next, from the Sort dialog, click on the Order drop-down and select the Custom List option. Press OK after that.

• As a result, the Custom List dialog appears, click on the New List, type the list items in the List entries box, and click Add.

• After the list is added to the Custom List, press OK to close the window.

• Now we are back to the Sort dialog, click OK to apply the custom list in the Occupation column.

• Finally, we will get the below result. All the data are sorted by the list: Lawyer, Engineer, Journalist.

Read More: Excel Sort and Ignore Blanks

4. Inserting Excel SORT Function to Order Data by Value

This time, I will use the SORT function in Excel to organize data by value. If you are using Excel 365/Excel 2021, then you can sort data very quickly using this function.

Steps:

• First, type the below formula in Cell E5 and press Enter.

• Upon entering the formula, we will get the below result in an array.

Here, the SORT function orders data in range B5:C14 by column 2.

Read More: How to Sort Data Using Excel Formula

5. Combining INDEX, MATCH & SMALL Functions to Sort by Numerical Value

In this method, I will sort data by numerical value using the combination of INDEX, MATCH, ROW & SMALL functions. For instance, I will order people’s names in the below dataset according to the corresponding ages.

Steps:

• Type the following formula in Cell F5 and hit Enter.

• As a result, excel will return the result in the below array. All the age values are sorted in ascending order.

🔎 How Does the Formula Work?

• ROW(A1:A10)

Here the ROW function returns the row number in the range A1:A10 which is:

{1;2;3;4;5;6;7;8;9;10}

• SMALL(C5:C14,ROW(A1:A10))

Next, the SMALL function returns the k-th smallest values in the range C5:C14 as:

{22;25;28;30;32;33;35;40;43;45}

• MATCH(SMALL(C5:C14,ROW(A1:A10))

Now, the MATCH function will return:

{3;7;10;2;6;8;1;4;9;5}

• MATCH(F4:H4,B4:D4,0)

Then, the above part of the formula returns:

{1,2,3}

• INDEX(B5:D14,MATCH(SMALL(C5:C14,ROW(A1:A10)),C5:C14,0),MATCH(F4:H4,B4:D4,0))

Lastly, the INDEX function returns the below result:

{Miller}

Read More: How to Undo Sort in Excel

Things to Remember

• We can get the Sort option by right-clicking the cell value too.

• Alternatively, you will find the Sort option by following the path Home > Editing group >  Sort & Filter.

• You can sort data by cell, font, or conditional formatting icon color.

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Conclusion

In the above article, I have tried to discuss several methods to sort data by value in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

Further Readings

• Excel Sort Shortcuts
• How to Sort Alphanumeric Data
• How to Remove Sort in Excel

<< Go Back to Sort in Excel | Learn Excel