Select All or a Specific Category in Multiple Columns in Filter in Google Sheets

This post explains how to select all or a specific category in multiple columns using the Filter function in Google Sheets.

In Google Sheets, there are three options to perform a filter, one of the most common tasks we do in Spreadsheets.

They are;

  1. Two menu commands in the DATA menu. They are Create a filter or Filter views > Create new filter view.
  2. FILTER function.
  3. QUERY function.

In this tutorial, we are going to learn to use the Filter function to select all or specific category values in multiple columns in Google Sheets.

If you prefer the Data menu, you can easily do it as follows.

Assume the data is in A1:C, where columns A, B, and C contain Items (text field), Supplier (text field), and Stock (numeric field), respectively.

Steps:

  1. Select A1:C.
  2. Click Data > Create a filter.
Select All or a Specific Category in Multiple Columns - Example

If you want to filter all the items from “Supplier 1,” click on cell B1 down arrow and uncheck all the values except “Supplier 1.”

  • Columns A and C – Set to select all (default).
  • Column B – Set to a specific category, i.e., Supplier 1.

If you prefer, you can show the “Stock” equal to or greater than a certain quantity. How?

Click C1 (down arrow) > Filter by condition > Greater than and type 500 in the given field.

To revert to select all in column B, click cell B1 (down arrow) > Select all (link).

How to select all or a specific category in multiple columns using the Filter function in Google Sheets?

Let’s learn that below.

Get Select All in Multiple Columns in Filter Function

Follow the below steps to select all or specific categories in multiple columns in the Filter function in Google Sheets.

There are two main steps and they are the drop-down part and the formula part.

1. Drop-Down Part

As per our sample data, we require to create three drop-down menus for columns A, B, and C and all should contain the option “All” in addition to the unique values from the corresponding column.

Multiple Drop-downs for Filter

We can create them using the Data validation option in Google Sheets. Here is how.

In cell E2 click Data > Data validation and select “List of items” against Criteria. There in the given field, copy-paste the below lists.

Gravel 20-40 mm, Gravel 10-20 mm, All

They are the unique items from column A and an “All” string. Similarly, create drop-downs in F2 and G2.

List for use in F2;

Supplier 1, Supplier 2, Supplier 3, Supplier 4, All

List for use in G2;

0, 500, 1000, All

Note:- If you prefer to use the “List from a range” against Criteria in the Data validation, then please follow this guide – How to Get an All Selection Option in a Drop-down.

2. Formula Part – Filter Function and Select All in Multiple Columns

In cell E4, insert the following formula.

=filter(A2:C,if(E2="All",row(A2:A),A2:A=E2),if(F2="All",row(B2:B),B2:B=F2),if(G2="All",row(C2:C),C2:C>=G2))

How does this formula be able to filter all or selected category values from each column?

For example, we use the following plain Filter formula to filter based on specific conditions in columns A, B, and C.

=filter(A2:C,A2:A=E2,B2:B=F2,C2:C>=G2)

To see it working, choose “Gravel 10-20 mm” in E2, “Supplier 2” in F2, and 500 in G2.

You will get the rows matching the above conditions.

The “All” in the drop-downs are not a criterion in columns A, B, and C. But the Filter function will treat it so!

So in our ‘original’ formula, I have used three IF logical tests to make them correctly ‘readable’ to the Filter function.

For example, the highlighted part in if(E2="All",row(A2:A),A2:A=E2) tests whether the E2 value is “All” or not.

If it’s “All,” the filter criterion in column A is row(A2:A), and if not, A2:A=E2.

You may doubt whether row(A2:A) is a condition in Filter.

Yep! It defines a condition similar to len(A2:A). The difference is as follows.

row(A2:A) – It means have row numbers.

len(A2:A) – It means whether the rows have values or not.

I prefer the former to get select all in columns in Filter.

If you want select all but exclude blank cells in that column, use the latter.

Example Sheet 22422

That’s all. Thanks for the stay. Enjoy!

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.