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 3. Total comments: 202

  1. For example, if I have a cell =A1 and if I want to drag it down to be =A3 not =A2, is it possible?

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

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

  4. Method 3 for copying formulas without changing cell references! The one where you change the = to /, and back again!

    This is a life changer! I wish I knew this 5 years ago.

    Thank you!

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

    I want to copy cell data from say Sheet1'A2 and paste it to Sheet2'A2, the copied data will however have a formula. Is there a way I can copy the Same data from Sheet2'A2 and paste it to Sheet3'A2 as Values without having to do it manually?

    "Sheet1 has lots of columns and the final data doesn't need all columns, that's where Sheet2 comes in to format the data with the required columns and arrangement."

  7. Hi everyone!
    Hope your are doing great...
    Please help me out to resolve my query.. i need to paste the formula reference cell in another sheet in filtered cells but it changes the sequence rows..
    for example:

    Sheet 1:
    Row# Column#
    A
    1 500
    2 900
    3 1100

    Sheet : 2 (Filtered cells)
    Row# Column#
    P
    1 ='sheet 1'!A1 (500)
    3 ='sheet 1'!A3 (1100)
    4 ='sheet 1'!A4 (0)

    Kindly resolve it

  8. Hi everyone!
    Hope your are doing great...
    Please help me out to resolve my query.. i need to paste the formula reference cell in another sheet in filtered cells but it changes the sequence rows..
    for example:

    Sheet 1: Sheet : 2 (Filtered cells)
    Row# Column# Row# Column#
    A P
    1 500 1 ='sheet 1'!A1 (500)
    2 900 3 ='sheet 1'!A3 (1100)
    3 1100 4 ='sheet 1'!A4 (0)

    Kindly resolve it

  9. I am trying to select multiple cells (B2, H2, I2, J2, N2 & R2) from my S/S and insert the information into cell C2. I am using the below formula but it is not working. I need to add in mm after the numbers from Cells H2, I2 & J2 for measurements. Then also put line breaks in after each Cell.

    =B2,(IF(H2="NUMBER", H2&"mm")CHAR(10),IF(I2="NUMBER",I2&"mm")CHAR(10),IF(J2="number",J2&'mm",))) CHAR(10),N2,CHAR(10),R2

    I don't know what I am doing incorrectly but it is coming up as Error.

    1. Hello!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =IF(ISNUMBER(B2),B2&"mm "&CHAR(10),"")&IF(ISNUMBER(H2),H2&"mm "&CHAR(10),"")&IF(ISNUMBER(I2),I2&"mm "&CHAR(10),"")

      1. That worked fantastically and was just what i was after. Thank you so much :)

  10. Hi
    I want to copy formula and past it but I don't want to change column reference.
    example I want to multiply B2=A2*B1 and I copy that and past it to multiple cells for example I past it to B3 and I get B3=A3*B2 but I want it to be B3=A3*B1, B4=A4*B1can I past formula without changing column reference.

    1. Hi!
      Pay attention to the following paragraph of the article above — Copy a range of formulas without changing cell references

  11. Excel 2016 crashes when coping formulas in the formula view mode, even under safe mode. How to copy formulas of selected cells into the clipboard to paste into another editor for transposing?

  12. Hello... Hoping for some guidance here...

    In my case, A1 is |=NOW()|, B2 is |16:00:00| and B3 is the difference between the two |=A1-B1|. How do i convert B3 into decimal? I know the formula is |=B2*24|, but that is not giving me the actual result. I have tried conditional formatting for B3, but it is not giving me the actual result.

    The one that works for me is when i manually enter, for example, |00:45:00| in E2 and E3 shows |0.75| instantly (E3 for formula is =E2*24) and conditional formatting for E2 is Custom>h:mm:ss

    What am i missing here?

    1. Hello!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =(B3-INT(B3))*24

      If this is not what you wanted, please explain the problem in more detail.

      1. no worries... here it is... i start work at 1600 hrs. (B1). A1 is =NOW(). B3 is the time i have spent in my shift. hence A1-B1. for example if it is 2009 hrs. at the moment, B2 will show 4:09 (without any conditional formatting). i want B2 in decimals.

        now.... if i manually enter 4:09 in C1 and add =C1*24 formula on C2, i will get 0.75 as a result. reason? coz i added conditional formatting to C1 as Custom > h:mm:ss and C2 as Number

        I need B2 (shift hours worked) in decimals...

        1. Hi!
          Explain why 4 hours 09 minutes is 0.75? As a number, this is 4.15
          What result do you want to get exactly?
          Read how to convert time to decimal number, hours, minutes or seconds in Excel in this article.

          1. I m sorry. 04:09 is indeed 4.15 in decimals *when you type 04:09 manually*. i want difference of time now & 16:00:00 in decimals.

            i have been through the article. maybe i did not pick up what i need.

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

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

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

  16. Hi

    anyone can help me for this,

    IFERROR(SUMIFS('[OEM FY CYCLE.xlsx]DATA'!$P:$P,'[OEM FY CYCLE.xlsx]DATA'!$B:$B,">=01-07-2020",'[OEM FY CYCLE.xlsx]DATA'!$B:$B,"<=30-06-2021",'[OEM FY CYCLE.xlsx]DATA'!$F:$F,$B$9,'[OEM FY CYCLE.xlsx]DATA'!$L:$L,$J$2),0)

    Posting Date / BLOCKS/ OEM /VALUES
    B COLUMN / F /L /P.

    HERE i am looking for sum values in particular duration , once type the formula its working only same cell,

    if copy or drag its not working . values coming only 0, why my formula working only same cell.

    if its will work other cell mean what should i have to do .. any one please help me on this

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice.
      Copy the formula to another cell. Press F2 and see which cells it refers to. I think you will see the error.

  17. Fantastic guide! Thank you very much!

  18. Excellent guide! Just what I was looking for, thank you.

  19. Brilliant guide, I am an expert in Excel but there were few things in here that I didn't know. Thank you.

  20. Thank you! The simple section of: "Method 3. Copy formulas exactly by using Excel's Find and Replace" saved me DAYS of work.

  21. Hi!
    I would appreciate any help on the following matter: I have a column with MAX formula in every row. I have changed the range in the first row and I'm trying to find a way how to automatically update formulas on the rows below. The problem is that Excel doesn't recognize the new range. E.g. MAX(C801:C900)/MAX(C901:C1000)/MAX(C1001:C1100) is what I would like to have but Excel is offering instead MAX(C801:C900)/MAX(C802:C902)/MAX(C803:C903).
    Thanks so much in advance!

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

  23. Dear, Nice trick to replace "=" with "\" and vice versa for copy pasting formulas without changing cell references. It was helpful in a difficult situation. Thank you!

  24. I have made a splendid (probably very simple) excel sheet for calculating my weekly/monthly expenses etc and I would like to copy all the formulae, but not the data, to new sheets for different months. (I only want 3 months on a sheet so that I can see all the totals at once).
    So I think my question is, is it possible to lift all the formulae, but none of the data, from their cells in worksheet 1 and paste them into worksheet 2 so that worksheet 2 does exactly the same things as worksheet 1 did but with new data. And to do this in one go! Obviously I can do it a cell or row or column at a time but I can't find a way to do the whole table.

  25. OMG, thanks for the tip to use the notepad and alter via find/change in show formulas mode.
    Amaznig! Many thanks.

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

    I am trying to copy the formula down several rows but it changes to =SUM(sheetname!Q2), and I need the Number to change and Latter to constant. I hope that was clear enough. Thanks!

  27. Can you please help me out for below -
    i want to know how to remove duplicates in ROW. is there any formula or shortcut.
    please help to provide solution.

  28. THANK YOU FOR THIS!!!

  29. Method 2. Copy Excel formulas without changing references via Notepad
    ***This is a life saver***
    Thank you!

  30. Hi thanks for this. I want to copy a formula from one sheet to another in a workbook. I am using paste special formulas. But the name of the sheet in the formula (within the brackets []) needs to stay the same. When I do it now, the name changes to the current sheet I am working in. I tried putting a dollar in front of the brackets, but it was just a wild aspiration that of course didn't work.

  31. I'm using vlookup formula and I have to enter/change every cell to get exact value. If I copy the vlookup formula and paste the formula automatically changes according to cell column and rows. Please help me how to copy formula without changing the references cell which is exact I copied.

  32. Your information is an EXCELLENT resource
    I cannot thank you enough!!!

  33. I want to generate a formula for calculating the Gross Salary when one knows his/hers Net pay.

  34. Hi, I find your tutorial very useful. Still I cannot make full use of absolute refference and copy function. In brief, I have values in the first column and I need to count them every hour (data are every 10 sec) if the next column says "W". For that I am using COUNT IF function and absolute reference for the column. What I want to do, is to be able to advance to every hour (360 rows) with a paste formula function. And right now its just copying the same data from the previous cell. Could you help?

  35. Brilliant. This has been plaguing me for time immemorial. Thanks!

  36. Hello folks,

    I want to copy the whole raw with all the formulas in that raw but without the the numbers or information in cells.

    Anyone knows how to make it??

    Thanks

  37. How do i quickly do the following

    Data Formula
    a =a+1
    b text
    c =b+1
    d text
    e =c+1
    ... ...

  38. I had a format and all my formulas done and then got feedback to just change the row positions but when I copied and pasted it kept changing the cell reference so a friend gave me this trick.
    Highlight the cell or cells that you want to move (without changing cell reference). Click and hold shift and then go to the top of the cell you want to move and get the 4 arrow icon and click and hold the mouse (which puts in move mode) then drag the highlighted data up to the row you want. This will move the cell range without changing the formulas!!! Saved me hours of work.

  39. need column B to change to C, D, etc when drag down keeping the row (37) the same...

    ='PRK'!B$37
    ='PRK'!C$37
    ='PRK'!D$37
    :
    :

    is it possible? how? Thanks.
    when drag down

  40. Hi,

    I need to copy a formula from one cell to another cell in a series of 3,7,10,etc. Not able to do, when i
    tried the fill as series working for numbers not for the formula.
    Ex:- If A1 is ='Jan18'!AK113 then A2 should be in series of 7 like ='Jan18'!AK120.

    Hope you can help in this...!
    Thanks in advance...

  41. Hi, How would you copy the heading from this:

    (raw file format)
    Heading 1
    data
    data
    data
    data
    Heading 2
    data
    data
    data
    data

    to

    (preferred format)
    heading1 data
    heading1 data
    heading1 data
    heading1 data
    heading2 data
    heading2 data
    heading2 data
    heading2 data

    is there any shortcut for that?

  42. very usefull

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

  44. I tried using the ctrl and ` function and could see the formulas, I copied the whole column with formulas and then pasted it into a notpad.

    It did not copy many of the cells. I have ranges of rows that I have bunched up and they do not show on the spread sheet but are there. They do not copy.

    Why do they not have this as a option in one of the paste menus ? Many times I want the exact formula copied , I don't want to change the references. I cannot sit there and change all the formulas to absolute references.

    Also I noticed that if I have reduced a cell height to zero the select a bunch of cells in the same column with this cell included, the amount in the cell is not summed and shows the wrong value in the sum displayed at the bottom of the page.

    This was driving me crazy. why on earth would they program it this way. It took me 15 minutes to find a value in a hidden cell that got in there by accident.

  45. I have tired double clicking and dragging down the formula of first row to the below rows.
    It is showing me the result of first row in all the rows below.
    I need to press enter in each and every row to get output for that particular row.

    For example:

    anirudh s
    swaminathan a

    must be my out put when drag down the formula or copy the formula.
    instead i get

    anirudh s
    anirudh s only , when i go on the formula in the second row and press enter then only i get the correct answer.

    need help

    Thanks,
    Anirudh

  46. I am working on a scoresheet for athletic events. I have multiple weight classes. I have 7 events and overall score and rank in each weight class. I'm using "=IF(G18>0,(IFERROR(RANK(G18,G$18:G$37,IF(G$16="Low",0,1)),0)),)" to figure the points awarded for each event. How do I copy the entire weight class worth of formula to a new weight classes? Every time I try, it will change the G18 but not the G$18:G$37, which should be G39,G$39:G$58 after copying.

  47. Hello

    I would appreciate your help.
    For some reason I cannot paste a formula ONLY into the same cell in a different worksheet (same file) and have the cell references automatically adjust. It only pastes the exact same formula. These are simple formulas:
    =SUM(M9:M14). So instead of this formula adjusting to the rows say M9:M21 it stays at M9:M14. I don't remember this being that way - is there something I did that made this happen? The cells adjust if I do that in the same worksheet but NOT between worksheets.

    Thank You, James

  48. Hi
    I have a question what if you are dragging down and the formula is not showing the updated result, how do I return to the mode where dragging down a formula within the cell allows me to copy but the results will be updated as well
    thanks

  49. Under the section "How to copy formula without changing references" your Method 2 is brilliant and totally new to me. It saved me a couple of hours of tedious and error-prone labor. I would gladly pay $75 for the help you have given me. Is there a way?
    Jim J.

  50. > Hi! Great article! The Best in The Web! Congratulations & Respect! :-)

    >> One 'Add-in':

    >>> Ref. above sections: "Copy a range of formulas without changing cell references" & "Create an Excel table...":

    In order to copy whole column of formulas intact, in other words to 'duplicate' them:

    Create Table / Make formula, in a cell of a column adjacent to the Table, referring to Table's several columns - then: Table automatically expands to this cell and column and copies formula to this whole new column / select this new column of formulas / copy it to the next adjacent column outside the Table / Voila!: the formulas in the newest column are copied exactly the same as they are in the previous one - with no change of references!

    > Hope it will help! BstRgds.AR:-)

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)