Tutorial: Boost Your Excel Skills with the SORTBY Function

 

Welcome to this tutorial on mastering the SORTBY function in Microsoft Excel. This feature is one of the most efficient ways to organize data dynamically, saving you time and reducing the risk of errors that often come with manual sorting. Whether you are a manager reviewing payroll, an HR professional organizing departmental reports, or someone preparing clean datasets for analysis, SORTBY will help you handle sorting tasks effortlessly.

 

Table of Contents

1. Understanding the SORTBY Function
2. Example # 1 Sorting by Salary in Ascending Order
3. Example # 2 Sorting by Department and Then by Salary
4. Example # 3 Sorting Alphabetically by Name
5. Example # 4 Sorting by Performance Score
6. Example # 5  Filtering and Then Sorting
7. Example # 6 Generating a Ranking Column
8. Key Takeaways and Best Practices
9. Best Practices

 

 

---

Understanding the SORTBY Function

Before working with examples, let’s understand how this function operates. SORTBY creates a sorted version of your dataset based on one or more criteria while leaving the original data untouched.

The basic syntax is:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …)

• array: The dataset to sort (e.g., A2:E11).
• by_array1: The first column used for sorting (e.g., D2:D11 for salary).
• sort_order1: Defines direction — 1 for ascending and -1 for descending.
• Additional criteria can be added with more pairs of by_array and sort_order.

When you enter this formula into an empty cell, SORTBY automatically spills the sorted results into the surrounding cells, creating a reorganized version of your dataset.

In this guide, we will use a sample employee dataset in columns A to E (rows 2 to 11) that includes Employee ID, Name, Department, Salary, and Performance Score. By the end of this tutorial, you will be confident in applying SORTBY for different real-world needs.

---

Example # 1 Sorting by Salary in Ascending Order

Suppose you want to quickly review salaries from the lowest to the highest. This helps in identifying entry-level pay or spotting outliers without manually moving rows around.

• Place your cursor in an empty cell. 
• Enter the formula:
• =SORTBY(A2:E11, D2:D11, 1)
• Press Enter, and Excel will produce a neatly sorted list, starting with the employee earning the least and moving upward.

This is one of the simplest yet most practical uses of SORTBY for salary analysis.

---

Example # 2 Sorting by Department and Then by Salary

Imagine you need to prepare a departmental salary report where employees are grouped by department and, within each group, ranked by their pay from highest to lowest.

• Select a blank area.
• Enter the formula:
• =SORTBY(A2:E11, C2:C11, 1, D2:D11, -1)
• The results will first arrange employees alphabetically by department. Inside each group, salaries will be shown from the highest to the lowest.

This layered sorting is especially useful for HR reporting or departmental comparisons.

---

Example # 3 Sorting Alphabetically by Name

Sometimes the simplest need is to organize employee names alphabetically, such as when creating contact lists or attendance sheets.

• In empty cell, type:
• =SORTBY(A2:E11, B2:B11, 1)
• Press Enter, and the list will be displayed in alphabetical order, from A to Z, while keeping all associated details aligned.

This approach is perfect for preparing quick lookups or clean directories.

---

Example # 4 Sorting by Performance Score

If you are evaluating employees based on performance reviews, it is often helpful to see the highest performers at the top. SORTBY makes this effortless.

• In empty cell , enter:
• =SORTBY(A2:E11, E2:E11, -1)
• The results will display employees ranked from the highest score to the lowest, giving you a clear performance leaderboard at a glance.

This is particularly valuable when making decisions on promotions, rewards, or recognition programs.

---

Example # 5  Filtering and Then Sorting

There are times when you do not want to sort the entire dataset, but only a specific group of employees. For example, you may want to look only at employees from the Sales Department and then rank them by their Performance Score from best to worst. Instead of manually creating a filtered table first, you can combine the FILTER and SORTBY functions in a single formula.

• Choose a blank output cell.
• Enter the formula:
• =SORTBY(FILTER(A2:E11, C2:C11="Sales"), INDEX(FILTER(A2:E11, C2:C11="Sales"),,5), -1)

How this works:

• FILTER(A2:E11, C2:C11="Sales") extracts only the rows where the Department column equals “Sales.”
• INDEX(...,,5) selects the fifth column of that filtered dataset, which is the Performance Score.
• -1 sorts those scores in descending order (highest first).

After pressing Enter, you will see only Sales Department employees listed, ordered from the top performer downwards. The formula updates automatically if new employees are added to the Sales team.

This advanced trick combines the strengths of both FILTER and SORTBY, creating a dynamic report that updates instantly whenever your dataset changes. It is especially useful for managers who oversee specific teams and want quick insights without adjusting the whole table.

---

Example # 6 Generating a Ranking Column

In some cases, you may want not just to sort the data but also to assign ranking numbers alongside employees based on a specific criterion. This can be done by combining SORTBY with the SEQUENCE function. For instance, you may wish to create a ranked list of employees according to their salary from highest to lowest.

• Select an empty cell.
• Enter the formula:
• =HSTACK(SEQUENCE(ROWS(A2:A11)), SORTBY(A2:E11, D2:D11, -1))

How this works:

• SEQUENCE(ROWS(A2:A11)) generates a column of sequential numbers starting at 1 and continuing downwards, equal to the number of employees.
• SORTBY(A2:E11, D2:D11, -1) sorts the dataset by salary in descending order.
• HSTACK combines the ranking column with the sorted employee dataset, producing a table where the first column displays ranks (1 for the highest salary, 2 for the second highest, and so on).

The result is a professional ranking table where employees are clearly numbered in order of their salaries. This is highly useful for executive reports, bonus calculations, or leaderboards.

---

Key Takeaways and Best Practices

Let’s summarize what you’ve learned:

• Sort by one column: =SORTBY(A2:E11, D2:D11, 1) — simple ascending salary list.
• Sort by multiple criteria: =SORTBY(A2:E11, C2:C11, 1, D2:D11, -1) — grouped by department, ranked by salary.
• Sort alphabetically: =SORTBY(A2:E11, B2:B11, 1) — names listed from A to Z.
• Sort by performance ranking: =SORTBY(A2:E11, E2:E11, -1) — scores ordered from best to worst.
• Filter and then sort dynamically: =SORTBY(FILTER(...), INDEX(...), -1) — generate department-specific reports.
• Create ranking tables: =HSTACK(SEQUENCE(...), SORTBY(...)) — produce clear ranked lists for analysis.

Best Practices

• Always keep the original dataset intact and direct SORTBY results into a new area.
• Use Excel Tables so your formulas automatically expand when new rows are added.
• Combine SORTBY with other dynamic functions like FILTER, SEQUENCE, and HSTACK for advanced reporting.
• Practice building these formulas from scratch to strengthen your speed and accuracy.

 

By mastering these variations, you will not only improve your Excel efficiency but also gain the ability to produce professional level reports that update automatically as your data evolves.