To select every second or nth column in Query in Google Sheets you can use the Select clause. It’s easy if the number of columns in your dataset is limited like 5-10 columns.

If there are a large number of columns it’s a tough task to code the Query formula without making typos. You must use dynamic column reference in Query select clause then. You can find why I am telling this below.

Must Check: Learn Google Sheets Query with Examples.

In this example, I have 10 columns and selected every 3rd column with Query.

=query(A1:J,"Select C,F,I")

The same formula will be easier if you use column numbers as column identifiers.

=query({A1:J},"Select Col3,Col6,Col9")

Then what about the range A1: AZ? You may end up with a Query formula like this, right?

=query({A1:AZ},"Select Col3, Col6, Col9, Col12, Col15, Col18, Col21, Col24, Col27, Col30, Col33, Col36, Col39, Col42, Col45, Col48, Col51")

There are a total of 52 columns in the range A1: AZ. The above Query selects every 3rd column in that range. When you add more columns or modify the range, sometimes you may find problems with your formula.

To sort out this, I have a dynamic formula to select every nth column in Query. With the help of the said formula, you can automate the column numbers in select clause in Query.

In that, you can easily change the nth column to your choice like select every 2nd column, 3rd column, 4th column and so on.

Select Every nth Column in Query in Google Sheets (
Dynamic Formula)

Take a look at the last Query formula above. In that, we want to dynamically select the column numbers.

So in that formula up to the below part would be the same.

Part 1 Formula:

=query({A1:AZ},"Select

We want to find a formula to generate every nth column.

Col3, Col6, Col9, Col12, Col15, Col18, Col21, Col24, Col27, Col30, Col33, Col36, Col39, Col42, Col45, Col48, Col51

The below formula generates that nth columns to use in Query.

Part 2 formula:

=ArrayFormula(join(", ",text(query(transpose({column(C1:AZ1);mod(column(C1:AZ1)-column(C1),3)}),"Select Col1 where Col2=0"),"Col0")))

Then the final Query formula will be like below.

Part 1 & Part 2 Formula:

=query({A1:AZ},"Select "&ArrayFormula(join(", ",text(query(transpose({column(C1:AZ1);mod(column(C1:AZ1)-column(C1),3)}),"Select Col1 where Col2=0"),"Col0"))))

To select every 3rd column in Query, you can use the above formula. To justify the title of this post, i.e., Dynamic Formula to Select Every nth Column in Query, I must explain how to modify this formula.

Select Every nth Column in Query, the Dynamic Way

In the above formula, the select clause selects Col3, Col6, Col9 etc. To change this to start from Col1, Col4, Col7 likewise, change the cell reference C1 in the formula to A1. It appears thrice in the formula.

How to change the every third column to every second column?

It’s controlled by the divisor in the MOD formula. It’s at present set to 3. Change that to 2 or any other number.

Conclusion

This time I didn’t go for any formula explanation. My aim was to help you select dynamic n columns in Google Sheets Query.

I hope I have succeeded in that aspect. If you ask me about the formula logic, it’s quite simple to understand.

With the help of the MOD formula, I have populated an array of numbers and in that the nth column numbers are 0. Similarly, I have generated column numbers in sequential order using the function COLUMN.

I have used the TRANSPOSE to form a two column array in that the first column contains the sequential numbers and the second column the MOD output.

Then filtered the first column if the second column contains 0. That is the nth columns. Then added the text “Col” with the help of the Text and Join functions.

Related Reading:

• How to Get Dynamic Column Reference in Google Sheets Query.