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.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
The Paste Special feature in Excel can be used to uniformly adjust values and formulas. This tip shows how powerful this ...
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