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.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
If you need to update a bunch of values in a worksheet by a standard amount, you can use the Paste Special feature of ...
Discover MoreThe 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 MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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 © 2025 Sharon Parq Associates, Inc.
Comments