Percentage values in Google Sheets Query Where clause must be used the way you use number literals in it. See how to filter a column that contains percentage values using Query function in Google Sheets.

Actually, there are two methods.

1. Number Literals: You can use the criteria (percentage value) in decimal notation like 0.1 for 10%, 0.2 for 20% and so on.
2. String Literals: This method is like converting the percentage value column to a text value column and using the criteria (percentage value) as a string like “10.00%”, “20.00%” and so on.

To know more detail about Query Literals, please check my guide – Examples to the Use of Literals in Query in Google Sheets.

Let’s go the examples on how to filter a column contains percentage values using the Query function in Google Sheets.

One more thing! Actually, I have only mentioned the use of Query function in the percentage value column. But you can use the Filter function too which is much easier for users to understand. So I will provide you formulas based on both the functions.

Query Formula to Filter a Percentage Value Column in Google Sheets

Method 1: Criterion in Decimal Notation

Recommended: ✓

The data to use in Query is in the range A1:C. I am going to filter the name of students (and their marks) who have scored more than 50% marks. That means column C is the column to filter.

=query(A1:C,"Select A,B where C>0.5",1)

To refer to a percentage criterion which is entered in a cell, use it as below.

=query(A1:C,"Select A,B where C> "&D1&"",1)

In this, cell D1 contains the value 0.5 which represents 50%.

Method 2: Criterion within Single/Double Quotes

Recommended: ✖

In this method, to use the percentage value criterion as a string, first, you should convert the percentage value column to a text column using the To_Text Function. We can do that within the Query Data itself.

I am not recommending this method as it has one drawback. This method will only work when you use the percentage value criterion in assignments, not in comparisons as above.

The below Query formula will extract the rows containing the value 95% in column C.

=ArrayFormula(query({A1:B,to_text(C1:C)},"Select Col1,Col2 where Col3='95.00%'",1))

You can’t use comparison operators to filter percentage values like '>50.00%' here.

Other changes in the formula are the use of ArrayFormula and change in column identifiers from A to Col1, B to Col2 and C to Col3.

We should change the column identifiers as above when our Query data is within Curly Brackets or the Query data is the output of another formula.

For example, you can use "Select A" when the Query data is like =query(A1:Z10,"Select. But you should change it to "Select Col1" when the Query data is like =query({A1:Z10},"Select.

I have used the ArrayFormula because the To_Text function is a non-array function. I have used the To_Text in an array (entire column), right?

You can enter the percentage value as a string in cell D1 like '95.00% and use it in Query.

=ArrayFormula(query({A1:B,to_text(C1:C)},"Select Col1,Col2 where Col3='"&D1&"'",1))

You have got two different types of Query formulas to filter a column that contains percentage values in Google Sheets.

Filter a Percentage Value Column Using Filter Function in Google Sheets

The Filter function is the most efficient way to filter data in Docs Sheets. To simply filter a percentage value column, there is no need to use Query.

Use the function Query, if you want to perform data manipulation functions, to change the data order, etc.

If you prefer to use the Filter function, then the formula will be as follows.

Formula 1:

=filter(A1:B,C1:C>50%)

Formula 2:

=filter(A1:B,C1:C>0.5)

Use either of the above two filter formulas.