Results 1 to 9 of 9
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Copying Down a Long Column - Excel 2003

    I have a 1000-row worksheet. I have a formula in B3 that I want to copy down the entire column, B3 to B1000.

    The only way I know how to do this is to select B3, then drag down the whole column, then use CTRL+D to copy the formula down.

    It seems like there should be a more efficient way to select B3:B1000, but I'm not aware of it. Who can tell me about a better way?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you have value in A3:A1000, you can select the bottom right corner of B3 (cursor changes from wide plus to narrow plus) and dlb-click. It will autofill.

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Lou Sander (2013-03-27)

  4. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Very nice! I've been doing it the slow way since 1989. Sheesh!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    By the way, that works if values are adjacent to the left or the right column, however, it will only copy to the same amount of occupied adjacent cells. If you don't have values in an adjacent column with the same range, then it can be none by: Right click B3 and select copy. Hold in the shift key and select B1000. The whole range will be selected. Now right click and paste.

    HTH,
    Maud

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    Lou Sander (2013-03-27)

  7. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Well, this is ever more interesting now that I've played with it for a while. These methods are better than scrolling down and copying because they are faster, but there are some pitfalls:

    One blank adjacent cell will thwart the first method, maybe without it being detected.

    The second method doesn't have that drawback, but if the range into which the new stuff will be copied has any populated cells that you've forgotten about or are unaware of, they will be overwritten.

    If you drag down through the whole range then use CTRL+D, it's tedious, but at least you're looking at the cells you will be writing into, and you know they will all be written to.

    I think I'm going to adopt the two suggested methods, though, unless I'm REALLY concerned that something might go wrong. Thanks to both of you for helping me.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Lou

    Re: "I've been doing it the slow way since 1989. Sheesh! "

    What other shortcuts do you want?

    zeddy

  9. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    You can also type B3:B1000 in the name box, then enter your formula in B3 and CTRL+Enter to fill.

    Double clicking the fill handle is like double clicking the divider bar between two columns. The column to the left becomes the width to accommodate the largest entry in that column.
    Last edited by kweaver; 2013-03-28 at 11:02.

  10. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Fun with double-click.

    If you put the mouse on the border of the active cell (4-directional arrows appear) and double-click on the bottom/top border, the new active cell will becomes the last/first entry in that column. Double click on the sides and it's the last/first entry in that row.

    Double-click the format painter and it stays on until you turn it off (click or ESC).

  11. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Just another way (Blend between kweaver's post #7 and my post #4)

    Right click on cell B3 and copy. B3 will be shown in the name box. Now click in the name box and enter B1000. Hold the shift and press enter. The range will be highlighted. Now paste the the formula.

    By the way, it doesn't have to be just one dimensional. Use the left upper corner and right lower corner of a rectangular range

    Neat thing about Excel...there are a million ways to do the same thing
    Last edited by Maudibe; 2013-03-29 at 20:46. Reason: afterthought

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •