Microsoft 365 & Power Platform Training and Consulting

Contact

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

—

1–2 minutes

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.

Discover more from Nate Chamberlain

Subscribe to get the latest posts sent to your email.

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

  1. Jayapriya CM Avatar
    Jayapriya CM

    Thanks for the steps, it worked!

    Reply
  2. Joy Toussaint Avatar
    Joy Toussaint

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

    Reply
  3. Darren Wellington Avatar
    Darren Wellington

    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!

    Reply
  4. Sudheer Babu Avatar
    Sudheer Babu

    I cant thank you enough 🙏🏻

    Reply
  5. David Erlank Avatar
    David Erlank

    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…

    Reply
  6. Shepherd Avatar
    Shepherd

    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.

    Reply
  7. Well-wisher Avatar
    Well-wisher

    You are amazing. No other webpages wrote it clearly.

    Reply
  8. rocky Avatar
    rocky

    Thank you it worked

    Reply
  9. Sossity C. Corby Avatar
    Sossity C. Corby

    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.

    Reply
    1. Alicia Shearer Avatar
      Alicia Shearer

      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.

      Reply
  10. Top 4 excel không kéo được công thức mới nhất năm 2022 – Final Blade

    […] https://natechamberlain.com/2018/09/25/solution-excel-drag-to-fill-not-working-value-is-copied-formu… […]

    Reply
  11. Top 3 kéo công thức trong excel không được mới nhất năm 2022 – Final Blade

    […] https://natechamberlain.com/2018/09/25/solution-excel-drag-to-fill-not-working-value-is-copied-formu… […]

    Reply
  12. Top 3 kéo công thức trong excel bị lỗi mới nhất năm 2022 – Final Blade

    […] https://natechamberlain.com/2018/09/25/solution-excel-drag-to-fill-not-working-value-is-copied-formu… […]

    Reply
  13. Kathy Avatar
    Kathy

    Thank you!! I was going nuts and this was just a simple fix.

    Reply
  14. Elvis Avatar
    Elvis

    You are a lifesaver ! 😊

    Reply
  15. Ashley Avatar
    Ashley

    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.

    Reply
    1. Bonita Avatar
      Bonita

      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.

      Reply
  16. Ashley Avatar
    Ashley

    Thank You so much! This totally helped me!

    Reply
  17. Heather Avatar
    Heather

    Anyone figure this out? I checked all settings but still not working. I drag and the cells remain empty!

    Reply
  18. Tonya Schoenfuss Avatar
    Tonya Schoenfuss

    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.

    Reply
  19. Pedro Avatar
    Pedro

    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!

    Reply
  20. Hildah Avatar
    Hildah

    This worked like a Charm. Thanks.

    Reply
  21. Gary Avatar
    Gary

    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.

    Reply
    1. Sossity C. Corby Avatar
      Sossity C. Corby

      What and where was this filter? I am having the same problem.

      Reply
  22. Carles Avatar
    Carles

    THANKS A LOT!!! I WAS VERY WORRIED ABOUT WHAT WAS HAPPENING TO MY EXCEL

    Reply
  23. Ayo Avatar
    Ayo

    Thank You! You are a life saver.

    Reply
  24. Scarlet Avatar
    Scarlet

    This is very helpful. Thanks!

    Reply
  25. RN Singh Avatar
    RN Singh

    Thanks, this was of great help

    Reply
  26. Casey L. Avatar
    Casey L.

    You saved my computer’s life and quite possibly my own. THANK YOU!

    Reply
  27. yazzzd09 Avatar
    yazzzd09

    Saved my life, thanks a lot!

    Reply
  28. Ken Avatar
    Ken

    The two modifications you suggested did not work – can you help with another solution?

    Reply
  29. aadharsingh Avatar
    aadharsingh

    Saved me from crying today. Major thanks for this!

    Reply
  30. Deepak Patel Avatar
    Deepak Patel

    Thanks, it saved my day.

    Reply
  31. Mahesh Avatar
    Mahesh

    Thank you so much! It saved me a day.

    Reply
  32. thebirdandthemoth Avatar
    thebirdandthemoth

    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.

    Reply
  33. Kellie Avatar
    Kellie

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

    Reply
  34. Mada Avatar
    Mada

    OMG!!!! Thank youuuuu!!!!!!

    Reply
  35. Reena Avatar
    Reena

    Thank you, fill series solution helped!

    Reply
  36. Peter Avatar
    Peter

    Thank you. I searched for a long time before I found this explanation.

    Reply
  37. Falcon Avatar
    Falcon

    much appreciated I just ran into this problem all of a sudden

    Reply
  38. Tamara Snider Avatar
    Tamara Snider

    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?

    Reply
    1. Tammy Avatar
      Tammy

      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.

      Reply
  39. Amyrah Avatar
    Amyrah

    Very useful and easy to understand. Work for me. Save my day! Thank You.

    Reply
  40. jammah Avatar
    jammah

    Thank you, you’re great
    I lost one jour looking for a solution.
    I had exactly this problem!

    Reply
  41. DEBRA Avatar
    DEBRA

    THANK YOU!

    Reply
  42. Rasheed Avatar
    Rasheed

    usefull content, it worked for me, thank you 🙂

    Reply
  43. felizia svendsen Avatar
    felizia svendsen

    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

    Reply
    1. Marjorie Bernadeau Avatar
      Marjorie Bernadeau

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

      Reply
  44. Dana Knowles Avatar
    Dana Knowles

    Thank you – I never deselected this option, but you saved the day!

    Reply
  45. Saiyan Avatar
    Saiyan

    not working

    Reply
  46. krozbit Avatar
    krozbit

    Awesome! Solved it!

    Reply
  47. Joe Avatar
    Joe

    Many thanks, would never have found the solution to this myself!

    Reply
  48. Roger Avatar
    Roger

    Thanks for this! Helped me today with a user

    Reply

Leave a Reply

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

Discover more from Nate Chamberlain

Subscribe now to keep reading and get access to the full archive.

Continue reading

Code of Conduct - Terms and Conditions - Privacy Policy