Comments on: How to copy formula in Excel: down a column, without changing references, etc.

In this tutorial, you will learn a few different ways of copying formulas in Excel: how to copy formula down a column, to all of the selected cells, copy a formula exactly without changing cell references or formatting, and more. Continue reading →

Comments page 2. Total comments: 87

  1. I've got a tip.
    I found out that if you use ctrl+x it will copy all of your referances to the new location. but if you want to copy all the information you ctrl+c copy the group of cells with the various relations then paste it to a new open location for temporary storage then go back to the group and ctrl+x the original group and paste it to the new desired location. then go back to the copied group and ctrl+c to copy it again and paste it back to the original location. go back to the copied group and delete it. What you are left with are two groups that reference the same cells This works for pretty much everything except things ctrl+x won't ie. dropdown list data locations.

    1. You just saved me SO MUCH TIME. Thank you Paul!

  2. Hello! When i drag the formula down, it doesn't adjust to the row in the formula, it only copies the original formula exactly and doesn't change to the correct row it's in. Is there a way for the formula to enable it to adjust to the specific row its in? Thank you!

  3. how to copy/fll
    c4, c8, c12... continue

  4. Hi there,

    How can I copy a formula with the return value with the original font color?

    Example:
    =XLOOKUP($D3,NoOwnersDec15!$P$2:$P$1799,NoOwnersDec15!$Q$2:$W$1799,"Not Found",0,1)&" "

    The lookup value and the return array (NoOwnersDec15) has a red font but I'm not able to return the same font color red using the option (values and source & source formatting or keep the source formatting).

    Thank you,

    Rose

    1. Hello!
      If I understand the problem correctly, then Excel formulas cannot change or copy the font color. This is possible with a VBA macro.

  5. I wish to copy the formula =VLOOKUP(AL6,Sheet1!E4:F11,2,FALSE) all the way to the last Excel cell in the column. I wish the only thing that changes is the AL cells such that:

    =VLOOKUP(AL10,Sheet1!E4:F11,2,FALSE)
    =VLOOKUP(AL2102,Sheet1!E4:F11,2,FALSE)
    =VLOOKUP(AL10147,Sheet1!E4:F11,2,FALSE)

    Please assist

    1. Hi!

      Simply, lock the array with the $ sign (absolute reference) and drag the formula down to copy it to the below cells:

      =VLOOKUP(AL10,Sheet1!$E$4:$F$11,2,FALSE)

  6. I have a daily report that part of it has an opening number and closing number for the day i.e. a totalizator. Each sheet is ID as 1st, 2nd...31st. I need day 2 to pick up the closing number from day 1 as the opening number and day 3 to pick up the closing number from day 2,, etc. I can easily write in the cell appropriate cell in day 2. =1stL36 which will bring over the closing number from day 1 to the opening number in day 2. Long winded how do I copy and paste this to all the cells at one time so it will update the sheet? When I copy and paste the formula keeps the first sheet 1st. I just do not want to edit everyday. There are more items that have the same process so it is not like I only have to edit this one event.
    Thanks

    1. Hello!
      If your worksheets are named 1,2,3, etc., this formula will return a reference to cell L36 of the previous worksheet.

      =INDIRECT("'"&(--RIGHT(CELL("filename"), LEN(CELL("filename")) - SEARCH("]",CELL("filename"),1))-1)&"'!L36")

      I hope it’ll be helpful.

  7. Hi,
    I'm using an autotext/shortcuts app to insert common used text and formulas into cells. I use the following formula a lot: =E2&" "&F2&" "&C2 to get the text of those 3 cells into one cell. The problem is I have to manually edit the row number.
    How can I write the formula so it dynamically changes to the current row it is pasted in (the column must stay the same), for example if I paste it in A9 the formula must be =E9&" "&F9&" "&C9 & if I paste it in A11 then =E1&" "&F11&" "&C11 etc.

    Thank you

  8. Hi there,
    I am doing a large data entry and need help.
    For one of my columns I need to copy the value to the cell underneath it, but I need this to that for the entire column.
    For example, I need it to copy every other line, if that makes since. So for line 4 I need it to automatically fill from line 3. So 3 and 4 should both have 11. Both 5 and 6 should have 10. What would the formula be for this and how do I implement it? thanks in advance
    1.12
    2.12
    3.11
    4. blank
    5.10
    6.blank

    1. Hello!
      The formula copies the value from the odd line to the next even line.

      =IFERROR(SUMPRODUCT(A1,--(MOD(ROW(),2)=0)),"")

      After that you can copy this formula down along the column. I hope it’ll be helpful.

  9. cell I54 shows my total overtime hours
    I need cell H57 to calculate if the hours less than or equal 20 to be multiplied by $54.69, and if the hours is more than 20 and less than 40 to be multiplied by $62.5, and the last thing if the hours is equal or more than 40 to be multiplied by $70.31.
    please advise how to write this equation
    BR
    Ahmed

    1. Hello Ahmed!
      Please try the following formula:

      =IF(I54<=20,I54*54.69, IF(I54<40,I54*62.5,I54*70.31))

      Hope it will be helpful.

  10. 1-Jan-1996
    1-Jan-1962
    1-Jan-1972
    1-Jan-1956
    1-Jan-1998
    1-Jan-1978
    1-Jan-1994
    1986
    1997
    1979
    1989
    1996
    1983
    how can I copy the those first 3 rows and extend them to those years list without changing them?( I mean copying 1-jan)

    1. Iradakunda:
      If I understand your question correctly you can copy those rows by first selecting them and then move your mouse to the lower right corner of the selection over the solid square. When the cursor turns into a solid black cross click and drag the selection down the column or across the rows.

  11. HEY GUYS. I THOUGHT THIS MIGHT BE HELPFUL I DID THIS IN ALL CAPS ON PURPOSE...
    FOR ALL THOSE WHO WANT TO COPY A RANGE OF CELLS WHILE KEEPING THE REFERENCES(THIS IS IMPORTANT CAUSE IT CAN SAVE A LOT OF TIME WHILE EDITING)
    I'M NOT SURE IF IT WAS MENTIONED IN THE TUTORIAL ABOVE BUT WHILE READING I GOT IMPATIENT AND TRIED THIS AND IT WORKED... WELL HERE GOES.
    FIRST COPY THE ENTIRE WORKBOOK THAT THE CELLS ARE, IN FOR THOSE WHO DON'T KNOW HOW TO.. GOOGLE IT IT'S EASY.. THEN ONCE IN THE COPIED WORKBOOK "CUT" THE CELLS YOU WANT AND PASTE THEM IN THE ORIGINAL BOOK WHERE DESIGNATED... EASY PEASY

    1. Dude, you just save my work life!!! Thankssss

  12. To copy and paste a cell's content without changing references, you write:

    >> Open Notepad or any other text editor and press Ctrl + V to paste the formulas there. Then press Ctrl + A to select all the formulas, and Ctrl + C to copy them as text.

    This is entirely unnecessary. Simply enter the cell first (F2 or Cmd-U) and then paste the content. No need to use a separate text editor.

    (Kinda makes me wonder if you know whut your doin...)

    1. Dear Leo,

      F2 works fine when copying a single formula. The section you refer to shows how to copy multiple formulas in a range of cells keeping all cell references intact.

    2. You are nasty, don't reply if you don't want to help someone.

      (Kinda makes me wonder if you know whut your doin...)

  13. I am copying from one sheet to another but need to skip every

    On sheet 1, i have # in cells c4, e4, g4, ect (every other column).

    I want to copy the above row to another sheet. BUT I need to skip the empty columns. So on this sheet I want a consecutive row of # from the other sheet.

    ex: on the second sheet I want, cell g4 to hold sheet1!c4. then h4 to hold sheet1!e4. then i4 to have sheet1!g4... and on. Is there a way to create the first two formulas and then copy the remaining?

    I've tried to make the first two and drag, but it messes up the column letters.

    1. I too, would love to find out if this is possible. Please let me know if you have found a solution.

  14. Hi

    I have a large amount of spreadsheets with amounts aleary in the cells. I need to add 60% to each cell amount. I have tried using the F4 formula and dragging it down but it is changing the original amount in the cell to the amount of the cell above.

    Can you please help

    1. I have a formula that uses the MATCH function. I need to copy it down a column where the MATCH goes up in sequential order. Is this possible some how?
      Here is the formula.
      =IFERROR(IF('Main BOM'!$N$1="NO",(INDEX('Main BOM'!$A$3:$A$75,MATCH(1,'Main BOM'!$AA$3:$AA$75,0))),(INDEX('Main BOM'!$A$3:$A$75,MATCH(1,'Main BOM'!$Y$3:$Y$75,0)))),"")

      I have tried many ways to get the MATCH(1 to go up in sequence, but nothing has worked yet. Any ideas?

      1. Hello!
        The MATCH function only searches downward. I am assuming that you want to find the last match in the column. If you are an Office365 user, you can use XMATCH or XLOOKUP functions. Otherwise, I recommend using the LOOKUP function as described in this manual.
        I hope I answered your question. If something is still unclear, please feel free to ask.

    2. Hello Lydia,

      Most likely it happens because of incorrect use of absolute and relative cell references. Basically, if you have a percentage (60%) entered in some cell and you refer to that cell in your formula, you should fix the reference using the $ sign, like $A$1. A reference to the amount cell shall be relative or mixed like F4 or $F4, and you should refer to the top-most cell with data, ignoring column headers. If this does not help, please post the exact formula here, and we will try to help.

  15. Ok so here is my formula:
    =SUM(sheetname!P2:sheetname!P53)

    I am trying to copy the formula down several rows but it changes the 2 and 53, and I need the letters to change. I hope that was clear enough. Thanks!

    1. Hi Mona,

      Fix the row numbers using the $ sign, like this:

      =SUM(sheetname!P$2:sheetname!P$53)

  16. Is there a way to copy a formula that contains a range that keeps the range intact? For example:

    =sum(A1:A7)

    Then I want to copy that to the next row down and have it be:

    =sum(A8:A14)

    When I copy the formula and paste, all it does is increment by one, so I end up with:

    =sum(A2:A8)

    I am trying to summarize weekly data in a different section of the spreadsheet.

    Thanks!

    1. I'm having the same issue as above, and the answer given by dezzy was not helpful. Please advise?

    2. just keep the cell you want to remain with f4 that is for example =product(G2*$D2$) . This is what will appear if you type f4 after inputing the relevant firm.

  17. Dear Sir,
    I want to drag a formula in excel which should refer to alternate cells and not continuous cell references.
    Example. Cell A1 has a formula referring to data in Cell F6.
    Dragging the formula to Cell A2 should refer to F8 and not F7 and so on.
    One way is to drag the formula as it is and delete the alternate rows.
    Is there any way the formula can be modified to comply to the above requirement.
    Thanks & Regards

    1. Hi,

      If you want to dont want to change cell while pasting formula then make a formula like this for an example. Must use ($cell$column). Example below.

      Suppose want A1=100, B1=5, and B1=200 and want to calculate with B1 cell for both A1&A2 then formula will be like this..

      Result in D1 cell: =A1*$B$1, in cell D2 it will be =A2*$B$1.

      Hope you got it..

      1. Perfect - thanks!!!

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)