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

When in Paste Special, if you click on the Transpose option, the Paste Link option is automatically disabled. However, there are still ways to use them at the same time.

can you paste link and transpose in Excel

 


How to Paste Link and Transpose in Excel: 8 Ways

Paste Link copies the reference to the main source instead of copying only values, allowing you to track the changes between the original and the copy. Transpose allows you to paste data where rows become columns and columns become rows. We will use the following table to combine the features.

can you paste link and transpose in Excel


Method 1 – Using the TRANSPOSE Function to Paste Link and Transpose in Excel

We have a simple dataset that we will transpose.

can you paste link and transpose in Excel

Copying the Formatting:

  • 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

  • Check the Formats option and the Transpose option and then press OK.

TRANSPOSE Function

  • We will get the following transposed formats of the dataset.

can you paste link and transpose in Excel

Copying the Values:

  • 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

  • Here’s the result.

can you paste link and transpose in Excel

If you use a version other than Microsoft Excel 365, you have to press Ctrl + Shift + Enter instead of Enter to apply the TRANSPOSE function.

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


Method 2 – Using the OFFSET Function to Paste Link and Transpose

We’ll use the same dataset.

can you paste link and transpose in Excel

Steps

  • Write the serial numbers from 0 to the total row number-1 (so until 5, as there are six rows) 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 1, as there are two columns) to the left of the designated area.

OFFSET Function

  • Use the following formula in cell B11
=OFFSET($B$3,B$10,$A11)

$B$3 is the cell reference from which it starts moving, B$10 (using the $ symbol before Row 10 fixes this row) is the number of rows it moves downwards and $A11 (using the $ symbol before Column A fixes this column) is the number of columns it moves to the right. B$10 and $A11 both are zero, so the function provides the value in the cell $B$3.

OFFSET Function

  • Press Enter, then drag down and to the right the Fill Handle tool.

OFFSET Function

  • Copy the formatting style to those pasted values by following Method 1.

OFFSET Function

  • Here’s the result.

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

We’ll use the same dataset as before.

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.
  • Click on the Paste Link option of the Paste Special dialog box.

Drag and Drop

  • We have pasted the data source as links.

can you paste link and transpose in Excel

  • Drag each of the cells from here and drop them to create a transposed dataset.
  • Select the first cell value of cell E3 and drag it to cell B10.
  • Repeat for other cells with their respective row and column numbers.

Drag and Drop

  • After dragging each cell of Column E to Row 10 and Column F to Row 11, we get the following linked values.

Drag and Drop

  • Copy and transpose the formatting style from the table following Method 1.

can you paste link and transpose in Excel

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


Method 4 – Using a Named Range to Paste Link and Transpose

We have named the range as Sales.

can you paste link and transpose in Excel

Steps:

  • Use the following formula in cell B10
=TRANSPOSE(Sales)

Named Range

  • Hit Enter.

Named Range

  • Follow 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 the Find & Replace Option to Paste Link and Transpose

We’ll use the same basic dataset.

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.
  • Click on the Paste Link option of the Paste Special dialog box.

can you paste link and transpose in Excel

  • We have pasted the data source as links.

Find & Replace

  • Select the pasted dataset and hit Ctrl + H to go to Find & Select > Replace.

Find & Replace

  • Then the Find and Replace dialog box will open up.
  • Use the following settings:

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

  • Select the Replace All option.

Find & Replace

  • You will get a message box notifying you about the total number of replacements.

Find & Replace

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

Find & Replace

  • Copy the new dataset by pressing Ctrl + C.

Find & Replace

  • Select the cell where you want to paste the values, click on the Transpose option of the Paste Special dialog box and hit OK.

Find & Replace

  • We’ll get the transposed strings. Select them.

can you paste link and transpose in Excel

  • Open the Find and Replace dialog box again, put *= in the Find what box and = in the Find what box, and hit Replace All.

Find & Replace

  • You’ll get a message box, so click OK.

Find & Replace

  • Here’s the result.

Find & Replace

  • Copy the cell formatting and transpose it from the original dataset (as in Method 1).

can you paste link and transpose in Excel


Method 6 – Using the INDEX Function to Paste Link and Transpose in Excel

We’ll start with the same dataset as before.

can you paste link and transpose in Excel

Steps:

  • Use the following formula in cell B11:
=INDEX($B$3:$C$8,COLUMN(A1),ROW(A1))
  • 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 the Fill Handle down and to the right.

INDEX,COLUMN,ROW

  • Here’s the result.

INDEX,COLUMN,ROW

  • Follow the first part of Method 1 to transpose the cell formatting from the original.

can you paste link and transpose in Excel

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


Method 7 – Using the VLOOKUP Function to Paste Link and Transpose

We’re starting with a basic dataset.

can you paste link and transpose in Excel

Steps:

  • We have copied the formatting style following Method 1.
  • Manually transpose the first column by entering or copying values.

VLOOKUP Function

  • Use the following formula in cell C12:
=VLOOKUP(C11,$B$3:$C$8,2,FALSE)

VLOOKUP Function

  • Press ENTER and drag the Fill Handle to the right.

VLOOKUP Function

  • Here’s the result.

can you paste link and transpose in Excel


Method 8 – Using the INDEX and MATCH Functions to Paste Link and Transpose in Excel

  • We have copied the formatting style and manually transposed the first column.

can you paste link and transpose in Excel

  • Use the following formula in cell C12:
=INDEX($C$4:$C$8,MATCH(C11,$B$4:$B$8,0))

INDEX-MATCH

  • Press Enter and drag the Fill Handle tool to the right.

INDEX-MATCH

  • You will get the transposed and linked dataset.

can you paste link and transpose in Excel

If you use a version other than Microsoft Excel 365, you have to press Ctrl + Shift + Enter instead of Enter to apply the formula.

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


Practice Section

We have provided a Practice section like below in a sheet named Practice.

practice


Download the Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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