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

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.

Advertisements

Written by SharePoint Librarian

I'm a SharePoint Business Analyst and Jayhawk from the Kansas City Area.

Leave a Reply

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