How to Filter Rows And Columns in Google Sheets

Knowing how to filter rows and columns in Google Sheets is a prized skill in the vast realm of data analysis. It helps you extract meaningful insights, organize data, and make decision-making more efficient and manageable.

Data filtering in Google Sheets isn’t just about taming overwhelming datasets; it’s about revealing hidden patterns, trends, and nuggets of wisdom that can transform data into actionable knowledge.

Join us as we embark on a journey through data filtering and learn more about how to filter rows and columns in Google Sheets. Whether you’re a novice seeking to tame unwieldy data or a seasoned analyst hungry for novel insights, this guide will be your gateway to becoming a virtuoso in the symphony of data manipulation.

Method 1: Filter Rows and Columns in Google Sheets using the ‘Create Filter’ Feature

Let’s look at a simple way to filter rows by specific words. You will use sample data from tracking your time as a freelancer for different clients.

Here is how our Google Sheets look:

To filter the data by a specific word, follow these steps:

Step 1: Select All the Data in the Google Sheet

On your worksheets, Click the blank rectangle above row number 1 and to the left of Column A to highlight the entire page.

Step 2: Go to Data > Create a Filter

Alternatively, click on the filter icon in the top-bar menu section. It’s the one that looks like a funnel. Once active, the document borders will turn green, as seen below.

Step 3: Click on the ‘Filter Icon’ of your desired column

Identify the column with the data that contains the name you want to filter by. In this case, this is column B, the memo column. Then click the filter button on the top-right corner of the column to reveal a menu dropdown.

Step 4: Go to the ‘Filter by condition > Text Contains’

Navigate to the Filter by condition option and click it. A new dialog box with up and down arrows on the right side will appear.

Click on the arrows and choose Text Contains.

Step 5: Enter Your Filter Text

After clicking Text Contains, two new dialog boxes will appear, one below the other. Click on the second one; it has a search icon on the right-hand side, and add this filter text: #alchemist.

Step 6: Press Ok

Scroll to the bottom of the dropdown menu and press okay to reveal your filtered results.

You should also note that if you’d like to return to your original data set and remove the filter, you only have to click the filter icon at the top menu bar.

Method 2: Filter Rows and Columns in Google Sheets Using ‘Advance Techniques’

Now that you’ve seen how to filter data using rows by specific words. Let’s see how filtering by column works.

Our sample data set provides information on different apps by region, division, sales, and profit. You aim to get the apps’ sales and profit by region. But first, here is how the data looks.

Here are the steps to filter the data by columns.

Step 1: Select a Cell

Select cell F3 and add the heading Select Region. You will also use this cell to create a dropdown list.

Next, select cells G4, H4, and I4 and add App, Sales, and Profit headings, respectively. These will allow you to return each region’s data.

Step 2: Create a Dropdown List in Cell G3

Select cell G3 and right-click to reveal a menu option. Navigate to the bottom of the options, hover the cursor over View More Cell Actions, and click Data Validation.

A new menu option will appear on the right side of the screen. Click Add rule to expound the menu option.

Change the Criteria option from Dropdown to Dropdown (from a range).

Then click the grid on the right side of the dialog box below to select the data range.

Once you click it, a new dialog box will appear. Activate the dialog box by clicking on it. Then, go to the regions section on the table and select cells B2 to B17, as seen below.

To help you account for more data you may add to this table in the future, delete the last two numbers, which in this case is 17, and leave only the letter representing the column, B. Press okay to set your list from the range.

Then move to the bottom right corner of your screen and click Done on the Data validation rules menu.

Step 3: Input Filter Function

In the new dropdown list you’ve created, select Europe. Then, select cell G5 and copy this formula:

=FILTER(C2:E,B2:B=G3)

Step 4: Press Enter

Once you press enter, app, sales, and profit data for the European region should auto-fill the relevant cells.

And that’s not all. If you use the dropdown menu to change the region, say, Asia, the table will also reflect the Asian data, as seen below.

Now, what if you want to add more conditions? Let’s say you want to update the function to include sales values greater than zero.

So, instead of displaying zeros in your sales and profit columns, the formula disregards them and only shows results greater than zero.

Step 5: Add More Conditions

Looking at South America, you will note that two regions had no sales or profit to report.

To remove these two regions from the list, double-click cell G5 and add this new condition to the formula:

=FILTER(C2:E,B2:B=G3,D2:D>0)

The new condition, D2:D>0, essentially means that the function only returns results when the values in the sales column, column D, are greater than zero. Now press enter, and let’s see how the results now look.

The regions with zeros have been filtered out, and your data looks more neat. You should also note that you can add more conditions as needed or even use another function simultaneously to help you sort the results even further.

Data Formatting Tips to Ensure Easy Filtering of Rows & Columns in Google Sheets

Here are some more tips to help you to easily use the filter option in Google Sheets:

Ensure Data Structure: Make sure your data is organized in a table format, with headers in the first row.
Enable Filters: Click on any cell within the dataset and then click the filter icon in the toolbar. This activates the filter option for the entire dataset.
Filtering Columns: Click the dropdown arrow in a column header to access filtering options for that column. You can sort data, filter by specific values, or apply custom conditions.
Filtering Rows: Use the Filter by condition option in the filter menu to set up more complex criteria for filtering rows based on values, dates, or formulas.
Clear Filters: To remove all filters and revert to the original data view, click the filter icon again or use the Data menu to clear filters.

Moreover, when using the filter function to filter rows and columns in Google Sheets, you should note that the filter function spills. This essentially means you don’t need to worry about the dollar sign when setting your range references. You just need to write your formula, press enter, and you’ll get all the results back.

Conclusion

In the dynamic landscape of data analysis, where information flows like a river, the ability to channel and direct that flow is paramount. As you conclude your journey through the intricacies of filtering rows and columns in Google Sheets, you stand at the threshold of newfound insights and empowered decision-making.

What once might have seemed like a daunting sea of numbers and text has now been demystified and transformed into a canvas of opportunity. From using the filter function to filter out different columns to using specific words to narrow down your rows.

Learning how to filter rows and columns in Google Sheets will undoubtedly be helpful in your data analysis. So, what filter uses do you foresee in your applications? Let us know in the comments section below.

FAQs

What is data filtering in Google Sheets?

Data filtering in Google Sheets is the process of selectively displaying specific rows and columns within a dataset based on certain criteria. It lets you focus on relevant information and temporarily hide the rest without altering the original data.

Can I filter data by both rows and columns?

You can filter data by both rows and columns. When you filter rows, you display specific rows based on criteria. When you filter columns, you choose which columns to display while hiding the others.

What are filter views in Google Sheets?

Filter views are customized filter configurations that you can save and reuse. They allow you to switch between different filtering setups without altering the original data or other filter views.

Can I combine multiple filter conditions?

You can combine multiple filter conditions using logical operators like AND, OR, and NOT. This allows you to create more complex queries to refine your data display.

Can I filter data based on text and numeric values?

You can filter data based on text and numeric values. Use text filters to display rows containing specific keywords and numeric filters to show rows based on values such as greater than, less than, or within a range.

How can I clear filters and revert to the original view?

To clear filters and return to the original data view, click the filter icon (funnel-shaped) in the toolbar again. You can also go to the Data menu and Turn off the filter.

Are hidden columns affected by filtering?

Yes, hidden columns still affect your filtered data. If you hide a column containing important information, it can impact the accuracy of your filtered results. Be cautious when working with hidden columns.

Can I color-code filtered cells for better visualization?

Yes, applying color coding to filtered cells can help you visually distinguish them from the rest of the data. This is particularly useful when dealing with large datasets and complex filtering setups.

Is it possible to save different filter configurations for later use?

Yes, that’s where filter views come in. You can create and save different filter configurations for various analyses and switch between them as needed without affecting the original data.

Can I use filter views for collaborative work?

You can use filter views for collaboration. Different team members can create their own filter views to analyze the same dataset based on their specific needs.

Are filter views permanent changes to the data?

No, filter views are temporary and do not alter the underlying data. They provide a personalized view of the data that is only visible to you and can be easily switched or deleted.

Can I filter data based on dates in Google Sheets?

You can filter data based on dates in Google Sheets. Use the Filter by condition option and choose Date is or Date is between to display data within specific date ranges.