Excel 2024: 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.

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? Yes, using the TRANSPOSE function. In the following image, use =TRANSPOSE(C2:N2). The new =WRAPCOLS(C2:N2,1) will also produce the same result.

If you don't have Microsoft 365 and you don't have access to the dynamic array formulas, you can 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.

In the figure below, =FORMULATEXT in column C shows how the formula changes when you copy down.

---

---

Bonus Tip: Protect Rows with an Old-Style Array Formula

Here is an odd use for an array formula: Say that you don't want anyone to delete or insert any rows in one section of a worksheet. Scroll far to the right, off the screen, and build an array in those rows. Select Z1:Z9. Type =2 and press Ctrl+Shift+Enter. You can use any number, =0, =1, =2, and so on.

If someone tries to delete a row, Excel prevents it and shows a cryptic message about arrays, shown below.

Thanks to Excel Ace and Tracia Williams for suggesting this feature.