1

I'm familiar with vertical filters, but wondered if it can be done so I would only show the the columns that matched my query.

Is it possible to filter horizontally in Google Spreadsheets?

  • No horizontal filters. But you could use TRANSPOSE with Filter view or FILTER or QUERY – TheMaster Sep 17 '17 at 16:30
  • I have closed the older post as a duplicate of this one, because I think the answer here is far better. – jonsca Sep 19 '17 at 23:22
3

The filter command works equally well with rows and columns. Its format is

=filter("two-dimensional array", "1-dimensional true-false array")

The direction of filtering is determined by the orientation of the true-false array. Think of it as sitting on a margin of the 2D array, which can be a horizontal or vertical margin. For example,

=filter(A1:E10, B1:B10 > 2)

keeps only the rows of the range A1:E10 where the entry in B is greater than 2. On the other hand,

=filter(A1:E10, A3:E3 < 10)

keeps only the columns of the range A1:E10 where the 3rd row entry is less than 10.

Query

The =query function is asymmetrical; it only selects rows meeting some conditions. To use it for filtering columns, apply transpose, for example like

=transpose(query(transpose(A1:E10), "select * where Col3 < 10")))

Note that since the transposed array is built "on the fly" (it's not physically present in the spreadsheet), the query string addresses it as Col1, Col2, etc instead of column labels. The query above picks the columns where the 3rd row is less than 10.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.