How to Sort Data in Excel Using Formula

Sorting data is a basic task, but relying on Excel’s built-in Sort button makes your reports static. A simple change in your source data means you have to manually sort it all over again. This tutorial shows you how to use formulas to create dynamically sorted lists that update automatically, keeping your original data completely intact.

Why Sort with Formulas instead of the Sort Feature?

While the Sort feature (found on the Data tab) is quick for a one-time task, using formulas offers several massive advantages for ongoing reporting:

• It’s Dynamic: When you change a value in your original dataset, your formula-sorted list automatically updates. No need to re-sort every time.
• It’s Non-Destructive: Formula sorting creates a new, sorted copy of your data, leaving your original source data undisturbed in its original order. This is perfect for preserving the integrity of raw data logs.
• It’s Flexible: You can embed sorting formulas inside other functions to create powerful, multi-step calculations and build sophisticated, interactive dashboards right in an Excel sheet.

Imagine you have a sales leaderboard. With a traditional sort, you would have to manually highlight the data and re-sort it every time a new sale is logged. With a formula, the leaderboard updates itself in real-time.

The Easiest Method: The SORT Function (Microsoft 365 & Excel 2021)

If you're using a modern version of Excel, you have access to dynamic array functions, and the SORT function is a game-changer. It makes creating a sorted list incredibly simple.

The syntax is: =SORT(array, [sort_index], [sort_order], [by_col])

• array: The range of data you want to sort.
• [sort_index]: (Optional) The column number to sort by. By default, it sorts by the first column.
• [sort_order]: (Optional) Use 1 for ascending (A-Z, smallest to largest) or -1 for descending (Z-A, largest to smallest). Default is ascending.
• [by_col]: (Optional) Use FALSE to sort by row (default) or TRUE to sort by column.

Example: Sorting a Sales Leaderboard

Let’s say we want to create a leaderboard from the following unsorted data, which is in the range A2:B11:

1. Simple Sort: Alphabetical by Name

If you just want to sort the reps alphabetically, select a new cell (let's say D2) and enter the simplest version of the formula:

=SORT(A2:B11)

Press Enter, and Excel will automatically "spill" the sorted results into the required number of cells below and to the right. You'll notice a thin blue border around the output - this indicates a dynamic spill range.

2. Sort by Column: By Sales Performance

To create a true leaderboard, we need to sort by the 'Sales Q1' column, which is the 2nd column in our selected array (A2:B11). We'll also sort from highest to lowest sales.

In a new cell, enter this formula:

=SORT(A2:B11, 2, -1)

• A2:B11 is our data.
• 2 tells Excel to sort by the second column (Sales Q1).
• -1 tells Excel to sort in descending order.

Now, your leaderboard is set. If you change a sales number for any rep in the original data, the sorted leaderboard will instantly update itself.

Advanced Sorting: Using the SORTBY Function

What if you need to sort by multiple criteria? For instance, what if you want to sort by department alphabetically, and then by sales within each department? For this, the SORTBY function is perfect.

The syntax is: =SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)

Our goal is to organize this by Department (A-Z) and then by Sales (highest to lowest). We'll use the range A2:C20.

=SORTBY(A2:C20, A2:A20, 1, C2:C20, -1)

Let's break that down:

• A2:C20: This is the full table of data we want the output to show.
• A2:A20, 1: This is our first sorting rule. We're telling Excel to sort using the values in the Department column (A2:A20) in ascending order (1).
• C2:C20, -1: This is our second sorting rule. After applying the first rule, we then sort the results using the values in the Sales Q1 column (C2:C20) in descending order (-1).

The result is a beautifully organized list where all members of a department are grouped together, and within that group, they are ranked by sales.

Legacy Method: Sorting without Dynamic Arrays (Excel 2019 & Older)

If you don't have Microsoft 365, sorting with formulas is much trickier and requires a combination of functions to achieve the same result. This method typically involves creating a "helper" column to establish a unique rank for each row and then using that rank to look up and arrange the data.

This process demonstrates why the SORT and SORTBY functions are such a massive improvement.

Using our sales data in A2:C20, let's sort by sales (highest to lowest).

Step 1: Create a Unique Rank Helper Column

The biggest challenge in older versions of Excel is handling duplicate values (e.g., two reps with the same sales figure). We can create a unique rank by using a combination of RANK.EQ and COUNTIF.

In cell D2, enter this formula and drag it down for all your data:

=RANK.EQ(C2, $C$2:$C$20) + COUNTIF($C$2:C2, C2) - 1

• RANK.EQ(C2, $C$2:$C$20) finds the rank of the sales number in cell C2.
• COUNTIF($C$2:C2, C2) - 1 adds a small tie-breaking value. The expanding range ($C$2:C2) ensures that if a number is repeated, the formula counts how many times it has appeared so far, creating a unique value.

This gives you a column of unique numbers from 1 to 19, representing the rank of each row.

Step 2: Use MATCH and INDEX to fetch the sorted data

Now that you have a unique ranking column, you can look up the rank (1, 2, 3...) and retrieve the corresponding records in the correct order.

In your new sorted table's area (say, starting in F2), enter this formula:

=INDEX($A$2:$C$20, MATCH(ROW(A1), $D$2:$D$20, 0), 1)

• ROW(A1): As you drag this formula down, this acts as a simple counter, producing numbers 1, 2, 3, etc. We use it to look for the 1st highest rank, then the 2nd, and so on.
• MATCH( ... , $D$2:$D$20, 0): This finds where our row number (1, 2, 3...) appears in our helper rank column (D). It returns the row position.
• INDEX($A$2:$C$20, ... , 1): This function fetches the final value. It looks in our entire data range ($A$2:$C$20), goes to the row number provided by the MATCH function, and returns the value from the 1st column.

To get the values for the other columns, you would copy this formula across and change the final column number from 1 to 2 (for Sales Rep) and 3 (for Sales Q1). As you can see, this is a lot of work compared to a single SORT function!

Final Thoughts

Using formulas like SORT and SORTBY moves your Excel reporting from being static and manual to dynamic and automated. It allows you to build powerful dashboards and lists that respond in real-time to changes in your source data, all while keeping that original data safe from accidental changes.

For many teams, manually setting up these dashboards is still a time-consuming step. At Graphed, we automate this entire process for marketing and sales data. Instead of building complex formulas to sort sales leaderboards or campaign performance, you can connect your data sources (like your CRM, ad platforms, or even Google Sheets) and simply ask for what you need in plain English: "Show me my top 10 sales reps by deals closed this quarter" or "Create a dashboard sorting campaigns by ROI." We believe getting clear insights from your data should be a 30-second conversation, not a 30-minute formula-writing session.