Drag down formula and change ROW references instead of COLUMNS

excel excel-formula rows drag
Unknownymous picture Unknownymous · Mar 7, 2014 · Viewed 77.6k times · Source

In excel I have sheet1 contains total every month. (please see image below)

enter image description here

Then in my sheet2 it will display horizontally. (please see image below)

enter image description here

I used the formula =Sheet1!$B$1, my problem is when i drag it to the right it increments the column letters. How should i make it increment the row only and the column letter is constant in column "B".

Any ideas and alternatives is much appreciated.

Thank you in advance!

Answer

Tim Williams picture Tim Williams · Mar 7, 2014

You can use OFFSET:

=OFFSET(Sheet1!$B$1,COLUMN()-2,0)

Or TRANSPOSE: Select B2:M2 and then enter

=TRANSPOSE(Sheet1!$B$1:B12)

as an array formula (using Ctrl+Shift+Enter)

Related questions

Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More