Horizontally filter / filter columns?

Question

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?

Answer 1

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.