When you are building a spreadsheet in Microsoft Excel, you might suddenly realize that your data is laid out incorrectly. For example, you might have typed the months of the year vertically down a single column, but you now realize they need to be displayed horizontally across a single row to make room for your sales data. Instead of manually retyping twelve different cells and trying to copy over the formatting, you can use the Transpose feature to instantly rotate your data 90 degrees.
How to Transpose Data Using Paste Special
The fastest and most reliable method to switch rows to columns (or columns to rows) is by using the Paste Special menu.
- Open your spreadsheet in Microsoft Excel.
- Use your mouse to highlight the entire range of data you want to rotate (e.g., highlight cells A1 through A12).
- Copy the data by pressing Ctrl + C on Windows or Command + C on a Mac. You will see a moving dashed line appear around the copied cells.
- Click on a new, empty cell where you want the rotated data to begin. Warning: Do not click inside your existing data range, or Excel will throw an error.
- Right-click the empty cell.
- In the context menu that appears, look under the “Paste Options” section for an icon showing a small clipboard with a right-angle arrow pointing down and to the right. Click this Transpose icon.
Excel will instantly paste the data into the new location, perfectly converting your vertical column into a horizontal row. All of your original fonts, colors, and borders will be carried over seamlessly.
Once you verify the transposed data is correct, you can safely delete the original, vertically aligned data.
How to Transpose Data Using a Formula
The Paste Special method creates a static, one-time copy of your data. If you change a number in the original vertical list, the horizontal list will not update. If you need the rotated data to remain dynamically linked to the original data, you must use the TRANSPOSE formula.
- Click an empty cell where you want the new, dynamically linked data to begin.
- Type
=TRANSPOSE( - Use your mouse to highlight the original range of data (e.g., A1:A12).
- Type the closing parenthesis
)and press Enter.
If you are using a modern version of Excel (Microsoft 365 or Excel 2021), the formula will automatically “spill” across the required number of cells. Now, if you change a value in the original source column, the transposed horizontal row will instantly update to match it.