Don't miss Inspire 2024, taking place May 13 - 16, 2024 at the Venetian, Las Vegas. Register Now.

 

How to Sort Data in Google Sheets

Technology   |   Bertrand Cariou   |   Mar 30, 2022

How to Sort Data in Google Sheets

You’ve imported your data into Google Sheets—now you need to sort it. Thankfully, there’s an easier way than moving your columns up or down by hand. Google Sheets allows you to automatically sort your data numerically or alphabetically.

 

In this post, we’ll review how to sort data in Google Sheets as well as how to filter data in Google Sheets. Read on to learn more. 

 

How to Sort Data in Google Sheets (Alphabetically or Numerically) 

Any type of text data can be sorted alphabetically in Google Sheets. In this case, we have a list of names that we’d like to sort.  

 

  1. First, start by selecting your sheet by clicking the blank square in the upper left corner.
  2. Under Data, select “Sort Range,” which will prompt a pop-up window to appear.
  3. To exclude the header row in our sort, we’ll check the box that says “Data has header row.”
  4. Under “Sort by,” we’ll select the “Name” column that we want to sort. We’ll keep the “A → Z” option selected since we want the names organized in ascending alphabetical order.

 

This same function also works with numbers. If we select our column of donation amounts, we could choose to organize those amounts from high to low (Z → A) or low to high (A → Z).  

How to Sort Data in Google Sheets Across Multiple Columns

Google Sheets also gives us the option to sort multiple columns at once. For example, we could give our donor names first priority (sorted A → Z) and our donation amounts second priority (sorted Z → A.). 

 

Setting that up in Google Sheets would look like this: 

Before setting up this sorting logic, our “Names” column was sorted alphabetically, but there was no sorting preference for the coinciding donation amounts. 

 

In the image below, we can see that donor Amelia made three donations in the month of September, but her donation amounts aren’t organized in any particular order. 



 

Now, let’s watch how those donation amounts change once we apply our multi-column sorting logic: 

Amelia’s name is still listed alphabetically, but her donations values have been reorganized to be listed from highest to lowest (Z → A). Theoretically, we could also add a third column to be considered, such as the date of the donation, and should there be any repeat donors and repeat donation amounts, the date of donation would determine their order. 

Here’s more detailed instructions on how to sort data in multiple columns:

  1. First, start by selecting your sheet by clicking the blank square in the upper left corner.
  2. Under Data, select “Sort Range,” which will prompt a pop-up window to appear.
  3. To exclude the header row, we’ll check the box that says “Data has header row.”
  4. Select the first column that you’d like to sort and whether the values should be listed from high to low or low to high.
  5. Click “Add another sort column” to add your second column. Repeat until you’d selected all columns you’d like to sort. 

How to Filter Data in Google Sheets
Filtering data in Google Sheets is a great way to highlight certain data while removing (without deleting) other data that you aren’t interested in. It’s especially useful if you’re working on a shared document; there may be different questions that you’re looking to answer about the data vs. your colleagues. Filtering protects the integrity of the data while allowing you to quickly find the insights that you need. 

Here’s how to do it: 

1.Select the column or range of columns where you want to apply your filter.

2. In the upper right corner, click on the three dots and select the funnel “Create a filter.”

3. Now, the column(s) that you selected will be highlighted in green. Click on the green funnel next to your column name.

4. This will open up a pop-up window where we’ll decide how we want to filter the data. In this case, we’re going to be applying a conditional filter. A conditional filter allows you to apply certain rules; in this case we want to look at every donation amount above $75 so that we can analyze which donor has donated large amounts.

 

The resulting data looks like this: 

Of course, there’s also the option to simply filter out certain values. For example, if you had a list of products and the states they were purchased in, you may want to filter out certain states to see the product’s popularity by region. Or, you could filter out products to see if they are more popular in certain states. 

5. You have the option to save any filter you create so that other collaborators can reuse it. Simply return to the funnel in the upper right and click on the drop down arrow where you’ll select “Save as filter view.”

 

6. To close out of your filter view, click on the funnel once again so that it is deselected. When you want to return to your filter view, go back to the drop down arrow and select the name of your filter (in this case, “Filter 1”). 

Where Sorting or Filtering Data in Google Sheets Can Fall Short
Sorting or filtering data can be the answer for those trying to find out how to organize data in Google Sheets. But it’s also often used as a means to explore and better understand the contents of data—and this is where these methods can come up short. 

Though filtering and sorting data in Google Sheets does offer insight into your data’s trends, at the end of the day, you’re still looking at rows and columns. Which means it’s hard to get a clear picture of the data and any outliers, commonalities, etc. that it may contain. This difficulty will only amplify as the data increases and you’re no longer scanning tens or hundreds of rows—but thousands. 

Finally, users must remember that sorting or filtering data will do little good if the data isn’t cleaned properly. For example, say you’re filtering for all mentions of “California” in your data. The filter will not bring up any misspellings or abbreviations of the word. While there are ways to search for alternative representations, such as using a conditional filter of “Text starts with” or “Text ends with,” this can still be a time-consuming (and ultimately imperfect) process. 

 

The Alteryx Designer Cloud Data Preparation Platform

Though Google Sheets is an excellent tool for simple reporting or analytic tasks, many organizations are adopting data preparation platforms like Designer Cloud in order to prepare big or complex data for analysis—or to simply ensure that data of any size is free of errors. 

The Designer Cloud platform automatically presents visual representations of your data based upon its content in the most compelling visual profile. This allows an immediate understanding of the data at a glance—no more searching or filtering through spreadsheets to find trends across your data.

It also alerts users to any data quality concerns, such as missing or invalid data, so that these data quality issues don’t slip through to the end analysis. And, since Trifacta is powered by machine learning, the platform is smart enough to recognize what the user is trying to do. If they want to standardize all versions or misspellings of California, for example, the tool will automatically suggest things like “CA” or “Calif.” 

To be clear, there is no direct competition between Google Sheets and a data preparation platform like Trifacta—they are simply two great tools used for different purposes. In fact, the Designer Cloud technology can be found on the Google Cloud Platform as Google Cloud Dataprep. And while using Cloud Dataprep, it’s easy for users to pull in Google Sheets data to explore, join, and prepare for analytic use. 

To learn more about Designer Cloud, kick off your 30-day free trial for free today! 

Tags