Excel Transpose Formulas Without Changing References (4 Easy Ways)

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-Excel Transpose Formulas Without Changing References

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

TRANSPOSE (array)

array; the range of data you want to transpose.

Step 1: Select a range of blank cells close to the original dataset.

Transpose function-Excel Transpose Formulas Without Changing References

Step 2: Type the following formula in the active cell.

=TRANSPOSE (B3:E12)

Inserting transpose formula

Step 3: As TRANSPOSE is applied on an array. Press CTRL+ SHIFT+ENTER altogether. The outcomes will be much like the picture below.

Transpose formula result

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)

transpose formula selecting extra column

Step 5: Then Go to the Formula Bar and Hit CTRL+SHIFT+ENTER again. All the original dataset entries will now appear.

Final Transpose formula result

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.

Paste Special-Excel Transpose Formulas Without Changing References

Step 3: Right Click on the selected cell (B14), from the options select Paste Special>> Transpose.

Paste special feature

The whole range of data gets transposed similar to the image below.

Paste special result

 

Keep in mind that you cannot auto-update the transposed dataset by simply updating the original dataset via Paste Special Method.

Read More: How to Transpose a Table in Excel (5 Suitable Methods)


Similar Readings


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 (#=).

Find and replace feature-Excel Transpose Formulas Without Changing References

Step 3:  Tab on Replace All.  All the cells will appear in the shape of the picture below.

Result of find and replace

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.

selecting paste special

A picture similar to the image below will appear.

Result of paste special

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 (=).

Find and replace window

Step 6: Select Replace All. We get all the original data from the dataset.

Final result of find and replace method-Excel Transpose Formulas Without Changing References

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

=B4
in cell C14 and the original data shows up in cell C14.

Exact cell reference-Excel Transpose Formulas Without Changing References

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

Exact cell reference

Step 2: Drag the Fill Handle horizontally close or equal to the number of rows of original data.

Drag handle usage

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 (=).

using find and replace in exact cell reference

Step 5: Click on Replace All. All the prefixes (pl) get replaced with equal sign (=) and data from the original dataset appears.

Exact cell reference result-Excel Transpose Formulas Without Changing References

Conclusion

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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo