Table of Contents
Often you may want to move every other row to a column in Excel.
Fortunately this is easy to do by using the following formulas:
Formula 1: Move All Even-Numbered Rows to Column
=IF(ISEVEN(ROW(B2)),B2,"")
Formula 2: Move All Odd-Numbered Rows to Column
=IF(ISODD(ROW(B3)),B3,"")
The following example shows how to use both of these formulas in practice.
Example: How to Move Every Other Row to Column in Excel
Suppose we have the following dataset that contains information about sales of new and old versions of various products:
Suppose we would like to move every other row from the Sales column into column C so that only products with “Old” in the name appear in the new column.
We can type the following formula into cell C2 to do so:
=IF(ISEVEN(ROW(B2)),B2,"")
We can then click and drag this formula down to each remaining cell in column C:
Notice that every other row from the Sales column has been moved into the Old Product Sales column.
This formula uses an IF function to check if the row number of cell B2 is even. If it is, then the formula returns the value from cell B2. If it is not, then the formula returns a blank.
Suppose instead we would like to move every other row from the Sales column into column C so that only products with “New” in the name appear in the new column.
We can type the following formula into cell D2 to do so:
=IF(ISODD(ROW(B3)),B3,"")
We can then click and drag this formula down to each remaining cell in column D:
Notice that every other row from the Sales column has been moved into the New Product Sales column.
This formula uses an IF function to check if the row number of cell B2 is odd. If it is, then the formula returns the value from cell B2. If it is not, then the formula returns a blank.