Tanjima Hossain
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.
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.
Download Workbook
8 Ways to Paste Link and Transpose in Excel
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.
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.
Step-01:
➤ 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.
As we are pasting the formats only now by transposing them,
➤ Click on the Formats option and the Transpose option and then press OK.
Then, we will get the following transposed formats of the dataset.
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.
Finally, you will be able to paste the dataset as a link and transpose it like the following figure.
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: Exchange (Copy, Import, Export) Data Between Excel and Access
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.
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.
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.
➤ Press ENTER, drag down, and to the right the Fill Handle tool.
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.
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.
Read More: Formula to Copy and Paste Values in Excel (5 Examples)
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.
Steps:
➤ Copy the dataset by pressing CTRL+C.
➤ Select the cell where you want to paste the values and then click on the Paste Link option of the Paste Special dialog box.
In this way, we have pasted the data source as links.
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.
After dragging each cell of Column E to Row 10 and Column F to Row 11 we have got the following linked values.
➤ Follow Step-01 of Method-1 to have the formatting styles in the transposed dataset.
Read More: How to Copy and Paste in Excel without Formulas (7 Easy Tricks)
Similar Readings
- Copy and Paste Thousands of Rows in Excel (3 Ways)‌
- How to Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
- Excel Formula to Copy Cell Value from Another Sheet
- Update One Excel Worksheet from Another Sheet Automatically
- How to Copy and Paste in Excel Using VBA (7 Methods)
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.
Steps:
➤ Type the following formula in cell B10
=TRANSPOSE(Sales)
After pressing ENTER we will get the transposed dataset with links to the reference of the main dataset.
➤ Follow Step-01 of Method-1 to have the formatting styles in the transposed dataset.
Read More: Excel VBA to Copy Only Values to Destination (Macro, UDF, and UserForm)
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.
Steps:
➤ Copy the dataset by pressing CTRL+C.
➤ Select the cell where you want to paste the values and then click on the Paste Link option of the Paste Special dialog box.
In this way, we have pasted the data source as links.
➤ 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.
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.
Then you will get a message box notifying you about the total number of replacements which is 12 here.
Finally, we have converted the formulas in the cells to text strings by replacing = with *= and now we will transpose it.
➤ Copy the dataset by pressing CTRL+C.
➤ 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.
Then, you will have the transposed dataset with the text strings and the final task is to replace *= again with =
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.
Then a message box will appear mentioning about 12 replacements.
Finally, we will get back the values with links to the reference of the main dataset.
➤ Follow Step-01 of Method-1 to have the formatting styles in the transposed dataset.
Read More: Macro to Copy Data from One Workbook to Another Based on Criteria
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.
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
➤ Press ENTER, drag down, and to the right the Fill Handle tool.
Then, you will be able to paste links and transpose them at the same time as below.
➤ Follow Step-01 of Method-1 to have the formatting styles in the transposed dataset.
Read More: How to Copy the Same Value in Multiple Cells in Excel (4 Methods)
Similar Readings
- Copy Rows Automatically in Excel to Another Sheet (4 Methods)
- How to Disable Copy and Paste in Excel without Macros (With 2 Criteria)
- Copy Excluding Hidden Rows in Excel (4 Easy Methods)
- How to Copy Rows in Excel with Filter (6 Fast Methods)
- [Fixed!] Cannot Copy Merged Cells in Excel (4 Solutions)
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.
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.
➤ 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.
➤ Press ENTER and drag the Fill Handle tool to the right.
In this process, you will get your transposed and linked dataset like below.
Read More: Use VBA to Paste Values Only with No Formatting 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.
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.
➤ Press ENTER and drag the Fill Handle tool to the right.
Afterward, you will get your transposed and linked dataset like below.
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 function.
Read More: How to Paste From Clipboard to Excel Using VBA
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.
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
- VBA Code to Compare Two Excel Sheets and Copy Differences
- [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
- How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
- Difference Between Paste and Paste Special in Excel
- How to Copy Alternate Rows in Excel (4 Ways)
- Copy and Paste in Excel and Keep Cell Size (7 Examples)
- How to Copy Rows in Excel (4 Easy Ways)
