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

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

  2. very usefull

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

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

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

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

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

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

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

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

  11. Thanks useful.

  12. I'm trying to create a formula for a basic point of sale type stock control sheet. I'm using the IF function to pull prices from the purchases sheet into the recipes sheet, but I'm finding that if I need to add or remove stock items to the purchases sheet, deleting rows knocks out my formula - probably because I am using absolute references in my IF formula: =IF(Purchases!$A$5="Avo";Purchases!$W$5;0). I've been looking up the Excel INDIRECT formula, but am struggling to make head or tail of the tutorials I've read online - can anyone tell me how to create a formula that will keep the cell references as they are without regardless of whether or not I am deleting rows? Any help would be appreciated!

  13. this kind of formula is the best!!!!

  14. Thank you for the helpful tips...found exactly what I was looking for!

  15. Hi, I am trying to copy a formula from say cell A1 that has references from another Data source tab (say C1). I want to copy cell A1 and paste it in say cell A9 in the same sheet but want the cell reference in the formula to change by one row in the Data Source tab (from C1 to C2). How do I do this?

  16. Thank you so much! was reading all these different forums and every answer seemed excessively complex, 10 seconds into this page and you got it in one :)

  17. i would like to drug down a formula. it gives varring values correspondning to the intededed cells for a certain range downwards. but after that range the calculated values are the same regardless of variations in the in the data being calculated. please help

  18. I have got a Cash Flow Analysis Workbook that reorts on Daily, Weekly and Monthly values. When I wish to extend the spreadsheet, the Daily values are a simple copy / paste. However, on the Weekly analysis Worksheet, I need to jump 7 columns each time for the totals (i.e. Copying A1 to the next column gives me B1 when I actually need H1) I have the same issue with the monthly analysis Worksheet when I need to go from A1 to AD1 in the next column. Is there a simple way to copy with this jump on references or is it a case of manually amending the references each time|?

  19. I have an Excel spreadsheet which I made last year for a luncheon with about 300 people. I linked a page to it which creates name badges, using some of the information from the first list. This is an annual function and this year we would like to change the font on the name badges. I have created formulas so that if I change a line on the first badge, all of the badges will change as well. However, if I change the font on the first badge, no other badges will change fonts. Only the wording changes. How I get the font on all of the badges to change at one time to whatever is in that first badge? We will be using this spreadsheet for many years to come, but would to occasionally change the font, and I know there must be an easier way other than copy and paste! Thank you in advance for your help.

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

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

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

  23. Well done dear,

    Excellent Job. thanks so much for supporting the learners.

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

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

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

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

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

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

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

  31. HOW TO APPLY TRANSPOSED FORMULA IN MERGE CELL?

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

  33. very helpful.

  34. Thank you very much!

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

  36. Thank you! Thank you!

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

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

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

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

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

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

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

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

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

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

  47. correct

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

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

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

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