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.
Now I will sort the ages of the above people first. Then I will sort their names too.
- To sort the ages of the people, select the age values of column C first.
- Then, to sort in the 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. Age values of the people are sorted in ascending order.
- Similarly, you can organize the above age data in descending order by going Data > Z to A icon.
You can sort the names of column B in alphabetical descending/ascending order too by following the below 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.
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.
Now, I will organize the above range based on the people’s names in ascending order.
- 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 and hence updating the order of the data in the rest of the columns.
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.
- 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.
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.
- 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.
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.
- 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.
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.
- 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?
Here the ROW function returns the row number in the range A1:A10 which is:
Next, the SMALL function returns the k-th smallest values in the range C5:C14 as:
Now, the MATCH function will return:
Then, the above part of the formula returns:
Lastly, the INDEX function returns the below result:
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 Color, Font Color, Conditional Formatting Icon etc.
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.