How to Sort Data by Two Columns in Excel (5 Easy Ways)

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.

5 Easy Ways to Sort Data by Two Columns in Excel


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.

Sort Data by Two Columns from Editing Group

  • Further, select the Price column. And, we want the order from Largest to Smallest.
  • Then, click on the OK button.

Sort Data by Two Columns from Editing Group

  • Finally, like the picture below we can see that we have the two sorted data lists in our preferable columns.

Sort Data by Two Columns from Editing Group

Read More: How to Sort and Filter Data in Excel (A Complete Guideline)


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.

Applying SORTBY Function to Sort Values by Two Columns

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.

Applying SORTBY Function to Sort Values by Two Columns

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

Applying SORTBY Function to Sort Values by Two Columns

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.

Sort Data by Two Columns with Excel Sort Command

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

Sort Data by Two Columns from Editing Group

  • Select the Price column as well. We also need the order to be Largest to Smallest.
  • Then, press the OK button.

Sort Data by Two Columns from Editing Group

  • Finally, the data will be sorted into two columns.

Sort Data by Two Columns with Excel Sort Command

Read More: How to Use Excel Shortcut to Sort Data (7 Easy Ways)


Similar Readings:


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.

Using Filter Options to the Table Headers in Excel

  • Then, select a Price from the drop-down menu. And, select the order Sort Largest to Smallest as well.

Using Filter Options to the Table Headers in Excel

  • Finally, we can view the sorted result in our desired column.

Using Filter Options to the Table Headers in Excel

Read More: How to Auto Sort Table in Excel (5 Methods)


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.

Utilize Conditional Formatting to Sort Values by Two Columns

Sorting by color scales is dependent on the numerical data supplied.

Related Content: How to Create Custom Sort List in Excel


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!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo