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

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

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

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

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

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

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

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

  8. THANK YOU FOR THIS!!!

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

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

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

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

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

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

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

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

  17. How do i quickly do the following

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

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

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

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

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

  22. very usefull

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

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

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

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

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

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

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

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

  31. Thanks useful.

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

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

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

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

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

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

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

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

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

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

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

  43. Well done dear,

    Excellent Job. thanks so much for supporting the learners.

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

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

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

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

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

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

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

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