How to Use the SORTBY Function in Microsoft Excel
Microsoft Excel offers powerful tools to compile, organize, and visualize your data in just about any way imaginable. Sometimes, you’ll come across a scenario where you need to create multiple specific views of your information.
The SORTBY function in Excel offers a convenient way to create complex breakdowns and craft specialized data arrangements that add clarity to your information, without creating confusion inside your original dataset.
Uses for Excel’s SORTBY Function in Excel
There are multiple ways to sort your data within Excel, though the methods which most readily come to mind can have drawbacks for more complex needs.
Using the SORT functioncreates a separate, organized data set, but you can only sort by one set of criteria.Sorting with Excel’s Sort and Filter tooloffers much finer granularity, but it sorts the original data set directly, which makes it more challenging to create and display multiple views, especially if you want to see them at the same time.
The SORTBY function, in essence, combines the best parts of these two features; it creates a separate view of your data set (or an “array”) that can be sorted by more than one criterion. So, for instance, you could sort a scoreboard of players in a game by team name, then by score, then by specific in-game achievements.
In the example we’ll be using for this article, we have a list of sales data across a region. The columns contain the last names of our sales staff, the state where they work, the number of sales they’ve made, and the total they’ve earned from those sales.
How to Use the SORTBY Function in Excel
To create our first sort with the SORTBY function, let’s organize our salespeople by state.
This function tells Excel to sort our entire range (cellsA2throughD16) by the “State” values in cellsB2throughB16. It defaults to ascending order (A-Z) automatically. The resulting array lists our data organized by state (Arizona first, then California, then New Mexico…).
Note that the list of names is not sorted alphabetically, because that’s not what we asked for it to do. However, it’s easy to change this formula to specify multiple criteria.
Sorting by Multiple Criteria Simultaneously With SORTBY in Excel
Let’s change our new data set to first sort by state, then by the number of sales that were made, creating a miniature ranking of how successful each of our salespeople were in every state.
The resulting new array has a slightly more complicated formula now that we’re asking Excel to sort by multiple fields.
The formula starts the same as the previous formula, in that we’re asking Excel to sort ourarrayin columns A through D by the “State” information in column B, but given we now have multiple ranges we’re looking to sort by, we have to clarify to Excel both the arrays we’re looking to sort by (theby_arraysyntax), and also thesort_order, or the direction that we want Excel to arrange the data in.
Each of theby_arrayarguments can only be one column wide, with the values listed vertically as we have them here (for example, cells B2:B16), or one row long, with the values listed across horizontally (for example, cells A4:D4).
Even though the two columns we’ve just asked this formula to sort, columns B and C, are next to each other, we can’t combine the two columns into a single range request within the formula (as “B2:C16”). Doing so results in a “#REF!” error, as Excel isn’t sure which of those two fields to sort by, or in what order.
Not to mention that it’s entirely possible that the ranges you want to sort are separated from each other (like columns B and D here, for instance), or you’d like to sort your cells by ranges that aren’t in order within your data (such as if you wanted to sort by “State” in column B, then by “Name” in column A, as suggested earlier).
Thus, the two columns have to be listed within the formula as separate ranges, to explicitly tell Excel the order we want to sort the array in—in the case of the example above, first by state, then by number of sales.
Thesort_ordersyntax can be defined in two directions: “1” for ascending order (A-Z order or lowest numbers ordered first) or “-1” for descending order (Z-A order or highest numbers ordered first). Thesort_orderfields cannot be any other numbers besides 1 or -1, or else a “#VALUE!” error will be returned, as there are no other ways to order the data.
Specifyingsort_orderwas only optional in the first version of our formula, because it was immediately clear what we were sorting by, and we were already sorting in ascending order.
However, if we wanted to sort those values in descending order, or whenever we’re sorting by multiple criteria, it’s a required syntax to fill in, because the sort order can be arranged in different directions for different ranges, as we did in this formula.
Also, we can’t ask this formula to partially sort our array—sorting A1:D16 by range B1:B10, for instance. Every range argument within the formula must span the same number of cells—so if our originalarrayvalue has 15 cells, then all theby_arrayarguments we ask the formula to sort by must also contain 15 cells. Otherwise, we’ll get a “#VALUE!” error.
Now that we understand the building blocks of this formula, it’s easy to see how to use SORTBY to organize our data by as many criteria as we want or need. Enter thearrayfirst, then type in theby_arraysort, then thesort_order, then our nextby_array, our nextsort_order, and so on, like in the formula syntax:
SORTBY Is Yet Another Powerful, Versatile Function in Excel
The SORTBY function in Microsoft Excel makes it easy to create specified breakdowns of information, and is a versatile tool to effortlessly organize messy data sets in complex ways to make your information clearer and more readily actionable.
There are even more ways to use Excel’s many functions, tools, and features to organize your spreadsheets and make more efficient and productive use of your time.
There are many ways to organize your Excel spreadsheet in a way that looks more appealing. Here’s how to do it!
The key is not to spook your friends with over-the-top shenanigans.
The best features aren’t the ones being advertised.
Windows is great, but adding this makes it unstoppable.
My iPhone does it all, but I still need my dumb phone.
Don’t let someone else take over your phone number.
