Quickly Copy Excel Formula Down

Recently, I saw this tweet, from someone stuck on an Excel problem:

  • An Excel problem I have never solved – easily copying a formula down tens of thousands of rows where the row next to it may be blank.

Yes, it's easy to copy a formula down a column, if the adjacent column is filled. Follow these steps to copy the formula down to the first blank cell in the adjacent column.

  • Select the cell with the formula
  • Point to the fill handle, at the bottom right of the selected cell
  • When the pointer changes to a black plus sigh, double-click the fill handle


Fill Down with Empty Adjacent Column

However, if the adjacent column is empty, this fill handle trick doesn't work. How can you quickly enter formulas in a column, if you're setting up a workbook, with lots of empty cells?

Here's how I do it – maybe you have another trick to do the same thing:

  • Select the cell with the formula
  • Click in the Name Box, and type the range where you want the formula entered. In this example, the formula is in cell D2, and it should go down to cell D2000.


  • Press the Enter key, to select the range of cells
  • On the keyboard, press Ctrl + D, to fill the formula down through the selected cells.


You may also like...

31 Responses

  1. Gregory says:

    Fill down with empty cells in adjacent column is a common problem I solve by using the Ctrl+arrow keys to navigate to the first column, then last row, then as far as I can get to the formula column. The last little bit I’ll use the arrow keys to move over to the column with the formula, then use Ctrl+Shift+Up Arrow to shoot up to the first row where the formula is located, then use Ctrl+Down to copy the formula.

    Your method works great, but I don’t normally know what the last row of the range is, so I’m using Ctrl+ arrow keys to find out anyway.

  2. JonC says:

    “Ctrl+D” is not valid for some other countries. French people are using CTRL B.

  3. Shairal says:

    Excellent tip – did not know I could do this! :-)
    I usually copy my formula, then Ctrl+g and enter the cell I want to copy to, then Ctrl+Shift+Up Arrow and paste.

  4. Matt L says:

    Very handy, thanks very much!

  5. SK says:

    Great tip, and well work keeping in your Excel tool arsenal! Works in Excel 2013 as well. I needed to fill only 40 empty lines for a static form and this fit the bill perfectly.

  6. dvp says:

    There is a much easier way.
    You can still use the fill handle by simply selecting the cell in the blank column and the cell you wish to copy down. Double-clicking the fill handle copies your formula down the contiguous range without changing the blank column.

  7. Rlstevens14 says:

    I’d like to copy a formula down several thousand cells with only one aspect of an IF function changing in each cell. Excel automatically changes every reference to a cell +1. Is there a way to hold everything in the function constant except for 1 variable? Here’s the function I am trying to work with:

    =IF(auction1!E27=A2,auction1!E27,””) in cell B2
    =IF(auction1!E27=A3,auction1!E27,””) in cell B3
    =IF(auction1!E27=A4,auction1!E27,””) in cell B4 and so on…

    Thanks for any input!

  8. Lavanya says:

    This is cool. Thanks for the helpful tip!

  9. Ami says:

    Thank you!! A 1 minute Google search for “excel copy formula” just saved me 30 minutes of work! :D

  10. Trevor says:

    I used the Name Box option to copy a formula to 127,000 lines

    Brilliant !!!!!!!

  11. Sue Fox says:

    totally helpful! thank you.

  12. Haitham says:

    Good work uninterrupted for ever …
    Many thanks …

  13. Gary C says:

    Leave it to a real user to give much better info than the MS website. Thanks for a quick way to do exactly what I needed to do.

  14. Elzee says:

    This is an excellent tip – thank you! If your workbook is not set up to automatically calculate, press F9 when you are done the above steps to show the new calculations.

  15. Sharif from Bangladesh says:

    Thanks a lot. This is is new to me. And excellent one. Keep it up. We want to learn more from you. Thanks.

  16. Jason says:

    Thanks, double click, it works, but I can’t believe I never knew that. This is a big relief as I have 300,000 rows to copy a formula down through.

  17. YOGIRAJ says:

    Thanx,It was helpful..

  18. Nitin says:

    Thank you so much. Saved my time and effort.

  19. David watts says:

    If formula
    When I copy the if formula into many adjacent cells it does not work

    I use absolute reference to lock the column header
    In my first reporting column
    The formula picks up data from a column to the left
    I end up writing the if formula in all columns
    I feel that a copy and paste approach should work
    Pivot tables work
    But I want to instantly visualise the result in the relevant column

    Many thanks for a brilliant resource

  20. steve says:

    How do I copy up

  21. Miriam says:

    Thanks for the help, it will save me many minutes of dragging formulas down thousands of rows!

  22. David says:

    Debra, thank you so much for posting this.

    It may now be four years later, but that is some shortcut!

    I knew about the double-click but I have a database which has formulas in A2:Y2 (referring to data in another sheet) which I needed copying down to row 127541!

    Putting A2:Y127541 in the “Name” box and hitting Enter followed by Ctrl-D did exactly what I needed.

    You are a star!

    Thank you!

  23. Roth Lenzini says:

    YOU ARE AMAZING! I researched formulas-functions for a week. Couldn’t get any of them to correct the #NAME,etc! Almost gave up until you showed =PRODUCT as a function ( and how to do it)- WOW Your explanations are so clear and extremely workable. When do you open the Debra Dalgleish “online University”? Your webpages and connections are fine examples of HOW THEY SHOULD BE as an instructional format. With sincere APPRECIATION, Roth l.

  24. Girish Talpade says:

    Was struggling to resolve this problem of copying formulas down a column adjacent to an empty column. Your solution was perfect.
    Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *