Note: Video of solution at bottom of post.
A client of mine recently ran into an issue I hadn’t seen before. When she would click a formula cell and drag down to calculate it across multiple rows, it only copied the value. The formulas were correct, but the value being shown was from the original cell:
Solution
Somehow, sheet calculation had been set to manual. To fix this issue:
- Click on “Formulas” from the ribbon menu
- Expand “Calculation options”
- Change “Manual” to automatic
All of your calculations should now be done correctly.
Additional troubleshooting
If you’re still having an issue with drag-to-fill, make sure your advanced options (File –> Options –> Advanced) have “Enable fill handle…” checked.
You might also run into drag-to-fill issues if you’re filtering. Try removing all filters and dragging again.
I had already tried both of these and neither worked. You suggested deleted filters but didn’t say how to do it.
I have an identical problem with a spreadsheet, however mine is a bit different. I can autofill just fine using the standard process, but as soon as I create a macro and run the macro, failure. The formulas autofill just fine, but the values stay the same. Can anyone think of a reason why this might be? Appreciate any help!
I cant thank you enough 🙏🏻
If all else fails make sure that the format of the cells is correct, for example if you have data that you calculated with a formula in the cells like: =IF(RIGHT(E21)>=1,”-“,”N”) and you’re still experiencing a problem it means that the formula is right but it’s populating the result as text and there fore any additional formulas against this data/cell will not return results that you want to see… you would need to change the formula to something like: =IF(ROUND(RIGHT(E21,1),0)>=1,”-“,”N”) that way it deals with it as a number… just my experience – I broke my formula into separate cells to find the issue…
5 Years after you published this, and it’s still helping those with Excel blindness like me.
Thank you for explaining it with pictures! Soooo much easier.
You are amazing. No other webpages wrote it clearly.
Thank you it worked
I tried all of the suggestions, still not working, I have wasted an hour trying to figure this out, ended up having to calculate each cell individually, took me less time doing this than trying to find a solution to the problem. Luckily, there were not that many cells to calculate in my assignment, but if I ever had to use this on the job, it would be useless, this is supposed to be thee spreadsheet app, but pretty useless for this.
I am having the same issue. Formula is correct, Calculation Options is correct, Enable Fill handle and cell drag-and drop is checked and I still CANNOT drag a formula.
Thank you!! I was going nuts and this was just a simple fix.
You are a lifesaver ! 😊
I just posted a Thank You before trying. But in viewing both options, my ‘automatic’ was already checked; as well as my file/options/data fill check box. 🙁
It is still not working.
Always try to hold CTRL and then drag if just dragging doesn’t work. · The same process works perfectly if you want to drag horizontally and increase numbers.
Thank You so much! This totally helped me!
Anyone figure this out? I checked all settings but still not working. I drag and the cells remain empty!
THANK YOU!!!!!! I would not have figured this out on my own, and it made Excel useless to do anything I need to do. Next question – Why did it randomly start doing this? I did not change a thing.
Thanks a lot!! I have an examn in two days, and all of a sudden my excel had stopped working properly. You saved my exam!
This worked like a Charm. Thanks.
After I tried all of this, didn’t work – I found I had a filter on and that was somehow hindering it. After taking off the filter it starting working.
What and where was this filter? I am having the same problem.
THANKS A LOT!!! I WAS VERY WORRIED ABOUT WHAT WAS HAPPENING TO MY EXCEL
Thank You! You are a life saver.
This is very helpful. Thanks!
Thanks, this was of great help
You saved my computer’s life and quite possibly my own. THANK YOU!
Saved my life, thanks a lot!
The two modifications you suggested did not work – can you help with another solution?
Saved me from crying today. Major thanks for this!
Thanks, it saved my day.
Thank you so much! It saved me a day.
Thank you so much! I encountered this problem yesterday, had a nightmare about it last night, and this morning decided to Google but didn’t even know how to explain what was happening. Switching to automatic calculation solved it. What a relief.
also make sure your format is set to general or number (mine was text) – I was going crazy!
OMG!!!! Thank youuuuu!!!!!!
Thank you, fill series solution helped!
Thank you. I searched for a long time before I found this explanation.
much appreciated I just ran into this problem all of a sudden
Automatic is already selected under Calculation options and enable fill handle/cell drag and drop also already selected. Still not working. I do recently get a new laptop with Office 365 and have found a variety of little glitches to figure out – but no luck figuring this one out! Any other ideas?
did you figure this out. I’m having the same issue. My enable is on, calculation option is on Automatic and number at top is set. Cell are all highlighted but will not fill.
Very useful and easy to understand. Work for me. Save my day! Thank You.
Thank you, you’re great
I lost one jour looking for a solution.
I had exactly this problem!
THANK YOU!
usefull content, it worked for me, thank you 🙂
Morning, I have both Automatic selected under calculations options and confirmed that enable fill handle is selected in options, unfortunately it still isn’t working
Did you find the problem. I’m currently encountering the same issue and don’t know how to fix it
Thank you – I never deselected this option, but you saved the day!
not working
Awesome! Solved it!
Many thanks, would never have found the solution to this myself!
Thanks for this! Helped me today with a user