How to Move Values in Every Alternate Row to Columns in Google Sheets

You can’t use Transpose to move values in every alternate row to columns. It does not support skipping rows. So what’s the solution?

To move value in every second row, I have three different solutions.

All formulas are array-based. So you can set it and forget in one or two cells.

You can move values in every alternate row to columns using Query and IF or a combination of formulas.

Among the formulas, the IF function-based formula is the easiest one.

Let me start with an example screenshot.

I’ve used a Query formula and its Skipping clause to get the below response.

You can find that formula and detail in this post’s last part.

Example to Move Alternate Row to Columns in Google Doc Sheets

IF Function to Move Values in Every Alternate Row to Columns

Even though the IF function is the simplest one to move value, it has one limitation.

The formula requires some identifiable values in column A.

This formula returns all the dates against the “Project Start.”

=ArrayFormula(if(A2:A11="Project Start",B2:B11,))

The below formula returns all the dates against the “Project End.”

=ArrayFormula(if(A2:A11="Project End",B2:B11,))

I have applied the formulas in cells D2 and E1. See the below image.

IF statement to move alternate row values to columns

Using ISEVEN, ISOOD, and ROW Combo

The following ISEVEN and ISODD approach is more flexible. It doesn’t depend on any other columns like the IF-based formula above.

The first set of rows:

=ArrayFormula(IF(ISEVEN(ROW(B2:B11)),B2:B11,""))

The second set of rows:

=ArrayFormula(IF(ISODD(ROW(B2:B11)),B2:B11,""))

Apply the first formula in cell D2 and the second formula in cell E1. Just refer to the above screenshot.

Note:- Here, you can alternatively use two FILTER formulas to move values in every alternate row to columns,s. It removes blanks too.

Formula # 1:

=filter(B2:B11,iseven(row(B2:B11)))

Formula # 2:

=filter(B2:B11,isodd(row(B2:B11)))

Move Values in Every Other Row Using Query in Google Sheets

Do you know how to use the Skipping clause in Google Sheets Query? If not, here it’s.

I am slightly deviating from the topic to make you understand how to control the Query function to move values in every other row to a column.

Similar to the LIMIT clause, you can use the SKIPPING clause in Query to skip a certain number of Rows in Google Sheets.

Similar: Three Different Ways to Limit Number of Rows in Google Sheets Query

For example, you can skip 1 row, 2 rows, 3 rows, etc., in a range.

The Query formula used in the below example returns the values after skipping every alternate row, which means every 1 row.

=query(A1:A10,"Select A skipping 2")
Query Skipping in Google Sheets 1

That means you should put the number 2 in the Query Skipping clause to skip every alternate row.

In other words, if you want to skip 2 rows, put 3 in the skipping clause as below.

Query Skipping Example 2

Now back to the topic. Here I am using # 2 in the Query skipping clause.

Here also, there are two Query formulas. But I’ve combined them. So you only need to apply the formula this time in one cell.

={query(B2:B,"Select B where B is not null skipping 2",0),query(B3:B,"Select B where B is not null skipping 2",0)}
skip every other row to columns in Google Sheets with the help of Query

I think Query is the more flexible one among the provided three formulas.

It’s up to you which one to choose.

Thanks for the stay. Enjoy!

Related Resources

  1. How to Move Each Set of Rows to Columns in Google Sheets.
  2. How to Move New Lines in a Cell to Columns in Google Sheets.
  3. Move Single Column to Multiple Columns Using Hlookup in Google Sheets.
  4. Move Time In and Time Out to the Same Row in Google Sheets.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

2 COMMENTS

  1. What if you have a list and you want to auto deposit it into another column – but skipping every 4th entry? So I have a list in Column A and want to transfer it to Column B but with a blank cell in between each row. Almost as if it was the reverse of =query(A:A, "select A skipping 2")

    A quick response would be greatly greatly appreciated. Thank you very much!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.