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.
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.
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.
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.
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, which may mean adding a new column.
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.
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.
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.
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 using a keyboard shortcut and choosing the top-left cell, then holding down the Control key (the Mac shortcut is to use the Command key). Complete the shortcut by pressing the Right arrow to select the full width of the table.
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.
Step 3: From the Google Sheets Data menu, choose Sort range > Advanced range sorting options.
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.
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.
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.
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.
Step 2: A panel will open on the right, and you can type a name for this range.
Step 3: The Named ranges panel will remain open, and you can select the entire range again by choosing it from this panel.
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.
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."
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.
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.
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.
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.
Step 2: It's also possible to download a Google Sheet to share over email or print it to send via regular mail.
Google Sheets provides several ways to sort data, and this can make in big difference when trying to analyze a complicated data set.
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.
