Sorting and filtering data in Google Sheets

Get a selection of expert articles

Google Sheets is a great tool that’s capable of holding huge amounts of data in one place. But, as your datasets get larger, so does the complexity in handling them. Luckily, Google Sheets has various features dedicated to sorting and filtering data, that can transform the way you manage and analyze your data.

In almost every situation when you’re handling large amounts of data, the sorting and filtering features on Google Sheets can help to streamline your process and increase overall efficiency. They play a key role in organizing and managing your data so that it’s ready for more important steps, such as analysis or sharing within or outside of your organization.

In this blog post, let’s go through a step-by-step of how to sort and filter your data in Google Sheets, and how it can transform the way you deal with large datasets.

Filtering data in Google Sheets

Filtering your data in Google Sheets is a great way to narrow your search when looking for specific data within a large dataset. Instead of scrolling through rows upon rows of data, you can apply certain filters to return your desired results.

As a use case, let’s say that I have a large Google Sheets database containing all the information of every employee who works in my business worldwide. There are thousands of rows of data, each with several columns of data.

sorting and filtering data 1

My task is to find all the employee information of those working in the China offices. Rather than scrolling through thousands of rows of data and noting each employee who works in the China offices, I can simply create and apply a filter to do the work for me.

1. Create filter

First, select any cell within your dataset, then head to Data > Create a filter.

sorting and filtering data 2

Google Sheets will automatically create data filters for each column of your spreadsheet. You should be able to see the filter icon on each column header.

If you click on the filter icon in a column, you’ll see that Google Sheets has already organized data in filters, ready for you to use.

sorting and filtering data 3

2. Apply filter

In order to filter your data. Simply untick the different filters that you don’t want to see. For example, if I want to only see the employee data of those in the China office, I’ll untick every option until ‘China’ is the only one ticked.

If you have a large number of filters to untick, click the Clear link just above the list of filters to deselect them all, then tick your desired filter.

Once you have the right filter selected, click Ok.

sorting and filtering data 4

As you can see below, Google Sheets has successfully filtered the data.

You can identify when a column has been filtered through the funnel icon that now replaces the original filter option in the header.

sorting and filtering data 5

Want to apply multiple filters to your spreadsheet? Simply repeat the process!

Let’s filter our department column to ‘Engineering’. Now we have data of employees in the Engineering department in the China offices.

sorting and filtering data 6

3. Conditional filters

If your datasets are a little more complex – whether that be your data is constantly changing, or some columns of data are not one simple value – you can use conditional filters to manage your spreadsheet.

Let’s say I want to filter my data so that only the employees born after 1990 show up. If I use the manual filters, it will take a long time to deselect multiple dates. Instead, I can use the conditional filters to streamline this process.

Click the filter icon in the column containing the data you want to filter and expand the section named Filter by condition. Click on the empty field, and a large list of various conditional filters will appear.

sorting and filtering data 7

As I want to filter my data to employees born after 1990, I should click the filter named Date is after. In the next dropdown, select Exact date and then enter your desired date.

In this case, let’s type 01/01/1990. Now press Ok.

sorting and filtering data 8

As you can see below, Google Sheets has successfully filtered my data so that only those with birth dates after 01/01/1990 appear.

sorting and filtering data 9

4. Reset and turn off filters

To reset your filters so that all of your data is available again, simply click on the filter icon of your column and press Select all.

sorting and filtering data 10

To completely eliminate the filters applied to your columns, head to Data > Turn off filters.

The entire dataset will return once again.

sorting and filtering data 11

Sorting data in Google Sheets

Sorting your data in Google Sheets can be extremely useful when you have to compare data through ranges, such as a period of time. By sorting data in a certain way, you can easily track and monitor changes in data in order to help obtain important insights.

Let’s say I want to sort my spreadsheet data alphabetically. Using the sort feature, I can organize the last names of each employee from A – Z.

There are two ways that you can use the sort feature:

1. Sort sheet

Start by clicking on a cell within the column you want to apply the sort feature.

Then head to Data > Sort Sheet by A – Z.

sorting and filtering data 12

Alternatively, if you’d like your column in descending order, click the Sort sheet Z – A option.

As you can see, the dataset has successfully reorganized into alphabetical order of the last name.

sorting and filtering data 13

2. Sort range

Another way you can sort data is through the Sort range option. With this, you can either sort an entire column of data, or you can sort just a selected range of data within a column.

For example, let’s say I only wanted to sort the first 10 rows of data alphabetically. I can highlight the first 10 rows beneath Column B, then head to Data > Sort range.

sorting and filtering data 14

In the pop-up, I can choose which way to sort my data. Once finished, press Sort.

sorting and filtering data 15

As you can see, the first 10 rows of data are sorted alphabetically by the last name.

sorting and filtering data 16

Please note: If you’re sorting data in an entire row, check the Data has header row box in the pop-up so that your headers remain at the top of your spreadsheet.

Combining the filter and sort feature in Google Sheets

You can combine both the filter and sort features in Google Sheets for the ultimate data management. Simply employ the instructions mentioned above and the features will work hand-in-hand.

Here’s an example of filtering my dataset to China offices, and sorting the employee names alphabetically.

sorting and filtering data 17

How to sort and filter data in Google Sheets

And there you go! Organizing and managing large datasets becomes a lot more effective when using the filter and sort features in Google Sheets.

If you’d like to learn more about the various features of Google Sheets, why not take a look at our blog post on How to freeze rows and columns in Google Sheets?

Alternatively, check out related blog posts below!

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

Ready to streamline your spreadsheet data?

You may also like…