Skip to Content

Google Sheets Filter Based On Cell Value

Posted on Last updated:

The ability to perform a Google Sheets filter based on cell value stands out as a valuable tool, especially for those new to spreadsheets. 

In this beginner-friendly guide, we’ll explore how to utilize Google Sheets’ filtering feature, specifically focusing on filtering based on cell value.

But before we jump into the practical aspect of today’s tutorial, let’s quickly unveil a scenario that provides a better insight into what it entails to filter based on cell value in Google Sheets.

Scenario/Use Case

Imagine you’re in charge of tracking inventory for a small retail store. Your Google Sheet contains a comprehensive list of products, including their names, quantities, and prices. Now, suppose you need to identify which products are running low and need to be restocked.

Without filtering based on cell value, you would have to manually scroll through the entire list, which can be time-consuming and error-prone. However, with this powerful Google Sheets feature, you can swiftly filter your inventory to display only the items with quantities below a specific threshold. This enables you to quickly identify what needs replenishing, ensuring that your store runs smoothly and efficiently.

In the following sections, we’ll provide step-by-step instructions on how to apply a filter based on cell value in Google Sheets. Whether you’re managing inventory, organizing personal finances, or working on any other data-driven task, mastering this skill will enhance your spreadsheet prowess.

Let’s dive in and simplify your data management processes.

Filter Data In Google Sheets Based On Cell Value – Practical Example

Now that we’ve set the stage, let’s delve into the exciting world of filtering based on cell value in Google Sheets. Don’t worry if you’re new to spreadsheets or feeling a bit apprehensive – we’ll guide you through the process step by step, making it a breeze to grasp.

We will use the following sample data to teach you how to filter based on cell value in Google Sheets. 

Let’s dive in, shall we?

Now that our data is all tidy, let’s dive into how to use Google Sheets to filter based on cell values. What we want to do is sift through our spreadsheet and display only the cells where the Quantity of products sold is less than 10. 

Don’t worry; we’ll guide you through this process step by step, so keep reading. 

Step 1: Highlight The Cells With Value You Want To Filter

First things first, let’s quickly highlight the cells with values we want to filter. Since we are interested in products that have sold less than ten, we will go ahead and highlight cells in column D. 

Step 2: Data > Filter View > Create A New Filter View

With cells in your desired column selected, go to the menu bar and click “Data.” A dropdown menu will appear, and you’ll find the option for “Filter view” there. Click on it and proceed to select the option for “Create new filter view. 

This action should add the filter icon to the selected column. Here, take a look at what column D in our spreadsheet now looks like executing the steps we detailed above. 

Step 3: Setting The Filter Criteria

Here’s where the magic happens. Click on the filter icon in the column you’ve selected (in our case, “Quantity”). A drop-down menu will appear with various options. To filter based on cell value, choose “Filter by condition.”

Step 5: Define Your Conditions

Now, you can specify your filtering conditions. For our inventory example, what we want to do is choose to display only items where the quantity is less than 10. To do that, we will select the option for “Less than” and enter “10” in the input field.

Step 6: Apply The Filter

Finally, click “OK,” and watch the magic unfold. Your spreadsheet will instantly hide all rows where the quantity exceeds 10, leaving only the items that need restocking visible.

Once you’ve followed the filter steps we talked about earlier, your spreadsheet will change to match the rules you’ve set. Here, take a look at what our spreadsheet looks like after filtering based on cell values:

Take a good look at the screenshot above. You can see that we’ve successfully filtered the data in column D based on cell values. That’s all there is to using Google Sheets to filter this way. Pretty straightforward, isn’t it?

Using Filter Function to Filter Based on Cell Value

In addition to the method we discussed earlier, Google Sheets offers another powerful tool called the FILTER function to filter data based on cell values. This method can be especially handy when you want to apply more complex or dynamic filters.

To demonstrate how to use the filter function to filter data based on cell value, we will use the sample data we used for the previous example. 

Here’s a step-by-step guide on how to use the FILTER function for filtering based on cell values:

Step 1: Select A Destination Cell

Let’s begin by choosing a cell where we want our filtered data to appear. For this example, we will use cell H1. 

Step 3: Enter The FILTER Function

With the cell where we want our filtered data generated selected, navigate to the formula bar and type in the following formula: 

=FILTER(A2:E20, D2:D20 < 10)

Step 4: Press Enter

After entering the formula, press Enter. Our filtered data will now appear in the selected cell, showing only the rows that meet the specified condition. 

Here, take a look at what our spreadsheet now looks like: 

Take a look at the screenshot above, and you’ll see how we’ve used the filter function to sort through our spreadsheet based on cell values. 

But it’s not just about finding products that sold less than ten items; we’ve also got a wealth of additional information at our fingertips. This includes knowing the prices, product IDs, and various other details about these specific items.

Final Thoughts 

Filtering data in Google Sheets can seem daunting, especially if you’re just getting started, but guess what, it isn’t as complicated as it may seem. 

In today’s guide, we embarked on a journey to show you how to harness the power of Google Sheets and its fantastic filtering abilities, specifically focusing on filtering based on cell value. We’ve covered essential steps and scenarios to make this seemingly complex task much more manageable for beginners.

By now, you’ve seen how this feature can save you valuable time and help you make informed decisions by extracting specific data from a sea of information. Whether you’re managing inventory, organizing your finances, or working with any kind of data, knowing how to filter based on cell value will save you a lot of headaches. 

Remember, in the world of spreadsheets, filtering is your trusty sidekick. It helps you focus on what’s essential, whether it’s identifying products that need restocking, analyzing sales data, or any other data-driven task.

Other Related Google Sheets Tutorials