by Sorting data in Google Sheets doesn't have to be daunting. Especially since spreadsheets provide all the right techniques and tools for the task. And in this guide, I will prove just that.
Today you will learn all aspects of sorting data in Google Sheets. See how to arrange entire sheets, specific ranges, by color, date and multiple columns. Additionally, I’ll show you a couple of special functions and tools.
Whether you're a beginner or an advanced user, I invite you to take a cup of tea, make yourself a copy of the example spreadsheet to follow along, and read on. Don't worry, you won't be here long — the tea just makes everything better 🙂
How to sort whole Google sheet
Sorting a whole Google sheet is a breeze when you know the right techniques. I'll show you the three main ones below.
Way 1. Quick sort by a column
Want to quickly sort your Google sheet by a specific column? It’s super simple:
- Right-click on the letter of the column you want to arrange by.
- Choose either Sort sheet A to Z or Sort sheet Z to A:
All columns on the current sheet will be arranged after the data in this selected column. For example, let's use this to quickly sort the students' list by last name in Google Sheets:
Tip. You will get the same result if you select any cell in the column you'd like to sort by and go to Data > Sort sheet > Sort by column:
This menu will always feature the column with the selected cell.
Both these methods are perfect for quick, one-off sorts when you just need a fast reorganization. It’s especially useful for sorting text alphabetically or numbers in ascending or descending order.
How to sort by date in Google Sheets
This way is also suitable to sort Google Sheets by date. Provided there are columns with dates of course :)
Right-click its column letter and pick Sort sheet A to Z to sort dates from oldest to newest:
Sort sheet Z to A will sort dates from newest to oldest.
Way 2. Sort sheet by multiple columns
Sorting your entire Google sheets by multiple columns involves a few more steps but gives you a more nuanced order. Opt for this method when you need to order data based on primary and secondary criteria.
- Click on that grey cell between column letters and row numbers to select your entire sheet data:
- Since now you have the range selected, you can sort by one column, then another column (and more) in a way I describe below (a section devoted to sorting ranges).
Way 3. Sort from enabled filter
Google Sheets filters aren’t just for narrowing down data. They will help with ordering too!
- Even with the filter enabled, to sort all data on your sheet, you will still have to select the whole sheet first. Otherwise, the filter will just detect your used range (data before the first empty row and column).
So click that grey cell between column letters and row numbers to do that:
- Then enable the filter by clicking its icon on the spreadsheet toolbar:
- You'll see filter icons appearing next to each column header. Hit the one you're interested in and select your order preference, e.g. Sort A to Z:
All columns from your sheet will be organized by this one column.
Tip. If you collaborate on a spreadsheet with other users, you can enable sorting just for yourself. This will let you change the order of the data without interfering with other users' views. Filter views available in Google Sheets will help with this.
This is also great for quick adjustments because it makes switching between different views of your data quick and easy.
How to sort ranges in Google Sheets
Would you agree that most of the time, you only need to organize specific data ranges rather than entire sheets? 🙂 Whether it's the whole table or a certain part of the table, here's how you do it.
Way 1. Google Sheets menu to sort range by one column
- Select the range.
Tip. I advise against using Ctrl+A (for Mac its Cmd+A) shortcut in this case because this shortcut also selects your column headers. It means they will be reorganized along with other data. Opt for Way 2 below to learn how to make spreadsheets ignore your column headers when reorganazing rows of data.
- Go to Data > Sort range, and pick A to Z or Z to A:
I sorted my range by last name A to Z using this method, and my table headers also moved down:
To avoid that, it's better to select the cell range manually rather than using key combos. You see, Ctrl+A (Cmd+A for Mac) always selects the header row as well. Other shortcuts like Ctrl+Shift+right or Ctrl+Shift+down make sense only if you're okay to arrange data by the leftmost or rightmost column.
As a pretty good alternative, you can try the next method to exclude column headers from the work.
Way 2. Convert range to table — new way to sort by column
Google Sheets has just introduced a table feature: converting your selected range into a neatly formatted table. And as a whole table, it can be organized with a special corresponding option. As a bonus, your header row will remain at the top.
- To start, select any cell within your data and turn everything into a table through the menu Format > Convert to table:
- After turning your data into a table, simply click the arrow next to a column name to sort by (Dates, for instance). Then pick Sort column > Sort A to Z or Sort Z to A:
- All data in this column will be alphabetized (or sorted by date in my case). All data in other columns will be organized accordingly:
Way 3. How to sort range by multiple columns in Google Sheets
The next method solves two problems. You can sort by multiple columns simultaneously and keep all headers fixed at the top so they don't move down.
- Select your range.
Tip. To quickly select the whole used range, click any cell in your table and hit Ctrl+A (for Mac it's Cmd+A). This will automatically select all data till the first empty row & column.
- Go to Data > Sort range > Advanced range sorting options:
- You will see a new pop-up window with these advanced options:
Don't worry, advanced doesn't mean complicated here. It's about custom sort in Google Sheets. You will tweak these additional settings to gain more control over how your data is organized.
For instance, if the first row is your table header, tick the box Data has header row so Google Sheets doesn't move it with the rest of your data.
Also, here you can sort by multiple columns. Once you select the primary column in this window (Last Name for me), click Add another sort column. Pick additional columns, set up their order and click Sort:
In my example, I sort the table alphabetically first by Last Name then by Grade:
It means that the whole table is first arranged from A to Z by last names. Then all grades belonging to each last name are arranged from A to Z.
Way 4. Arranging selected data from filters
Enabling filters is also a way to sort ranges in Google Sheets. Only this time filter icons appear above the data set you select — wherever it is.
- Select your desired range. This time, I will show you how it works in the middle of your data.
- Hit the filter icon on the spreadsheet toolbar to enable filters for this selection.
- Click the filter icon on the column you want to arrange the data by:
What are the pros of such Google Sheets sorting? It lets you focus on a specific portion of your table without disturbing the rest of your data.
Any cons? Alas, cells with filter icons will be treated as headers and won't change their position.
How to sort by color in Google Sheets
Sorting Google Sheets by color is a fantastic way to organize data visually. Want to take the most important pieces to the top? Categorizing records? Here are 2 ways to do it.
Way 1. Standard Google Sheets tool to sort by color — one color at the top
Well, it's two tools, actually, but they're super similar. Pick one depending on the overall looks of your table.
1. I haven't formatted my range — Convert range to table
If you haven't formatted your range yet (haven't picked fonts, sizes, etc.), start by transforming it into a table. Just click any cell within your data and go to Format > Convert to table:
Two clicks and your range instantly turns into a neatly formatted table view:
Why bother? Because you can handle this table just like a filter! For instance, to sort by color your Google Sheets table 🙂
So click that down-arrow next to a column with colored cells and select Sort column > Fill (or Text) color:
All rows with the selected color will move to the top while all other rows will retain their original order:
Tip. To organize all rows by all colors, get to know the Sort by Color tool I describe below.
2. My table is formatted and beautiful already
If you style your data your own way, you can sort it by color from the filter settings.
Tip. This article will teach you how to create good-looking tables and save your styles for later use.
- Enable the filter for your data by clicking its icon on the spreadsheet toolbar:
- In the header of the color-coded column, click Filter > Sort by color > Fill color or Text color. Then select the color you want to order by:
All rows will the selected color will move to the top. Rows with other colors won't be handled at all — they will remain below in their original order:
To organize all colors rather than focus on one color (otherwise you could go along with a filter, right?), this next tool is for you.
Way 2. Sort by Color from Power Tools — new color order
If you know our Power Tools collection for Google Sheets, you know it offers an entirely different Sort by Color tool.
Unlike the standard Google Sheets feature, this add-on sorts all rows by all colors (by their order in the color palette):
Here are the steps to sort colors in Google Sheets this way:
- Install Power Tools for free from Google Workspace Marketplace.
- Open it from Extensions > Power Tools > Start > Process:
- When the Process group opens in the right sidebar, find and click Sort by Color:
- You'll be able to specify the color-coded column, pick font colors or fill colors, and choose the desired order:
And you'll get all colors arranged in this particular order:
Tip. Feel free to visit the tutorial for the Sort by Color tool to see how each option contributes to a more precise result.
Functions to organize your data in Google Sheets
When working with large data sets in spreadsheets, functions can offer more flexibility and automation compared to manual sorting. And I hope you won't be surprised if I tell you there are three functions aboard for the job.
Google Sheets SORT function
The first function that comes to mind is SORT. Yes, it will create a copy of your original table. But it will organize it dynamically by multiple columns even as new entries are added to the source table.
And it's super-easy to use:
- enter the range of cells you want to sort.
- for the sort_column you specify the number (1, 7, etc.) or a column (D2:D46) to arrange the data by. It can be a column within the range or outside it, it doesn't matter. What's important is that it must have the same number of rows as your range.
- is_ascending determines the sort order. Use TRUE for ascending and FALSE for descending.
- [sort_column2, is_ascending2, ...] are additional (optional) columns and sort orders that you can nest in order to organize Google Sheets by multiple columns.
Now let's see this function in action. I will arrange my exam results first by Exam Date (in ascending order), then by Grade (in descending order):
- Create a new sheet to place the result there.
- Since the function handles all data from the supplied range, I suggest copy-pasting the header row from the original table manually.
Tip. Lock the first row right away (View > Freeze > 1 row) for future convenience.
- In A2 on this new sheet, enter the formula:
=SORT(Sheet1!A2:G46,1,TRUE,5,FALSE)
The formula returns a copy of my original data from Sheet1, only it's first sorted by date in ascending order (from earlier to later), then grades for each date are arranged from the worst to the best.
Pros and Cons of Google Sheets SORT function
Using Google Sheets SORT function has its pros and cons.
Pros:
- automatically updates the sort order as data changes.
- which means no need for manual rearrangements.
- you can sort by multiple columns in different order.
Cons:
- requires a place for a copy of your original data.
- the range specified in the formula is static and may require updating if new rows are being added.
Tip. To foresee that, just specify a bigger cell range from the beginning, e.g. A2:G1000 rather than A2:G46.
- if you're a newbie, you will need to get accustomed to functions and formula syntax.
- may be less intuitive compared to all those drop down menus and other options I described earlier.
Google Sheets SORTN function
If Google Sheets SORT function gives you a robust way to organize your entire data set, Google Sheets SORTN function takes it a step further. It lets you sort and return only a specified number of rows from your data.
This can be particularly useful when you need to extract and analyze the top or bottom entries in your data set, such as the highest grades or the earliest exam dates.
- range is the whole table that you want to organize.
- [n] is that optional argument that lets you limit the number of rows to return. If omitted, SORTN will return one row.
- sometimes you will encounter rows with identical values in the columns you're sorting by. These are called ties. This third optional argument — [display_ties_mode] — lets you control how these ties are handled:
- 0 (also a default value if you omit this argument) will simply return the exact number of rows that you specify in the previous argument.
- 1 will display a maximum of [n] rows plus all extra rows that are identical to the nth row.
- 2 will display a maximum of [n] rows, excluding ties. For example, if you only want the top 5 unique grades and don't want any tied rows to be included beyond this limit.
- 3 will display a maximum of [n] rows, including ties. Use it to display up to [n] rows and want to include all tied rows (duplicates).
- [sort_column1, is_ascending1] are optional and repeatable. Here you are to specify the index of the column to sort by and the order. This lets you sort Google Sheets by multiple columns.
Let me show you the SORTN function in action. I will use the same table with exam results to find the top 5 students with the highest total scores, organized by totals in descending order.
- Create a new sheet to place the result there.
- Since the function handles all data from the supplied cell range, I suggest copy-pasting the header row from the original table manually:
Tip. Lock the first row right away (View > Freeze > 1 row) for future convenience.
- In A2 on this new sheet, enter the formula:
=SORTN(Sheet1!A2:G46,5, 0, 7, FALSE)
This formula looks at my table on Sheet1 (Sheet1!A2:G46), sorts it by the 7th column in descending order (FALSE), and returns 5 rows exactly displaying a single row in case of ties (0).
Pros and Cons of Google Sheets SORTN function
This function is no exception to both pros and cons.
Pros:
- quickly extracts the top or bottom entries from a data set.
- automatically updates as your data changes.
- supports multiple sorting criteria and custom tie-breaking rules.
Cons:
- its syntax seems more complicated compared to SORT function.
- managing ties can be less intuitive, especially with complex data sets.
- the output range is fixed which may require adjustments if your data range changes significantly.
QUERY function for Google Sheets: another way to auto-sort
Google Sheets QUERY is also worth exploring when it comes to auto-sorting data in Google Sheets. It's a powerful function used to organize data using SQL-like queries.
However, since we've already covered the QUERY function in detail, including its sorting capabilities, I won't duplicate the info here.
Please check out that article to learn how to use Google Sheets QUERY to arrange your data (formula examples are included).
And that, kids, is how you sort data in Google Sheets :) Whether by sheet, range, color, date, or multiple columns, spreadsheets provide a variety of tools and functions. Try them all and find the ones that best fit your needs.
If you have any questions or tips of your own, feel free to share them in the comments below!
Practice spreadsheet
How to sort in Google Sheets (make yourself a copy to practice)
