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.
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.
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.
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.
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.
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.
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.
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.
As you can see below, Google Sheets has successfully filtered my data so that only those with birth dates after 01/01/1990 appear.
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.
To completely eliminate the filters applied to your columns, head to Data > Turn off filters.
The entire dataset will return once again.
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.
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.
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.
In the pop-up, I can choose which way to sort my data. Once finished, press Sort.
As you can see, the first 10 rows of data are sorted alphabetically by the last name.
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.
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.
