How to Use the Excel SORTBY Function to Organize Data Using Hidden Columns

The Problem with Messy Source Data

A common scenario in data management involves receiving a master spreadsheet that is organized logically, but not in the way you need to present it.

Imagine you have a sales report with three columns: “Employee Name”, “Region”, and “Total Sales”. You want to present a clean, dynamic list showing only the “Employee Name” and “Total Sales”, but you want that list sorted alphabetically by the “Region”.

Historically, to achieve this, you had to either manually sort the entire table and then hide the “Region” column (which breaks if the data updates), or use a complex array of index/match formulas.

With Excel’s Dynamic Array functions, you can solve this elegantly using SORTBY. This function allows you to sort a range of data based on the values in a completely different range, without actually displaying that sorting criteria in your final output.

Step 1: Understanding SORT vs. SORTBY

Before using SORTBY, it is vital to understand why the standard SORT function fails in this scenario.

The SORT function requires the column you are sorting by to physically exist inside the data you are outputting. If you use =SORT(A2:C10, 2), Excel outputs all three columns (A, B, and C) and sorts the result based on the 2nd column in that array.

If you only want to output columns A and C, you cannot use the basic SORT function to sort by column B, because column B is no longer part of the output array.

SORTBY detaches the output data from the sorting criteria.

Step 2: The Basic Syntax

The syntax for the function is:
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

• array: The data you actually want to display on the screen.
• by_array1: The hidden column containing the data you want to sort by. (Crucial: This array must be the exact same height as the first array).
• sort_order: Type 1 for Ascending (A-Z) or -1 for Descending (Z-A).

Step 3: Executing the Hidden Sort

Let’s use our previous example. Your master data is in columns A, B, and C (rows 2 through 20).

• Column A = Employee Name
• Column B = Region (e.g., North, South, East, West)
• Column C = Total Sales

You want to create a new dashboard in column E that shows only the Names and the Sales, but grouped/sorted alphabetically by their Region.

First, we need to extract only columns A and C. We can do this by wrapping SORTBY around a CHOOSECOLS function, or more simply, by selecting two non-contiguous ranges if we just want a single column output. Let’s assume we just want the Names sorted by Region.

Click on cell E2 and type:

=SORTBY(A2:A20, B2:B20, 1)

How it works:

1. Excel looks at the Names in A2:A20 (the data to output).
2. It then looks at the Regions in B2:B20 (the hidden sorting criteria).
3. It alphabetizes the Regions (because of the 1).
4. It outputs only the Names, but rearranged so that all the “East” employees appear first, followed by “North”, etc.

The “Region” data is used purely as the engine for the sort, but it remains entirely hidden from the final visual output.

Step 4: Sorting by Multiple Hidden Columns

You can stack multiple sorting criteria to break ties.

Suppose you want to sort the list of Employee Names primarily by Region (alphabetically), but if two employees are in the same region, you want the one with the highest Total Sales to appear first.

You simply extend the formula by adding a second by_array and sort_order.

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

The Logic:

• Sort the Names (A) first by Region (B) in Ascending order (1).
• Then, sort by Sales (C) in Descending order (-1).

The result is a perfectly prioritized list of names, dynamically generated and sorted by two entirely invisible columns of data.