How to Paste Link and Transpose in Excel (8 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

By noticing the fact that when you click on the Transpose option then the Paste Link option is automatically disabled, a question may arise that can you Paste Link and Transpose in Excel simultaneously.

can you paste link and transpose in Excel

The answer to this question is YES. This article will explain to you the ways to Paste Link and Transpose in Excel at a time. So, let’s start our main article.


How to Paste Link and Transpose in Excel: 8 Ways

By using Paste Link you can copy the reference to the main source instead of copying only values so that for further alteration of any value the pasted values will be changed automatically and by using the Transpose option you can paste your data with a 90-degree rotation that means rows become columns and columns become rows.
Here, we will use the following table to do these combinations using different ways.

can you paste link and transpose in Excel

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using TRANSPOSE Function to Paste Link and Transpose in Excel

Here, we will use the TRANSPOSE function to copy the following dataset and then Paste Link with Transpose feature simultaneously.

can you paste link and transpose in Excel

Step-01:
➤ Copy the dataset by pressing CTRL+C.

TRANSPOSE Function

➤ Select the cell where you want to paste the values and then open the Paste Special dialog box by pressing ALT, E, S one by one.

TRANSPOSE Function

As we are pasting the formats only now by transposing them,
➤ Click on the Formats option and the Transpose option and then press OK.

TRANSPOSE Function

Then, we will get the following transposed formats of the dataset.

can you paste link and transpose in Excel

Step-02:
Now, it’s time to paste the values as links by using the following formula

=TRANSPOSE(B3:C8)

TRANSPOSE will convert the vertical range B3:C8 to the horizontal range.

TRANSPOSE Function

Finally, you will be able to paste the dataset as a link and transpose it like the following figure.

can you paste link and transpose in Excel

Using any other versions except for the Microsoft Excel 365 version you have to press CTRL+SHIFT+ENTER instead of pressing ENTER for using the TRANSPOSE function.

Read More: How to Move Data from Row to Column in Excel


Method-2: Using OFFSET Function to Paste Link and Transpose

In this section, we will use the OFFSET function to paste the following dataset as a link and convert the rows into columns.

can you paste link and transpose in Excel

Steps:
➤ Write the serial numbers from 0 to the total row number-1 or 6-1 or 5 (as here total row is 6) to the top of the area where we will paste the data and use the serial numbers from 0 to the total column number-1 or 2-1 or 1 (as here total column is 2) to the left of the designated area.

OFFSET Function

After giving input the serial numbers, now we will move to the use of the OFFSET function with these numbers.
➤ Use the following formula in cell B11

=OFFSET($B$3,B$10,$A11)

Here, $B$3 is the cell reference from which it starts moving, B$10 (using the $ symbol before Row 10 we are fixing this row) is the number of rows it moves downwards and $A11 (using the $ symbol before Column A we are fixing this column) is the number of columns it moves to the right. For this case, B$10 and $A11 both are zero and so it will not move anywhere and gives the value in the cell $B$3.

OFFSET Function

➤ Press ENTER, drag down, and to the right the Fill Handle tool.

OFFSET Function

Then, we will be able to paste links and transpose at the same time like below and now we need to copy the formatting style to those pasted values by following Step-01 of Method-1.

OFFSET Function

After completing all of the procedures, we will have the following dataset with the conversion of the horizontal ranges to vertical ranges including the link to the reference of the main values.

can you paste link and transpose in Excel

Read More: How to Flip Data from Horizontal to Vertical in Excel


Method-3: Paste Link and Transpose in Excel by Dragging and Dropping

You can paste links and transpose the values by dragging and dropping also.

can you paste link and transpose in Excel

Steps:
➤ Copy the dataset by pressing CTRL+C.

Drag and Drop

➤ Select the cell where you want to paste the values and then click on the Paste Link option of the Paste Special dialog box.

Drag and Drop

In this way, we have pasted the data source as links.

can you paste link and transpose in Excel

Now, we have to drag each of the cells from here and drop them in the area where we want to have our transposed dataset. Be careful in doing this drag and drop process.
➤ Select the first cell value of cell E3 and drag it to cell B10.

Drag and Drop

After dragging each cell of Column E to Row 10 and Column F to Row 11 we have got the following linked values.

Drag and Drop

➤ Follow Step-01 of Method-1 to have the formatting styles in the transposed dataset.

can you paste link and transpose in Excel

Read More: How to Change Vertical Column to Horizontal in Excel


Method-4: Using Named Range to Paste Link and Transpose

Here, we have named the range as Sales and now using this named range we will transpose the dataset with a link to the main dataset easily.

can you paste link and transpose in Excel

Steps:
➤ Type the following formula in cell B10

=TRANSPOSE(Sales)

Named Range

After pressing ENTER we will get the transposed dataset with links to the reference of the main dataset.

Named Range

➤ Follow Step-01 of Method-1 to have the formatting styles in the transposed dataset.

can you paste link and transpose in Excel

Read More: How to Flip Columns and Rows in Excel


Method-5: Using Find & Replace Option to Paste Link and Transpose

Here, we will use the Find and Replace option to paste links and transpose them at a time easily.

can you paste link and transpose in Excel

Steps:
➤ Copy the dataset by pressing CTRL+C.

Find & Replace

➤ Select the cell where you want to paste the values and then click on the Paste Link option of the Paste Special dialog box.

can you paste link and transpose in Excel

In this way, we have pasted the data source as links.

Find & Replace

➤ Select the pasted dataset and then go to Home Tab >> Editing Group >> Find & Select Dropdown >> Replace Option.
You can go there also by pressing CTRL+H.

Find & Replace

Then the Find and Replace dialog box will open up.
➤ Type and select the followings

Find what → =
Replace what → *=
Within → Sheet
Search → By Rows
Look in → Formulas

➤ Select the Replace All option.

Find & Replace

Then you will get a message box notifying you about the total number of replacements which is 12 here.

Find & Replace

Finally, we have converted the formulas in the cells to text strings by replacing = with *= and now we will transpose it.

Find & Replace

➤ Copy the dataset by pressing CTRL+C.

Find & Replace

➤ Select the cell where you want to paste the values and then click on the Transpose option of the Paste Special dialog box and finally press OK.

Find & Replace

Then, you will have the transposed dataset with the text strings and the final task is to replace *= again with =

can you paste link and transpose in Excel

Like the previous way, again open the Find and Replace dialog box and just type *= in the Find what box and = in the Find what box, and the remnants will be the same as the preceding one.

Find & Replace

Then a message box will appear mentioning about 12 replacements.

Find & Replace

Finally, we will get back the values with links to the reference of the main dataset.

Find & Replace

➤ Follow Step-01 of Method-1 to have the formatting styles in the transposed dataset.

can you paste link and transpose in Excel


Method-6: Using INDEX Function to Paste Link and Transpose in Excel

In this section, we will use the INDEX function, COLUMN function, ROW function to paste the following dataset as a link and then transpose them.

can you paste link and transpose in Excel

Steps:
➤ Type the following formula in cell B11

=INDEX($B$3:$C$8,COLUMN(A1),ROW(A1))

Here, $B$3:$C$8 is the reference, and we have used column number as row argument and row number as column argument to transpose them.

  • COLUMN(A1) → returns the column number of the A1 cell
    Output → 1
  • ROW(A1) → returns the row number of the A1 cell
    Output → 1
  • INDEX($B$3:$C$8,COLUMN(A1),ROW(A1)) → becomes
    INDEX($B$3:$C$8,1,1)
    Output → Product

INDEX,COLUMN,ROW

➤ Press ENTER, drag down, and to the right the Fill Handle tool.

INDEX,COLUMN,ROW

Then, you will be able to paste links and transpose them at the same time as below.

INDEX,COLUMN,ROW

➤ Follow Step-01 of Method-1 to have the formatting styles in the transposed dataset.

can you paste link and transpose in Excel

Read More: How to Transpose Multiple Columns to Rows in Excel


Method-7: Using VLOOKUP Function to Paste Link and Transpose

Here, we will use the VLOOKUP function to paste links and transpose the following dataset at a time.

can you paste link and transpose in Excel

Steps:
We have copied the formatting style following Step-01 of Method-1 and for this method, you have to manually transpose the first column like below.

VLOOKUP Function

➤ Use the following formula in cell C12

=VLOOKUP(C11,$B$3:$C$8,2,FALSE)

Here, C11 is the lookup value, $B$3:$C$8 is the lookup range, 2 is the serial number of columns in the range from which we will extract the value for our corresponding lookup value and FALSE is for an exact match.

VLOOKUP Function

➤ Press ENTER and drag the Fill Handle tool to the right.

VLOOKUP Function

In this process, you will get your transposed and linked dataset like below.

can you paste link and transpose in Excel


Method-8: Using INDEX and MATCH Functions to Paste Link and Transpose in Excel

You can also use the INDEX function and the MATCH function for pasting links and transposing them. For this purpose, we have copied the formatting style and we have manually transposed the first column like below.

can you paste link and transpose in Excel

Steps:
➤ Use the following formula in cell C12

=INDEX($C$4:$C$8,MATCH(C11,$B$4:$B$8,0))

Here, C11 is the lookup value, $B$4:$B$8 is the lookup array and 0 is for an exact match. Finally, MATCH will return the row or column index number of the lookup value in the data range.
MATCH will return here 1.
Then, it will pass this value to the INDEX function which returns the information we actually want by using the return range $C$4:$C$8.

INDEX-MATCH

➤ Press ENTER and drag the Fill Handle tool to the right.

INDEX-MATCH

Afterward, you will get your transposed and linked dataset like below.

can you paste link and transpose in Excel

Using any other versions except for the Microsoft Excel 365 version you have to press CTRL+SHIFT+ENTER instead of pressing ENTER for using the combination of the INDEX and the MATCH functions.

Read More: How to Convert Multiple Rows to Columns in Excel


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Download Practice Workbook


Conclusion

In this article, we tried to cover the ways to paste link and transpose in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles


<< Go Back to Transpose Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo