Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Transpose Formulas Without Changing References in Excel

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.

excel transpose formulas without changing references


Download Practice Workbook

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.

Dataset to transpose formulas without changing reference in Excel

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.
Not to mention, here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


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.

Using TRANSPOSE Function to transpose formulas without changing references

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.

Applied formatting in worksheet in excel

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.

Utilizing INDIRECT Function to transpose formulas in Excel

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


Similar Readings


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.

Incorporating INDEX Function to transpose formulas without changing references in Excel

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.

Employing OFFSET Function in Excel


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.
  • From the context menu, select the Paste Link (N) option.

Pasting Link to transpose formulas in Excel

Immediately, the output becomes visible in the B12:E18 range.

  • While keeping it highlighted, press CTRL + H on your keyboard.

Using Keyboard shortcut

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.

Find and Replace dialog box in Excel

Just after that, a new MsgBox pops up.

  • Here, click OK to neglect it.

Message box in Excel

  • At this time, highlight the whole range (B12:E18).
  • Then, press CTRL + C to copy the data.

Copying data to transpose formulas without changing references in Excel

  • Correspondingly, go to cell G4 where we want the final transposed output.
  • After that, right-click here.
  • Next, click on the Transpose (T) icon from the Paste Options.

Paste Transpose without changing references in Excel

  • As the transposed data gets laid in the G4:M7 range, press CTRL + H again.

Keyboard shortcut in Excel

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

Find and Replace dialog box in Excel

Finally, it’s done. All the columns and rows get transposed with formulas.

Using Paste link and Find and Replace to Transpose formulas without changing references in Excel


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.

Practice Section


Conclusion

This article explains how to transpose with formulas without changing references in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.


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