Efficient Techniques for Converting Excel Rows Into Columns and Back

Quick Links

• The Static Method
• Transpose Data with the Transpose Formula
• Transposing Data with Direct References

If you started to enter data in a vertical arrangement (columns) and then decided it would be better in a horizontal one (rows), Excel has you covered. We’ll look at three ways to transpose data in Excel.

The Static Method

In this method, you can quickly and easily transpose data from a column to a row (or vice versa), but it has one critical drawback: It’s not dynamic. When you change a figure in the vertical column, for example, it won’t automatically change it in the horizontal one. Still, it’s good for a quick and simple fix on a smaller dataset.

Highlight the area you want to transpose and then press Ctrl + C on the keyboard to copy the data.

Right-click the empty cell where you’d like to display your results. Under “Paste Options” click “Paste Special.”

Check the box next to “Transpose” and then press the “OK” button.

Transpose Data with the Transpose Formula

This method is a dynamic solution, meaning we can change the data in one column or row and it will automatically change it in the transposed column or row, too.

Click and drag to highlight a group of empty cells. In an ideal world we’d count first, as the formula is an array and requires you to highlight exactly the number of cells you need. We’re not going to do that; we’ll just fix the formula later.

Type “=transpose” into the formula bar (without quotes) and then highlight the data you want to transpose. Instead of pressing “Enter” to execute the formula, press Ctrl + Shift + Enter instead.

As you can see, our data has been cut off because we didn't select enough empty cells for our array. That's okay. To fix it, click and drag the box at the bottom, right-hand side of the last cell and drag it out further to include the rest of your data.

Our data is there now, but the result is a little messed up because of our lack of precision. We’re going to fix that now. To correct the data, just go back to the formula bar, and press Ctrl + Shift + Enter one more time.

Transposing Data with Direct References

In our third method of transposing Excel data we’ll use direct references. This method enables us to find and replace a reference with the data we want to display instead.

Click an empty cell and type in a reference and then the location of the first cell we want to transpose. I’m going to use my initials. In this case, I’ll use bcA2.

In the next cell, below our first one, type in the same prefix and then the cell location to the right of the one we used in the previous step. For our purposes, that would be cell B2, which we’ll type in as bcB2.

Highlight both of these cells and drag the highlighted area out by clicking and dragging the green box at the bottom right of our selection.

Press Ctrl+H on your keyboard to bring up the “Find and Replace” menu.

Type your chosen prefix, “bc” in our case (without quotes), into the “Find what” field, and then “=” (without quotes) into the “Replace with” field.

Click the “Replace All” button to transpose your data.

You might be wondering why we didn’t just add “=A2” to the first empty cell and then just drag it out to autofill the rest. The reason for this is due to the way Excel interprets this data. It will indeed autofill the cell next to it (B2), but it will quickly run out of data because C3 is an empty cell and Excel reads this formula from left to right (because that’s the way we’re dragging when transposing our data) instead of top to bottom.