14 Ways to Sort Data in Google Sheets

Last Updated on January 2, 2024 by Jake Sheridan

In this guide, you will learn all about SORT in Google Sheets to sort alphabetically, by numbers, names, color and more.

Are you making the most of Google Sheets’ sorting to manage your data?

In this this guide you will different methods to sort data.

Each way is designed to help you organize information more effectively and save valuable time.

1. How to sort all data in Google Sheets by alphabet

Google Sheets allows you to sort your data in a variety of ways.

One of the most common methods of sorting data is selecting a certain column and sorting the dataset alphabetically by that column.

For example, you may have a dataset of employee records that you want to sort. With Google Sheets, we can add a filter to the range and sort the records alphabetically using the Last Name column.

To start sorting all data in Google Sheets alphabetically, just follow these steps:

Sorting an entire sheet:

Step 1

Open your Google Sheets document. If your document has multiple sheet tabs, choose the sheet containing the data you want to sort.

In our sample spreadsheet above, we have a dataset of business contacts containing information such as the contact’s business email, contact numbers, and their employer.

Step 2

Click on the column header of the column you want to sort the entire sheet by.

In this example, we’ve selected column B since we want to sort our data by last name.

Step 3

Click on Data and hover over the Sort sheet option. You can choose between sorting the range by the selected column in alphabetical or reverse alphabetical order.

For this example, let’s sort our sheet in alphabetical order by clicking Sort sheet by column B (A to Z).

Step 4

The entire sheet should now be sorted alphabetically according to the selected column.

Do note that sorting the entire sheet will also try to sort any headers as well as any data found outside the range containing your data.

If you want to keep headers on top, we recommend freezing them first.

Sorting a range:

If you only want to sort a specific part of your sheet, you can opt to sort a range instead of sorting the entire sheet.

Step 1

Open your Google Sheets document. Choose the sheet containing the data you want to sort.

Step 2

Highlight the range of cells you want to sort.

If you want to sort the entire sheet, click the top-left corner between the first row and the first column to select everything.

You can also select the entire table by selecting any cell in the table and using the keyboard shortcut Ctrl+A (Windows) or Cmd+A (Mac).

In this example, we’ve selected the range A1:F101 which contains all our business contact information.

Step 3

Click on Data in the menu bar. From the dropdown, select Sort range.

If the sort column does not appear as the first choice, click on Advanced range sorting options.

You should now see a Sort range dialog box containing options for how you want to sort your range.

If your data includes headers, make sure to check the box that says “Data has header row.” If not, leave this unchecked.

Step 4

Choose the column you want to sort by from the dropdown menu.

For alphabetical sorting, select the column with text data.

In this case, we’ll select Column B which contains the last names of our contacts.

Step 5

Select the sort order. Choose “A → Z” for ascending (alphabetical) order or “Z → A” for descending order.

Step 6

Click “Sort” to apply the changes.

Your data will now be sorted alphabetically based on the selected column.

You may also sort the range in reverse alphabetical order.

2. Sort sheet by a column in Google Sheets

Here’s all the steps you need to know to sort a sheet by a certain column in Google Sheets.

Step 1

Navigate to the Google Sheets document containing the sheet you want to sort.

Select the column to sort.

Click on the letter at the top of the column by which you want to sort your data. This should highlight the entire column.

In our example above, we want to sort a sheet by the first column which contains a list of color names.

Do note that the column must contain text data if you want to sort the text alphabetically.

Step 2

Right-click on the selected column to access the context menu.

You can either sort the sheet from A to Z (alphabetical) or Z to A (reverse alphabetical).

For this example, we’ll sort the sheet from A to Z.

Step 3

The selected sheet should now have its rows arranged alphabetically according to the selected column.

3. How to sort data in Google Sheets with a header row

Follow this guide if you need to sort data in a Google Sheets document while keeping the header row fixed.

Step 1

Open your Google Sheets document containing the range you want to sort.

Step 2

Select the header row of your dataset. This is typically the first row of your spreadsheet.

In the View menu, click on Freeze > 1 row to freeze the first row of your sheet.

Step 3

Once the header row is frozen, you can proceed to sort the entire sheet.

Select the column you wish to sort by.

Click on Data > Sort sheet and click on your preferred sorting order (either A to Z or Z to A)

Your sheet should now be sorted with the header range still found at the top of the sheet.

4. How to sort data in Google Sheets by range

Google Sheets includes a Sort range feature which allows you to sort a specific data range rather than the entire sheet.

This can be useful if you have other tables in the same sheet that might be broken up when you try to sort the entire sheet.

Here’s how to sort a specific range in Google Sheets.

Step 1

In this example, we want to sort the range A2:B102 without sorting cells that are part of our summary table in columns D and E.

Step 2

Select the range you wish to sort.

Step 3

Head to the Data menu and hover over the Sort range option.

You can choose to sort the range in ascending order (A to Z) or in descending order (Z to A).

If you want to sort your range by another column or you want to exclude the header row, click Advanced range sorting options.

Step 4

In the Advanced range sorting options pop-up, click the Data has header row option if applicable.

You’ll then be able to choose the sort column by the column name. In our example, we’ll sort our selected range by the “color” column.

Click on Sort to proceed.

Since we’re only sorting a specific range, other columns outside the range are not affected by our sort.

5. How to sort by value in Google Sheets

We can sort data in a Google Sheets spreadsheet based on any type of value.

However, Google Sheets typically labels the sort order as either in A to Z or Z to A. Sorting values from A to Z follows the typical alphabet order while Z to A follows the reverse.

When your data includes numbers, sorting from A to Z sorts your range from the lowest number to the highest number.

Meanwhile, sorting from Z to A sorts your range from the highest value to lowest value.

If your sort column contains multiple types of data, you may be wondering what comes first when you sort from A to Z.

Google Sheets sorting always sorts numbers first when sorting in ascending order.

After numbers, special characters are then typically placed.

Next, text starting with alphabetical characters are sorted from A to Z. Lastly, empty cells appear at the end.

The reverse order will take effect when sorting from Z to A, except empty cells, which will still be added at the end of the sort.

Using a Custom Sort Order:

In some instances, you want to sort your data based on a custom order rather than alphabetically.

For example, given a list of tasks, you may want to sort by the Priority field, which may contain values such as “Low”, “Medium” or “High”.

In this case, sorting our data alphabetically will not give us the results we want.

To apply a custom sort, we can use the following formula:

=ARRAYFORMULA(SORT(range_to_sort,MATCH(sort_column_range,{custom_sort_array},),is_ascending))

  • Range_to_sort refers to the cell range containing the data we wish to include in our sort
  • Sort_column_range is a reference to our sort column
  • Custom_sort_array must be a list of comma-separated values that specifies the exact order they will appear
  • Is_ascending is a TRUE or FALSE value that will determine how you want to sort your range.

Let’s take a look at a simple example.

Suppose you want to sort a list of T-shirt orders by the size of each T-shirt (“S”, “M”, “L”, and so on).

We can use the following formula to output our data with a custom sort order:

=ARRAYFORMULA(SORT(A2:C13,MATCH(A2:A13,{“XS”,”S”,”M”,”L”,”XL”,”2XL”,”3XL”},),TRUE))

6. How to sort data by numerical value in Google Sheets

Besides sorting by a text column alphabetically, users can also sort data given a column with numerical data.

Data can be sorted from lowest value to highest value (ascending order) or vice highest value to lowest value (descending order).

Sorting data from lowest to highest:

Here’s how to sort your data by a numerical column from lowest to highest:

Step 1

First, open the Google Sheets document containing the data you wish to sort in ascending order. Identify the numerical column you wish to use to sort.

In our table above, we have a dataset of credit card transactions.

We want to use the values in column D (amount) to sort our data in ascending order based on the amount associated with the transaction.

Use your cursor to select the entire range you want to sort.

Step 2

Right-click on the selected range and click the Sort range option.

Step 3

You should now see a Sort range dialog box. Check the “Data has header row” option if applicable.

Next, select the column you wish to sort by and select A to Z as our sort order.

Click on Sort to apply the sort to the range.

Sorting data from highest to lowest:

Here’s how to sort your data by a numerical column from lowest to highest:

Step 1

Open the Google Sheets document with the data you wish to sort from highest to lowest.

Select the entire range you wish to include in your sort.

This range must include the numerical column that will be the basis of our sort.

In our example here, we’ll sort our data from highest to lowest amount using the values in column D.

Step 2

Right-click on the selection and click the Sort range option.

Step 3

You should now see a Sort range dialog box. Check the “Data has header row” option if applicable.

Next, select the column you wish to sort by and select Z to A as our sort order.

Your data should now be sorted from highest to lowest value.

7. How to sort data in Google Sheets by two different columns

Sorting data in Google Sheets by two different columns is useful when you need to organize data based on primary and secondary criteria.

Here’s how to do it:

Step 1

Open your Google Sheets document. Navigate to the sheet containing the data you wish to sort.

Identify the columns you want to sort by. Decide which column will be your primary sort criteria and which will be secondary.

In our example, you might first want to sort alphabetically by credit card type (Column A) and then by the transaction amount (Column D) from highest to lowest..

Step 2

While the range is still selected, click Data > Sort range > Advanced range sorting options.

Step 3

Specify sorting preferences in the sort window.

In the sort window, first, ensure the “Data has header row” box is checked if you have headers.

Then, select your primary sort column and choose whether to sort it in ascending (A to Z) or descending (Z to A) order.

Step 4

Click on “Add another sort column” to add your secondary sort criteria and set its order.

Step 5

Your selected data should now be sorted by two different columns.

8. How to sort data by date in Google Sheets

Sorting data by date in Google Sheets is a crucial skill, especially when you need to sort your data to view the most recent entries in your dataset.

Since dates are considered numerical data in Google Sheets, we can use the same technique from earlier to sort dates.

Here’s how to do it:

Step 1

Open your Google Sheets document.

Go to the range that contains the date column you wish to sort by.

Step 2

Identify the column with dates. Make sure that all the entries in this column are formatted as dates.

You can format the entire range to ensure that the entire column is formatted as a date with the same format.

In our current example, our date values are found in column C.

Step 3

Select the range to sort. If you want to sort the entire sheet by the date column, click on the letter at the top of the date column to select it.

If you want to sort a specific range, click and drag to select the cells including the date column or use the Ctrl+A shortcut to select the entire table.

Step 4

Right-click on the selection and select the Sort range option to access the Sort range dialog box.

Step 5

In the Sort by drop-down list, select the column containing your dates.

You can then choose to sort from A to Z or from Z to A. If sorted from A to Z, your range will be sorted from oldest dates to most recent dates.

Click on Sort to proceed.

Your data should now be sorted according to the date column.

9. How to sort data in Google Sheets without messing up formulas

When you sort data in your spreadsheet, you may encounter issues with other formulas you’ve set up.

Sorting your data may break the cell references used in your other cells. To fix this, you can try to reference an entire range rather than specific cells.

Let’s take a look at a quick example.

In the example above, we used the SUM function to compute the sum of all values in column A that are greater than 20.

While SUM gives us the right answer, it is still prone to breaking later on.

When we added a filter to sort our values in descending order, our SUM function returns an incorrect answer.

This is because the sort action does not automatically revise cell references in existing formulas.

To ensure that our answer does not change when sorting, we can use the SUMIF function instead.

This formula looks at an entire range rather than fixed cell references.

10. How to sort data but keep blank rows on top in Google Sheets

When using the Sort tool, you may notice that all blank rows will automatically appear at the bottom regardless of whether the data is sorted in ascending or descending order.

Luckily, we can still bring blank cells on top using the QUERY function.

Step 1

Open the Google Sheets document containing the range you want to sort that contains blank rows.

Step 2

Select a new area where you want to use the QUERY function.

In this example, we’ll place our QUERY function in cell H1.

Step 3

Type the QUERY function and enter the range you want to sort as the first argument.

For the second argument, enter the quert string “SELECT * ORDER BY A”.

You can replace A with any column that you wish to sort with.

The QUERY function places blank values first before any character when sorting alphabetically. This ensures that the result will always display blank rows on top.

11. Automatically sort data in Google Sheets using the SORT function

Sorting data in Google Sheets using the SORT function allows you to dynamically organize your data.

The basic syntax of the SORT function is as follows:

=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, …])

  • The range argument refers to the range of cells you want to sort.
  • The sort_column argument must be a number representing the index of the column you want to sort by.
  • is_ascending determines whether to sort the output in ascending order (if set to TRUE) or descending order (if set to FALSE)
  • [sort_column2, is_ascending2, …] are optional parameters that allow you to sort by additional columns. Each succeeding column you must sort must be added as a pair of arguments consisting of the index of the column to sort and a boolean value for the is_ascending argument.

 

Now that you know how the SORT function works, here’s how you can use it in a spreadsheet:

Step 1

Open your Google Sheets document. Navigate to the sheet that contains the data you want to sort.

Identify the range of data you want to sort.

In this example, we want to sort the range A2:F101.

Step 2

Select a new area in your sheet where you want the sorted data to appear.

For this example, we’ll place our SORT function in cell H1.

Step 3

Type the SORT function and specify the range and sort criteria within the function.

In the example above, we used the formula =SORT(A2:F101,2,TRUE) to sort our column by last name.

The first argument indicates the range we want to sort.

The second argument determines the index of the column we want to sort by.

In this case, we entered 2 since we want to sort using the last name column (second column in the range).

Lastly, the third argument determines the order we want to use.

If set to TRUE, we’ll arrange our data in ascending order according to the selected sort column.

Step 4

We can sort by multiple columns by simply adding an additional pair of arguments to our range.

Each pair of additional arguments must consist of a column index and a boolean value.

The boolean value will determine whether to sort in ascending (TRUE) or descending (FALSE) order.

Do note that the SORT function will prioritize sorting by the order they appear in the formula.

For example, the formula above first sorts our range in ascending order by company (using the arguments 5 and TRUE).

If there are any rows with the same company, these values are then sorted in ascending order by last name (using the arguments 2 and TRUE).

12. How to sort data in Google Sheets into different sheet

If you need to sort data into different sheets in a Google Sheets document, you can use either the QUERY or FILTER function to split your data based on specific criteria.

For example, suppose you have a master spreadsheet with sales data that includes details like the sale date, product name, sale amount, and the region where the sale occurred.

We can use the QUERY or FILTER functions to sort data into different sheets based on regions.

Each sheet will use a formula to automatically pull in sales data relevant to that particular region from the master sheet.

Using the FILTER function

The FILTER function allows you to return a filtered version of a source range, returning only the rows that meet a certain set of conditions.

Here’s how to use the FILTER function to sort your data into multiple sheets:

Step 1

Open your Google Sheets document containing the data you want to filter.

Identify the range of data (including headers) that you will be working with.

In our current example, we have a dataset containing sales data that we want to split by region.

Specifically, we want to create two separate sheets for the Los Angeles region and Dallas region.

Step 2

We’ll create new sheets in our document to hold our filtered data.

In this example, we’ll add two new sheets by clicking the Add Sheet button (+).

We’ll rename them to identify what type of data we want to place there.

Step 3

In our current example, rows from the original data that match the region “Dallas” will now be displayed.

We’ll repeat the previous steps to create a similar formula for the rest of the sheets.

We’ll just replace the criteria of our FILTER function to match the data you want to show in the sheet.

Using the QUERY function

Similar to the FILTER function, we can use the QUERY function to split our original data into multiple tables and sheets.

However, one advantage of the QUERY function is that it also allows us to handle header rows automatically and can even allow us to apply sorting to our output.

Here’s how to use the QUERY function

Step 1

Open your Google Sheets document that contains the master sheet with the data you want to split and sort.

Step 2

Create new sheets within the same document for each category you want to filter by.

Go to the first new sheet and select the cell where you want to start displaying the filtered data.

Step 3

We’ll use the QUERY function to return all data in the original sheet that fits a specific condition.

The first argument of the query function should be the cell range containing the original data.

The second argument must be a string containing the query that will filter through your data.

In our example, if you want to select all rows where the region is Dallas, we can use the formula =QUERY(‘Source Data’!A:C,”SELECT * WHERE B=’Dallas'”).

We’ll then use a similar formula to fill out the remaining sheets. We’ll just need to adjust the WHERE section of our query to change the condition.

13. Reverse sort data in Google Sheets

Reversing a sorted range is quite simple. We just need to change the sorting from ascending order to descending order or vice versa.

For example, when sorting with the SORT function, we can simply adjust the third argument (is_ascending) to either TRUE or FALSE to reverse the sort order.

However, if you wish to reverse the order of an unsorted list, we’ll need to use another method.

Here’s how to return a dataset in reverse order in Google sheets:

Step 1

First, identify the range you want to sort and select the cell where you want to start returning the reverse sorted output.

For this example, we’ll reverse sort a range of color names in A2:A10. We want to place the output starting at cell C2 in a new table.

Step 2

In the blank cell, type the SORT function and enter the target range.

Afterwards, we’ll use the ROW function to create a new array of values to use as a sorting criteria.

For example, ROW(A2:A10) returns an array from 2 to 10 which corresponds to the row number of each of our colors.

We’ll set the third argument to FALSE which indicates we want to return our output in descending order according to their row number.

Step 3

Hit the Enter key to evaluate the function. The formula should now return the data in reverse order.

In the example above, we were able to use the SORT and ROW functions together to reverse the order of our array.

14. How to sort a data set by color in Google Sheets

Here’s how to sort a Google Sheets dataset by either fill color or text color:

Step 1

First, open the Google Sheets document that you want to sort by color.

In this guide, we’ll try to sort an employee dataset containing rows highlighted in different colors.

Step 2

Select the dataset you want to sort then click Data > Create a filter.

Step 3

Click on the Filter icon of the column containing the color you want to sort with.

Click Sort by color > Fill Color and select the color you want show at the top.

Since the entire row is highlighted a single color in our example, we’ll just sort using the ID column.

We’ll sort our dataset so that light purple cells appear on top.

Step 4

The top rows in our sorted range should now be the color we’ve selected.

We can also use color to sort all rows in our dataset that have no color. Simply select “white” as the color you want to sort by.

Step 5

If you want to sort your dataset so that multiple colors appear at the top, you can simply redo the previous step multiple times.

Suppose you want to sort your dataset that yellow cells appear first, followed by all red cells.

We can apply the sorting in reverse order of the intended order you want.

First, use the filter tool to sort by the color red then use the filter tool again to sort by the color yellow.

The most recently sorted color will always be sorted to the top.

Step 6

Google Sheets also allows you to sort by text color.

For example, if certain values in your range have a red text color, we can choose to sort the range so that these formatted values appear on top.

Click on the filter options of the column where you can find these formatted cells.

Under Sort by color, hover over Text Color and click on the color name you want to base your sort on.

Do note that we can only sort by existing colors found in the column.

In the example below, we were able to sort our dataset so values with red text color appear on top.


What is the SORT function in Google Sheets?

The Google Sheets SORT function sorts the rows of a given array or range by the values in one or more columns. Basically, it arranges a list of items in a specific order based on the values in selected columns.

  • Syntax: SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
  • range – The data being sorted
  • sort_column – The index of the column in range OR a range outside range containing the values by which to sort*
  • is_ascending – TRUE or FALSE indicating whether to sort sort_column in ascending order (FALSE sorts in descending order)

(*NOTE: A range specified as a sort_column must be a single column with the same number of rows as range.)

Google Sheets SORT Examples

Here are some text formula examples following this syntax:

  • =SORT(A2:B26, 1, TRUE) – Sorts the range A2 to B26 based on the values in column A in ascending order.
  • =SORT({1, 2; 3, 4; 5, 6}, 2, FALSE) – Sorts the provided 3×2 array based on the values in the second column in descending order.
  • =SORT(A2:B26, C2:C26, TRUE) – Sorts the range A2 to B26 based on the values in column C in ascending order.

Summary

Hopefully this guide has given you an overview of sorting in Google Sheets.

What’s Next?

Explore some of the other useful resources on Sheets for Marketers: