Spreadsheets are REALLY good at sorting. In a fraction of a second, they can at take a data set of thousands of entries and re-arrange it however you like. Do you want your field group roster alphabetized? Done. How about your budget lines, sorted from highest to lowest? Easy.
It's so easy, in fact, that we kind of take it for granted. "Duh, spreadsheets sort stuff. I don't need a Spreadsheet Sorcerer Supreme to tell me that."
Or DO YOU?
Because there are actually quite a few ways to accomplish a Sort, including two formulas that I'm willing to bet you haven't used before. Today, we'll cover five different ways to sort data in Sheets. If you want to follow along, make a copy of this example sheet so you can try these techniques as we go.
If you'd rather consume this Tech Tip in video form, you can also follow along with this how-to video. All of the heading links below will link you directly to the relevant section of the video.
Sorting Rules for Ascending and Descending
Before we get started, it is important to know how Sheets treats the data it's about to re-order. I'll make this quick.
You have two options when sorting your data: Ascending (A->Z) and Descending (Z->A). If you choose an Ascending sort, Sheets will order your data like this:
- First, you'll see negative numbers at the top of the list. The lower the number, the higher it appears on the list. -50 will show up above -49.
- Then, you'll get any zero values, followed by all of your numeric values in ascending order: 1,2,3,4, etc.
- Next comes the space character .
- Then come special characters like $, ^, or &.
- Then come your letters, in alphabetical order. Sheets doesn't care about capitalization when sorting names; "aaron" will show up before "Buford".
- Last, you get any empty cells.
So, to recap, an Ascending Sort, instead of being abbreviated (A->Z), should really be abbreviated (-50 -> 0 -> 50 -> -> !@#$%^& -> A -> Z -> Blank cells ). A Descending Sort, as you'd expect, reverses that order with one key exception: Z -> A -> )(*&^%$# -> -> 50 -> 0 -> -50 -> Blank cells. So, Empty Cells are always sorted to the bottom of the list, either way.
When you perform a sort, Sheets will keep all of your Row data together (unless you tell it otherwise.)
Sheet Sort. That's fun to say. Sheet sort. Sheet sort. Sheet sort.
Anyway, the easiest way to sort data in a Google Sheet is to sort the entire spreadsheet according to the data in one of your columns. One nice thing about sorting the entire sheet is that there's no ambiguity about what data is going to be included in the sort. EVERYTHING is included! Let's say you have a list of (fake) donor records:
You want to order these records alphabetically by the name of the donor. Here's how.
1. First, you'll want to make it clear to Sheets that your data has headers in Row 1, otherwise it will treat the data in cell A1 (the word "Name") like any other name and re-order it between Naghini (What? Snakes donate too!) and Nancy. Go to View -> Freeze -> 1 Row. This will make your 1st row constantly visible as you scroll, and make it clear to Sheets that these are the headers for your data.
2. Next, select a cell in the name column. It can be any cell in that column, it doesn't really matter. I'll pick Mary G:
3. Go to the Data menu, then Sort, sheet, then select Sort Sheet by column A, A->Z:
4. Done. All of the names in column A are now in alphabetical order and each donor's corresponding donation has moved to stay with the correct donor.
We've already covered Filters in a previous solution, but they're making a guest appearance here. Applying a filter to your data is actually one of the easiest ways to Sort it. Here are the basics:
1. Select the range of data that you want to sort. The easiest way to do this (at least, if your data is nice and tidy) is to select the cell in the top left-hand corner, then press Ctrl + Shift + down arrow, followed by Ctrl + Shift + right arrow. You can also use your trackpad or mouse to click and drag across your selection.
2. Great, your data is selected! Next up, you'll want to apply filters by opening the Data menu and selecting Create a Filter:
3. Each column header will now have a small filter-y icon next to it. Click on the icon for the column you want to filter, then choose "Sort A->Z" or "Sort Z->A":
Filter-sorting is a great technique to use if you have data that you know you'll be sorting and re-sorting often. By applying a data filter, you'll have easy access to the filter drop-down menu whenever you need to re-order your data.
Of course, you aren't limited in sorting your data by just one column. You can actually re-organize your data based on several factors.
Let's say that I want to split my list of donors into two groups: those with payroll deductions set up, and those that don't. Then, within each subset, I want to sort by how much each person donated in 2015. Bring on the Multi-Level Sorting!
1. As with a Filter Sort, select the range of data that you want to re-organize.
2. Next, open the Data menu hover over Sort range, and select Advanced range sorting options.
3. You'll see a dialog box pop up. If your data has headers (as mine does), you'll want to check the box "Data has header row". I'm going to select "Payroll Deduction" as my first Sort criteria.
4. Now, you can stop here if you want, but we want to add another sort column, so we click that link. Next, we'll sort by 2015 Contributions and, since we want to see the big money at the top, we'll sort Descending (Z->A). Then click the Sort button at the bottom.
5. If we look at the results, we'll see all of the folks without Payroll Deductions grouped at the top of the list and they're all ordered by how much they contributed in 2015:
Keep in mind that the order of your sort columns really matters when doing a Multi-Level Sort. Your second (and third and fourth) sort column will only be used as tie-breakers in the columns that are evaluated first. As a consequence, multi-level sorts work best when you start with with Discrete Data (yeah, we're talking stats now!) like "Yes" or "No" before you get to Continuous Data (like donation amounts).
Sheets also has one last trick it can perform on a sorted range: it can UN-SORT a range. If you have reason to shake up a data set, you can highlight the range, then go to Data -> Randomize Range.
This will keep each row's data together, but it will randomize the order of the rows in your selected range.
OK, so all this sorting has been fun, but what if you want to re-order your data...without re-ordering your data!
The SORT function allows you to generate a separate, sorted copy of your data, without altering your original range. In order to work properly, the SORT function needs three pieces of information, in this order:
- The range of data you want to sort (don't include headers, or they'll get sorted too)
- The column you want to sort by. Sheets wants the number of the column, so if you want to sort by Name (my first column), you should put the number 1 here.
- Whether you want an Ascending or Descending order for your sort. If you want Ascending, you'll put TRUE here; for Descending, use FALSE.
So, my final formula will look something like this. The translation here, in English, is "Take the data ranging from cell A2 to F53, and sort it by Name, in Ascending order)
The formula will show me another parallel version of my data set, but sorted the way I want:
The SORT function can also do multi-level sorts; check out the official documentation for more information.
Sheets also has an advanced function called SORTN that's worth mentioning. It's actually a NEW function, just introduced in 2017. Think of SORTN as being a 'Top 5' generator for a list of data. Like SORT, SORTN will take a big array of data and put it order based on whatever column you specify. SORTN, however, goes one step further by then shortening your list to just the top 5 results (or whatever N number you want; that's why it's called SORTN). Let's say I want to generate a list of the top 5 donors in 2015. Here's the information I need to pass along to SORTN in order get that Top 5 List:
- The range of data I want to examine
- The number of results I want to see. Do I want the top 3, top 5, or top 25? I'll stick with 5.
- The number 0; just trust me on this one, it has to do with how Sheets interprets ties.
- The sort column number. In my case, I want to sort by column 3, which has 2015 Contributions.
- Whether you want Ascending or Descending results. Like with SORT, you use the arguments TRUE or FALSE, respectively.
If I want a Top Five list of 2015 donors in descending order (which puts the highest numbers at the top), I might create a function like this:
And for my results, I get my Top 5 list:
The SORTN function is a bit more complicated than SORT, but I happen to think it's a lot more useful. If you're in a position where you need to share summaries of long lists of data, SORTN might be worth your time to investigate further. You can read into the details at Google's documentation page.