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

  1. Hello. I am trying to help a friend with an Excel sheet. At this time his sheet looks like this: The numbers under the month represent the number of people with these reasons. All of a sudden, the boss wants it in a different format, and i can't get it where it needs to be for him.

    Right now it is like this:
    County Reasons Jan Feb March April May
    Adams Don't want 4 0 5 2 3
    Benton Don't have 0 4 7 8 2
    Benton Don't like 1 1 1 1 1

    Boss wants it like this -
    County Reasons Month Clients
    Adams Don't want Jan 4
    Adams Don't want Feb 0
    Adams Don't want Mar 5
    Adams Don't want April 2

    We have 39 counties and 10 possible reasons and 12 months. When I used the INDEX function, transpose or anything else, I just could not make it work. I don't want to have to write this all manually. It's impossible. Happy to work by phone if easier. Just let me know.

  2. how can i achieve this kind drag increment
    '=IF(B7>D7,A7,IF(D7>B7,E7))
    '=IF(B9>D9,A9,IF(D9>B9,E9))
    ,=IF(B11>D11,A11,IF(D11>B11,E11))
    ,=IF(B13>D13,A13,IF(D13>B13,E13))
    and so on...

  3. Well done dear,

    Excellent Job. thanks so much for supporting the learners.

  4. 1. Cut the Range of formulas.
    2. Paste itin the new location.
    3. Copy the contents, from the new location, that you just pasted.
    4. "Undo" the Cut-Paste operation (this will put the original content back to the original range).
    5. Paste the contents from the Clipboard(!) to the new desired location (Like 2 above).
    (The formulas will have the original references).
    IF it looks like a lot of work – take a look at the keyboard shortcuts:
    1. Ctrl-X
    2. Ctrl-V
    3. Ctrl-C
    4. Ctrl-Z
    5. Ctrl-V
    --------------------------
    Michael (Micky) Avidan
    “Microsoft® Answers" - Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2017)
    ISRAEL

  5. "W.O.228WO No-228 (Location.2.109/ Town Center) 21" in this value i want copy only 2.109 this is the word no 28-31 please tell me formula

  6. Pls help, i must copy a lot of cell info from 1file to another, downwards. But if i copy a range downwards and past it does not copy it in seq
    Ect
    +'[file name]'!$j$3
    And i select a range it only copy to the next range on new file +'[file name]'!$j$3 on all cell but i want it to be +'[file name]'!$j$4. +'[file name]'!$j$5. Pls help. Regards

  7. I would like to copy a changing daily value as it moves down a column in my main worksheet to a single cell in another tab. The cell location in this other tab always remains in this one location.

    Thank you for any feedback.

    Craig

  8. I would like to copy a changing value as it moves down a column in my main worksheet, to a single cell in a separate tab. It will not change cells in the separate tab but always copy to this single cell while it still changes daily in the main worksheet.

  9. I have a form on sheet 1. All the the raw data's i have are all on sheet 2 (all reference). When i enter a number on the form (Sheet 1) the information automatically goes to Sheet 3. What i'm thinking is how can i not make the result on Sheet 3 not disappear when i clear out the form on Sheet 1? and when i fill out the form on Sheet 1 again, how will i make the result be put on the next row on Sheet 3? Please help me...:-(

  10. These explanations are worthless. (1.) Nowhere does it tel us how to copy a back of cells and paste them somewhere without changing cell references, if even if that is possible or not in EXCEL. It is such a common need that it is ridiculous no to address it.

    (2.) The method of explain things is at sea. What die sit mean to:
    Ctrl + R - Copy a formula from the cell to the left and adjusts the cell references.

    For example, if you have a formula in cell A2 and you want to copy it to cell B2, select B2 and press Ctrl + R.

    What else if happening when you type Ctrl + R????? Are you clicking the mouse at the same time. One has no idea from reading the explanations. There are far too many hidden assumption that the authors think everyone knows about.

  11. HOW TO APPLY TRANSPOSED FORMULA IN MERGE CELL?

  12. how can i copy a formula without changing the 2nd column example: =SUMIF(D6,E1,C6) =SUMIF(D7,E1,C7) =SUMIF(D8,E1,C8) by dragging down.. thanks

  13. very helpful.

  14. Thank you very much!

  15. Hi,

    Could you help me out with a formula? i have the following values.

    Column A Column B Column C
    1- 300.00 Jan-17
    2- 400.00 Jan-17
    3- 600.00 Feb-17
    4- 1,500.00 Feb-17

    I would like to have a formula that, when column B is equal to Jan-17 then multiply column A by 25% and shows that 25% in column C. But i want to add the 25% off all columns that shows as Jan-17 in column b. Is this possible

  16. Thank you! Thank you!

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

  18. Hi hope someone can help. I am trying to setup a formula that will repeat on each work sheet, but when I copy the formula to the various worksheets it put a 0 (zero) and I would like to have an empty cell that and the info to appear when typing it on the master sheet. For example I would like the employee name and employee number to appear on my master sheet for tracking vacation then then I would like this info repeated each month on seperate worksheet that I have created?

    John Smith 333000
    Bruce Jones 456097
    Allison Sweeney 236890
    Tim Horton 4789567

  19. Hi Team,

    IF I enter any values in specified blank cell(A1)according to the applied formula,I am getting the accurate system time in cell (A2), but if I try the same in next Cell (B2) the current time gets automatically updated on both cells(A2 & B2)...

    ​
    =+IF((ISBLANK($A2))=FALSE,NOW(),"")
    =+IF((ISBLANK($B2))=FALSE,NOW(),"")

    Example Output

    =+IF((ISBLANK($A2))=FALSE,NOW(),"") output A2: 12:27 PM
    =+IF((ISBLANK($B2))=FALSE,NOW(),"") output B2: 12:27 PM

    The time should be different for get accurate duration calculation please help me on this.

    Thanks,
    Jayaram

  20. I am trying to figure out how to replicate this formula setup down an entire column but when I do it changes to numerical order not the formula series I have created. Can anyone help??

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

  22. Thanks very much for this very clear and helpful set of tips. Excel was driving me nuts refusing to copy a table, and this provided some handy work-arounds.

  23. first time I've been able to get this work ! Thanks a big time saver.

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

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

  26. OMG THANK YOU SO MUCH !!!! though I feel stupid lol BUT THANKS !!!!

  27. correct

  28. Please give me a solution for the below issue.

    I need to copy all the different formulas altogether from some specific area and paste these formulas to another area. While pasting, it should paste only the formulas (all formulas) from the copied area and other cells which have numbers should not change.

    For eg:
    I have some data on column A and C. The cells A3, A8, A24 etc.. have different formulas. I need to get these formulas altogether on column C at the same rows (C3, C8, C24, etc..). But the other cells on column C should not be changed. Is there any way, other than copying one by one formulas?

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

  30. please give me the right way to create the formula for calculate a cell value from another two book(files)in a new book and then drag it

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

  32. The clearest tutorial on Excel I've come across - Thanks!

  33. hello

    Copy the cell to another cell by pressing the button

    thank you

  34. I am trying to track training for multiple people. I have a summary sheet with everyone on it and all modules listed. Then I have individual sheets for each individual. I want to fill in the individual sheets and have that auto fill in the summary sheet. I have used a formula like this ='OLD MODULES TO NEW'!B4. Is there an easy way to drag down and auto copy this? I want the sheet name to change on every line, but the cell reference will stay the same.

  35. I have stuck with this, I want to know which formula to use to get the total CRE and their Status
    CRE Name Status
    A Balance
    B Balance
    A Admission
    A Admission
    C Admission
    B Balance
    A Balance
    A Balance
    C Balance
    C Admission
    A Admission
    B Admission
    B Admission
    A Admission
    I want the status in the format of
    CRE Name Admission Balance Total Admission
    A
    B
    C
    Total
    Kindly help.

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