It’s not so hard to flatten every other column in Google Sheets.

You may use brackets to combine every other column horizontally. Then flatten it!

There are different types of brackets, and here I’m talking about braces (curly brackets).

Here is an example of flattening every other column in Google Sheets (please refer to image # 1 below).

Formula # 1:

=flatten({C5:C10,E5:E10,G5:G10})

The above formula flattens the arrays C5:C10, E5:E10, and G5:G10.

The drawback of the above approach (braces) is it’s only handy when you have a few columns.

Flexible Formula to Flatten Every Other Column in Google Sheets

Here I have the sample data in C4:H10. It’s a price list of items.

We have already discussed the formula in cell J5.

Let’s code a flexible formula to flatten every other column.

Steps:-

1. Find the column number of the first column in the range. Here we can use =column(C4) for that.
2. If the output is an odd number, we will use the ISODD function within FILTER else, ISEVEN.
3. In our case, we can use the below FILTER and ISODD combination formula.

Formula # 2:

=flatten(filter(C5:H10,isodd(column(C4:H4))=TRUE))

It will flatten every other column in Google Sheets.

The formula filters the range C5:H10, i.e., filter(C5:H10,, based on the first-row column number, i.e., isodd(column(C5:H5))=TRUE).

When you have the data in C4:Z10, i.e., in a much larger range, just modify the formula as below.

=flatten(filter(C5:Z10,isodd(column(C4:Z4))=TRUE))

Use ISEVEN instead of ISODD in the above formulas to get the price columns.

Troubleshooting (Blank Rows and Columns in the Flatten Range)

In this troubleshooting section, let me answer some of the common questions you might want to ask.

1) I have blank columns in my range to flatten, which causes blank rows in the result. How do I solve it?

We can solve it as below using another criterion within the formula.

Formula # 3:

=flatten(filter(C5:Z10,isodd(column(C4:Z4))=TRUE,len(C4:Z4)))

The LEN function returns >0 (evaluates to TRUE) or =0 (evaluates to FALSE).

The formula will flatten every other column and exclude the columns with no field label.

2) What about blank rows?

Open the range C5:Z10 (closed) to C5:Z and wrap the formula with a QUERY to remove blank rows.

=Query(flatten(filter(C5:Z,isodd(column(C4:Z4))=TRUE,len(C4:Z4))),"Select * where Col1 is not null")

3) Is replacing ISODD with ISEVEN enough to flatten the price columns?

Of course, that will work. Here you go!

Formula # 4:

=Query(flatten(filter(C5:Z,iseven(column(C4:Z4))=TRUE,len(C4:Z4))),"Select * where Col1 is not null")

But when you flatten every other column in Google Sheets using both ISODD and ISEVEN, the better way is to use a single formula.

Otherwise, you may face issues with mismatching rows. Here is an example.

Formula # 5 (A4):

=Query({flatten(filter(C5:Z,isodd(column(C4:Z4))=TRUE,len(C4:Z4))),flatten(filter(C5:Z,iseven(column(C4:Z4))=TRUE,len(C4:Z4)))},"Select * where Col1 is not null")

Note:-

In the combined formula, if you want to exclude cell range G4:G, remove G4 and H4 (header) values.

Otherwise, mismatching row sizes may occur.

Flattening and Adding Field Labels to Every Other Column in Google Sheets

Just flattening every other column may not be fruitful in some cases, especially when you want to go for lookups.

So you may require to add field labels. Have a look at formula # 3 (Query) above.

Let’s modify that and add field labels (headers).

Formula # 6:

=query(ArrayFormula(split(flatten(filter(C4:Z4,isodd(column(C4:Z4))=TRUE,len(C4:Z4))&"|"&filter(C5:Z,isodd(column(C4:Z4))=TRUE,len(C4:Z4))),"|")),"Select * where Col2 is not null")

Unlike the said formula, here inside FLATTEN, we have used two filters (one for header and the other for records) and combined them.

I’ve used the | delimiter as the separator. The SPLIT splits it and makes two columns.

Further, I have made the required changes in the QUERY where clause.

Similarly, you can modify formula # 4 to flatten every other even column and add row headers.

Regarding formula # 5, replace the “pale pink” highlighted part with the relevant highlighted part from the above formula # 6.

Also, in the query, replace where Col1 is not null with where Col2 is not null.

Since the formula returns three columns, you make sure that there are that many columns for it to expand.

That’s all about how to flatten every other column in Google Sheets.

Thanks for the stay. Enjoy!

Resources

1. How to Move Values in Every Alternate Row to Columns in Google Sheets.
2. How to Sum Every Alternate Column in Google Sheets [Flexible Formula].
3. Dynamic Formula to Select Every nth Column in Query in Google Sheets.
4. How to Partially Flatten a Multi-Column Array in Google Sheets.
5. Array Formula to Multiply Every Two Columns and Total in Google Sheets.
6. How to Highlight Every Nth Row or Column in Google Sheets.
7. A Simple Formula to Unpivot a Dataset in Google Sheets.