How to Use the SORT and SORTBY Functions in Excel

The SORT and SORTBY functions allow you to dynamically organize and analyze large datasets in Excel without changing the original data. These functions are extremely useful when you want to quickly identify insights like the highest revenue jobs, the oldest equipment systems, or top-performing technicians. In this lesson, you’ll learn how both functions work and see how they can sort operational data in different ways while keeping the underlying dataset unchanged.

Download the Excel file used in this tutorial:

Step By Step Instructions

The SORT and SORTBY Functions

1. Prepare the dataset for sorting

Start with a dataset where the columns are not currently ordered by any specific field.
Typical columns might include date, work order number, technician, equipment age, revenue, and location.
The goal is to sort the data without changing the underlying dataset, creating a separate dynamic view instead.

2. Copy the dataset headers to a new location

Copy the column headers from the original dataset.
Paste them in a separate area of the worksheet where the sorted results will appear.
This allows the sorted table to stay visually organized while the original dataset remains untouched.

3. Use the SORT function to organize the dataset

Start the formula with the SORT function.
Select the full dataset range as the array to be sorted.
Identify which column should control the sorting by using the sort index argument.
Choose the sort order:
- Ascending for smallest to largest or oldest to newest
- Descending for largest to smallest or newest to oldest

This instantly returns a dynamically sorted version of the dataset in the new location.

4. Change the column used for sorting

The sort index determines which column controls the order.
For example, you can sort by:
- Revenue to see the highest revenue jobs at the top
- Equipment age to see the oldest systems first
- Date to view records chronologically

By adjusting the column index, you can quickly view the same data from different perspectives.

5. Adjust the sort order when needed

Modify the sort order argument to change the direction of the sort.
Switching between ascending and descending lets you analyze:
- Highest vs lowest revenue jobs
- Oldest vs newest equipment
- Earliest vs latest service dates.

6. Format the sorted results

Apply formatting to make the sorted table easier to read.
Typical formatting steps include:
- Setting the date column to a date format
- Formatting revenue as currency
- Adjusting column widths and alignment.

7. Use the SORTBY function to sort using a different column

The SORTBY function allows you to sort one range based on another column.
First select the array you want returned.
Then specify the column that should determine the order.
Finally choose the sort direction.

This is useful when the column controlling the sort is not part of the returned dataset.

8. Sort a subset of columns while referencing another column

With SORTBY, you can return only certain columns (such as customer location or city).
The sorting logic can still be based on a completely different column such as date or revenue.
This allows you to create focused reports that highlight only the fields you want to display.

9. Verify that the underlying data remains unchanged

One of the biggest advantages of these functions is that they do not modify the original dataset.
The formulas simply return a dynamically sorted version of the data.
This allows you to analyze the same dataset multiple ways without disrupting the source table.

10. Use SORT and SORTBY to analyze data from different perspectives

Identify top revenue jobs instantly
Find the oldest equipment systems
Rank technicians or service locations
Organize service records by date

These functions make it easy to build dynamic views of your data while keeping the raw dataset intact.

FAQ

The SORT and SORTBY Functions in Excel

Q1. What does the SORT function do in Excel?
The SORT function rearranges a dataset based on a selected column and sort order (ascending or descending). It allows you to quickly organize data such as revenue, dates, or equipment age to identify patterns and top-performing records.

Q2. What is the difference between SORT and SORTBY?
The SORT function sorts a dataset using the position of a column within the array. The SORTBY function, on the other hand, allows you to sort one dataset based on values from another column or range, giving you more flexibility when analyzing data.

Q3. Why use SORT or SORTBY instead of Excel’s built-in sorting tools?
Using these functions keeps your original dataset unchanged while displaying a sorted version elsewhere in your worksheet. This is especially useful when building dashboards, reports, or dynamic analyses.

Q4. What types of analysis are these functions useful for?
They are commonly used to identify insights such as highest revenue jobs, oldest equipment, most profitable customers, or top-performing technicians. Any dataset that benefits from ranking or ordering can use these functions.

Q5. Can these functions update automatically when new data is added?
Yes. Because they are formula-based, the results automatically update when new data is added to the dataset, making them ideal for dynamic reports and dashboards.

Q6. When should I use SORTBY instead of SORT?
Use SORTBY when you want to sort a dataset based on a column that may not be part of the returned data, or when you need more advanced sorting logic using multiple criteria.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Databoards

Analysis & Development