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

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

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

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

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

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

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

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

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

  9. How do i quickly do the following

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

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

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

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

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

  14. very usefull

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

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

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

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

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

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

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

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

  23. Thanks useful.

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

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

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

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

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

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

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

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

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

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

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

  35. Well done dear,

    Excellent Job. thanks so much for supporting the learners.

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

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

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

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

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

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

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

  43. HOW TO APPLY TRANSPOSED FORMULA IN MERGE CELL?

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

  45. very helpful.

  46. Thank you very much!

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

  48. Thank you! Thank you!

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

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

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