A quick way to copy formula down hundreds of rows.


December 16th, 2005 by admin admin

If you have 2 columns with 1000 rows each (say column A and B) and you created a formula in the first row (e.g. in column C). In most cases, you would have copied the formula throughout the 1000 rows by dragging the formula down. And one problem you faced is that you would take quite a while to drag to the 1000th row. In addition, you are likely to drag beyond the 1000 rows and have to back track.

Now, there is a simpler way. It just take 2 seconds to copy the formula down 1000 rows, or even 10000 rows. What you have to do is to move your mouse cursor to the bottom right corner of the formula cell in column C until a cross appears. Then double click on your left mouse button. Excel will make reference to the cells to the left of the formula cell (Column B in this case) and copy the formula all the way down to the last row (reference to column B). And you are done.

Enjoy your weekend!

7 Responses to “A quick way to copy formula down hundreds of rows.”

  1. stocksystm Says:

    Thanks, this will save me a lot of time. I just copied a formula down 59,000+ rows. It takes the spreadsheet awhile to make the calcuations.

  2. Catherine Says:

    OMG…if you knew all the “solutions” I have read that didn’t work until I got to yours…so simple. Thank you!!!

  3. xJust Says:

    Hi Andy, me againThere are a few issues that have arsien. Are you still willing to help me with them, or have I exceeded my quota of requests?I am forwarding to you the most current copy of the worksheet. I worked all weekend long trying to resolve a couple of them, but i could not. One i think the only resolution is to enter the data manually. But I’ll put the question to you just in case you know a way it can be done.It is: L3 which is Total Days. The formula we are using is giving us an incorrect answer. Not to mention, i was looking for the answer to the wrong question besides. Total Days is supposed to tell us what number Trading Day of the Trade (Calendar) year the current day is. There are approximately 250 trading days in the year. Since January 1st, what trading day is the current day (today). The formula =Sum(u5:u3218) we currently have in L3 adds up the totals from the column No of days in a trade . It is an attempt to answer the wrong question, and it answers the wrong question incorrectly. My bad!Can you figure out how to come to a correct answer of what formula to use to get the result I am looking for? Or should we just resolve to entering it manually each day?The OTHER issue I worked on the most this weekend. The thing is, i think i’m using the correct formula. I searched and searched the internet and everything seemed to keep saying the same thing. Use the formula i currently have. But it is bringing back an incorrect answer. It is Cell G3, # of Winners. I counted manually and the answer should be 82, but it keeps saying 217. The formula entered is =COUNTIF(T5:T2318, >=0 ) which should be adding only the positive %s in the column. But it’s pulling more than just the positives from somewhere and i cannot figure out why, or how to resolve it? I believe Winning %age to be incorrect also, but should correct itself when # of Winners is corrected.Can you please help me with these two issues?Thanks for your consideration and all you’ve done so far!!

  4. Rama Says:

    ok i have a tough one if you don’t mind. Column A has the date i entered a trade, say 2 days ago. Column B can eihetr be blank or have the same date, or any day after that. I need a column that has the total # of days in the trade, or else blank. If the dates in column A and B are both the same, that’s zero days in the trade and needs to show zero. But if column B is empty, the total # of days column needs to be empty. and of course, if column B column A is 1 or more days then the totals column needs to reflect that. Is this possible???Thanks!PS I sent you an email with a copy of the spreadsheet i’m working on.

  5. BenneTan BenneTan Says:

    Hi Rama. Assuming your row starts from A2, do this formula in C2:
    =IF(B2=”",”",B2-A2)
    Copy that formula & press CTRL + DOWN arrow key. It should take you to the bottom of the worksheet. Press the LEFT arrow key twice to go to Column A. Press CTRL + UP arrow key to reach the last filled cell of Column A. Press the RIGHT arrow key twice to Column C again. Now press CTRL + SHIFT + UP arrow key & you should see all the necessary cells you want to paste highlighted. Press ENTER.

    let me know if it helps

  6. Sal Says:

    What if I want to copy it horizontally? For ex. =SUM(B3:B12) then =SUM(C3:C12) across all rows? Please help.

  7. BenneTan BenneTan Says:

    Hi Sal, where does your column end after pasting into column C? the double-clicking stated in the post does not work horizontally. but here’s an alternative method.

    assuming the formulas you want to paste ends in Column Z & you always have 12 rows you want to copy & paste. click on B3, scroll to Z12 without clicking on the worksheet. now hold on to the SHIFT key & click Z12. all the range you want to paste should be highlighted. press CTRL + R. it fills data to the right.

    hope that works for you.

Leave a Reply

You must be logged in to post a comment.

s2Member®