Sabrina Ayon
While working with a dataset, sometimes it’s important to sort the data list. Actually, sorting the data helps to arrange the list in a meaningful order. In Excel, we can do this as our preferred order easily. In this article, I will demonstrate some quick and easy ways to sort data by two columns in Excel.
Download Practice Workbook
You can download the workbook and practice with them.
5 Easy Ways to Sort Data by Two Columns in Excel
While working with excel, sorting data is a great practice to visualize the data in a form that makes the data easier to understand. To sort by two columns, the following dataset is used to perform the methods. The dataset represents a list of the car shops. The subsequent dataset contains only three branded cars “Hyundai, Nissan, and Suzuki” which are shown in column C, the models of the cars in column B, and the price of each card in column D, also in column E, the delivery date is listed.
Now, suppose we need to sort the data by only the Product Model and Price of each car. In the following sections of the article, I will sort the data by these two columns in an explanative way.
1. Sort Data by Two Columns from Editing Group
In excel, to sort by two columns promptly, the Sort & Filter command is the finest compound. With this, we can sort in different schemes. So, as we are going to sort the two-column Product Model and Price, the sorting will be different in each column. Product Model will sort by A to Z and Price will be sorted in Largest to Smallest order. Let’s see the steps below.
STEPS:
- First, select anywhere in the dataset, I will recommend selecting any cell from the 1st column that will be sorted as preferable order.
- Second, go to the Home tab on the ribbon.
- Next, from the Editing group, select the Sort & Filter command. Then, choose Custom Sort from the drop-down menu.
- This will appear in the Sort dialog box.
- Now, select Product Model from the sort by drop-down menu under the Column section.
- And, the order is automatically set as A to Z which is under the Order section.
- After that, click on Add Level to add the second column that we wish to sort next.
- Further, select the Price column. And, we want the order from Largest to Smallest.
- Then, click on the OK button.
- Finally, like the picture below we can see that we have the two sorted data lists in our preferable columns.
Read More: How to Use Excel Shortcut to Sort Data (7 Easy Ways)
2. Applying SORTBY Function to Sort Values by Two Columns
To sort the data by two columns only, we can easily do this by applying SORTBY Function. First, let’s get the idea of the SORTBY function.
➤ Syntax
The syntax of the SORTBY Function is:
SORTBY(array, by_array1, [sort_order1], …)
➤ Arguments
array: Array or a range to sort.
by_array: Array or a range to sort by.
sort_order: [optional] if the Sort order is 1 it means ascending by default, and if the sort order is -1 it means descending.
The SORTBY Function mainly sorts the contents of a range or array using a formula and values from another range or array.
So, let’s see how to use this function to sort by two columns.
STEPS:
- Firstly, we need another set of the same dataset. So, we add another table under the first one to apply the SORTBY Function based on the above data table.
- Secondly, select the output Cell B14 and type the formula below.
=SORTBY(B5:E11,B5:B11,1,D5:D11,-1)- Then, press Enter.
Formula Description
First, we take the whole data range B5:E11 which is our array, as we want to sort by two columns from this first range of the table. Then, we sort our first column in ascending order so we take the range B5:B11 which is our Product Model column. After that, we take the second column which we are going to sort in descending order. So we take the range D5:D11. And we will be able to see the sorted column in the second table.
Problem while Using Function
Sometimes, there is a problem with the dates in excel. By using the function it may show the dates in a general format as shown in the picture below. And we can easily solve the problem by changing the format of the date column.
- For this, select the date column first. Then, just go to the Home tab on the ribbon and from the drop-down menu under the Number section, select Short Date.
The problem will be solved by doing the above task.
Related Content: How to Use Sort Function in Excel VBA (8 Suitable Examples)
3. Sort Data by Two Columns with Excel Sort Command
Data sorting is an arrangement of the set of data in an organized and understandable way. To sort by two columns Sort command is the easiest solution. Let’s go through the steps of sorting the data by two columns with the Sort command.
STEPS:
- In the beginning, select the cell from the first column that we wish to sort.
- Next, go to the Data tab from the ribbon and click on Sort.
- The Sort dialog box will display.
- Now, under the Column section, select Product Model from the sort by drop-down menu.
- And, under the Order section, the order is automatically set to A to Z.
- After that, click on Add Level to add the second column we want to sort.
- Select the Price column as well. We also need the order to be Largest to Smallest.
- Then, press the OK button.
- Finally, the data will be sorted into two columns.
Read More: How to Sort Multiple Columns with Excel VBA (3 Methods)
Similar Readings
- VBA to Sort Table in Excel (4 Methods)
- How to Sort IP Address in Excel (6 Methods)
- VBA to Sort Column in Excel (4 Methods)
- How to Add Sort Button in Excel (7 Methods)
- Sort by Color in Excel (4 Criteria)
4. Using Filter Options to the Table Headers in Excel
We can sort the columns in the easiest way by using the Filter on the table headers. So, let’s demonstrate the steps below.
STEPS:
- First, select the headers of the tables which we want to sort.
- Next, go to the Home tab.
- Select the Sort & Filter command from the Editing group. Then, from the drop-down option, select Filter.
- Now, click on the Product Model drop-down. And, select the order Sort A to Z.
- Then, select a Price from the drop-down menu. And, select the order Sort Largest to Smallest as well.
- Finally, we can view the sorted result in our desired column.
Read More: How to Use Sort and Filter with Excel Table (4 Suitable Ways)
5. Utilize Conditional Formatting to Sort Values by Two Columns
If we wish to sort columns by giving the data colors, we can use conditional formatting to make the sorted values more visually appealing. Assume that, we want to sort column Price and Delivery Date. For this, let’s follow the steps below.
STEPS:
- Likewise the earlier methods, first select column C which indicates the price of the cars.
- After that, go to the Home tab on the ribbon.
- Next, go to Conditional Formatting under the Styles section.
- Further, go to Color Scales from the drop-down menu.
- Then, select the Green color.
The green color indicates where each cell value falls within that range.
- Also, select the Delivery Date as before and apply the other color gradient of cells.
- And, finally, we can see that our two desired sorted columns are sort of showing the color gradient.
Sorting by color scales is dependent on the numerical data supplied.
Read More: How to Sort Column by Value in Excel (7 Easy Ways)
Conclusion
The above methods assist you to sort data by two columns in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
