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 sorting data by value.


Download Practice Workbook

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


5 Easy Methods to Sort Data by Value in Excel

1. Sort Excel Data by Value in a Column

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

Sort Excel Data by Value in a Column

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.

Sort Excel Data by Value in a Column

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

Sort Excel Data by Value in a Column

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

Sort Excel Data by Value in a Column

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

Sort Excel Data by Value in a Column

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

Sort Excel Data by Value in a Column

⏩ Note:

You can sort the names of column B in alphabetical descending/ascending order too 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 and Filter Data in Excel (A Complete Guideline)


2. Data Sorting in a Range or Table by Value

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

Data Sorting in a Range or Table by Value

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

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

Data Sorting in a Range or Table by Value

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

Read More: How to Sort Range Using VBA in Excel (6 Examples)


3. Sort Data by Value Using Custom Sort 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. Sort 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.

Sort Data in Multiple Columns

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

Sort Data in Multiple Columns

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

Sort Data in Multiple Columns

  • Finally, we will get the below result.


3.2. Sort 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.

Sort Data Using a Custom List

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.

Sort Data Using a Custom List

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

Sort Data Using a Custom List

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

Sort Data Using a Custom List

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

Sort Data Using a Custom List

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

Read More: How to Create Custom Sort in Excel (Both Creating and Using)


Similar Readings


4. Use 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.
=SORT(B5:C14,2)

Use Excel SORT Function to Order Data by Value

  • 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 Use Sort Function in Excel VBA (8 Suitable Examples)


5. Combine 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.
=INDEX(B5:D14,MATCH(SMALL(C5:C14,ROW(A1:A10)),C5:C14,0),MATCH(F4:H4,B4:D4,0))

Combine INDEX, MATCH & SMALL Functions to Sort by Numerical Value

  • 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 Sort Numbers in Excel (8 Quick Ways)


Things to Remember

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

Things to Remember

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

Things to Remember

  • You can sort data by Cell ColorFont Color, Conditional Formatting Icon etc.


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.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo