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:
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:
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
Thank you. Problem resolved.
Thanks a ton!
I Just did a happy dance – been trying to find this trick for a few days. Thanks!
Problem solved to manual / automatic calculations, no idea how it switched to manual.. thank you!
Thank you very much…your suggestion worked on Excel 2013
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.
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!!!
You are very welcome Joanne
Thank you so much, I was going nuts!!!! Saved me tons. OMG.
Thanks God! Finally I could solve this issue!
Thanks – solved on 2016 exel
Thank you! Made my day end on a happy note!
Same here..got a bit apprehensive and frustrated as to what’s happening with my referencing.. But F9 had the magic..many thanks !
Thank you thank you thank you!! You have saved my JOB
This is soo helpful. Thank you
Thank you!!!
Thanks a lot!
This was driving me crazy. Thank you so much.
Thank you!
Amazing thank you!!! This was driving me crazy!
Thanks a lot.. It saved me lot of time from figuring out the issue…
Thanks a Lot!!
Thanks so much!
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.
OMG i was stuck for a day! Thank you. Do you have any tips to improve Vlookup times?
Another double thumbs-up. VERY pleased that this old friend is fixed.
Helpful. Thank you.
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!
What about 2016?
Excellent, thank you
But puzzlingly, if I change the setting from Manual to Automatic, it keeps reverting to manual
Any idea why?
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
This is awesome!! Thanks so much for the help!!
It worked perfectly! Thanks greatly.
Thank you very much
Thank you so much.
Thank you SO much!
very helpful. thanks
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!
Thank you Michel. I’m glad that you find my tutorials helpful
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))
thank you!
Thanks
Thank you thank you! THat was driving me CRAZY! 🙂
This was driving me crazy. Thanks so much! Saved my life.
THANK YOU!!! This has been driving me insane!!!
I am having this problem now, and all settings are set to automatic. F9 doesn’t do anything. Using Excel 2013.
thank youuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu
Thank you, very much..!!
Thank you so much! Can’t believe it was that easy!
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!
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
thank you very much
Thanks a lot.. It really helped.
THANK YOU SO MUCH
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
I pressed F9 and it did not work. So now I’m unable to copy any formulas still
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.
this worked for me yayyyy
Thank you very much.
thank you 🙂
thanx problem solved
Thanks tons my friend. You saved me a lot of thinking!
I was also struggling with issue, This helped me. Thanks a lot !!!
This post was so helpful. Thank you all!
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
Thanks for sharing the details. It helped me big time.
Thank you very much. This helped a great deal.
Changed to Automatic calculation and that fixed it. Thanks so much! Saved my sanity!!!
I thought my office suit has gone nuts and was thinking of reinstalling it… day saved.. Thank You!!
Awsome!
Awesome.. Thanks for helping
I have searched the web for 2 hours and tried 1,000 different things. FINALLY, something that worked!!
Thank you!
thank you; but i wonder how that changed at first time.
Thank You! Thank You! Thank You! I thought I was going crazy!
SO SO SO SO SO SO SO SO SO HELPFUL! Thank you! This was making me crazy.
Thank you! Like many other users, I have no idea why the default was switched to “Manual”