30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Change Column to Row (Vice Versa) in Excel
By Puneet Gogia (Microsoft MVP)

How to Change Column to Row (Vice Versa) in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

To turn columns into rows, use =TRANSPOSE(A1:B6). In Excel 365 and 2021 you just type it into one cell and press Enter — the result spills across automatically.

On Excel 2019 and earlier you have to select the destination range first, with the dimensions reversed (six rows by two columns becomes two rows by six columns), then enter the formula with Ctrl+Shift+Enter.

The formula stays linked to the source, so editing an original cell updates the transposed copy; to break that link, copy the result and paste as values (Ctrl+Alt+V → V).

If you don’t want a live formula at all, the quicker route is to copy the range, right-click where you want it, and choose Paste Special → Transpose (Ctrl+Alt+V → E), which pastes static values in one step. And if the data is a proper table you’ll be reshaping repeatedly, Power Query’s Transpose does it as a refreshable step.

In Excel, if you want to change columns to rows, you can use the TRANSPOSE function. With this function, all you need to do is select a range with the same number of cells where you want to add the changed data, refer to the range, and use CONTROL + SHIFT + ENTER. In Microsoft 365 and Excel 2021 or later, you can simply enter the formula in one cell and press Enter.

In the following example, you have a list of names and ages. And we need to change these two columns into rows.

list-of-names-and-ages
Change Columns To Rows / Excel Formula

Steps to Change Columns to Rows

  1. First, select the range D1:I2 (two rows and six columns, the opposite of A1:B6) and enter the TRANSPOSE function.
  2. Next, refer to the range A1:B6.
  3. After that, type the closing parenthesis.
  4. In the end, press and hold CTRL + SHIFT and press ENTER.
transpose-function-columns-to-rows
=TRANSPOSE(A1:B6)

As you can see in the above example, we have successfully converted our columns into rows.

To understand how the TRANSPOSE function works, you need to understand that it takes the array (range) you specify and converts vertical cells into horizontal cells and vice versa.

But there is one thing that you need to take care of: in Excel 2019 and older versions, you need to select the same number of cells the opposite way (rows become columns), and you need to use CONTROL + SHIFT + ENTER to make this function work. In Microsoft 365 and Excel 2021 or later, TRANSPOSE spills the result on its own, so you just press Enter.

Here’s one more thing: TRANSPOSE works in a dynamic way, so once you change a value in any of the original cells, that value also changes in the transposed range.

And if you want to change the transposed range into values and don’t want to connect it with the original values, you can use Paste Special ➜ Values: select the range, press Ctrl + C, and then press Ctrl + Alt + V, V and Enter.

Change Columns to Rows with Paste Special

If you don’t need a formula, Paste Special can do it in one go, and the result isn’t linked to the original data:

  1. First, select the range A1:B6 and press Ctrl + C to copy it.
  2. After that, click on the cell where you want the rows to start, like D1.
  3. Next, press Ctrl + Alt + V to open Paste Special, tick the Transpose box (or press E), and click OK.

Turn a Column into a Single Row with TOROW

In Microsoft 365, there’s also the TOROW function. It puts all the values of a range in one row, even when the range has more than one column. The second argument 1 tells it to skip blank cells:

=TOROW(A2:A6,1)

Common Problems with TRANSPOSE

  • Blank cells show 0: TRANSPOSE returns 0 for empty cells. Join an empty string to the range to keep them blank, as in the formula below this list (note that numbers then become text).
  • #SPILL! error: in Microsoft 365, the cells where the result needs to go must be empty. Clear them, and the result appears.
  • Can’t change part of an array: in older versions, you can’t edit or delete one cell of the transposed range. Select the whole range to change or delete it.
=TRANSPOSE(A1:B6&"")

Frequently Asked Questions

Does TRANSPOSE keep the formatting?

No. TRANSPOSE only returns the values. Paste Special with Transpose copies the formatting as well.

Can I change rows to columns the same way?

Yes. TRANSPOSE and Paste Special work both ways. For a row to a single column in Microsoft 365, use the TOCOL function.

Why does my TRANSPOSE formula show only one value?

In Excel 2019 and older versions, you pressed Enter instead of Ctrl + Shift + Enter, or you selected only one cell. Select the full output range, type the formula again and press Ctrl + Shift + Enter.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment