How to paste transposed and keep formula reference in Excel?
When working with Excel, the Transpose feature is often used to switch the orientation of data from columns to rows or vice versa. However, a common challenge arises when this data includes formulas—Excel will, by default, adjust the cell references to match the new orientation. As demonstrated in the screenshot below, this automatic adjustment frequently breaks the intended calculations, especially in complex or linked datasets. Understanding how to paste transposed data while retaining original formula references is essential for anyone dealing with financial models, engineering calculations, or linked dashboards where maintaining formula integrity is important. This article explains several practical methods for achieving this outcome, addresses their best- and worst-case use scenarios, and offers troubleshooting advice for smoother operations.
Use F4 key to convert formula references to absolute and transpose the data
Transpose and keep reference with Find and Replace function
Transpose and keep reference with Kutools for Excel
VBA Code - Transpose cells while preserving formula references (relative or absolute)
Excel Formula - Manually recreate transposed formulas with INDIRECT or address construction
Use F4 key to convert formula references to absolute and transpose the data
1. Select the Formula Cell
Click on the cell that contains the formula you want to adjust.
2. Open the Formula Bar
Click into the formula bar to place your cursor inside the formula.
3. Convert to Absolute References
Select the whole formula in the formula, then press the F4 key.
This toggles the reference format between relative, absolute, and mixed.
Repeat this for all cell references in the formula until they are fully absolute.
4. Copy the Data
Select the data range you want to copy and press Ctrl + C.
5. Paste as Transposed Data
Right-click on the destination cell, then select Paste Special → Transpose.
Tip:
Absolute references ensure that the formula always refers to the same cells, even when copied or moved. Transposing the data switches rows to columns or columns to rows—perfect for reorganizing data layout.
Transpose and keep reference with Find and Replace function
To transpose a range of cells and retain the original formula references in Excel, you can use the Find and Replace feature to temporarily convert formulas to text, reposition them, and then revert them back to formulas. This approach is suitable for small to medium-sized datasets and is helpful when you do not have additional add-ins installed or prefer not to use VBA.
1. First, select the range of cells containing the formulas you want to transpose. Press Ctrl + H to open the Find and Replace dialog.
2. In the Find and Replace dialog, type = in the Find what field, and type #= in the Replace with field. This step converts live formulas into plain text by replacing the equal sign. Doing this prevents Excel from converting formula cell references during the copy and transpose process.
3. Click Replace All. A dialog box will appear, displaying the number of replacements made. Click OK and then Close to exit the dialogs.
4. With the now-converted text cells still selected, press Ctrl + C to copy them. Move to your desired paste location, right-click, and from the context menu, select Paste Special > Transpose to transpose and paste. Be mindful that if you have a large dataset or your formulas use volatile functions, you may need to check the pasted results carefully.
5. After pasting, press Ctrl + H once more to open the Find and Replace dialog. Now, reverse the original substitution: type #= in the Find what box and = in the Replace with box. This will convert the text back to functional formulas.
6. Click Replace All, then OK > Close to finish. Your formulas are now transposed and keep the references as in the original range.
This manual method works best for small datasets. For more complex ranges or when dealing with mixed reference styles, double-check the results to ensure formulas recalculate as expected. If you have named ranges or external references, these may need to be reviewed after transposing.
Transpose and keep reference with Kutools for Excel
If you frequently need to transpose data that includes formulas, Kutools for Excel provides a streamlined approach. Its Convert Refers utility allows you to quickly convert all formula references to absolute references prior to transposition. This ensures the original references remain intact after transposing, minimizing manual intervention and the risk of broken formulas.
After installing Kutools for Excel, follow these steps:
1. Select the cells containing the formulas you need to transpose, then click Kutools > More (in the Formula group) > Convert Refers. This will open the reference conversion dialog.
2. In the Convert Formula References dialog box, select the To absolute option and click OK. This step ensures all cell references in your selected formulas are set as absolute references (with $ signs). As a result, references will not shift when you transpose the cells.
3. Now, select the cells again and press Ctrl + C to copy them. At the desired paste location, right-click and select Transpose from the Paste Special submenu in the context menu. This will transpose your data and retain the correct formula references.
The Kutools solution is most effective if you regularly perform such tasks, especially with larger volumes of data or more complex spreadsheets containing multiple formulas. As a precaution, always check whether absolute references are desired after transposition; you may need to convert references back to relative as required using the same feature. If your original formulas mix relative and absolute references, review them for accuracy following the conversion and transpose operation.
VBA Code - Transpose cells while preserving formula references (relative or absolute)
For advanced scenarios, writing a VBA macro enables you to automate the process of transposing formulas while preserving original reference types—whether they are relative, absolute, or mixed. This solution is well-suited for users comfortable with macros, and especially useful for larger data ranges or when this operation is performed frequently. VBA offers flexibility, accommodates complex patterns of references, and handles various formula structures directly.
1. First, enable the Developer tab in Excel if it is not already visible. Go to Developer > Visual Basic to open the VBA editor.
2. In the VBA editor, click Insert > Module to open a new module window, then copy and paste the following VBA code into this window:
Sub TransposeFormulasPreserveReferences()
Dim ws As Worksheet
Dim sourceRange As Range
Dim destRange As Range
Dim numRows As Long, numCols As Long
Dim i As Long, j As Long
Dim tempArray As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set sourceRange = Application.InputBox("Select the range you want to transpose", xTitleId, Selection.Address, Type:=8)
If sourceRange Is Nothing Then Exit Sub
numRows = sourceRange.Rows.Count
numCols = sourceRange.Columns.Count
Set destRange = Application.InputBox("Select the upper-left cell for the transposed output", xTitleId, , Type:=8)
If destRange Is Nothing Then Exit Sub
tempArray = sourceRange.Formula ' Store original formulas
' Transpose formulas, cell by cell
For i = 1 To numRows
For j = 1 To numCols
destRange.Offset(j - 1, i - 1).Formula = tempArray(i, j)
Next j
Next i
End Sub3. To run the code, click the 
button, or press F5. Follow the prompts: select the source data (including formulas) you wish to transpose and the starting cell for the output. The macro will copy and transpose all formulas, keeping the references the same as in the original location. If your formulas use relative references, be aware that their context may change (resulting values may not match the originals), but the formula text itself will not be adjusted, preserving the reference type.
This approach is particularly useful for large datasets, repeated operations, or when granular control is needed. If an error occurs (such as not properly selecting a destination area of sufficient size), rerun the macro and check your range selections carefully.
In summary, Excel provides several ways to transpose data while keeping the original formula references, including manual Find and Replace, advanced tools such as Kutools, VBA automation, and formula-based approaches using INDIRECT or ADDRESS. When choosing a method, consider your data size, formula complexity, and need for automation versus manual control. Always double-check the result—especially with relative references—to ensure calculations remain correct, and save a backup copy of your workbook before executing any bulk changes or running macros. If you encounter issues such as "ref" errors or unexpected values, verify that no references have spilled outside the correct range or that absolute/relative mixed references have not shifted incorrectly. When in doubt, try your method on a small sample first to build confidence in the process.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in