How to sort your data in Google Sheets


Google Sheets is a remarkably powerful and convenient tool for collecting and analyzing data, but sometimes it can be hard to understand what that raw data means. One of the best ways to see the big picture is to sort it to help bring the most important information to the top, showing which is the largest or smallest value relative to the rest.

It’s not surprising that Google Sheets has a powerful search feature. It’s also easy to sort data in Google Sheets, but a few concepts need to be clear to achieve the best result and glean the most valuable insights. With a few tips, you’ll quickly master sorting by one or more columns and be able to bring up different views for a better understanding of what the data means.

How to quickly sort a simple sheet

Sorting a Google sheet by a single column is quick and easy. For example, with a table of foods that are good sources of protein, you might want to sort by name, serving size, or amount of protein. Here’s how to do that.

Step 1: Move the mouse pointer over the column that you want to sort by and select the Downward arrow that appears to open a menu of options.

Options appears after selecting a column's triangle menu.

Step 2: A little over halfway down the menu are the sort options. Select Sort sheet A to Z to sort the entire sheet so the chosen text column is in alphabetical order. Sort sheet Z to A places that column in reverse alphabetical order. 

There are two text sort options.

Step 3: To sort a numerical column, follow the same procedure. Choose Sort sheet A to Z for a low-to-high order of numerical values or Sort sheet Z to A to see the highest values at the top.

The same sort options are available for numbers.

Step 4: No matter which column is sorted, the information from all other columns in the sheet is moved to keep the same order. In our example, a cup of walnuts continues to show as 30 grams, and a 2.5-ounce steak has 22 grams of protein.

Sorting a sheet by a column keeps row data aligned.

Step 5: If a numerical column shows values in more than one unit, the sort will fail to give the expected result. In our example, the Google Sheet about protein sources, the serving-size column includes cups, ounces, tablespoons, and slices. Units are ignored by the built-in sort feature conversion, so 1 cup will incorrectly be treated as if it’s smaller than 3 ounces. The best solution is to convert the data to use only one unit per column.

Google Sheets doesn't consider units when sorting.

Sorting a sheet with headers

The quick-sort method described above is convenient; however, it doesn’t work as expected when the sheet has headers. When you’re sorting the entire sheet, all rows are included by default, which can mix headings and units with the data. Google Sheets can lock cells so they can’t be changed, but it’s also possible to lock one or more rows in place at the top so headers don’t get confused with your information.

Step 1: To freeze the position of header rows, choose the Google Sheets View menu. Instead of using your browser’s view menu, open the View menu at the top of the web page that’s open. Then, select the Freeze option and choose 1 row or 2 rows, depending on the number of header rows in your sheet.

Freeze one or two rows to lock the header rows.

Step 2: If there are more headers, it’s possible to freeze more rows. Simply select a cell in the lowest header row, then choose Freeze, then Up to row X in the View* menu, where X is the row number.

Google Sheets can freeze more header rows.

Step 3: After freezing headers, a thick gray line appears to show where the split occurs. A sheet can then be sorted using the column menu’s Sort sheet A to Z or Sort sheet Z to A option. This leaves the headers in place at the top of your Google sheet while rearranging your data into a more useful table.

The column menu can sort while keeping frozen headers in place.

Sort by more than one column

A single-column sort is quick and handy, but often, there’s more than one variable to consider when comparing figures. In our example, you might be most interested in which has the least fat but also want to get more protein. That’s easy to do with a sort range.

Step 1: Select the cells that you want to sort, including one header row. This can be done quickly by choosing the top-left cell, then holding down the Control key (Command key on a Mac) and pressing the Right arrow to select the full width of the table.

Use Control+Right Arrow to select a horizontal range

Step 2: The same can be done by selecting the full height, holding Control, and pressing the Down arrow. Now the entire table of data will be selected.

Use Control+Right Arrow then Control+Down Arrow to select a range.

Step 3: From the Google Sheets Data menu, choose Sort range > Advanced range sorting options.

Google Sheets advanced sorting is in the Data menu

Step 4: A window will open that lets you pick multiple sort columns. If your sort range includes a header row, check the box beside Data has header row.

You can see the names of header rows if that box is checked

Step 5: The Sort by field will now show your header column names instead of making you choose columns by using their letter designation. Pick the primary sort column — for example, Fat (g) — and make sure A-Z is selected for sort order.

With header row checked, the names show up in the Sort by field.

Step 6: Choose the Add another sort column button to have a secondary sort, such as protein. It’s possible to add as many sort columns as you want before selecting the Sort button to see the results.

You can add more sort columns before sorting your data.

Save a data range

Instead of selecting a range every time you want to re-sort it, you can save the table as a named range.

Step 1: Select the range of data that you want to save for easier access, then choose Named ranges from the Google Sheets Data menu.

Named ranges can be selected faster.

Step 2: A panel will open on the right, and you can type a name for this range.

Type a name for the selected range.

Step 3: The Named ranges panel will remain open, and you can select the entire range again by choosing it from this panel.

Choose a named range to select all of its data.

Saving a sort view

It’s also possible to save more than one advanced sort operation for easy access in the future. This lets you switch between different views when making a presentation or when you need to analyze information from different angles. This is possible by creating a Google Sheets filter view.

Step 1: Select the range of data you want to sort, then choose Create new filter view from the Filters submenu in Google Sheets Data menu.

Create a filter view to save advanced sorts.

Step 2: A new bar will appear at the top of the sheet with filter view options. Choose the name field and type a name for this filter view, such as “Low Fat/High Protein.”

Filter views can be named so they are easy to remember

Step 3: The name of each header column will now include a sort menu on the right side. Using our example data, choose the Protein sort menu and then select Sort Z-A to place the highest values at the top.

Use the header row's sort menu

Step 4: Repeat this process on the Fat sort menu but choose Sort A-Z to show the lowest values first. This new filter view will show a sort with low fat being the top priority and high protein as a secondary consideration.

Multiple columns can be sorted with a filter view.

Step 5: You can create more filter views in the same way and use as many sort columns as needed. To load a filter view, open the Data menu, select Filter views, then select the view that you’d like to see.

Filter views can be loaded in the Data menu.

How to share a sorted Google Sheet

Google Sheets is readily available to anyone with an internet connection, making this a great tool for sharing information with others.

Step 1: Sorted spreadsheets can be shared by selecting the big green Share button in the upper-right corner.

The Share button makes it easy to collaborate with others.

Step 2: It’s also possible to download a Google Sheet to share over email or print it to send via regular mail.

You can also share a Google sheet via email.

Google Sheets provides several ways to sort data, and this can make in big difference when trying to analyze a complicated data set. For more information, check out our complete beginner’s guide that shows how to use Google Sheets.

We also have a guide that explains how to make graphs and charts in Google Sheets, a great way to see data in an easy-to-digest visual form.

Editors’ Recommendations

Source link

Share it with your friends

Crowded Hell

Crowded Hell

Leave a Reply

Your email address will not be published.