Indzara

How to Sort Data by Multiple Columns in Excel?

In this post, let us look at how better we can visually present a data table with multiple columns: by sorting them.

Sorting your data based on multiple columns is useful in a lot of real-life scenarios like:

Working with sales data, you should sort by date and then by sales amount to see the progress of sales over time and much more!

Consider, a sample data of customers with date of payment and amount,

Sort Data by Multiple Columns sample data

We shall look at how to sort this sample data by customer and then by date and amount.

If you’ve been following our posts, one thing we always recommend is to convert your sample data into a table to ensure data readability and scalability when data expands.

To do this, select all data, press CTRL+T, and assign a name (say DATA).

Sort Data by Multiple Columns select data

Before we begin sorting our data with formulas, Excel has built-in SORT available. Go to the Data tab and click on Sort.  

Sort Data by Multiple Columns sort option

A pop-up window will appear and you can choose the column(s) and the order to sort.

Sort Data by Multiple Columns choose columns

However, this is not dynamic. If you add more data to your existing table of data, you might want to do this Sort all over again.

Let us see how to use Excel formulas to sort data with multiple columns. To do this we will use the SORTBY function.

This function sorts or arranges data from a given range/array (1st argument) based on another range/array (2nd argument). You can also specify the sort order(3rd optional argument), that is either ascending (1) or descending (-1). You can add additional columns to sort by as the next arguments (optional).

To get a clearer picture, the syntax of SORTBY function is,

Sort Data by Multiple Columns SORTBY function

So, for our sample data, we shall sort the entire DATA table, first based on the CUSTOMER column in ascending order and, secondly by the DATE column in ascending order (chronological).

Our SORTBY for the sample data would be:

And our sorted data would be

Sort Data by Multiple Columns sorted dat

Apply conditional formatting

Since we are talking about data presentation in our article, let us highlight our sorted data, and to do this, we shall use conditional formatting.

There are numerous ways to highlight a dataset, in this case, we shall highlight every row that has a new customer.

To achieve this, select all the newly sorted data, click on conditional formatting, and select a new rule:

Sort Data by Multiple Columns new rule

Since our data starts from cell G7, our formula should check the previous cell’s entry and if they are not the same, highlight it since it’s a new customer. The formula would be

We’ll choose to format the cells in green.

So, the whole conditional formatting rule would be:

Sort Data by Multiple Columns edit conditional formatting rule

Once we apply this, our new sorted data will look like this:

Sort Data by Multiple Columns new sorted data

We have a detailed explainer video on sorting multiple columns in Excel:

Leave a Reply

Your email address will not be published. Required fields are marked *