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: 202

  1. Hi. How to do the calculation of , example detergent 3L is rm23 per bottle. how its automatically out the total if customer buy 4 bottle?

  2. Hi Mr.Alexander, I have my subject marks in different subject mark shhets. I bring them them into my Composite marksheet by referencing them to the subject mark sheet. Then I take the marks to individual students report work sheet from the composite mark sheet. Each student will be doing different subject. Now I have included 2 different marks for each month, for each subject. So in the composite mark sheet, I have included all of them again. When I copy the formula, how will I make it to refer to the new mark, which is some 10 columns away?
    =MasterSheet!C8 =MasterSheet!C30 =MasterSheet!E8 =MasterSheet!E30 when I copy =MasterSheet!C8 =MasterSheet!C30 and paste in the next call, it changes to the relative cell address =MasterSheet!E8 =MasterSheet!E30 In between Column C and Column X I have many columns with different subject marks.
    But I want it to refer to =MasterSheet!X8 =MasterSheet!X30. How can I do that?

    Thank you,

    1. Hi!
      Unfortunately, without seeing your data, it is difficult for me to give you advice. I cannot imagine all of your tables. Ask a more specific question or explain in more detail and I will try to help.

  3. Is there a way to copy a formula down a column if the spacing of the reference columns is not uniform?

    For example: I want the B column of sheet1 to reference the D column of sheet two, but the specific cells I need to reference are D2,D3,D6,D12,D16 etc.

    Sheet1 is a display of production numbers calculated on sheet2. As such, sheet one only needs one line per item, where sheet2 needs multiple, and varying, numbers of lines for each item.

    Thanks.

  4. How to fix decimals to apply percentage.

  5. Hi,

    How to copy the data from column C to B without affecting on rows that has no price changes.

    Column B Column C
    Product Description Price / UoM New Discounted Price
    Alaska Pollock Fillets $7.03 $4.00
    Brown Vinegar $0.87 $0.60
    Cheddar Cheese $12.14 no changes
    Chocolate Chip Cookies $1.11 no changes
    Corned Beef $1.53 $1.00
    Cornmeal $1.48 $1.00
    Couverture White Chocolate $6.36 $5.00
    French Dressing $1.37 no changes
    French Mustard $1.43 $1.20

  6. I have excel file with couple of tables, i only need extract two tables associated with other ones and paste it into separate excel file, when doing so all cells becomes as REF. is there a way to copy paste table without losing figures in cells?

    1. Hello!
      If you highlight a table on a worksheet and copy that range to another workbook, your formula references will be broken. In this case, I recommend replacing all formulas in the worksheet with their values before copying. For instructions, see here: How to quickly convert formulas to values in Excel.
      I recommend copying the entire worksheet to the new workbook as described in this guige: How to copy and move sheets in Excel.

  7. suppose that we have data in a row from cell A1 TO CELL AB1
    AND THE SAME GOES FROM A2 TO AB2 -A3 TO AB3 ETC ETC
    its possible by clicking copy at the cell A1 to genarate a copy fuction from row a1 to ab1 in order to paste the data of each row?
    please help
    thanks in advance

  8. Greetings,

    I'm trying to move my training data from a table to a more aesthetic view for my boss. Currently I'm using a formula in the pretty view to grab data for the first person, but I have to modify each person afterwards, because copying the first block of cells (A3:U10) results in getting data for the person ~7 rows down. Considering I have ~90 students to keep track of, this is less than desirable.

    Table example:
    1Name|Enrolled A|Score A|Enrolled B|Score B
    2Name|Enrolled A|Score A|Enrolled B|Score B
    ...
    7Name|Enrolled A|Score A|Enrolled B|Score B

    Aesthetic example:
    1NAME|Other|Other| | A | B |
    Other| Other|Other| | Other | Other | Other | Other |
    Other| Other|Other| |Enrolled A| Score A|Enrolled B| Score B|

    7NAME|Other|Other| | A | B |
    Other| Other|Other| | Other | Other | Other | Other |
    Other| Other|Other| |Enrolled A| Score A|Enrolled B| Score B|

    18NAME|Other|Other| | A | B |
    Other | Other|Other| | Other | Other | Other | Other |
    Other | Other|Other| |Enrolled A| Score A|Enrolled B| Score B|

    1. Hi!
      Sorry, I do not fully understand the task. For me to be able to help you, please specify which formula you mean and describe the problem in more detail.

  9. Thank you for saving what's left of my hair with that easy column formula trick. Much appreciated!

  10. Hi, i have made a formula that retrieve data (product description) from one sheet to another when i type a number (text) into a cell, but when i try to just copy / paste, my code doesnt work, Can anyone explain me why?

    Thank you in advance

    1. I got it for everyone else, It has spaces behind the numbers that needs to be deleted.

  11. Hi
    how to copy a formula from a cell to another cell that is one row below and one column to the right please
    For example in cell d3, i want the sum(a1:c1) ; in cell d4, i want the sum ( b2:d2) and so on
    Many thanks in advance

  12. Why are all your helps so freaking long winded and full of all kinds of diversions? I cannot read this post with so much garble for something so simple.

    1. Hi Will, sorry you're a hurt human. Hope you heal!

    2. Hi Wilmark,

      If you are looking for a simple solution, you can just read the very first example - coping the formula by dragging the fill handle covers 90% of cases. All other examples are for those who want to learn something new and sharpen their Excel skills.

  13. Method 3 is super cool tip. Thanks

  14. Thank you! I have my own tab to analyze & summarize a report sent to me monthly. Method 3 above was super effective and saved me time. Thanks!

  15. beautiful solutions from a beautiful person

  16. I have data in sheet2 as
    A1=sheet1! A1
    A2=sheet1! B1
    A3=sheet1! C1.
    Now, I need to copy/drag to whole sheet2 by giving leaving A4 blank & again as
    A5=sheet1! A2
    A6=sheet1! B2
    A7=sheet1! C2.
    Please give ideas

  17. Thanks a lot, very useful!

  18. Here is my issue,

    I want to copy a cell from my table on my sheet2 cell B4, on my sheet 1 cell A1.
    I made macro to add row with new information on my table sheet 2.

    My formula for my sheet 1 cell A1 is: A1='sheet2'!B4

    But every time I run my macro, my formula changing for A1='sheet2'!B5

    How should I write my formula to keep A1='sheet 2'!B4 ?

  19. 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!

  20. 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!

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

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

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

  24. 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!

  25. 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)

  26. 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."

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

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

  29. 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 :)

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

  31. 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?

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

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

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

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

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

  37. Fantastic guide! Thank you very much!

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

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

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

  41. 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!

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

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

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

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

  46. 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!

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

  48. THANK YOU FOR THIS!!!

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

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

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 :)