Is there a way to have a growth formula in excel that goes horizontally to work vertically?

Question

I am trying to move data from excel sheet A for the growth of a country's carbon emissions that is displayed horizontally (with country name in the far left row and years going across left to right; 1980, 1981, ...) in to a bigger excel sheet B which has the country's name in the column and years moving down vertically.

When I use the initial growth formula from the data in A and drag down the function it change so instead of inputting the year on year growth for each individual country it calculates the growth by comparing Afghanistan's first year with Albania's first year vertically, which doesn't show true values.

Is there a way to have a formula that is initially coded horizontally to work vertically?

Answer 1

This might be inspiration to you how to solve your problem

https://1drv.ms/x/s!AncAhUkdErOkgqR2B0gNpz5EJyvHxA?e=Y7l8c6

Indirect Method

E19 =INDIRECT(ADDRESS(ROW(B$11)+COLUMNS($E$19:E19)-1;COLUMN($B11)+ROWS($E19:E$19)-1))

A lot of Indirect formulas will make your workbook slow.

Offset Method

M19 =OFFSET($B$3;COLUMNS($E$21:E$21)-1;ROWS($E$21:$E21)-1)

This formulas you can copy down and to the right, they would be shorter if you just want to copy it down.

Index Method (easiest)

M33 =INDEX($B$3:$H$8;COLUMNS($E$33:E33);ROWS($E$33:E33))