Transpose data means data in Excel’s rows are shifted in Excel’s columns where auto-update is necessary or not. Transposing formulas is the same as transposing data. But it requires extra effort in the case of transposing formulas without changing references. In this article, we use the TRANSPOSE function, Paste Special, Find and Replace features as well as Direct Cell References to excel transpose formula without changing its references.
Suppose, we have a dataset of Name and Salary Data consisting of columns Name, Salary, Allowances, and Total Salary like the image below.
Dataset For Download
4 Easy Ways to Excel Transpose Formulas Without Changing References
There are two types of outcomes regarding transposing; one is Static data and another one is Dynamic data.
Static data means after transposing the transposed data will not be updated if any changes are made in the original dataset.
Dynamic data updates the transposed data with anything changes in the original dataset.
Method 1: Using TRANSPOSE Function (Dynamic)
The syntax of the TRANSPOSE function is
array; the range of data you want to transpose.
Step 1: Select a range of blank cells close to the original dataset.
Step 2: Type the following formula in the active cell.
Step 3: As TRANSPOSE is applied on an array. Press CTRL+ SHIFT+ENTER altogether. The outcomes will be much like the picture below.
In the picture, you can find some dissimilarities as the transposed data is missing some of the entries (exactly two of them) from the original dataset.
Step 4: You can fetch the missing data by selecting Extra Columns (exactly two of them)
Step 5: Then Go to the Formula Bar and Hit CTRL+SHIFT+ENTER again. All the original dataset entries will now appear.
After applying the TRANSPOSE function, if any update in the original dataset is done then the transposed dataset will update automatically without changing any cell references in formulas whatsoever.
Read More: How to Transpose in Excel VBA (3 Methods)
Method 2: Using Paste Special Feature (Static)
In Excel Paste Special feature transposes data with cell references unchanged. With this process, auto-update in the case of changing anything in the original dataset will not happen.
Step 1: Copy the whole range of data you want to transpose.
Step 2: Select any blank cell (B14) you want to place the transposed data.
Step 3: Right Click on the selected cell (B14), from the options select Paste Special>> Transpose.
The whole range of data gets transposed similar to the image below.
Keep in mind that you cannot auto-update the transposed dataset by simply updating the original dataset via Paste Special Method.
- How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
- Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)
- How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
- Transpose Multiple Columns into One Column in Excel (3 Handy Methods)
- Convert Columns to Rows in Excel Using Power Query
Method 3: Using Find and Replace Feature (Static)
In the dataset, the SUM formula is used to calculate the Total Salary. We can transform the formula into text using Find and Replace feature. Then applying Paste Special Transpose, we can transpose the original data. Again, using Find and Replace, we can transform the text back into a formula.
Step 1: Press CTRL+H, Find and Replace window will appear.
Step 2: In the Find what command box, type Equal Sign (=) and Replace with command box Hashtag Equal sign (#=).
Step 3: Tab on Replace All. All the cells will appear in the shape of the picture below.
Step 4: Copy the Name and Total Salary data column individually in two cells. Select any two blank cells (B14 and B15). Right-Click on the cells and Select Paste Special>> Transpose.
A picture similar to the image below will appear.
Step 5: Again, Press CTRL+H altogether. The Find and Replace window pops up. In the Find what command box type Hashtag Equal Sign (#=) and in the Replace with the command box type Equal Sign (=).
Step 6: Select Replace All. We get all the original data from the dataset.
Read More: Conditional Transpose in Excel (2 Examples)
Method 4: Using Direct Cell References (Dynamic)
When you refer a cell to an original cell, the referred cell reflects the data from the original cell. Suppose, you want the data of cell B4 to be displayed in cell C14. You can just type
This process is super easy to apply to a small dataset but impractical if the dataset is large. Therefore, initially prefix(pl) before the cell references are used then the Find and Replace feature replaces the prefixes with equal sign (=).
Step 1: In any two blank cells (C14 and C15), type plB4 (for Name) and plE4 (for Total Salary).
Step 2: Drag the Fill Handle horizontally close or equal to the number of rows of original data.
Step 3: Hit CTRL+H altogether, Find and Replace window pops up.
Step 4: Inside the Find What command box type pl and Replace with command box type Equal (=).
Step 5: Click on Replace All. All the prefixes (pl) get replaced with equal sign (=) and data from the original dataset appears.
To transpose formulas without changing references, we use the TRANSPOSE function, Find and Replace, Paste Special features as well as Direct Cell References using prefixes. TRANSPOSE and Direct Cell References methods are dynamic as the transposed data auto-update if any original data changes. The other two methods; Find and Replace and Paste Special don’t offer this type of convenience. Hope you find the above methods useful to use and comment if you need further clarifications or have something to add.
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- Excel VBA: Transpose Multiple Rows in Group to Columns
- How to Transpose Rows to Columns Based on Criteria in Excel
- Reverse Transpose in Excel (3 Simple Methods)
- How to Transpose Rows to Columns in Excel (5 Useful Methods)
- VBA to Transpose Array in Excel (3 Methods)