Microsoft Excel provides powerful tools for compiling, organizing, and visualizing data in various ways. However, there are scenarios where you may need to create multiple specific views of your information.
The SORTBY function in Excel offers a convenient solution for creating complex breakdowns and crafting specialized data arrangements that enhance clarity without introducing confusion to your original dataset.
Applications of Excel’s SORTBY Function While there are several ways to sort data in Excel, the commonly known methods have limitations when it comes to more intricate requirements.
Using the SORT function generates a separate organized dataset, but it only allows sorting based on a single set of criteria. Sorting with Excel’s Sort and Filter tool offers finer control, but it directly modifies the original dataset, making it challenging to create and display multiple views simultaneously.
The SORTBY function combines the best aspects of these two features. It creates a separate view or “array” of your dataset that can be sorted by multiple criteria. For example, you could sort a scoreboard of players in a game by team name, score, and specific in-game achievements.
In the example we’ll explore in this article, we have a list of sales data across a region. The columns include the last names of our sales staff, the state where they operate, the number of sales they’ve made, and the total earnings from those sales.
How to use SORTBY Function in Excel? SORTBY function in Excel
To make our first type using the SORTBY function let’s group our salespeople according to states.
- Choose Cell 2.
- Inside the cell or in the formula bar, enter the following:
=SORTBY(A2:D16,B2:B16) - Enter. Press Enter.
This function instructs Excel to sort the all array (cells A2 through D16) by the “State” values of the cells B2 through B16. The default is to sort in ascending order (A-Z) automatically. The resulting array shows our data in order by states (Arizona first, followed by California and finally New Mexico …).
Be aware that the names are not alphabetically sorted, as this isn’t what we asked the formula to perform. It’s a breeze to modify this formula to include different requirements.
Sorting Multiple Criteria at the same time By using SORTBY in Excel
We can change our new data set to filter by state and and then by the amount of sales made and create a mini-ranking of how successful each one of our salespeople was in each state.
- Choose the cell 2 again. Alternately, if you’d like to use the formula from the beginning in order to compare the two formulas, choose the cell K2 instead.
- Inside the cell or in the formula bar, enter the following:
=SORTBY(A2:D16,B2:B16,1,C2:C16,-1) - Enter. Enter.
The new array that results has an a bit more complex formula since we’re now needing Excel to sort the array by a variety of fields.
The formula begins exactly the same way as the formula before, except the sense that it asks Excel to arrange the array in columns A to D according to using the “State” details in column B. However, because we have now different ranges that we’re trying to sort through we must clarify the to Excel which arrays are attempting to sort according to (the by_array syntax) as well as what we mean by sort_order or direction we’d like Excel to organize the data.
Every one of these by-array arguments can be one column in size and the values are shown vertically, just as we have them in this case (for instance, cells B2:B16) or a row, with values displayed horizontally (for example, cells A4 and D4).
Although our two columns, which we’ve instructed this formula to separate columns C and B are in close proximity We can’t mix the two columns into one range within this formula (as “B2:C16”). This results in an “#REF!” error because Excel doesn’t know which field to sort through or in which order to sort them in.
In addition, it’s very likely that the ranges you’d like to sort are not connected with one another (like column B and D in this case for example) or you’d like arrange your data based on the ranges that aren’t arranged inside your database (such as to sort your data by “State” within column B and and then in column A, then “Name” in the column A, as was suggested earlier).
Therefore both columns need to be listed in the formula as separate ranges in order to expressly inform Excel the order we wish to sort the array into. In the case of the above example first by state, followed by the number of sales.
Sort_order syntax can be described in two directions: sort_order syntax is defined with two different directions “1” in ascending orders (A-Z order or the lowest numbers that are ordered in order) and “-1” for ascending sorting order (Z-A order or the highest number placed first). Sort_order fields must be in the order of order of the fields can’t be any other numbers than 1 or -1, otherwise the “#VALUE!” failure will occur since there aren’t any other options to arrange the data.
The requirement to specify the sort_order was not necessary in the initial model of the formula since it was clear the criteria we were sorting according to and that we already sorted by ascending order.
If we want to sort the values in ascending order, or when we’re sorting using different criteria, this is a necessary syntax to complete, since the order of sorting can be set in various directions for various areas, like we have done using this method.
Additionally, we cannot ask this formula to only sort our array, such as separating A1:D16 into B1:B10 for example. Each range argument in the formula should be spanning the same amount of cells. So, that if our original array contains 15 cells all of the array arguments we’re asking this formula to arrange by must comprise 15 cell. In the absence of this, we’ll encounter the “#VALUE!” error.
After we’ve understood the fundamentals in this equation, it’s simple to understand how we can make use of SORTBY to arrange your data according to any criteria we’d like or require. Start by entering the array first and then enter the sort by_array, followed by the sort_order, and finally the next by_array, the next sort_order and so on similar to the syntax of the formula:
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],...)
SORTBY is Yet Another Powerful Multi-functional Function in Excel
The SORTBY function of Microsoft Excel makes it easy to design specific breaks of data, and can be a powerful tool to efficiently organize complex data sets in a variety of ways that help make your data easier to understand and actionable.
There are many ways to make use of Excel’s numerous functions tools and features to arrange your spreadsheets and increase efficiency and the productive utilization the time you spend.
