Excel 2011: Filling Down Columns

Posted on 2011-10-29
Last Modified: 2012-05-12
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
Question by:Cactus1994
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Try this formula


    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.)

    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    You cannot drag a formula down to make the references move left or vice versa.
    LVL 50

    Accepted Solution


    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


    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

    LVL 5

    Expert Comment

    Good discussion.

    Author Closing Comment


    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.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    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ā€¦
    Article by: Leon
    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.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now