# How to Transpose Formulas Without Changing References in Excel

Get FREE Advanced Excel Exercises with Solutions!

Transposing data means data in Excel’s rows are shifted into Excel’s columns, whether auto-update is necessary or not. Transposing with 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’ll demonstrate 5 easy and different approaches to transpose data with formulas without changing the references in Excel. You may download the following Excel workbook for better understanding and practice.

## 5 Easy Ways to Transpose with Formulas Without Changing References in Excel

Suppose, we are using a sheet of Employee Salary Information. This dataset includes the Name, and their corresponding Salary, Allowances, and Total in columns B, C, D, and E respectively. Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Now, we’ll use this dataset to transpose data with formulas in various ways without changing references in Excel.

### 1. Using TRANSPOSE Function

In our first method, we’ll get help from the TRANSPOSE function. This function needs only one argument to accomplish its task. That’s the array argument. Let’s use the function to do our task. Just follow these simple steps below.

📌 Steps:

• At the very beginning, select cell B12 and enter the following formula.
`=TRANSPOSE(B4:E10)`

In this case, B4:E10 represents the range of the whole dataset.

• Then, press ENTER. But the formatting won’t come automatically this way. In this situation, we have to do it manually. So, here’s the final look after applying the same formatting as the original dataset. Read More: How to Transpose in Excel VBA (3 Methods)

### 2. Utilizing INDIRECT Function

From the heading, you obviously get a hint that we are going to use the INDIRECT function here. But, we’ll combine ADDRESS, COLUMN, and ROW functions in the formula as well. Let’s see it in action.

📌 Steps:

• Firstly, go to cell B12 and insert the formula below.
`=INDIRECT(ADDRESS(COLUMN(B4)-COLUMN(\$B\$4)+ROW(\$B\$4),ROW(B4)-ROW(\$B\$4)+COLUMN(\$B\$4)))`

Formula Breakdown

• In the first argument of the ADDRESS function, “COLUMN(B4) – COLUMN(\$B\$4) + ROW(\$B\$4)” defines the row_num by converting the column number of cell B4.
• Output → 4
• Similarly, in the second argument, “ROW(B4) – ROW(\$B\$4) + COLUMN(\$B\$4)” defines the col_num by converting the row number of cell B4.
• Output → 2

• Then, hit the ENTER key. ### 3. Incorporating INDEX Function

In this method, we’ll integrate the INDEX function with the ROWS and COLUMNS functions to transpose the data in Excel. Let’s explore the method sequentially.

📌 Steps:

• Initially, select cell B12 and write down the following formula in the Formula Bar.
`=INDEX(\$B\$4:\$E\$10,COLUMNS(\$B12:B12),ROWS(\$B\$12:B12))`

Formula Breakdown

• COLUMNS(\$B12:B12) → the COLUMNS function returns the number of columns in the array \$B12:B12.
• Output → 1
• ROWS(\$B\$12:B12) → the ROWS function returns the total number of rows in the array \$B\$12:B12.
• Output → 1
• INDEX(\$B\$4:\$E\$10,COLUMNS(\$B12:B12),ROWS(\$B\$12:B12)) becomes INDEX(\$B\$4:\$E\$10,1,1).
• INDEX(\$B\$4:\$E\$10,1,1) → the INDEX function returns the value of the intersection of this row_num and col_num in the B4:E10 array.
• Output → Name

• After that, press the ENTER key. Read More: Conditional Transpose in Excel (2 Examples)

### 4. Employing OFFSET Function

When you have a tool like Microsoft Excel, you can effortlessly perform such a task in different manners. You could also use the OFFSET function. So, let’s see the process in detail.

📌 Steps:

• Firstly, select cell B12 and put down the following formula.
`=OFFSET(\$B\$4,COLUMN()-COLUMN(\$B\$12),ROW()-ROW(\$B\$12))`

Formula Breakdown

• COLUMN()-COLUMN(\$B\$12) → the COLUMN function returns the column number of the reference cell.
• Output → 0
• ROW()-ROW(\$B\$12) → the ROW function returns the row number of the reference cell.
• Output → 0
• OFFSET(\$B\$4,COLUMN()-COLUMN(\$B\$12),ROW()-ROW(\$B\$12)) becomes OFFSET(\$B\$4,0,0).
• OFFSET(\$B\$4,0,0) → returns a section from a data set with a specific height and a specific width, situated at a specific number of rows down and a specific number of columns right from a given cell reference.
• Output → Name

• Later, press ENTER. ### 5. Applying Find and Replace Feature

Last but not least, we will employ the Find and Replace feature of Excel to execute the task. Let’s explore the method step-by-step.

📌 Steps:

• Primarily, select cells in the B4:E10 range.
• Then, press CTRL+C on the keyboard.
• After that, click on cell B12 and right-click on the cell. Immediately, the output becomes visible in the B12:E18 range.

• While keeping it highlighted, press CTRL + H on your keyboard. Instantly, the Find and Replace dialog box will open up.

• In the Find what box, write down =.
• Contrarily, write down &= in the Replace with box.
• Lastly, click on the Replace All button. Just after that, a new MsgBox pops up.

• Here, click OK to neglect it. • At this time, highlight the whole range (B12:E18).
• Then, press CTRL + C to copy the data. • Correspondingly, go to cell G4 where we want the final transposed output.
• Next, click on the Transpose (T) icon from the Paste Options. • As the transposed data gets laid in the G4:M7 range, press CTRL + H again. • Again, write down “&=” in the Find what box of the Find and Replace dialog box.
• In the Replace with box, put down “=”.
• At last, click on the Replace All button. Finally, it’s done. All the columns and rows get transposed with formulas. ## Practice Section

For doing practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it yourself. ## Related Articles #### 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 Advanced Excel Exercises with Solutions PDF  