Solved

Excel 2011: Filling Down Columns

Posted on 2011-10-29
251 Views
How do I fill a column down (drag and fill) using data that is spread across the spreadsheet?

The only way I can currently accomplish this is manually changing the column letters after I've done the drag and fill down ... which takes an eternity for the spreadsheet I'm working with.

It would seem there would have to be an easier way. Please see the attached spreadsheet for a little more clarification.

Thank you! Fill-Down-for-Data-Across.xlsx
0
Question by:Cactus1994

LVL 43

Expert Comment

Try this formula

=SUM(OFFSET(\$E\$2,0,0,1,ROW()-ROW(\$A\$2)+1))
0

Author Comment

Thanks, ssaqibh. That will work for my example, but I should have been more clear in my question.

Is there a way that you can hold down keys (for example shift+control, or alt+shift+windows) or something similar, so that whatever formula you have in a cell that you need to drag and fill down a column, will work referencing data spread across the spreadsheet?

Sorry I wasn't more clear -- my fault. (But I can also use your example for other future calculations.)

Thanks.
0

LVL 43

Expert Comment

You cannot drag a formula down to make the references move left or vice versa.
0

LVL 50

Accepted Solution

Hello,

if you enter the formula =A1 into cell A2 and then drag down, it will turn into =A2, =A3, etc.

But if you want to show the contents of A1, B1, C1, etc when dragging down, you can use this formula in A2 instead

=INDEX(\$1:\$1,ROW(A1))

Drag it down and it will show the contents of row 1 across. See attached.

That principle can be applied in more complex formulas, too. Is that what you're after?

cheers, teylyn

Book1.xlsx
0

LVL 5

Expert Comment

Good discussion.
0

Author Closing Comment

teylyn:

Sorry for the delay accepting your answer as a solution ... I must never have hit the submit button when I entered it before.

The solution you provided was exactly what I was looking for. This will save me a huge amount of time, and I appreciate your help!

Thanks again.
0

Featured Post

Suggested Solutions

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all theā¦
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.