AutoFill Copies Values Not Formulas

Nov 10, 2014 | 68 comments

Last year I recorded a short video about the “Fill Handle” (the little black cross that allows you to quickly copy or autofill) in Excel not working. If you haven’t seen the video, check it out here.

I received a query this week about the video – “The autofill just copies the content of the cell, not the formula”. In other words:

autofill_copies_values_not_formulas1

Point the mouse at the fill handle at the bottom right hand corner of C2 and double click. Excel should copy the formula to C3:C6. In fact Excel does copy the formula down but all cells appear to have the same value:

autofill_copies_values_not_formulas2

The reason this happened was because Excel was set to Manual Recalculation. To solve the problem, select any cell in the worksheet and press F9. Immediately, every formula in the file is updated.

Switching Manual Recalculation back to Automatic is done via the Excel options and is done differently depending on the version of Excel.

Excel 2013: File > Options > Formulas section

Excel 2010: File > Options > Formulas section

Excel 2007: Office Button > Options > Formulas section

Excel 2011: Excel >Preferences  > Calculation section

68 Comments

  1. san
    san on May 24, 2016 at 2:08 pm

    Thank you. Problem resolved.

  2. Avinash Bajaj
    Avinash Bajaj on May 30, 2016 at 10:13 am

    Thanks a ton!

  3. Danielle
    Danielle on June 8, 2016 at 8:10 pm

    I Just did a happy dance – been trying to find this trick for a few days. Thanks!

  4. Lianne
    Lianne on June 17, 2016 at 8:42 am

    Problem solved to manual / automatic calculations, no idea how it switched to manual.. thank you!

  5. Abey
    Abey on July 4, 2016 at 1:49 pm

    Thank you very much…your suggestion worked on Excel 2013

  6. Zsolt
    Zsolt on August 29, 2016 at 9:53 am

    Worked, thanks for saving some time for me! When someone turns it off it can be a mess..

    Although, I would add a hint:
    As being in ‘File > Options > Formulas section’ it seems to be a _global_ Excel setting. However, it is still saved in the xls or xlsx file!

    Meaning: the first file you open sets this setting in Excel and all the others you open will have the same setting until you close all windows of Excel.

    I have opened a file received from someone and it had the Manual Recalculation set. It was the first xls file I opened. After that all other xls files I opened – having that first one still open – were manual…
    Stupid logic, but no worries. Closing all windows and opening another file will be automatic by default.

    Just change the setting for the file having the manual recalculation on and save it.

  7. Joanna M.
    Joanna M. on October 24, 2016 at 9:54 pm

    This SAVED MY LIFE!!! I never realized there was a such thing between automatic and manual auto fill until I happened to me. I must’ve accidentally hit F9 by accident. Anyways, this was the ONLY material that I was able to find to help me solve this issue. THANK YOU!!!

  8. Mike Thomas
    Mike Thomas on October 26, 2016 at 6:49 pm

    You are very welcome Joanne

  9. CoolDeep
    CoolDeep on November 11, 2016 at 12:00 am

    Thank you so much, I was going nuts!!!! Saved me tons. OMG.

  10. Andrea
    Andrea on December 29, 2016 at 6:13 pm

    Thanks God! Finally I could solve this issue!

  11. J
    J on January 20, 2017 at 10:04 am

    Thanks – solved on 2016 exel

  12. Josh
    Josh on March 29, 2017 at 12:30 am

    Thank you! Made my day end on a happy note!

  13. Goc Egeruan
    Goc Egeruan on March 29, 2017 at 2:50 pm

    Same here..got a bit apprehensive and frustrated as to what’s happening with my referencing.. But F9 had the magic..many thanks !

  14. art
    art on October 19, 2017 at 6:22 pm

    Thank you thank you thank you!! You have saved my JOB

  15. Joseph
    Joseph on November 14, 2017 at 2:10 pm

    This is soo helpful. Thank you

  16. Manjot Kaur
    Manjot Kaur on November 16, 2017 at 9:35 am

    Thank you!!!

  17. JM
    JM on January 30, 2018 at 4:35 pm

    Thanks a lot!

  18. Nik
    Nik on February 1, 2018 at 2:05 pm

    This was driving me crazy. Thank you so much.

  19. Linda
    Linda on February 2, 2018 at 5:49 pm

    Thank you!

  20. Ashley
    Ashley on February 6, 2018 at 5:25 am

    Amazing thank you!!! This was driving me crazy!

  21. Nantha
    Nantha on March 27, 2018 at 3:32 pm

    Thanks a lot.. It saved me lot of time from figuring out the issue…

  22. Syed
    Syed on March 28, 2018 at 10:43 pm

    Thanks a Lot!!

  23. Heluna
    Heluna on April 5, 2018 at 3:09 am

    Thanks so much!

  24. Michael
    Michael on April 16, 2018 at 9:59 pm

    Thank you so much. I was able to reset the calculation mode. I had no idea, thought the file was corrupted and tried to restore it. Thank goodness for your help. Much appreciated! And I found this easily in Google.

  25. steve
    steve on May 10, 2018 at 10:58 am

    OMG i was stuck for a day! Thank you. Do you have any tips to improve Vlookup times?

  26. Don
    Don on May 26, 2018 at 4:32 pm

    Another double thumbs-up. VERY pleased that this old friend is fixed.

  27. Funkytown
    Funkytown on May 29, 2018 at 7:36 pm

    Helpful. Thank you.

  28. Sara
    Sara on June 6, 2018 at 12:52 am

    I can only see the hand when I click on the bottom right corner. It won’t let me drag and drop the formula to the cells below. Help!

  29. Christina Wright
    Christina Wright on June 12, 2018 at 7:04 pm

    What about 2016?

  30. Jack Whittaker
    Jack Whittaker on June 13, 2018 at 11:08 am

    Excellent, thank you
    But puzzlingly, if I change the setting from Manual to Automatic, it keeps reverting to manual
    Any idea why?

  31. Jack Whittaker
    Jack Whittaker on June 13, 2018 at 11:16 am

    I think I can answer my own question.
    I was changing the File /Options /Formulas to Automatic, and it wasn’t remembering my choice
    If I go to Formulas /Calculation Options and set Automatic, it works as expected
    A teensy bugette

  32. Julie Cook
    Julie Cook on July 31, 2018 at 5:36 pm

    This is awesome!! Thanks so much for the help!!

  33. Luke Dumbiri
    Luke Dumbiri on September 19, 2018 at 11:30 am

    It worked perfectly! Thanks greatly.

  34. Daniel
    Daniel on September 26, 2018 at 8:35 am

    Thank you very much

  35. Thaung Htet Kyaw
    Thaung Htet Kyaw on October 30, 2018 at 4:56 am

    Thank you so much.

  36. Kim
    Kim on November 9, 2018 at 8:09 pm

    Thank you SO much!

  37. Lisa
    Lisa on November 16, 2018 at 2:53 pm

    very helpful. thanks

  38. Michel Krens
    Michel Krens on November 22, 2018 at 9:13 am

    Dear Mike,

    You’re the best. I’ve searched a lot to find what was causing the value copying. Many sites just don’t go a bit deeper into Excel and just repeat the basic Office help texts provided by Microsoft on their Office support site. I love my job and yours!

  39. Mike Thomas
    Mike Thomas on November 24, 2018 at 3:29 pm

    Thank you Michel. I’m glad that you find my tutorials helpful

  40. Nazrul
    Nazrul on November 25, 2018 at 9:14 pm

    When i Copy this formula and pest it other cell for values but why it make change? =CONCATENATE(LEFT(L407,8)&”-“&MID(L407,9,5)&”-“&MID(L407,14,9))

  41. clay
    clay on December 4, 2018 at 1:05 am

    thank you!

  42. Jayanta Deb
    Jayanta Deb on December 12, 2018 at 12:24 pm

    Thanks

  43. Susie
    Susie on December 20, 2018 at 3:49 am

    Thank you thank you! THat was driving me CRAZY! 🙂

  44. Magda
    Magda on January 15, 2019 at 5:57 pm

    This was driving me crazy. Thanks so much! Saved my life.

  45. Mary
    Mary on January 16, 2019 at 5:46 pm

    THANK YOU!!! This has been driving me insane!!!

  46. Cheryl Caiola
    Cheryl Caiola on February 20, 2019 at 3:55 pm

    I am having this problem now, and all settings are set to automatic. F9 doesn’t do anything. Using Excel 2013.

  47. just me
    just me on March 16, 2019 at 9:05 am

    thank youuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu

  48. Kamlesh
    Kamlesh on March 23, 2019 at 10:47 am

    Thank you, very much..!!

  49. Tom
    Tom on April 15, 2019 at 2:29 pm

    Thank you so much! Can’t believe it was that easy!

  50. Nikki Houser
    Nikki Houser on May 1, 2019 at 8:55 pm

    I have version 2008 and F9 is not working. Also to turn this to Automatic vs Manual I don’t see you have the instructions listed for the 2008.

    HELP!

  51. MJ
    MJ on May 6, 2019 at 6:10 am

    Thanks a million. I was wondering why excel was behaving abnormally all of a sudden while I was in the middle of a financial accounting class

  52. Guru
    Guru on May 17, 2019 at 12:28 pm

    thank you very much

  53. Pankaj
    Pankaj on May 17, 2019 at 8:23 pm

    Thanks a lot.. It really helped.

  54. Veronica L
    Veronica L on June 3, 2019 at 11:34 pm

    THANK YOU SO MUCH

  55. Imie
    Imie on June 11, 2019 at 9:30 pm

    it does not work for me! setting was on auto. I tried to kick it a bit and set manual and back to auto, but no result

  56. Debra Pfleiger
    Debra Pfleiger on July 28, 2019 at 5:21 pm

    I pressed F9 and it did not work. So now I’m unable to copy any formulas still

  57. Pak Mega Place (www.pakmegaplace.com)
    Pak Mega Place (www.pakmegaplace.com) on July 31, 2019 at 12:55 pm

    I use the SUP function in excel 2013 it was working well but at the end of the table is no longer give me the results, the cell still empty.

  58. Anthony
    Anthony on August 13, 2019 at 11:28 pm

    this worked for me yayyyy

  59. Abdul Majid Khan
    Abdul Majid Khan on August 15, 2019 at 4:54 pm

    Thank you very much.

  60. Kp
    Kp on August 19, 2019 at 8:49 am

    thank you 🙂

  61. Bhagya
    Bhagya on August 20, 2019 at 6:52 am

    thanx problem solved

  62. Amos
    Amos on August 27, 2019 at 10:49 am

    Thanks tons my friend. You saved me a lot of thinking!

  63. Renu
    Renu on September 12, 2019 at 9:53 pm

    I was also struggling with issue, This helped me. Thanks a lot !!!

  64. Heather
    Heather on October 16, 2019 at 5:45 pm

    This post was so helpful. Thank you all!

  65. Saif K
    Saif K on October 23, 2019 at 5:44 am

    Thank you. Issue resolved. still confused why and how it changed to manual in the first place. Anyways things are back to Normal 🙂
    Thanks again

  66. Sujeet Singh
    Sujeet Singh on October 31, 2019 at 7:23 am

    Thanks for sharing the details. It helped me big time.

  67. Emmanuel
    Emmanuel on November 18, 2019 at 4:54 pm

    Thank you very much. This helped a great deal.

  68. Stacy
    Stacy on January 6, 2020 at 4:50 pm

    Changed to Automatic calculation and that fixed it. Thanks so much! Saved my sanity!!!

Submit a Comment

Your email address will not be published. Required fields are marked *