How can I move every other row in an Excel spreadsheet to a new column?

By /

This method involves rearranging the data in an Excel spreadsheet by moving every other row to a new column. To do this, one can use the copy and paste function or the “transpose” feature in Excel. This process allows for a more organized and visually appealing layout of data, making it easier to analyze and interpret. By following these steps, the data can be rearranged in a structured manner, improving the overall presentation and organization of the spreadsheet.

Excel: Move Every Other Row to Column

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:

Excel move every other row to column

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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel: Move Every Other Row to Column
  2. How can we find the last column with data in an Excel spreadsheet?
  3. How do I get rid of the borders in my Excel spreadsheet?
  4. How to move a column to the front in a Pandas DataFrame?
  5. How do I use IMPORTRANGE within the same spreadsheet?
  6. How can I use Excel to calculate the sum of data based on specific column and row criteria?
  7. VBA: Find Last Used Row, What is the last row of my data set?
  8. How do you compare two dataframes row by row?
  9. AMENITY MOVE
  10. How to Move File from One Folder to Another in R