Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** Please Note:** This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later),

Excel offers many different ways to paste information that you have copied. You can see these different methods when you choose the Paste Special option from the Edit menu. Two of the most popular pasting methods are transposing and linking.

This turns the orientation of your copied data ninety degrees. If you had previously copied five cells from a column, they are pasted as five cells in a row.*Transpose.*You do this by choosing Paste Link from the Paste Special dialog box. It causes Excel to paste references to the original data instead of copied values. When you change the original data, the information is automatically changed in the place where you did the Paste Link.*Linking.*

Unfortunately, it seems that these two options are mutually exclusive. If you select the Transpose option, the Paste Link button is grayed out so you can no longer select it.

There are two ways you can get around this. One involves modifying the pasting process, and the other involves the use of a formula. The first method is as follows:

- Copy the data you want to transpose and link, as you normally would.
- Select any cell in an unused worksheet.
- Choose Paste Special from the Edit menu. This displays the Paste Special dialog box. (See Figure 1.)
- Click on the Paste Link button. Your information is pasted, as a link, in the worksheet. Make sure it remains selected.
- Press
**Ctrl+H**to display the Replace dialog box. (See Figure 2.) - In the Find What area, type an equal sign.
- In the Replace With area, type a pound sign followed by an equal sign.
- Click on Replace All. All the information you just pasted is essentially converted from formulas into text. Make sure it remains selected.
- Press
**Ctrl+C**to copy the range to the Clipboard. - Select the cell where you want the final transposed and linked range to reside.
- Choose Paste Special from the Edit menu. This displays the Paste Special dialog box.
- Choose the Transpose check box.
- Click on OK. The information is pasted, but it is transposed into the final location. Make sure it remains selected.
- Press
**Ctrl+H**to display the Replace dialog box. - In the Find What area, type a pound sign followed by an equal sign.
- In the Replace With area, type an equal sign.
- Click on Replace All. All the information you just pasted is converted back to formulas.

** Figure 1.** The Paste Special dialog box.

** Figure 2.** The Replace dialog box.

This may seem like a lot of steps, but it is not that bad in reality. Also, if you find yourself doing this procedure a lot, you can create a macro that does it for you.

If you would rather use the formula process, follow these steps:

- Make sure the range of cells you want to link and transpose have been given a name. For this example, let's say you named it MyRange.
- Count the number of rows and columns in the range.
- Starting at the cell where you want the information to end up, select a range that has the opposite number of rows and columns. Thus, if the original range had 3 columns and 2 rows, the target range would have 2 columns and 3 rows.
- Type this formula:
**=TRANSPOSE([Book1]Sheet1!MyRange)**. You should substitute different workbook, sheet, and range names, as necessary. - Press
**Shift+Ctrl+Enter**.

At this point your information, linked from the original, appears in the selected range.

*ExcelTips* is your source for cost-effective Microsoft Excel training. This tip (2652) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Transposing and Linking.

*Related Tips:*

**Solve Real Business Problems** Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out *Microsoft Excel 2013 Data Analysis and Business Modeling* today!

WOW! You just saved me a consult gig. This alone saved me hours and hours...

Thank you!

Thank you!

You saved me tons of time. Thank you!

who know ?

I have formula in 1st cell

=TRANSPOSE('[B813-32 after Vibro.xlsx]Blade-6'!$B$13)

in last

=TRANSPOSE('[B813-32 after Vibro.xlsx]Blade-6'!$B$24)

I need to change name of sheet in all column formula to name of "next sheet"

F EXM: Blade-6 TO Blade-7

and so in next column

when I try to use Shift+Ctrl+Enter

and change in array :

'Blade-7'--it place in all cells at

result of 1st

and when 'Blade-8'!$B10:$B21--

result tha same

I have formula in 1st cell

=TRANSPOSE('[B813-32 after Vibro.xlsx]Blade-6'!$B$13)

in last

=TRANSPOSE('[B813-32 after Vibro.xlsx]Blade-6'!$B$24)

I need to change name of sheet in all column formula to name of "next sheet"

F EXM: Blade-6 TO Blade-7

and so in next column

when I try to use Shift+Ctrl+Enter

and change in array :

'Blade-7'--it place in all cells at

result of 1st

and when 'Blade-8'!$B10:$B21--

result tha same

Thanks so much, this is just what I needed!

Very thankful for this great tip! A life saver :)

Thank you!

Splendid!

Awesome!!!!!!

so clever lol

Hello,

thanks for the great formula.

thanks for the great formula.

I have been looking for this forever! Thank you for sharing, I appreciate this site so much.

This is genius! Thanks!

OMG! This tip just saved my life, I´ve stuck on a spreadsheet forever because of this!!(I used the formula and it worked perfectly!)

Thank you so much!

Thank you so much!

Many thanks for this tips! Ill share this site with everyone...Just to say a word of thanks as many people wont realise that it is a lot of time, effort and dedication to make a site like this. It is much appreciated thank you Allen.

Thank so much

Excellent Thinking !!!

Evolution my idea

Excellent Thinking !!!

Evolution my idea

Awesome - i never write comments but this saved me a ton of time! Thank you for the saved hrs.

Great tip, thanks a lot !

...Excellent!

Many thanks from Spain

Many thanks from Spain

You just saved me hours of work! I knew there had to be a way!!

Really great solution. Thanks a lot.

Thank you for this site.

Please help me in my problem.

I was using this guide in linking cells from multiple sheets into a Master sheet.

For example, I copy A1-A5 from sheet 1 onto A1-A5 of the Master sheet.

Then A1-A5 of sheet 2 to B1-B5 of the Master sheet.

Then A1-A5 of sheet 3 to C1-C5 of the Master sheet, and so on...

But I have more than a hundred sheets. Is there a faster way to do this? Thank you very much. Any tip would be appreciated.

Please help me in my problem.

I was using this guide in linking cells from multiple sheets into a Master sheet.

For example, I copy A1-A5 from sheet 1 onto A1-A5 of the Master sheet.

Then A1-A5 of sheet 2 to B1-B5 of the Master sheet.

Then A1-A5 of sheet 3 to C1-C5 of the Master sheet, and so on...

But I have more than a hundred sheets. Is there a faster way to do this? Thank you very much. Any tip would be appreciated.

Great solution!!

Definitely appreciate this tip...you saved me at least an hour or 2 of work with it.

Dear Allen Wyatt,

Please accept my deepest gratitude for providing this tip. I have been searching for some time for this solution. It has made a big difference for my work. Many Thanks.

Christopher Owen

Please accept my deepest gratitude for providing this tip. I have been searching for some time for this solution. It has made a big difference for my work. Many Thanks.

Christopher Owen

For Steps 15-17- the links do not go back to the values, it takes me to a file folder. Any suggestions?

Rather than that copy 3 columns and 5 rows data (15 cells) from the source sheet. then goto another sheet and paste special > paste link... then fix all link cell by select cell > press F2 to edit > press F4 to fix cell (A1 --> $A$1) > then enter, repeat for all 15 cells. then select all those 15 cells and copy > paste special > transpose... there u done

Worked on Excel 2013... Great tip...Saves ton of work! Thanks!!

Perfect tip!!Exactly what i was looking for!thank you very much

Thank you very much for the great tip! It saved me from a lot of work. Keep on the good work!

Regards from Greece.

George

Regards from Greece.

George

Great tip, thank you very much. Saved me tons of time!

I figured it out! The same method works (changing to text and then back to formula)..thanks a lot!!!

How can I get rid of the zeros from blank cells?

This is most helpful...thanks!

If you don't want to define a bunch of names you can also select your range to transpose/link to and use the following paste:

=TRANSPOSE(Composite!$C$7:$L$7)

put your own range in there) followed by the control+shift+enter

=TRANSPOSE(Composite!$C$7:$L$7)

put your own range in there) followed by the control+shift+enter

really splendid... this is the best tip I got ever.

excellent....just loved it

genius!!!!! love it

Thanks very much these tips have saved me a huge headache! First formular worked perfectly for me.

Transposing and Linking - The solution above is so simple to solve the problem ... it is absolutely brilliant!!! I am a 40 year programmer and this thought never occurred to me ... if you cannot paste links, make the link text and then do it. Absolutely brilliant.

Awesome!! You are the best!!

Kudos, tks so much!

That is brilliant thanks saved me loads of time

Absolutely amazing! helped me build a reporting template that linked to different worksheets!!

One of the best (and surprisingly easy) tricks I've learned! Thank you! Hands down.

Unspeakably GOOD!

awesome

very helpful

very helpful

This is just one of the Absolutely Most Wicked Awesome Tip Evers.... Cheers and Much Hugs and Respect.

What a smart tip. You have saved he hours of work. Thank you so much

Nice tip. Excellent job

Fantastic. So simple. Thank you for sharing

Thank you for the tip. Great.

ah.. genius thank you. Nice trick to remember £= and = ... who knew.

Brilliant tip, thank you.

Thank you so much! This saved me some time. I used that =Transpose() array formula before, but it would not let me turn the transposed data into a table object. I thought I needed to write a macro, but with your copy/paste solution, I can now link my data and use a table object.

Fantastic solution! Thank you for sharing!

This is fantastic, so easy to follow and is going to save me so much time. THANK YOU

thank you very much. very clever. from Madagascar

This is so elegant - thank you! The trick of converting formulae to text and back I can imagine has other uses too.

GREAT JOB. You are an angel now with a transpose PhD! I can't believe it works!!! and this after 10 years suffering, time wasting and frustrations!

The next step is you to sell the Microsoft gang your build in macro solution! Call it Quickstep transpose TP F1-MAX.

Go for it, a fortune is waiting.

The next step is you to sell the Microsoft gang your build in macro solution! Call it Quickstep transpose TP F1-MAX.

Go for it, a fortune is waiting.

Simple = Best!

Thanks for this tip (I'm now using the manual method).

Thanks for this tip (I'm now using the manual method).

Thanks SO much - this helped me create dynamic forms containing calculated totals from multiple sheets. I appreciate your efforts :)

great tip... worked for me and saved me huge amount of time.

thank you

thank you

Brilliant! I have used (and taught at uni) Excel for 22 years and learnt 2 new procedures - the TRANSPOSE command and ctrl-shift-enter for mass entry of formulae. THANKS

Many thanks for this great tips.....

it works for me :D

Greeting from Indonesia

it works for me :D

Greeting from Indonesia