How to …

How to Drag Autofill and Transpose a Formula in Excel

Have you ever needed to drag a formula horizontally, when the source data is formatted vertically?

For example, Sheet 1 has a bunch of data going down column A, and on Sheet 2, for whatever reason, you need to do a calculation and you want it across row 1.  Or vice versa.

It's been frustrating me for a long time to have to manually make the change.  But, I've found the way to drag a formula and have it transpose.  The code works for either kind of transposition (horizontal to vertical or vertical to horizontal).

Assume source data is in Sheet1, A1:E1

In Sheet2

Put in A1:
=OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1)-1,ROWS($A$1:A1)-1)

Copy A1 down by as many rows as there are columns in Sheet1, i.e. down to A5

A1:A5 will return whats in A1:E1 in Sheet1

Basically, set the $A$1 value to whatever the SOURCE is (the top left cell of the source range).  So, if your source is in C26:M37, you would put:

=OFFSET(Sheet1!$C$26,COLUMNS($A$1:A1)-1,ROWS($A$1:A1)-1)

Then as you drag the formula horizontally or vertically, it will work.

Hope it helps!

Linda - http://how.best-free-information.com - The "How" Blog