When you need to move data from rows to columns, you need these two formulas.
Moving Every Other Row to a Column in Excel
I spend a lot of time in Google Analytics and a lot of time building presentations with charts using the data from GA.
Of course, there are times when we need to compare YoY or MoM and GA makes that very easy to do. However, when you export that data, it’s not in the best format to be turned into a chart or graph.
Here’s an example of what a Google Analytics data export comparing two periods of data looks like.
The Problem
We don’t want two rows of data representing week one. We’d prefer to have two columns of data representing this.
Here’s an example of where we want this to go. We want to move every other row from Column C to Columns D and E.
The Solution
To solve this, we’ll use the Excel formulas ISODD and ISEVEN combined with the ROW function. Here we go.
We’ll assume that our data runs down multiple rows in Column C. Column B is listing our time interval. Our new data will live in Columns D and E.
We want to move every other row to it’s own column.
In the cell D2, write the following Excel formula.
=IF(ISEVEN(ROW(C2)),C2,””)
What this is saying in plain English is “If the row that cell C2 is in is Even, then place the value of cell C2 here. Otherwise, leave it blank.”
Next, in cell E2, write the following Excel formula.
=IF(ISODD(ROW(C3)),C3,””)
What this is saying in plain English is “If the row that cell C3 is in is Odd, then place the value of cell C3 here. Otherwise, leave it blank.”
Next, copy these formulas all the way down your data set in columns C and D. You can do this by dragging the corner of the first cell all the way down. Or you can use this Excel keyboard shortcut to select the cells first and then write the formula.
Removing the Blank Rows
This method will leave you with every other row being blank in columns D and E. To remove them, it’s very simple.
Insert a new row at the top of your list and give each column a heading name.
Click once on cell E1 and then click on the Filter button in the Excel toolbar.
In the filter settings for cell E1, uncheck the box that says “blanks”.
Just like that, all of the blank rows disappear. If you select your resulting list and copy it somewhere else, you won’t get the blank rows.