Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 6

Filtering Data for Specific Criteria

Recommended resources

Introduction

In our last lesson, we mastered sorting, a technique for reorganizing your entire dataset to bring order to your information. While sorting is excellent for arranging data, you often need to focus on a specific subset without the distraction of irrelevant rows. This is where filtering comes in.

This lesson will teach you how to filter your data in Google Sheets. Unlike sorting, which reorders rows, filtering temporarily hides rows that don't match your criteria, allowing you to zero in on the information that matters most. For a data researcher, this is a daily activity—whether you're isolating sales from a particular region, finding all clients with overdue payments, or examining leads from a specific industry. We'll cover filtering by specific values, using logical conditions, and even saving your filters for future use.

What is Filtering?

At its core, filtering is about selectively reducing the amount of data you see. It allows you to define conditions, and Google Sheets will hide any rows that don't meet them. This is a non-destructive action; your data is still there, just hidden from view, and can be brought back instantly.

Let's start with a formal definition to ground our understanding.

Filtering Data — Introduction to Google Sheets and SQL

This article from Runestone Academy provides a concise and clear definition of what a filter is and what it accomplishes.

Please read the introductory paragraphs, starting from the beginning of the page. This will solidify the core concept of filtering as a way to subset your data.

Applying Your First Filter: Filtering by Value

The most common type of filter is "filtering by value." This is where you pick one or more specific values from a column, and Sheets shows you only the rows containing those exact values. For instance, you could filter a list of projects to see only those categorized as "Coaching."

Let's watch a demonstration of how to turn on the filter functionality and then use it to filter by a specific value.

Google Sheets: Sort and Filter Data Tutorial

This video from the Technology Central channel provides a clear, step-by-step walkthrough of creating a filter and using it to select a specific value.

First, watch the segment on how to apply a filter to your data, from this point. Notice how the filter icons (small inverted triangles) appear in your header row. Next, continue watching from here to see how to use the "Filter by values" list to show only the rows for the "United States." Pay attention to the process of clearing the selection first and then checking the desired value.

Practical Exercise, Part 1: Focus on a Single Client

Now, let's apply this to your My Freelance Invoices spreadsheet. Imagine a client, "Sunshine Navigations," has called with a question about their invoices. You need to see all of their projects at a glance.

  1. Open your My Freelance Invoices spreadsheet.
  2. Click any cell within your data table (e.g., A3).
  3. Go to the menu and click Data > Create a filter. You should see the filter icons appear in your header row.
  4. Click the filter icon in the header for your Client Name column.
  5. In the menu that appears, click the Clear link to uncheck all client names.
  6. Find and check the box next to Sunshine Navigations.
  7. Click OK.

Your sheet now displays only the invoices for Sunshine Navigations. Notice the row numbers on the left are now blue and may have gaps (e.g., 1, 4, 9, 25). This indicates that the other rows are hidden, not deleted. To remove the filter and see all your data again, click the green filter icon in the Client Name header and select Turn off filter.

Advanced Focus: Filtering by Condition

Filtering by specific values is useful, but the real power comes from filtering by conditions. This lets you apply logical rules to your columns, such as:

  • Numbers: Show rows where the amount is greater than 500, or is between 100 and 300.
  • Text: Show rows where the project type contains the word "Video," or the client name starts with "G".
  • Dates: Show rows where the due date is before today.
  • Blanks: Show rows where a cell is empty or is not empty.

These conditions are the building blocks of focused data analysis.

The 'Filter by condition' menu offers a wide range of logical operators for text, numbers, and dates.

Let's watch a quick video that shows how to apply a numerical condition.

Google Sheets: Sort and Filter Data Tutorial

This segment demonstrates how to set up a filter for a numerical range.

Watch the section from this timestamp. The instructor filters for an age range using the "is between" condition. This same logic applies to filtering invoice amounts or any other numerical data.

Filtering by a numerical condition lets you quickly isolate high-value items, overdue accounts, or other key metrics.

Practical Exercise, Part 2: Find High-Value Pending Invoices

Your goal now is to create a priority list of large, unpaid invoices. You need to find all invoices that are both "Pending" and have an amount greater than $100. This requires applying two filters at once.

  1. If you have any active filters, remove them first (Data > Turn off filter). Then, turn filters back on (Data > Create a filter).
  2. First, filter by status: Click the filter icon in your Status column header. Clear all selections, then check Pending and click OK. Your sheet now shows only pending invoices.
  3. Next, add a condition for the amount: Click the filter icon in your Amount column header. This time, click Filter by condition.
  4. In the dropdown menu under "Filter by condition," select Greater than.
  5. In the "Value or formula" box that appears, type 100.
  6. Click OK.

You are now looking at a very specific subset of your data: all pending invoices over $100. Notice that both the Status and Amount column headers now show a filled green filter icon, indicating they both have active filter rules.

Best Practice: Using Filter Views for Collaboration

When you apply a filter, it changes the view for anyone looking at the sheet. This can be disruptive if you're collaborating with a client or teammate. The solution is Filter Views.

A filter view is a saved, personal filter that only you can see. You can create multiple filter views for different purposes (e.g., "Overdue Invoices," "Q4 Projects") and switch between them without affecting what others see. This is an essential skill for any freelance data researcher sharing work with clients.

Filter Views - How to Filter Google Sheets Without Affecting Other Users

Leila Gharani's tutorial provides an excellent explanation of why and how to use Filter Views.

Start watching at this point to understand the problem Filter Views solve and to see how to save your current filter settings as a new view. Pay attention to the dark grey border that appears, indicating you are in a filter view. Then, continue watching until the end of the segment to see how to create another filter view from scratch and name it. This reinforces the process and shows how powerful reusable views can be.

Practical Exercise, Part 3: Create a Reusable "Action List"

Let's create a filter view you can use every day to see which invoices need your immediate attention.

  1. Make sure all regular filters are turned off (Data > Turn off filter).
  2. Go to the menu and select Data > Filter views… > Create new filter view.
  3. Your screen will change, with dark borders appearing around your data. At the top, you'll see a field for the name. Change Filter 1 to Overdue Invoices.
  4. Now, set up the filters for this view:
    • Click the filter icon on the Status column, clear the selections, and check Pending. Click OK.
    • Click the filter icon on the Due Date column. Select Filter by condition. Choose the condition Date is before and select Today from the next dropdown. Click OK.
  5. Your view is now active and configured. Simply click the 'X' in the top-right corner of the dark grey bar to close the view and return to the full dataset.

To use your saved view anytime, just go to Data > Filter views… and select Overdue Invoices from the list.

Conclusion

You have now added another essential tool to your data research toolkit. By mastering filters, you can cut through the noise of large datasets and focus precisely on the information needed to answer questions, identify trends, or manage tasks.

Here are your key takeaways from this lesson:

  • Filtering hides data that doesn't match your criteria, whereas sorting reorders it. It's a temporary and non-destructive way to focus your view.
  • You can apply filters based on specific values (e.g., "Coaching"), logical conditions (e.g., amount > 100, date is in the past), or even color.
  • Filter Views are saved, personal filters that are crucial for collaboration, allowing you to analyze data without altering the view for your clients or team.

So far, we've used conditional formatting to highlight data, sorting to reorder it, and filtering to view subsets of it. All of these tools react to the data that already exists. In our next lesson, we will enter the world of logical functions, starting with the IF function. This will allow you to create new data and labels in your sheet based on conditions, such as automatically marking an invoice as "Overdue" in a new column.