In this example, we will be looking at four different methods for sorting a table of data in Google Sheets. All of the examples are from this Google Sheet. We will review simple sorting, filter creation, utilizing the SMALL and LARGE functions, and using the SORTN function. These techniques can also be found in this this video on the Prolific Oaktree YouTube channel.
Sorting Data
The first technique is simply to sort the data in a sheet utilizing the menu options, then deleting what information we don’t want. This is a very rudimentary and simple method, but it unfortunately ends with us losing data (that we might need later).
First, begin by selecting the data that you want to sort, then go to “Data” in the top menu, and navigate to “Sort Range.” We specifically want to sort row C.
Click the “Data has a header row” box, and click the dropdown menu to “Run Time.”
Now that the data is sorted, you can manipulate it as you see fit, including deleting or moving the data you do not need. This technique, however, puts the data out of order and makes you lose the rest of the data.
Creating Filters
The second method of manipulating data is to select the data again, go to the Data menu again, and this time, instead of choosing sort, we will create a filter.
Filters can sort but have much more functionality. For this example, we will sort by Throw Distance to get the top three throwers. Click the dropdown at the right side of the header.
This dropdown organizes the data for you. You can sort it in various ways.
Hit Clear and then choose the longest three by scrolling down to the end and putting checkmarks on the last three values.
You can use functions in this menu for additional sorting capabilities. For this example, we will simply use the final three pieces of data. Since there are ties, the sorter decides to show all of these entries as well. The final sorted list is still in its original order.
The advantage of using Filters is that the data still exists and is still able to be interacted with. This means you won’t have to worry about missing data.
The LARGE and SMALL functions
The following two methods are much more powerful ways to manipulate and view your data that draw from the data without directly affecting it. We have created another area of the sheet where we can manipulate the data separately from the original list itself. Here, we will go over the LARGE and SMALL functions within Google Sheets.
What the LARGE function does is return the largest number in a chosen dataset. In this picture, we can see “$D$3:$D$21” which tells the function to look at the data collected from D3 through D21.
From here, we tell the function how to rank the data it finds. We can simply type in “1” or any other number depending on our needs, but in this case, we will refer to “H3,” which is the cell that contains our ‘Rank 1’.
We did this as a cell reference to H3 so I could drag the LARGE function down to do 2, 3, and 4.
Using ‘$’ on the range function in the formula tells the function, “Don’t shift this range down when I drag this formula down.” It’s a fixed reference for a range.
Rank 2 has the same formula, but by dragging it down, it is now looking for the second-largest value, and so on.
The SMALL function is simply the opposite of the LARGE function, and in the example here, it is picking the fastest run time and sorts to the slowest run time.
These functions don’t interact with any other row but also don’t affect the dataset itself.
SORTN Function
Native to Google Sheets and not found in Excel, the SORTN function is a powerful function that you can utilize to maximally sort to your desired preferences.
Start by highlighting all the data in your Sheet. This function will auto-populate all the fields, but you only have to type it once. Every variable is broken up with commas.
The function asks of these options: how many do you want me to return? We’ll put six. In the upper left corner of the screen, we can manually input 6 with another comma.
After 6, it asks what we would like to do with ties. For now, let’s choose 0.
The next variable is the column we are going to look at. This number is the number of columns from the left where the data resides. If we want to sort by throw distance, for example, it is the third column from the left, so we will enter 3.
Then we will select FALSE for “how to sort”. Then we hit Enter.
We have now picked up the longest six throw distances and it picked up names and jersey numbers, while leaving all the original data. Additionally, any changes to the original data will get automatically populated in the new function’s list.
Every situation is different, but now you have four options to consider. Let’s hope you can find the one that’s best for you!
