Solution: Excel drag to “fill” not working – value is copied, formula ignored

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:


Somehow, sheet calculation had been set to manual. To fix this issue:

  1. Click on “Formulas” from the ribbon menu
  2. Expand “Calculation options”
  3. 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.

52 Replies to “Solution: Excel drag to “fill” not working – value is copied, formula ignored”

  1. I had already tried both of these and neither worked. You suggested deleted filters but didn’t say how to do it.

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

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

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

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

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

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

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

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

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

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

  10. also make sure your format is set to general or number (mine was text) – I was going crazy!

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

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

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

    1. Did you find the problem. I’m currently encountering the same issue and don’t know how to fix it

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.