![Excel 2019: Turn Data Sideways with a Formula Excel Turn Data Sideways with a Formula. Photo Credit: Pauline Loroy at Unsplash.com](https://www.mrexcel.com/img/excel-tips/2019/07/excel-2019-turn-data-sideways-with-a-formula.jpg)
Excel 2019: Turn Data Sideways with a Formula
Someone built this lookup table sideways, stretching across C1:N2. I realize that I could use HLOOKUP instead of VLOOKUP, but I prefer to turn the data back to a vertical orientation.
Copy C1:N2. Right-click in A4 and choose the Transpose option under the Paste Options. Transpose is the fancy Excel word for “turn the data sideways.”
![Copy the horizontal lookup table in C1:N2. Right-click in a blank cell. The fourth icon under Paste Options is called Transpose. Choose that and you will paste a sideways copy of the original table.](https://www.mrexcel.com/img/content/2019/07/LXFig-173.jpg)
I transpose a lot. But I use Alt+E, S, E, Enter to transpose instead of the right-click.
There is a problem, though. Transpose is a one-time snapshot of the data. What if you have formulas in the horizontal data? Is there a way to transpose with a formula?
The first way is a bit bizarre. If you are trying to transpose 12 horizontal cells, you need to select 12 vertical cells in a single selection. Start typing a formula such as
=TRANSPOSE(C2:N2)
in the active cell but do not press Enter. Instead, hold down Ctrl+Shift and then press Enter. This puts a single array formula in the selected cells. This TRANSPOSE formula is going to return 12 answers, and they will appear in the 12 selected cells, as shown below.![Select the 12 cells B4:B15. Type a formula =TRANSPOSE(C2:N2) but do not press Enter. Instead, hold while holding down Ctrl and Shift, press Enter. This enters a single formula in 12 cells at one time.](https://www.mrexcel.com/img/content/2019/07/XLFig219.png)
As the data in the horizontal table changes, the same values appear in your vertical table, as shown below.
![The results: as the original formulas in C2:N2 recalculate, the results in the vertical range are updated.](https://www.mrexcel.com/img/content/2019/07/XLFig220.png)
But array formulas are not well known. Some spreadsheet rookie might try to edit your formula and forget to press Ctrl+Shift+Enter.
To avoid using the array formula, use a combination of INDEX and ROW, as shown in the figure below.
=ROW(1:1)
is a clever way of writing the number 1. As you copy this formula down, the row reference changes to 2:2 and returns a 2.The INDEX function says you are getting the answers from C2:N2, and you want the nth item from the range.
![A different solution that does not require Ctrl+Shift+Enter. The formula for January in B4 is =INDEX($C$2:$N$2,ROW(1:1)). The ROW(1:1) is a complicated way to write the number 1.](https://www.mrexcel.com/img/content/2019/07/XLFig221.png)
In the figure below,
=FORMULATEXT
in column C shows how the formula changes when you copy down.![As you copy that formula down, the reference to ROW(1:1) automatically changes to ROW(2:2) and so on.](https://www.mrexcel.com/img/content/2019/07/XLFig222.png)
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1
No comments:
Post a Comment