Switching Reports from Rows to Columns in Excel

TRANSPOSE and OFFSET solution

I was recently helping someone with a budget which they had built vertically, with the months going down the sheet. They then asked to display it horizontally, with the months going across the page. In the latest version of Excel this is straightforward.

The image below shows the structure we are starting with. You can download the example file at the button at the bottom of the post.

The yellow cells are for input. The formulas in cells A6 to E6 populate the cells beneath as they are dynamic array formulas. They will all expand based on the single entry in cell B1.

We want the highlighted cells in the image to be displayed across the sheet.

The issue is that range may expand if the value in cell B1 increases– see image below.

To handle that possible expansion, we can use the OFFSET function.

The formula in cell G1 that provides a flexible solution is.

=TRANSPOSE(B4:OFFSET(A4,B1+1,4))

The range to be transposed starts in cell B4. The end cell is provided by the OFFSET function as it on the other side of the colon in the range reference.

Based on the first image above, the OFFSET starts in cell A4. The second argument is B1+1. This value moves rows down from A4. In this case 12+1=13 rows down from A4 to A17. The 4 on the end moves 4 columns across from A17  to the right to cell E17 in this case.

The TRANSPOSE function uses the range B4:E17 to create the transposed range.

The TRANSPOSE function is in cell G1. It spills across and down as far as required to fit the range. Spilling is a term used in dynamic arrays.

This allows the calculations to be in one layout (vertical) but it can be displayed in another layout (horizontal).

You can learn more about dynamic arrays from this earlier blog post.

Download Example File

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.