Categories
Design

How to sort by multiple columns in google sheets

Google Sheets has many of the same features as Microsoft Excel. Whether you want to merge cells in Google Sheets or calculate values with formulas, you can do so with Google’s spreadsheet application.

However, many of these features are found in different locations than you might be used to with Excel, so there can be a bit of a learning period as you familiarize your self with Sheets.

One of the most commonly used features in Excel is the ability to alphabetize or sort your data. This feature is also present in Google Sheets, and can be found on the Data tab.

Our guide below will walk you through the process of selecting and alphabetizing a column on Google Sheets.

How to Alphabetize on Google Sheets

  1. Sign into Google Drive and open your spreadsheet.
  2. Click the column letter to alphabetize.
  3. Choose the Data tab.
  4. Select the Sort range option.
  5. Adjust the options, then click Sort.

Our guide continues below with additional information and pictures of these steps.

How to Alphabetize a Column in Google Sheets

The steps in this guide are going to show you how to select a column and alphabetize all of the data in that column.

While our example below is going to focus on alphabetizing text, you can use this same method to sort numerical data as well. Choosing the A to Z option with numbers will put the smallest value at the top, while choosing the Z to A option will put the largest numerical value at the top.

Step 1: Go to your Google Drive at https://drive.google.com/drive/my-drive and double-click the spreadsheet containing the data that you wish to alphabetize.

Step 2: Select each column that you wish to include in the sort.

If you only want to sort one column of data and leave the rest of the information in its current location, then only select that column. However, if there is data in the other columns that is related to data in your target column, then select the other columns, too.

How to sort by multiple columns in google sheets

Step 3: Click the Data tab at the top of the window.

How to sort by multiple columns in google sheets

Step 4: Click the Sort range option.

Note that you could select the Sort sheet by column option or the Sort range by column option now, too, but I will use the Sort range option for this example. For reference, Sort sheet by column will sort the selected column along with any data in the corresponding rows. The Sort range by column option will only sort the selected column and leave the rest of the columns intact.

How to sort by multiple columns in google sheets

Step 5: Check the box to the left of Data has header row option if you have a header row in your spreadsheet, then click the Sort by dropdown menu and select the column that you wish to sort alphabetically. Specify whether you want to sort from A to Z or Z to A, then click the OK button.

How to sort by multiple columns in google sheets

The “Sort range” that we identify in the steps above will only appear if multiple columns are selected. Otherwise you will only see a couple of basic sorting options at the top of the menu.

When you choose to sort a column and you have data in other columns surrounding it, those other columns will be sorted based on the selected column. This happens because Google Sheets assumes that data in rows are related to one another, so it wants to keep that related data in the same row.

Are you printing your spreadsheet, but the gridlines are making it look bad, or difficult to read? Learn how to hide gridlines in Google Sheets so that all that is visible on the screen is your data.

See also

Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.

After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.

His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.

Ben Stockton
How to sort by multiple columns in google sheetsBen Stockton
Writer

Ben Stockton is a freelance tech writer from the United Kingdom. In a past life, he was a UK college lecturer, training teens and adults. Since leaving the classroom, he’s been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloudwards.net. He has a degree in History and a postgraduate qualification in Computing. Read more.

How to sort by multiple columns in google sheets

When you’re analyzing complex data sets in Google Sheets, you can take advantage of its built-in sorting feature to organize the data. You can sort by individual columns or, for more complex data, you can sort by multiple columns.

To do this, you’ll need to open your Google Sheets spreadsheet and select the data set you wish to sort. You can do this manually by selecting the cells using your mouse, or by selecting one of the cells in your data set and pressing Ctrl+A on your keyboard.

Once your data is selected, click Data > Sort Range from the Google Sheets menu.

How to sort by multiple columns in google sheets

In the “Sort Range” option box, you can select how you wish to sort your data.

You can select the column to sort by, as well as whether to sort in ascending or descending order. Click the “Data Has Header Row” to be able to select columns by the header cell.

How to sort by multiple columns in google sheets

By default, Google Sheets will only offer one column to sort by. To add multiple columns to your sort options, select the “Add Another Sort Column” button.

How to sort by multiple columns in google sheets

When you sort by multiple columns in Google Sheets, the sort will take place in order from top to bottom. For instance, an example spreadsheet has product sales data with product names, sale dates, and prices shown.

If you wanted to sort this data by name and then by the amount sold, you’d want to select the product name column in column A (named “Product”) first and the amount sold in column C (named “Price”) second.

How to sort by multiple columns in google sheets

You could sort this in ascending order, starting with product names near the start of the alphabet at the top, then with prices sorted by the smallest amount. Select “A-Z” to sort this way or “Z-A” to sort in descending order instead.

You can mix and match these options, too. For this example, you could sort in ascending order for product names and descending order for sales amounts.

How to sort by multiple columns in google sheets

Once your sorting options are set, select the “Sort” button to sort your data. The data in your spreadsheet will be rearranged following the rules set in the “Sort Range” options box.

How to sort by multiple columns in google sheets

For this example, the data above has been sorted with product names and prices both in ascending order. This is shown clearly with the monitor and mouse products.

Priced at $100, the monitor would come second if the sort was only performed on the “Price” column (column C). As the sort was performed on the “Product” column (column A) first, however, the sold monitor was placed ahead of the mouse.

How to sort by multiple columns in google sheets

You can undo any of these sorts by performing another sort on your data set, by pressing the “Undo” button in the menu bar, or by pressing Ctrl+Z on your keyboard.

  • › The Best Gaming Keyboards of 2021: Be on Top of Your Game
  • › Why Sublime Text Is Great For Writers, Not Just Programmers
  • › What Is a ULED TV, and How Is It Different?
  • › Why Professionals Will Actually Want a 2021 MacBook Pro
  • › How to Add Images to Questions in Google Forms

How to sort by multiple columns in google sheets Ben Stockton
Ben Stockton is a freelance tech writer from the United Kingdom. In a past life, he was a UK college lecturer, training teens and adults. Since leaving the classroom, he’s been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloudwards.net. He has a degree in History and a postgraduate qualification in Computing.
Read Full Bio »

How to sort by multiple columns in google sheets

The SORTBY Function produces a dynamic array of sorted data.

In this example, we use the SORTBY Function to produce a dynamic array sorted by Team and then by Player Name:

How to sort by multiple columns in google sheets

Note: The SORTBY Function is written in only cell F3. The rest of the table will populate automatically.

The sorted table is dynamic, which means that if any of the Team or Player Name values change or records or added / deleted, then the output table will automatically re-size and re-sort.

Note: The SORTBY Function is not available in Excel 2019 or earlier. Read below for sorting options available for earlier versions of Excel.

Sorting in Excel 2019 or Earlier

In Excel 2019 or earlier, you can use a formula to sort a single column, but sorting by multiple columns is more challenging.

Of course, you can always use Excel’s built-in sort tool to sort by multiple columns:

How to sort by multiple columns in google sheets

How to sort by multiple columns in google sheets

How to sort by multiple columns in google sheets

Dynamic Sorting with SORT Function in Google Sheets

The SORTBY Function is not available in Google Sheets, but its SORT Function can be used instead and is more powerful than the SORT Function in Excel. It allows us to use multiple columns to dynamically sort data ranges.

In this example, we use the Google Sheets SORT Function to take a data table and produce a dynamic array that sorts it first alphabetically by Team and then by Player Name:

How to sort by multiple columns in google sheets

The Google Sheets SORT Function sorts data by the specified column numbers in the input data and allows us to set whether the sort order is ascending or not.

How to sort by multiple columns in google sheets

Excel Practice Worksheet

How to sort by multiple columns in google sheets

Practice Excel functions and formulas with our 100% free practice worksheets!

Sorting data in Google Sheets is pretty straight forward.

You can sort a single column (alphabetically or based on the numeric value in ascending/descending order) or multiple columns (based on one of the columns).

But what if you have multiple columns and you want to sort more than one columns at the same time.

For example, suppose you have a dataset as shown below and you want to first sort based on the region and then based on the sales. This way, you would have all the records for each region together and within each region, the sales can be sorted from highest to lowest.

How to sort by multiple columns in google sheets

In this tutorial, I will show you how to sort by multiple columns in Google Sheets.

Let’s get started!

Sort by Multiple Columns In Google Sheets

Multi-level sorting can easily be done in Google (as it has in-built functionality to do this).

Below is the data set that I will use in this tutorial.

Here are the steps to sort by multiple columns in Google sheets:

  1. Select the entire dataset (A1:C13 in this example)
  2. Click the Data tab
  3. Click on the Sort range optionHow to sort by multiple columns in google sheets
  4. In the ‘Sort range’ dialog box, click on the ‘Data has header row option’. No need to check this in case you don’t have a header rowHow to sort by multiple columns in google sheets
  5. In the Sort by drop-down, click on Region and the sort order as A –> ZHow to sort by multiple columns in google sheets
  6. Click on ‘Add another sort column’ optionHow to sort by multiple columns in google sheets
  7. In the ‘then by’ drop-down, select Sales and the sort order as Z –> A (as we want the sorting from highest to lowest sale value)How to sort by multiple columns in google sheets
  8. Click on SortHow to sort by multiple columns in google sheets

The above steps would sort the dataset first by the Region column (alphabetically in ascending order) and then each region data is then sorted using the sales value (highest to lowest).

How to sort by multiple columns in google sheets

In this example, I have shown you how to do this with two columns, but you can sort by more than two columns as well using the same steps. Just repeat steps 6 and 7 for more columns.

Pro Tip: In case you may need to revert back to original data, add an additional column (I call these helper columns) and have serial numbers in it (1 in the first cell, 2 in the cell below it and so on). When you want to revert to the original order, simply sort based on this helper column.

Once your data is selected, click Data > Sort Range from the Google Sheets menu. In the “Sort Range” option box, you can select how you wish to sort your data. By default, Google Sheets will only offer one column to sort by. To add multiple columns to your sort options, select the “Add Another Sort Column” button.

How do I average data from different sheets in Google Sheets?

Go to Add-ons > Get add-ons and install one called “Custom Count and Sum”. It has like 15 different functions and one is “Average all sheets”.

How do I average data from different sheets in Excel?

This same range of cells on multiple worksheets is a 3-D reference.

  1. Open the Excel workbook.
  2. Click the worksheet cell where you wish to enter the average value.
  3. Type “=AVERAGE(“ in this selected cell.
  4. Click the cursor or pointer over the tab of the first worksheet you wish to reference.

How do you find Q1 and Q3 in Google Sheets?

Example: How to Calculate IQR in Google Sheets

  1. Step 1: Enter the data. First, enter all of the values of a dataset into one column:
  2. Step 2: Calculate the first and third quartiles. Next, we’ll use the QUARTILE() function to calculate the first (Q1) and third (Q3) quartiles of the dataset.
  3. Step 3: Calculate the IQR.

Can Google sheets find quartiles?

To find the quartiles manually, the data must be ordered (sorted) from least to greatest. But you can use the Quartile function in Google Sheets in a sorted or unsorted dataset. Quartiles are actually three cut off points in an array or range. Mark the data in the middle (median), that results in two halves.

Is quartile 2 the median?

The second quartile, Q2, is also the median. The upper or third quartile, denoted as Q3, is the central point that lies between the median and the highest number of the distribution.

How do you find the percentile in Google Sheets?

In a blank cell, enter “=PERCENTILE(A1:A,)”, where is the row of the last value in the data set and is the decimal form of the desired percentile. For example, to find the 90th percentile of a data set from cell A1 to cell A50, enter “=PERCENTILE(A1:A50,0.9)” in a blank cell.

How do you find the top 10 percent in Google Sheets?

How to calculate percentage in Google Sheets

  1. Enter the below formula to D2: =C2/B2.
  2. Copy it down your table.
  3. Go to Format > Number > Percent in Google Sheets menu to apply the percentage view.

How do I figure out percentiles?

How to calculate percentile

  1. Rank the values in the data set in order from smallest to largest.
  2. Multiply k (percent) by n (total number of values in the data set).
  3. If the index is not a round number, round it up (or down, if it’s closer to the lower number) to the nearest whole number.

How do you find percentiles on a calculator?

How to Calculate Percentile

  1. Arrange n number of data points in ascending order: x1, x2, x3.
  2. Calculate the rank r for the percentile p you want to find: r = (p/100) * (n – 1) + 1.
  3. If r is an integer then the data value at location r, xr, is the percentile p: p = xr

How do you read a z score table?

To use the z-score table, start on the left side of the table go down to 1.0 and now at the top of the table, go to 0.00 (this corresponds to the value of 1.0 + . 00 = 1.00). The value in the table is . 8413 which is the probability.

What does the Z-table tell you?

What does the z score table tell you? A z-table, also called the standard normal table, is a mathematical table that allows us to know the percentage of values below (to the left) a z-score in a standard normal distribution (SND).

Organizing large batches of data in tools such as Google Sheets and Microsoft Excel is essential. Unorganized datasheets can confuse users and lead to errors. As it is easy to read and understand, sorting columns in the correct order is one of the ways to organize data. In such cases, if you know how to sort multiple columns in Google Sheets, it is an added advantage.

How To Sort Multiple Columns In Google Sheets Easily

Since Google Sheet provides a direct option for sorting a single column, it’s always easy to do so. But what can we do to sort multiple columns in a spreadsheet? For instance, let’s say you are given a list of states and a few cities in those states. How can you custom sort the cities in ascending order while adhering to the ascending order of the states?

Fortunately, Google Sheets provides an easy solution to sort data. You can use the Data tab in Google Sheets to sort multiple columns in a breeze. You can also use a pivot table in Google Sheets to sort order and summarize data as per your needs. Let’s take a glance at how we can do this quickly.

How To Sort Multiple Columns In Google Sheets

1. Open Google Sheets and select the dataset.
2. Open the sort range dialog box.
3. Sort the columns in order.
4. View the results.

Note: You are now aware of the quick steps taken to sort columns in Google Sheets. Let’s check out the detailed steps supported with images.

How To Sort Multiple Columns In Google Sheets – Details With Images

You can use Google Sheets to sort multiple columns because the tool offers a built-in feature to do so.

Consider the similar example as discussed above. Here, the primary is to sort the states in ascending order and then sort their cities in ascending order.

To know how to sort multiple columns in Google Sheets, select the dataset in the entire sheet. Here, the selected input range will be A1:B11 from column A and column B. Select the data range

Click on the Data tab from the menu bar. Once you see the sorting options, choose the Sort range option from the dropdown options. Doing this will open the Sort range dialog box. Open the sort range dialog box

Now comes the central part about how to sort multiple columns in Google Sheets. First and foremost, select the ‘Data has header row’ option checkbox. You need to choose this option if your data set has a header row. A column header allows users to have easy references for columns and identify them quickly.

Next, select ‘States’ in the Sort option and choose the A to Z option to arrange the column in ascending order. Make sure you choose the column you want to sort first in this field.

Then, click on ‘Add on another sort column’ to insert an additional column field and select ‘Cities’ this time. Google Sheets will sort the States column first and then sort the adjacent cities in ascending order. Sort the columns

Once you click on the Sort button to save the settings, the selected range of cells will be rearranged, as shown below. You can see that the states are arranged in ascending order in the first column. Other cities are also arranged in ascending order in the second column while adhering to the order of the States. View the results

We have sorted two columns in this example, but you can command Google Sheets to sort multiple columns by repeating the third step. You can also use the QUERY function or opt for the Google Sheets FILTER function to summarize the data in multiple sheets.

How To Use The Sort Function To Sort Multiple Columns

The sort function can also be used to sort multiple columns, in the same manner, we saw previously. Be it column names, column references, column with dates, or any column list, the built-in sorting functionality works perfectly.

Before we move on with the multiple columns in-built sorting functionality method, let’s see how the Google Sheets’ sort function works.

range – The defined range of selected cells

sort_column – The index number of the column you wish to sort by

is_ascending – The sorting order

You can also use is_descending to sort the columns in descending order.

Now, let’s focus back on the example. Here, we need to sort the custom list of the States column first and then arrange the cities in ascending order according to their respective states.

First, enter the SORT function in a cell as given below.

1 and 2 are the column indexes of the primary column that you want to sort. The ‘true’ value denotes that the selected columns should be arranged in ascending order. Use the ‘false’ value to organize the data in descending order. Enter the SORT function

Once you input the SORT function correctly, press Enter key to obtain the results. View the results

Likewise, you can use the SORT formula to know how to sort multiple columns in Google Sheets. You can further use conditional formatting in Google Sheets to change the rows by color to represent them in different groups.

Conclusion

Sorting an individual column in a Google spreadsheet or MS Excel is an easy task. The tool provides a direct option to sort the datasheet using the Data tab. The real issue arises when you need to go beyond Google sheets auto-sorting and sort two or more columns in a particular order. Be it product names or duplicates, sorting is a crucial part of organizing large and complex data. Hence, users need to be aware of how to sort multiple columns in Google Sheets.

You can follow any of the methods given above in Google Sheets to sort multiple columns. Also, it’s not possible to do multi-level sorting on mobile devices using the first method. However, you can use the SORT function to arrange data in the Google Sheets mobile app.

Is there a way to rank, and subsequently, sort this, where rank 1 is more important than rank 2, so it would end up with something looking like this?

How to sort by multiple columns in google sheets

1 Answer 1

If you want to create a new range, which is a sorted view of the input range, you would use =SORT . The =SORT function can take multiple columns as parameters:

In your case, you want to sort by column B in descending order, then by column C in ascending order. Given you have your data in column A through C , this should do what you want:

If you want to output the rank of a team in the sorted result set, the easiest way is to use the row number, =ROW .

So =ROW(H2) would yield 2 . Knowing that, we could say that the 1-based rank of a row is =ROW(any row in the result) – 1 . If we have the sorted result table in H2:J , we can output the rank of each row:

I set up an example spreadsheet to demonstrate, feel free to take a look and/or copy it.
And check the documentation for =SORT and =ROW .

If you want to sort the input range in-place, you can’t use a formula – because a formula needs an input range and an output range, which must be different.

For a one-time sort, select the input data (including the header row) and click Tools → Sort range. The dialog that appears has a checkbox for Data has header row – click it.

Specify a sort column (Rank 1), and descending order (Z→A). Then click Add another sort column, and select Rank 2, and ascending (A→Z).

This will sort the range – but only once, so if you alter the data, you will need to repeat the sort procedure.

The third option is to create a Filter view. Select the data, and click _Data → Filter views → Create new Filter view.

How to sort by multiple columns in google sheets Give the filter a name. Then, for each column you want to use in your sort, click the down arrow icon (2). Specify the sort order for each column. Close the filter view by clicking the X in the upper right corner.

This will bring you back to your input data (unsorted). To go back to the sorted view, click _Data → Filter views → [your filter view].

Also in this case, you need to repeat the sort procedure when data changes.

You can sort data in alphabetical and numerical order, or use filters to hide data you don’t want to see.

Learn more about this Coursera course. To get an example spreadsheet and follow along with the video, click “Make a Copy” below.

Sort data in alphabetical or numerical order

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Highlight the group of cells you’d like to sort.
  3. If your sheet includes a header row, freeze the first row.
  4. Click DataSort range.
  5. If your columns have titles, click Data has header row.
  6. Select the column you’d like to be sorted first and choose a sorting order.
    • To add another sorting rule, click Add another sort column.
  7. Click Sort.

Sort an entire sheet

  1. On your computer, open a spreadsheet in Google Sheets.
  2. At the top, right-click the letter of the column you want to sort by.
  3. Click Sort sheet by A to Z or Sort sheet Z to A.

Sort by color

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select a range of cells.
  3. Click DataCreate a filter.
  4. To see filter options, go to the top of the range and click Filter .
    • Sort by color: Choose which text or fill color to filter or sort by. Cells with the color you choose to sort by will move to the top of the range. You can sort by conditional formatting colors, but not alternating colors.
  5. To turn the filter off, click DataTurn off filter.

Filter your data

Important: When you add a filter, anyone with access to your spreadsheet will see the filter too. Anyone with permission to edit your spreadsheet will be able to change the filter.

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select a range of cells.
  3. Click DataCreate a filter.
  4. To see filter options, go to the top of the range and click Filter .
    • Filter by condition: Choose conditions or write your own.
    • Filter by values: To hide data points, uncheck the box next to the data point and click OK.
    • Search: Search for data points by typing in the search box.
    • Filter by color: Choose which text or fill color to filter by. You can filter by conditional formatting colors, but not alternating colors.
  5. To turn the filter off, click DataTurn off filter.

Create, save, delete, or share a filter view

Important: If you only have permission to view a spreadsheet, you can create a temporary filter view that only you can use. Your filter view won’t be saved.

On a computer, you can filter data so the filtered data only applies to your view of the spreadsheet. Your filter view changes are automatically saved.

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click DataFilter viewsCreate new filter view.
  3. Sort and filter the data.
  4. To close your filter view, at the top right, click Close .
  5. Your filter view is saved automatically.

To delete or duplicate a filter view, in top right, click Options Delete or Duplicate.

To remove all filters, go to each filter view and click Options Delete.

Tip: You can’t change the order of filter views.

See an existing filter view

Important: You can only apply one filter view at a time.

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click DataFilter views.
  3. Select a filter view.
  4. Your filter will be applied to the spreadsheet.
  5. To close your filter view, in the top right, click Close .

Save a filter as a filter view

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Apply a filter.
  3. Click DataFilter viewsSave as filter view.

Rename a filter view

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click DataFilter views.
  3. Select a filter view.
  4. At the top left of the sheet, next to “Name”, click the filter view name and type the new name.
  5. Press Enter.
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Apply the filter view .
  3. Copy the URL.
  4. Share the filter view link.

Learn more about filters & filter views

Filters and filter views help you analyze a set of data in a spreadsheet.

How do I automatically sort multiple columns in Google Sheets?

How to sort data in Google Sheets by two different columns

  1. Select the range and go to Data => Sort range.
  2. Select the primary column to sort by, then click the button to Add another sort column.

How do I automatically sort in Google Sheets?

In the above formula: A2:B is the data range that you want to sort, the number 1 indicates the column number that you want to sort data based on. Please change them to your need. 2. If you want to sort data in descending order, please apply this formula: =sort(A2:B, 1, false) .

How do I filter two columns in Google Sheets?

You can do this manually by selecting the cells using your mouse, or by selecting one of the cells in your data set and pressing Ctrl+A on your keyboard. Once your data is selected, click Data > Sort Range from the Google Sheets menu. In the “Sort Range” option box, you can select how you wish to sort your data.

How do you cross match data in Excel?

Select both columns of data that you want to compare. On the Home tab, in the Styles grouping, under the Conditional Formatting drop down choose Highlight Cells Rules, then Duplicate Values. On the Duplicate Values dialog box select the colors you want and click OK. Notice Unique is also a choice.

What is important formula in Excel?

Excel formulas help you identify relationships between values in the cells of your spreadsheet, perform mathematical calculations using those values, and return the resulting value in the cell of your choice.