Sorting and filtering data in Google Sheets

Google Sheets is capable of holding huge data sets. But huge data can mean difficulties with handling it. Especially in terms of organizing it in a way that we can traverse through easily. In this article, we will explore sorting and filtering data in Google Sheets that will help us arrange our data in the manner that we need.

Data filters

Consider the following data set in Google Sheets. It consists of all the sales revenues across different states and product lines within the country of the United States of America.

Navigating through these data records can be painful. There are 12 months, 50 states and three products – resulting in 1800 records. And if there are more years, and products, the count only goes higher. Let’s just say that we need to look at only the sales data of November and December. We could just scroll down 1500 rows to get there. Or, we could simply use the data filters and sorting tools.

Create filters

Select one of the cells within the data set, and navigate to Data > Create a filter.

In doing so, Google Sheets automatically recognizes the entire data set and inserts data filters in the table headers. We will see the corresponding drop-down symbols in each of the columns, as shown below. On selecting the first drop down, we see a pop-up where we can set filters to suit our needs.

Apply filter

In the pop-up, we see the list of all the unique values available in that column. By default, all the values are selected, as the data was unfiltered before. We can either deselect all the unwanted months one by one, and leave only November and December. Or, alternatively, click Clear hyperlink just above the list, and select November and December. The latter is a little faster if there is a huge amount of numbers of values to choose from. Once done, we click OK. In an instant, Google Sheets then displays just the records for November and December. And we will know that the data is filtered on a column with the presence of filter symbol on the header.

Apply multiple filters

What if we want to see the sales numbers for all the products in California state in the month of July? It is simple really. Apply filters in the first two columns just like we did in the previous case.

Conditional filters

The filters we have discussed thus far are based on a manual selection. But in the scenarios where the data is constantly changing, applying manual filters may not be a good idea. Especially when we have an advanced mechanism to do so. The conditional filters help us do a myriad of conditional checks for the data to be qualified as per the filter criteria. If the existing conditional filters aren’t enough, we can build our own conditions using formulas.

Turn off filters

To reset filters in a column, click on the filter pertaining to that column, click on Select all and then on OK. However, to remove the filters on the entire data set, navigate to Data > Turn off filters. Doing so will show us the entire data set.

Data sorting

Why do we need to sort the data in the first place? Probably we’d want to see the records that have the lowest/highest sales numbers at the very top. Or maybe we like to see the data ordered alphabetically. Whatever the need, data sorting is a great tool to organize the data. We can do this using two different methods.

Sorting and filtering data

The first approach is by using the data filters themselves. You’d have noticed by now that the data filters pop up also has sorting options at the very top. We can apply them in conjunction with the filters, or we could just simply apply the sorts without any active filter criteria. Below, we went for the latter option – sorted on the Revenue column in ascending order (A to Z) without applying any filter criteria.

Sorting and filtering data on multiple columns

Just like filters, we can sort the data in multiple columns and combine them with filter criteria too. Please have a look at the following snapshot. We sorted the Revenue column in ascending order, then the State column on ascending order.

It is very important to note that the sequence in which we sort the columns affects the outcome. For example, if we flip the sorting sequence in the above example, i.e. first sort State column in ascending order and then Revenue in ascending order gave us the results below.

Sort range

Instead of going with the Data filters and sorting route, we could also opt for Sort range functionality. Unlike the data filters, this won’t automatically recognize the data range. So, first, we need to select the range we need to sort before triggering it from Data > Sort range.

Doing so will give us a pop-up where we can define our sorting criteria. At the top, we can clearly see the data range we selected before triggering this. We need to mention if we have headers in the data selection. Then we can sort the column of our choice either on ascending (A > Z) or descending (Z > A) order. We can also add multiple sort columns according to our need.

The sorting mechanism works exactly as we discussed before in the first approach. Except we can’t use filters here.

To learn more on how to filter Google Sheets without affecting other users of the same spreadsheet, make sure to check the following post: Filter Google Sheets Without Affecting Other Users.