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)

68

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

MORE FROM ALLEN

Saving All Open Documents

Got a lot of open documents you are working with? You can save them all at one time by just holding down the Shift key while ...

Discover More

Printing Reversed Images

Ever need to print the mirror image of your document? This tip explains how to reverse your image so it can be used for ...

Discover More

Finding Columns of a Certain Width

If you need to find out how many columns are set to be a specific width, you'll need a macro to help determine the info. ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

MORE EXCELTIPS (MENU)

Concatenating Ranges of Cells

Putting the contents of two cells together is easy. Putting together the contents of lots of cells is more involved, as ...

Discover More

Switching Editing Location

Excel allows you to edit the contents of a cell in two places—the cell itself or in the Formula bar. If you want to ...

Discover More

Controlling Where You Edit Cell Contents

You can edit cell information either in the Formula bar or in the cell itself. Here's how you can configure Excel to specify ...

Discover More
Subscribe

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

View most recent newsletter.

Comments for this tip:

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

2017-04-07 10:07:57

Andres

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

Barbora

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

Fay

You saved me tons of time. Thank you!


2016-12-20 07:25:46

MIHA

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

Katie

Thanks so much, this is just what I needed!


2016-08-24 18:14:00

MC

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


2016-08-10 05:43:28

Niels

Thank you!


2016-08-09 14:51:10

Wizzard

Splendid!


2016-08-02 02:48:35

Taj

Awesome!!!!!!


2016-06-20 09:47:51

Chad

so clever lol


2016-03-15 06:20:35

anurag singhvi

Hello,

thanks for the great formula.


2016-02-10 03:47:10

Ninni

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


2016-02-04 11:10:21

Jens

This is genius! Thanks!


2015-10-28 14:48:33

Ana

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

Rhemerus

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

Enividlived

Thank so much
Excellent Thinking !!!
Evolution my idea


2015-09-21 22:38:45

gdub

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

Edgar

Great tip, thanks a lot !


2015-08-31 05:19:39

Fernando Cava

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

Arvind

Really great solution. Thanks a lot.


2015-07-11 14:38:50

Jayu

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

ss

Great solution!!


2015-06-09 15:21:22

Jordan G

Definitely appreciate this tip...you 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

Lisa

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

BM

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


2015-03-19 08:22:43

myrto

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


2015-03-15 07:59:17

George

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


2015-02-27 14:08:48

Oz

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


2015-01-23 11:44:17

eka

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

eka

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

matt

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


2014-12-16 02:55:45

banglaboy

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

tin

genius!!!!! love it


2014-12-09 06:29:48

Helen

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


2014-10-31 15:56:23

Larry

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

milton

Kudos, tks so much!


2014-10-08 10:34:51

Ben

That is brilliant thanks saved me loads of time


2014-09-17 11:14:20

dboy

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


2014-09-10 02:42:22

Ara

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


2014-09-01 10:59:36

JJ

Unspeakably GOOD!


2014-08-29 02:36:52

sunny lakhotra

awesome
very helpful


2014-07-22 17:21:01

samer

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


2014-06-26 11:24:38

Dermot

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


2014-06-15 14:20:34

NIRAJ

Nice tip. Excellent job


2014-04-19 02:30:21

lenogooch

Fantastic. So simple. Thank you for sharing


2014-04-18 08:42:11

Moe

Thank you for the tip. Great.


2014-04-08 08:49:16

Steph

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


2014-03-24 19:17:22

Mike

Brilliant tip, thank you.


2014-03-20 15:49:09

BKelly

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

G3X

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

Tony

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

ech0

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

raza

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

Itok

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
Subscribe

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.

Links and Sharing