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.

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

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

We have a simple dataset that we will transpose.

**Copying the Formatting:**

- Copy the dataset by pressing
**Ctrl + C**.

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

- Check the
**Formats**option and the**Transpose**option and then press**OK**.

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

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

- Here’s the result.

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

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

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

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

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

- Here’s the result.

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

**Steps**:

- Copy the dataset by pressing
**Ctrl + C**.

- Select the cell where you want to paste the values.
- Click on the
**Paste Link**option of the**Paste Special**dialog box.

- We have pasted the data source as links.

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

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

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

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

** Steps**:

- Use the following formula in cell
**B10**

`=TRANSPOSE(Sales)`

- Hit
**Enter**.

- Follow Method 1 to have the formatting styles in the transposed dataset.

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

**Steps**:

- Copy the dataset by pressing
**Ctrl + C**.

- Select the cell where you want to paste the values.
- Click on the
**Paste Link**option of the**Paste Special**dialog box.

- We have pasted the data source as links.

- Select the pasted dataset and hitÂ
**Ctrl + H**Â to go to Find & Select > 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.

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

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

- Copy the new dataset by pressing
**Ctrl + C**.

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

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

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

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

- Here’s the result.

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

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

We’ll start with the same dataset as before.

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

- Press
**Enter**. - Drag the Fill Handle down and to the right.

- Here’s the result.

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

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

** Steps**:

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

- Use the following formula in cell
**C12:**

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

- Press
**ENTER**and drag the**Fill Handle**to the right.

- Here’s the result.

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

- Use the following formula in cell
**C12:**

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

- Press
**Enter**and drag the**Fill Handle**tool to the right.

- You will get the transposed and linked dataset.

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

**Download the Practice Workbook**

## Related Articles

- Excel VBA to Transpose Array
- Excel Macro: Convert Multiple Rows to Columns
- VBA to Transpose Multiple Columns into Rows in Excel

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