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.

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

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

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

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

  4. 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 to Kellie Cancel reply

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