Software

Transpose Excel data from rows to columns, or vice versa

When you need to flip-flop data in an Excel worksheet, don't waste time doing it manually. Excel offers a handy Transpose option that will quickly take care of the task.

Here's a tip that eliminates the need to rekey data. Suppose you've entered your data with three column headings running across Row 1 and four row headings running down Column A, like the ones shown in Figure A.

Figure A

transpose

After working with the data for a while, you decide you'd rather have the current set of row labels (months) running across the columns. Whatever you do, don't even think about rekeying the data.

You'll find the best solution on the Paste Special menu. Start by selecting and copying your entire data range. Click on a new location in your sheet, then go to Edit | Paste Special and select the Transpose check box, as shown in Figure B. Click OK, and Excel will transpose the column and row labels and data, as shown in Figure C.

Figure B

paste special

Figure C

insert data

Note

You aren't limited to using the Paste Special | Transpose option to rearrange multiple rows and columns of data. It works just as well when you need to turn a single row of labels into a column, or vice versa.

36 comments
ofeore
ofeore

It worked! Thank you so much!

excelinexcel
excelinexcel

The transpose function can also transpose Excel data from rows to columns, or vice versa. TRANSPOSE must be entered as an array formula. To achieve this, finish by pressing CTRL + SHIFT + ENTER.

blackweaver
blackweaver

the method also works for openoffice.org 3.1

smackdab2
smackdab2

OK, thats great, now lets take it to the next level. suppose I have two columns of coma seperated values fname, Betty lname, Bing ext, 325 fname, Jeff lname, Wins ext, 334 and so on for about 60 extensions. I would like to convert that data to fname, lname, ext Betty, Bing, 325 Jeff, Wins, 334 How would I do that conversion and keep all the first names, last names, and extensions in the correct order?

brookslewis
brookslewis

I wish I had read this article a few days ago. I would have finished my project sooner. Thank you for this info.

kwrickman
kwrickman

I have been searching and searching for a way to do this and here it was right under my nose. Thanks a millaion saved hours. Karl

320vu50
320vu50

There is no EDIT selection as shown on the figures in this article. I assume it won't work in this new Excel.

richlane
richlane

This is a great tip, but it leaves the data as fixed values. If that's what you want, fine, but frequently I need to have live data transposed. For this I use the (what else) TRANSPOSE array function. Excel Help explains very well how to use it.

ericnumbers
ericnumbers

is there any way to this when the cells contain formula. Using the method listed the cell reference changes in the formula.

nondrakhali
nondrakhali

I was just using this yesterday and encountered a problem - i wanted to transpose a set of cells within a spreadsheet and the error message said first that i needed to select the range of cells, and when I did that, it came back with another error message that the cells did not match...?

jgtechie
jgtechie

Excellent tip, thanks for the post.

lady_jt
lady_jt

This is truly a great timesaver, when you are not sure how you want to data in, to be able to change it without all the extra keystokes.

sacbaker
sacbaker

I worked last night trying this and, somehow you answered my problem when I opened my email from you. Thanks a million.

debsontarget
debsontarget

Thanks a lot! One More reason why I LOVE Excel!

WayneAndersen
WayneAndersen

I usually open a new worksheet to paste the transposed data. Normally you don't really need to see the data both ways on one worksheet.

john
john

Great time saver, thanks.

hutchkl
hutchkl

I have used this type of action with other functions in Excel it works very well.

ChrisEvans
ChrisEvans

One of those hidden gems which can save a massive amount of time in editing!

Shakeeb.Ullah
Shakeeb.Ullah

Just right click and then press Paste Special. or Click the Paste Button to reveal Paste Special option.

nzimmerman67
nzimmerman67

you just have to use absolute references. put a $ in front of either the row or column label(s) in your formuls: $e$7+$d$4 or press F4 to have excel do it for you. this will ensure that the references don't change when transposing.

thinkrrr
thinkrrr

This is a great tip, thanks!!! I am very glad that this article caught my eye in my email inbox, considering I am working on a project that needs to transpose a large amount of data and was trying to think through how to do it programatically! I've used the transpose feature for one row of data, but I've never considered that it can be used for a set of data! Thanks again!

Kailashkumar
Kailashkumar

U should select appropriate cells and have to place the cursor in any empty cell and right click and select paste special.. I think u can surely do that

kbashir
kbashir

Make sure that you dont select an area. Just put the cursor in the cell from where you want to start and press transpose. Hope this helps.

320vu50
320vu50

When I make the "Paste Special" selection O get a pop-up box "Paste Special" There is an already clicked button (that can't be unclicked" and a box with several selections. Paste As: Microsoft Word Document Picture HTML Unicode Text Text and a section below that explains each of the above selections. This does not resemble any of the items shown in the article. That is what caused me to write the comment. This method was apparently NOT intended to work on Office Excel 2007. I am asking, "Is there is a method that will do this in this program?"

ericnumbers
ericnumbers

I am Transposing 100s of cells with formulas and was looking for away that I would not have to fix each cell

charleswdavis6670
charleswdavis6670

For an excellent presentation on the use of the Transpose function in Excel 2007, open Excel, either click on the help (question mark) or simply press the F1 key. Search for "rearrange (transpose) data" (without the quotes). Follow the instructions. The F1 key has never broken anything.

Brummy
Brummy

I am using 2007. I am not getting a problem when I follow the tip's instructions. Select the original range of cells; Ctrl + C to copy; right-click on an empty cell and select Paste Special; select Transpose; OK. Works every time. I've done this in both the comptibility *.xls mode and as an *.xlsx file I realise that's not answering your question, but I thought I'd let you know that it does work in 2007 from my experience. I have not seen the paste as MS Word, HTML, etc. I have been able to paste as a picture from the ribbon's paste button.

Editor's Picks