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), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Transposing and Linking.

Transposing and Linking

by Allen Wyatt
(last updated July 23, 2015)


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.

  • Transpose. 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.
  • Linking. 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.

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:

  1. Copy the data you want to transpose and link, as you normally would.
  2. Select any cell in an unused worksheet.
  3. Choose Paste Special from the Edit menu. This displays the Paste Special dialog box. (See Figure 1.)
  4. Figure 1. The Paste Special dialog box.

  5. Click on the Paste Link button. Your information is pasted, as a link, in the worksheet. Make sure it remains selected.
  6. Press Ctrl+H to display the Replace dialog box. (See Figure 2.)
  7. Figure 2. The Replace dialog box.

  8. In the Find What area, type an equal sign.
  9. In the Replace With area, type a pound sign followed by an equal sign.
  10. Click on Replace All. All the information you just pasted is essentially converted from formulas into text. Make sure it remains selected.
  11. Press Ctrl+C to copy the range to the Clipboard.
  12. Select the cell where you want the final transposed and linked range to reside.
  13. Choose Paste Special from the Edit menu. This displays the Paste Special dialog box.
  14. Choose the Transpose check box.
  15. Click on OK. The information is pasted, but it is transposed into the final location. Make sure it remains selected.
  16. Press Ctrl+H to display the Replace dialog box.
  17. In the Find What area, type a pound sign followed by an equal sign.
  18. In the Replace With area, type an equal sign.
  19. Click on Replace All. All the information you just pasted is converted back to formulas.

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:

  1. 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.
  2. Count the number of rows and columns in the range.
  3. 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.
  4. Type this formula: =TRANSPOSE([Book1]Sheet1!MyRange). You should substitute different workbook, sheet, and range names, as necessary.
  5. 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.

Author Bio

Allen Wyatt

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. ...


Changing Space between the Footnote Separator and Footnotes

When you add footnotes to a document, Word separates those footnotes from the document body with a separator line. Here's ...

Discover More

Converting Footnotes to Endnotes

When you spend a lot of time creating footnotes, how can you convert all of them to endnotes without entering them all ...

Discover More

Maintaining Fields in a Merged Document

When merging documents, you may want to include some fields in the merged documents. For some fields this is impossible, ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (menu)

Ensuring Rows and Columns are Empty

Before you go about deleting rows and columns helter-skelter, it is a good idea to determine if there is anything in the ...

Discover More

Shortcut for Selecting a Data Range

Want to select all the data in a contiguous section of a worksheet? The shortcut discussed in this tip makes it very easy.

Discover More

Quickly Selecting Cells

Need to quickly select a range of cells? Perhaps the easiest way is to use both the mouse and the keyboard together, as ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

2019-09-22 00:58:26


This saved my day, thank you!

2019-07-29 05:43:02


The best thing I was looking for to ease my work. Thanks

2018-08-31 17:20:07


(sorry for posting this in another thread!)

Thank you for this trick - it saved me a lot of time recently. However, today I tried doing it, and Excel now adds an apostrophe prefix to the cell contents after the 2nd step (replacing = with #= ; Excel now replaces it with '#= ). Any idea what might have caused this change in behavior?

2018-04-19 10:12:45


Thank you! the array formula was really helpful!

2017-09-21 14:38:53


Great, thanks!

2017-08-17 09:41:50


Excellent, always wondered how to do it

2017-05-22 10:02:00

Amir pegahfar

i like it
so nice

2017-04-07 10:07:57


Great! Thanks

2017-02-21 09:32:14

Jake Smart

Fantastic workaround! But god only knows why this feature isn't simply incorporated OOTB.

2017-02-14 04:36:40


Thank you!!!

2017-01-19 06:00:22

Mohamed Hassan

So far this site is helpful......I am an ACCA affiliated/completed and I feel the my excel skill is one my weakness areas an is obstacle to my employment.
I have some excel knowledge but it's not organised (intermediate , Advance)

Therefore I need assistance.

2017-01-12 20:44:45

Benjamin Allen Mercado

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

Thank you!

2016-12-20 11:48:33


You saved me tons of time. Thank you!

2016-12-20 07:25:46


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

2016-10-06 14:06:22


Thanks so much, this is just what I needed!

2016-08-24 18:14:00


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

2016-08-10 05:43:28


Thank you!

2016-08-09 14:51:10



2016-08-02 02:48:35



2016-06-20 09:47:51


so clever lol

2016-03-15 06:20:35

anurag singhvi


thanks for the great formula.

2016-02-10 03:47:10


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

2016-02-04 11:10:21


This is genius! Thanks!

2015-10-28 14:48:33


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!

2015-10-18 03:59:26


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.

2015-10-12 00:06:47


Thank so much
Excellent Thinking !!!
Evolution my idea

2015-09-21 22:38:45


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

2015-09-21 14:51:33


Great tip, thanks a lot !

2015-08-31 05:19:39

Fernando Cava

Many thanks from Spain

2015-08-18 17:22:52

Joan Richmann

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

2015-07-13 08:23:15


Really great solution. Thanks a lot.

2015-07-11 14:38:50


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.

2015-06-10 05:33:12


Great solution!!

2015-06-09 15:21:22

Jordan G

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

2015-05-03 23:14:56

Christopher Owen

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

2015-04-10 08:37:30


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

2015-04-01 05:14:58

Mohamed Mafaz

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

2015-03-21 13:50:17


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

2015-03-19 08:22:43


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

2015-03-15 07:59:17


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

2015-02-27 14:08:48


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

2015-01-23 11:44:17


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

2015-01-23 11:11:54


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

2015-01-22 14:44:39

Onyeka Nwaogu

This is most helpful...thanks!

2014-12-16 19:22:45


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:
put your own range in there) followed by the control+shift+enter

2014-12-16 02:55:45


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

2014-12-12 06:03:34

ganesh india

excellent....just loved it

2014-12-10 19:01:26


genius!!!!! love it

2014-12-09 06:29:48


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

2014-10-31 15:56:23


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.

2014-10-30 16:16:30

Bharat Naruka

Awesome!! You are the best!!

2014-10-21 04:58:03


Kudos, tks so much!

2014-10-08 10:34:51


That is brilliant thanks saved me loads of time

2014-09-17 11:14:20


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

2014-09-10 02:42:22


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

2014-09-01 10:59:36


Unspeakably GOOD!

2014-08-29 02:36:52

sunny lakhotra

very helpful

2014-07-22 17:21:01


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

2014-06-26 11:24:38


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

2014-06-15 14:20:34


Nice tip. Excellent job

2014-04-19 02:30:21


Fantastic. So simple. Thank you for sharing

2014-04-18 08:42:11


Thank you for the tip. Great.

2014-04-08 08:49:16


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

2014-03-24 19:17:22


Brilliant tip, thank you.

2014-03-20 15:49:09


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.

2013-11-20 14:42:47


Fantastic solution! Thank you for sharing!

2013-11-14 05:19:47

Jo Foster

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

2013-11-07 05:49:02

from Mada

thank you very much. very clever. from Madagascar

2013-11-06 10:22:23


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

2013-11-01 05:36:17

rodeyns Nicolai

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.

2013-10-18 07:27:08

Earle Wallbank

Simple = Best!
Thanks for this tip (I'm now using the manual method).

2013-08-27 07:23:03


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

2013-07-20 05:37:02


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

2013-06-15 02:51:27

Mac Benoy

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

2013-05-27 23:14:03


Many thanks for this great tips.....
it works for me :D
Greeting from Indonesia

This Site

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.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.