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:
2018-09-25_11-29-39.gif

Solution

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

  1. Click on “Formulas” from the ribbon menu
    EXCEL_2018-09-25_11-36-11.png
  2. Expand “Calculation options”
    2018-09-25_11-36-37.png
  3. Change “Manual” to automatic
    2018-09-25_11-35-39.png
    2018-09-25_11-31-34.gif

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.
EXCEL_2018-09-25_11-33-30.png

You might also run into drag-to-fill issues if you’re filtering. Try removing all filters and dragging again.

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

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

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

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

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

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

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

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