{"id":10198,"date":"2021-11-11T11:24:00","date_gmt":"2021-11-11T10:24:00","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=10198"},"modified":"2021-11-11T11:24:00","modified_gmt":"2021-11-11T10:24:00","slug":"data-filters-and-sorting-in-google-sheets","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/es\/blog\/spreadsheets-tips\/data-filters-and-sorting-in-google-sheets\/","title":{"rendered":"Clasificaci\u00f3n y filtrado de datos en Google Sheets"},"content":{"rendered":"

[et_pb_section fb_built=”1″ admin_label=”section” module_class=”sheetgo-post” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Google Sheets is a great tool that\u2019s 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.<\/p>\n

In almost every situation when you\u2019re 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\u2019s ready for more important steps, such as analysis or sharing within or outside of your organization.<\/p>\n

In this blog post, let\u2019s go through a step-by-step of how to sort and filter your data<\/a> in Google Sheets, and how it can transform the way you deal with large datasets.<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Filtering data in Google Sheets<\/h2>\n

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<\/a> upon rows of data, you can apply certain filters to return your desired results.<\/p>\n

As a use case, let\u2019s 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.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/07\/Web-1920-\u2013-24.png” alt=”sorting and filtering data 1″ title_text=”Web 1920 \u2013 24″ _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

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.<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

1. Create filter<\/h3>\n

First, select any cell within your dataset, then head to Data > Create a filter<\/b>.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/07\/Web-1920-\u2013-21-1.png” alt=”sorting and filtering data 2″ title_text=”Web 1920 \u2013 21″ _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

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.<\/p>\n

If you click on the filter icon in a column, you\u2019ll see that Google Sheets has already organized data in filters, ready for you to use.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/07\/Web-1920-\u2013-22.png” alt=”sorting and filtering data 3″ title_text=”Web 1920 \u2013 22″ _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

2. Apply filter<\/h3>\n

In order to filter your data. Simply untick the different filters that you don\u2019t want to see. For example, if I want to only see the employee data of those in the China office, I\u2019ll untick every option until \u2018China\u2019 is the only one ticked.<\/p>\n

If you have a large number of filters to untick, click the Clear<\/b> link just above the list of filters to deselect them all, then tick your desired filter.<\/p>\n

Once you have the right filter selected, click Ok<\/b>.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-4.png” alt=”sorting and filtering data 4″ title_text=”sorting and filtering data 4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

As you can see below, Google Sheets has successfully filtered the data.<\/p>\n

You can identify when a column has been filtered through the funnel icon that now replaces the original filter option in the header.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/07\/Web-1920-\u2013-23.png” alt=”sorting and filtering data 5″ title_text=”Web 1920 \u2013 23″ _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Want to apply multiple filters to your spreadsheet? Simply repeat the process!<\/p>\n

Let\u2019s filter our department column to \u2018Engineering\u2019. Now we have data of employees in the Engineering department in the China offices.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/07\/Web-1920-\u2013-24-1.png” alt=”sorting and filtering data 6″ title_text=”Web 1920 \u2013 24″ _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

3. Conditional filters<\/h3>\n

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.<\/p>\n

Let\u2019s 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.<\/p>\n

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

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-7.png” alt=”sorting and filtering data 7″ title_text=”sorting and filtering data 7″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

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

In this case, let\u2019s type 01\/01\/1990. Now press Ok<\/b>.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-8.png” alt=”sorting and filtering data 8″ title_text=”sorting and filtering data 8″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

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

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-9.png” alt=”sorting and filtering data 9″ title_text=”sorting and filtering data 9″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

4. Reset and turn off filters<\/h3>\n

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<\/b>.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-10.png” alt=”sorting and filtering data 10″ title_text=”sorting and filtering data 10″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

To completely eliminate the filters applied to your columns, head to Data > Turn off filters<\/b>.<\/p>\n

The entire dataset will return once again.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-11.png” alt=”sorting and filtering data 11″ title_text=”sorting and filtering data 11″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Sorting data in Google Sheets<\/h2>\n

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.<\/p>\n

Let\u2019s 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.<\/p>\n

There are two ways that you can use the sort feature:<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

1. Sort sheet<\/h3>\n

Start by clicking on a cell within the column you want to apply the sort feature.<\/p>\n

Then head to Data > Sort Sheet by A – Z<\/b>.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-12.png” alt=”sorting and filtering data 12″ title_text=”sorting and filtering data 12″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Alternatively, if you\u2019d like your column in descending order, click the Sort sheet Z – A<\/b> option.<\/p>\n

As you can see, the dataset has successfully reorganized into alphabetical order of the last name.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-13.png” alt=”sorting and filtering data 13″ title_text=”sorting and filtering data 13″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

2. Sort range<\/h3>\n

Another way you can sort data is through the Sort range<\/b> 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.<\/p>\n

For example, let\u2019s 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<\/b>.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-14.png” alt=”sorting and filtering data 14″ title_text=”sorting and filtering data 14″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

In the pop-up, I can choose which way to sort my data. Once finished, press Sort<\/b>.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-15.png” alt=”sorting and filtering data 15″ title_text=”sorting and filtering data 15″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

As you can see, the first 10 rows of data are sorted alphabetically by the last name.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-16.png” alt=”sorting and filtering data 16″ title_text=”sorting and filtering data 16″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Please note:<\/b> If you\u2019re sorting data in an entire row, check the Data has header row<\/b> box in the pop-up so that your headers remain at the top of your spreadsheet.<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Combining the filter and sort feature in Google Sheets<\/h3>\n

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.<\/p>\n

Here\u2019s an example of filtering my dataset to China offices, and sorting the employee names alphabetically.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/11\/sorting-and-filtering-data-17.png” alt=”sorting and filtering data 17″ title_text=”sorting and filtering data 17″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

How to sort and filter data in Google Sheets<\/h2>\n

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

If you\u2019d 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<\/a>?<\/p>\n

Alternatively, check out related blog posts below!<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.4.8″ background_color=”#f9f9ff” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ global_module=”26540″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n

\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"

Google Sheets is a great tool that\u2019s 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 […]<\/p>\n","protected":false},"author":41,"featured_media":27674,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"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 the data filters and sorting methods that will help us arrange our data in the manner that we need.\n

Data filters<\/h3>\nConsider 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.\n\n\"Data\n\nNavigating 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.\n

Create filters<\/h4>\nSelect one of the cells within the data set, and navigate to Data > Create a filter<\/strong>.\n\n\"Data\n\nIn 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.\n\n\"Data\n

Apply filter<\/h4>\nIn 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\u00a0huge 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.\n\n\"Data\n

Apply multiple filters<\/h4>\nWhat 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.\n\n\"Data\n

Conditional filters<\/h4>\nThe 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.\n\n\"Data\n

Turn off filters<\/h4>\nTo reset filters in a column, click on the filter pertaining to that column, click on Select all and then on OK<\/strong>. However, to remove the filters on the entire data set, navigate to Data > Turn off filters<\/strong>. Doing so will show us the entire data set.\n\n\"Data\n

Data sorting<\/h3>\nWhy 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.\n

Data filters and sorting<\/h4>\nThe 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.\n\n\"Data\n

Filtering and sorting on multiple columns<\/h4>\nJust 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.\n\n\"Data\n\nIt 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.\n\n\"Data\n

Sort range<\/h4>\nInstead of going with the Data filters and sorting route, we could also opt for Sort range<\/strong> 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<\/strong> range<\/strong>.\n\n\"Data\n\nDoing 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.\n\n\"Data\n\nThe sorting mechanism works exactly as we discussed before in the first approach. Except we can't use filters here.\n\nLet us know if you have any remarks or questions by commenting down below!","_et_gb_content_width":"","footnotes":""},"categories":[29],"tags":[28],"class_list":["post-10198","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-spreadsheets-tips","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/10198","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/41"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=10198"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/10198\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/27674"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=10198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=10198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=10198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}