Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Quickly Transposing Cells.
You probably know the feeling—you start creating a worksheet, get a good way into it, and realize that you should have made your columns into rows and your rows into columns. In other words, you want to turn your data by 90 degrees and continue working with the sheet.
Fortunately, Excel provides an easy way to accomplish this very task. In Excel's terminology, this process is known as transposing data. To transpose your data, follow these steps:
Notice that in step 2 you must use the copy command (Ctrl+C) rather than the cut command (Ctrl+X). This is because you can't choose Paste Special from the Edit menu when you cut information. For this reason, you may want to copy information from one worksheet (steps 1 and 2) and paste it into another (steps 3 through 5). You can play with this method of pasting and select the method that is best for you.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6248) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Quickly Transposing Cells.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
Excel's Paste Special command is used quite a bit. If you want to create some shortcuts for the command, here's some ways ...
Discover MoreWhen working with large numbers, you may need a way to quickly divide a range of those numbers by a specific value. ...
Discover MoreIf you need to update a bunch of values in a worksheet by a standard amount, you can use the Paste Special feature of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2026-03-16 08:02:23
jamies
From annoyances doing that in the past,
Excel may adjust some references and not adjust others -
So be careful to check
And there are also
OFFSET
ADDRESS
INDEX
INDIRECT
LOOKUP
to check
There are options to adjust what Excel does when pasting
And
I often resort to changing formulas to text -
Replace "=" with "£$%^" do the copy/move and then change them back -
but remember that the pre-paste format of a cell, as well as the format in the source
can have annoying effects on the paste result.
specially when the destination is set as a date, time, or text format, and the source wasn't
or the source is set as a date, time, or text format, and the destination wasn't
Also - check on the cells that had/have currency settings too - especially if the data is not just 2 decimal places
And ... ... there is also the limited number of columns for the rows.
Spill can also be a problem - especially as there are functions that produce 2 column results - the one in the function cell, and the other in the cell to the right of that .
2016-12-04 22:30:48
David
I knew that, but I went a long time before I discovered the transpose function which does the same thing but means if you use the same data in rows in one place and columns in another you only have to update the data in one place.
What I sometimes find myself wanting to do is present the transposed data in the reverse order. Is there a way to do that easily?
2016-05-16 19:24:03
Thomson
If you will need to transpose similar data a lot (e.g. Daily/Weekly/Monthly report) , you can setup a PowerQuery for this.
Just load it into Power Query -> Transform -> Transpose.
The Pro is you can just refresh the queue if data always located in same location.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2026 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments