Master Excel Sorting with the Powerful SORTBY Function

Introduction

Mastering sorting in Excel is a key skill for managing data efficiently. While traditional sorting methods work well, the SORTBY function, a dynamic array function, offers advanced sorting capabilities that can make complex tasks easier. This article walks you through how to use the SORTBY function to sort data by various criteria, such as Rotten Tomatoes scores and box office earnings, with tips for setting up tiebreakers and managing the output.

Getting Started with the SORTBY Function

In this guide, we will focus on sorting movie data in Excel, specifically by Rotten Tomatoes scores and box office earnings. To begin, locate the column that contains the data you want to sort. For this example, we’ll start by sorting movie titles by their Rotten Tomatoes review scores.

Start by selecting a blank cell where you want the sorted list to appear. In this example, we use cell G3. To use the SORTBY function, type =SORTBY followed by a parenthesis. The first argument Excel asks for is the range of data you want to sort. In this case, we’ll select the movie titles and their content ratings. Once the range is selected, add a comma to move on to the next argument, which is the criterion for sorting.

For this example, we’ll sort based on Rotten Tomatoes scores. Select the range that contains these scores, add a closing parenthesis, and hit Enter. The movies will now be sorted by their Rotten Tomatoes scores, from lowest to highest.

Customizing the Sort Order

After completing the basic sorting, you may want to customize the order in which the data is sorted. By default, the SORTBY function sorts in ascending order, meaning from the lowest score to the highest. You can change this by editing the formula.

To sort the data in descending order (highest score first), select the cell containing your formula. Click in the formula bar and add a comma after the range of scores. Then, either type -1 or select the descending option from the dropdown. Press Enter, and the movies will now be listed from the highest Rotten Tomatoes score to the lowest.

This simple adjustment allows you to quickly change how the data is presented, giving you flexibility depending on your needs.

Adding a Tiebreaker for More Complex Sorting

One powerful feature of the SORTBY function is its ability to handle multiple sorting criteria. This can be useful when you encounter ties, where two or more rows have the same value for your primary sorting criterion.

For example, if two movies have the same Rotten Tomatoes score, you can add a second sorting criterion to break the tie. Let’s say you want to sort by Rotten Tomatoes scores first and by box office earnings second. In your formula, after setting the initial sorting criterion, add another comma and then select the range for the second criterion, in this case, box office earnings. You can also specify whether this second criterion should be sorted in ascending or descending order.

With this method, the SORTBY function becomes even more powerful, allowing for multi-layered sorting that can handle complex datasets.

Sorting by Box Office Earnings

Now that we’ve covered sorting by Rotten Tomatoes scores, let’s move on to sorting by box office earnings. For this example, go to cell J3 and type =SORTBY, just like before. Select the movie titles as the first range, followed by the box office earnings as the second range.

After you’ve selected the range for earnings, add another comma and specify descending order by typing -1. Press Enter, and the movies will now be sorted by their box office earnings, from the highest to the lowest.

This method is great for quickly identifying top-earning movies and comparing them against other factors such as ratings or release dates.

Understanding Spill Data

One important aspect of the SORTBY function is how it generates what is known as “spill data.” Spill data refers to the way Excel outputs the sorted results into multiple cells, even though the formula is only entered into one cell.

For example, although it may look like the data is physically present in each cell of the sorted list, the entire output is dynamically generated from the cell containing the SORTBY formula. If you try to manually edit or delete individual cells in the sorted list, Excel won’t let you because those cells are linked to the formula.

This spill behavior ensures that the sorting remains dynamic and updates automatically if the underlying data changes, making it a powerful tool for handling large datasets.

Conclusion

The SORTBY function in Excel offers a sophisticated and flexible way to sort data based on multiple criteria. Whether you’re sorting by Rotten Tomatoes scores, box office earnings, or any other data points, this dynamic array function simplifies the process while providing powerful features like customizable sort order and tiebreakers. Understanding spill data also ensures that you can effectively manage and edit your sorted lists.

By mastering the SORTBY function, you can unlock new levels of efficiency in managing and analyzing your data.