Excel 2024: Turn Data Sideways with a Formula
June 28, 2024 - by Bill Jelen

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.
This article is an excerpt from MrExcel 2024 Igniting Excel
Title photo by Thom Milkovic on Unsplash