How to Switch Rows and Columns Using the TRANSPOSE Function in Excel

When building a spreadsheet in Microsoft Excel, you must often decide early on whether to list your data vertically in columns or horizontally in rows. Sometimes, halfway through a project, you realize you made the wrong choice. Perhaps you listed twelve months across the top row, but your data has grown so massive that the spreadsheet is impossible to read without scrolling endlessly to the right. It would be much easier if the months were listed vertically down the side.

You do not need to manually retype hundreds of cells to fix this layout. Microsoft Excel includes a powerful tool specifically designed to instantly flip vertical data to horizontal (and vice versa). This process is called Transposing.

Method 1: The Copy and Paste Special Method (Static)

If you simply want to flip the data once and never worry about the original source data again, the fastest method is using the “Paste Special” menu.

1. Highlight the entire block of data you want to flip (e.g., A1 to D5).
2. Right-click the highlighted area and select Copy (or press Ctrl + C).
3. Click on a completely blank, empty cell in your spreadsheet. This cell will become the new top-left corner of your flipped data. (Do not paste it over the original data, or it will overwrite itself and break).
4. Right-click the empty cell.
5. In the context menu, look at the “Paste Options” section. You will see several small clipboard icons. Hover over them until you find the one labeled Transpose (the icon usually shows two small arrows forming a right angle).
6. Click the Transpose icon.

Excel will instantly paste a perfect copy of your data, but rotated 90 degrees. What was previously in rows is now in columns. You can now safely delete the original, unflipped data.

Method 2: The TRANSPOSE Function (Dynamic)

The Paste Special method is “static.” If you change a number in the original data, the transposed copy will not update. If you need the flipped data to dynamically update whenever the source data changes, you must use the TRANSPOSE formula.

Note: If you are using Microsoft 365 or Excel 2021, this process is incredibly easy thanks to dynamic arrays. If you are using older versions like Excel 2016, the process is slightly more complicated.

For Modern Excel (Microsoft 365 / Excel 2021)

1. Click the single empty cell where you want the new, flipped data to start.
2. Type the formula: =TRANSPOSE(
3. Use your mouse to highlight the original block of data you want to flip.
4. Type the closing parenthesis ) and press Enter.

Excel will automatically “spill” the flipped data into all the necessary surrounding cells. If you edit a number in the original source, this new transposed grid will instantly update to match.

For Older Excel (2019 and older)

Older versions of Excel do not support “spilling.” You must manually highlight the exact destination area before typing the formula.

1. Count the dimensions of your original data. If your original data is 5 columns wide and 2 rows tall, you must highlight a blank destination area that is exactly the opposite: 2 columns wide and 5 rows tall.
2. With that blank destination area highlighted, click into the formula bar at the top of the screen.
3. Type =TRANSPOSE( and highlight the original source data, then close the parenthesis ).
4. Do not press Enter. You must press Ctrl + Shift + Enter simultaneously.

This creates a legacy “Array Formula,” locking the flipped data into the highlighted grid.