MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Turn Data Sideways with a Formula


July 30, 2020 - by Bill Jelen

Excel Turn Data Sideways with a Formula. Photo Credit: Pauline Loroy at Unsplash.com

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.

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.

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.

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.

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.

Title Photo: Pauline Loroy at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.