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

**Table of Contents**hide

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

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

**Similar Readings**

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

**C14**and the original data shows up in cell

**C14**.

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.

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

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