How to Transpose Data in Excel:
Turn Rows into Columns (vice versa)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Reorganizing data in a spreadsheet can be a pain.

Especially when you have to make big changes that a simple copy-and-paste won’t fix.

One of the common tasks that fall into this category is transposing: ie. turning rows into columns (or columns into rows).

Without any special help from Excel, that takes a long time!

But Excel has some built-in transposing capabilities that make the process easier 🙂

Let’s take a look at turning rows into columns (and vice versa) in Excel, as well as a few of the things you’ll need to keep in mind when you do so.

If you want to really use Excel’s power for displaying rows and columns, check out pivot tables.

They’re one of Excel’s best features.

Kasper Langmann, Co-founder of Spreadsheeto

Defining transposition

Before we start, let’s make sure we know exactly what transposing is.

In short, transposing is taking a row and turning it into a column, or a column and turning it into a row. Some people call this “rotating” a row or column.

The leftmost cell in a row will always become the topmost cell in a column, and the topmost cell in a column will always become the leftmost cell in a row.

This ensures that column and row labels are always in the right place.

There are a couple ways we can transpose data in Excel. Before we get into them, grab the free sample workbook so you can try it yourself!

Get your FREE exercise file

You can just read the guide below, but it’ll be easier to learn about transposing if you get some experience yourself.

Download the free exercise file and follow along!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Transposing with copy and paste

The first method of transposing is very simple.

Open up the first sheet in the workbook; you’ll see a list of countries along with monetary values. Let’s turn those columns into rows.

First, select the values in the Country column.

select-first-column

Next, hit CTRL + C to copy the values in the column.

Then click into an empty cell (we’ll use cell A13). Right-click on that cell.

context-menu

Under the Paste Options header, select the Transpose option (it looks like this):

paste-transpose-icon

When you mouse over this option, you’ll see a preview of the transposed data. Click Transpose (the icon highlighted earlier), and you’ll get your new row:

transpose-pasted

Do the same thing with the other column, and you’ll have turned both columns into rows.

To get them to the top of the sheet, delete the cells above by selecting the rows, right-clicking, and clicking Delete in the context menu:

delete-rows

That was pretty easy, wasn’t it?

Wouldn’t it be easier to transpose both columns at the same time? We can do that too.

To see how it works, we’ll select the values in both rows:

select-rows

Again, press CTRL + C to copy the data.

Then, click into an open cell (we’ll use A4), right-click, and select Transpose:

transposed-column

Both rows have now been turned into columns!

As we’ll see in a moment, this method doesn’t always work. But when you’re working with a simple set of data, it’s a very fast way to transpose.

Transposing tables and formulas

While you can transpose multiple rows or columns with the Paste > Transpose command, there are a couple situations in which this won’t work.

The first is if you’d like your transposed data to automatically update when the original data is changed (remember that Paste > Transpose only pastes values and not formulas).

The second is if you’re trying to transpose a table.

In these cases, you’re going to need the TRANSPOSE function. It’s a very simple function:

The syntax of the TRANSPOSE function

=TRANSPOSE(array)

Array is the array (the set of data) you’d like to transpose.

It seems simple, but there’s one thing that might trip you up. Because this is an array function, you can’t just hit Enter when you’re done. You need to press CTRL + Shift + Enter.

All array functions need this particular key combination.

On the second sheet in the example workbook, you’ll see a table that contains a list of names and scores.

If you’re not sure why you’d want to format a series of data as a table in Excel, keep an eye out—we’ll be talking about it in an upcoming post!

Kasper Langmann, Co-founder of Spreadsheeto

If you select part of the table, you can use the Paste > Transpose option. If you want to transpose the entire table, however, that option won’t be available:

no-transpose-option

Instead, you’ll need the TRANSPOSE function.

The first thing to do when using the TRANSPOSE function is a bit different from other functions; you need to select a range of cells that matches the exact size of the table you’re transposing.

For our example, we’re transposing a table of 2 columns and 11 rows, so we’ll select an area of 11 columns and 2 rows:

pre-transpose-array

After you’ve selected the cells, and without clicking into a specific cell, start typing the formula with “=TRANSPOSE(“:

start-transpose-formula

Then, fill the array parameter by clicking and dragging to select the table:

transpose-select-array

Close off the parentheses, but don’t hit Enter. Remember, because this is an array function, we need to hit CTRL + Shift + Enter instead.

If you hit Enter, you’ll get a #VALUE! error. The right key combination, however, results in a successful transposition:

transpose-function

As a side note, you’ll know that a formula has successfully been run as an array formula if there are curly brackets around it in the formula bar.

Kasper Langmann, Co-founder of Spreadsheeto
transpose-select-array

The table has now been transposed, and it will still behave as a table.

Try changing a value in the original data—you’ll see that it’s automatically updated in the transposed range.

This is useful when you’re creating reports or transposing a range of data onto a different sheet in your workbook. If you use Paste > Transpose, this won’t happen.

One thing to be aware, of though.

Transposing with absolute references

When you transpose, either using Paste > Transpose or the TRANSPOSE function, cell references and formulas will be updated.

In most cases, you’ll have relative references in your cells and formulas.

But that’s not always going to work out how you want it to. Let’s take a look at an example.

On the third sheet of the example workbook, we have a very simple table of money owed, an interest amount, and an extra fee.

The Total column contains the amount owed, plus interest, plus the extra fee from cell G1.

Let’s try transposing that by copying and pasting.

transpose-relative

As you can see, the totals don’t match up. If you click on one of the totals, you’ll see that instead of adding the extra fee from cell G2, it’s trying to add it from cell A13.

relative-reference-error

This is because of the relative cell reference.

Instead of interpreting the SUM command as adding cell G1 for the extra fee, Excel instead interprets it as summing the cell that’s three to the right and one up, or three to the right and two up, and so on.

When we transposed the data, we also transposed the reference.

So instead of looking at G1, our new totals are looking at A13.

Fortunately, there’s an easy way to fix this: with absolute references.

Let’s go back to the original Total column and make some adjustments. The first total is derived using this formula:

=SUM(A2,G1,(A2*B2))

By changing G1 to $G$1, we can tell Excel that we mean that specific cell, and not the cell three to the right and one above the cell we’re working with.

absolute-reference

After updating each of the formulas in the Total column to contain absolute references, we can try transposing again:

transpose-absolute-reference

As you can see, it worked this time. Excel knows that our extra fee is in cell G1, and that it’s always going to be in G1.

You don’t need to worry about relative and absolute references using the TRANSPOSE function, because it automatically pulls the values from the previous location.

To see how this works in this particular case, we can use the TRANSPOSE function to transpose these columns into rows.

Use the method above to transpose this data with the TRANSPOSE function (I placed it just below the copied and pasted version).

Now, try changing one of the interest values in the original data. I’ll change the 0.07 to 0.09 and hit Enter.

updated-transpose

The totals have now changed in the original data as well as the new data created by the TRANSPOSE function.

Notice that the values didn’t change in the data that was copied and pasted. This is because transposing with this method only copies values.

Kasper Langmann, Co-founder of Spreadsheeto

Try changing the extra fee to see what happens to the values in both tables!

Wrapping things up…

Now that you’ve seen a few ways to transpose your data in Excel and you know what to watch out for when you do it, you’re ready to turn rows into columns and vice versa at will!

Just remember that if you’re getting strange results, there’s a good chance you’re using a relative reference where you should be using an absolute one. That should be the first thing you check.

2019-04-20T16:09:37+00:00