Unable to Drag Cell Corner in Excel to Automatically Increment Values

Posted: December 17, 2010 in MS Office
Tags:

It’s a favorite feature of every Excel user. Simply select a series of cells with incremental values and drag the cell corner down to complete the rest of the series. Most common usage scenario is when people try to complete the serial # column.

This feature is turned on by default but for some reason disappeared from my system. It took few days for me to figure out where the toggle button is. Let me share it for the benefit of my blog readers.

In Excel 2010

  • Click on File on top left corner
  • Click Options again on the left hand side (above the Exit ) button
  • It brings up the Excel Options dialog box and select the Advanced section on left.
  • The relevant settings are highlighted in the image below

image

Comments
  1. Zahid Rouf says:

    Useful post . Thanks for sharing this .

  2. JK says:

    did you ever find out how it was turned off in the first place? I had the same thing happen, but I know I didn’t turn it off.

  3. ttttttt says:

    Thanks it worked.
    But the option is available in the tools menu and not the File menu

  4. Ravi Sankar says:

    @ttttttt I’m using Excel 2010 and there is no Tools menu

  5. Vinay Kumar says:

    Thanks man.

  6. sdfsd says:

    Thanks Buddy I was searching for this info from a long time. How did it turned off. I don’t know

  7. sum says:

    Simple and excellent post…….Its working….:)

  8. Anoop says:

    thanks man…mine had disappeared suddenly

  9. NPJ says:

    i was breaking my head to figure out why this feature work on other machines and not mine. Finally got it today from your post ! Good stuff.. Keep sharing !

  10. excellent buddy…wasted a lot of time figuring this…now works…

  11. Smitha says:

    This problem was driving me mad!
    Thanks a ton for the solution!

  12. Harsh Gupta says:

    Thanks a lot. It works in Excel 2007 also. 🙂

  13. R.C.Arora says:

    I am unable to drag formula in Excel 2003 as cursor does not does not appear

  14. IceMan says:

    Awesome..tks a lot for sharing the info 🙂

  15. Nagesh says:

    wow, its very good better to remember

  16. mahesh says:

    ITS GOOOD… its excel options button in excel 2003 at last of recent document..

  17. Ravishankar says:

    NICE POST

  18. Soumen says:

    thanks, it helped me finish my report in time! any idea how it turned off in the first place?

  19. MS says:

    Very useful , it helped me at the right time..

  20. R says:

    Thank you . I was struggling to find this out…

  21. Vikram Aditya Singh says:

    thank u buddy. useful in 2007 too.

  22. sathish says:

    Thanks

  23. Vikram says:

    Thanks for this

  24. malini says:

    Thank you so much !!!!!!! 🙂

  25. Gangadhar says:

    Thanks, helped a lot when this feature was disabled in my system! 🙂 But I am wondering whether it got disabled when I played with Macros settings when filling my IT returns..! 😮

    • satheesh says:

      i think you are right. this problem started happening to me exactly since i filed my IT return. that xls was having macros.. that may be the issue.

  26. mannu says:

    thanks a lots…….i was becoming mad in manually entering the value..

  27. srikanth boorela says:

    Thank you very much man.. I was unaware of this option.

  28. Abid says:

    Thanx pal, saved me a lot of tension. I was about to reload the touchpad driver but you have spared me the trouble.

  29. Ajit Patil says:

    wow… u r grate.

  30. Venkat says:

    Thanks a Bunch Buddy

  31. RK says:

    Thanks a lot dear, Also works in Excel 2007

  32. Kunal Saxena says:

    I was using it extensivly but suddenly one day, I can’t drag column.
    Thanks for sharing this post, Now i can do it .

  33. Not sure how it got turned off in my machine… Got it back, thanks to this article!

    Glad to have found it!

  34. Deependra Bisht says:

    Thanks … this was quick !!!

  35. Nitin says:

    excellent buddy…turning off was very iritatting.. not seems to be perfectly fine

  36. Debasish says:

    Thanks for sharing – a real useful tip.
    Debasish

  37. Gunjan says:

    Thanks a lot it really helps…

  38. Sahitya says:

    Helped tons! Thanks for saving me from headache since a week…

  39. Sumit says:

    Thanks for the post. It was really helpful.

  40. SBitha says:

    Thanks!!! Though a simple step but very important n useful…

  41. sumi says:

    Thanks for this post… It really helped..

  42. Subasish says:

    It works. Thanks

  43. KP says:

    Very useful… Thanks!

  44. Arun says:

    Thanks for the post…. very useful

  45. Rakesh says:

    Thanks a ton.

  46. Imran says:

    Thankx

  47. mukund says:

    many thanks for this small thing but very useful function, Cheers

  48. Sudheshna says:

    Thank you, It worked for me

  49. Rajat Mahajan says:

    thanks Buddy…and I thought this feature was not available in 2010….really helpful..dont really know why was it turned off…

  50. Manjunath says:

    click on MS Office Butten left side upper circle, excel option>Advanced> enable fill handle and cell drag

  51. Spider says:

    Thank you for this post. Very useful.

  52. Ankur Gupta says:

    thanks for ur help

  53. Satya T says:

    Thank you. I was struggling for a while.

  54. Dr. Rajeev says:

    Thanks! got it done.

  55. Amit Sudhir says:

    Cool! got nice work pal !

  56. Chintan Shah says:

    Thanks it works

  57. Reddy says:

    Thanks a lot….I was releived to find this info 🙂

  58. satya says:

    thanks it’s really usefull

  59. ravindra23891 says:

    thanx boss!

  60. Edwin says:

    It works. Thanks for the post

  61. Prakash says:

    Thanks for Post, Its work ! Keep it up

  62. anshul says:

    Afterr dis setting also it not working it ia nt ahowing the increment value while dragging.

  63. shino raj says:

    thank you very much

  64. Vikash says:

    Thanks you

  65. deepu says:

    yeah!! Ravi,
    really this blog is help full ….
    thanks.

  66. NK says:

    Thanks a lot mate

  67. rajesh says:

    thanks dude …….
    thanks a lot

  68. Shreyakapoor says:

    Thanks alot god bless you..

  69. Sandeep says:

    Thanks bro!

  70. Orson says:

    Thanks a Lot!!!

  71. SAL says:

    THANKS MAN, FOR THIS USEFUL TIP

  72. Harish says:

    Thanks !!! really great !!! unable to solve this since a long !!!

  73. Mallika says:

    Extremely useful post!

  74. Raj says:

    Thanks for quick help

  75. Praful says:

    Thank you…

  76. Ankita says:

    Thanks a lot

  77. hjjh says:

    Very Useful…Thanks a ton…

  78. S says:

    That helped. Thanks buddy.

  79. sujata siruvuri says:

    Thank you so much for sharing the solution.

  80. M.Raghupathi says:

    Thanks dear,
    This was very helpful and I was able get the drag optiob back which which very important for excel dependent work

  81. John says:

    Thanks Worked!!!!!!!!!!!

  82. harry says:

    Thanks a lot , it solved my problem

  83. Thiru says:

    Thanks it works now 🙂

  84. Jads says:

    Awesome!!!

    Really helpful

  85. Au says:

    Very helpful, thanks.
    Just a note – if the Auto filter is on, Excel 2010 (sometimes) still won’t let you auto-increment by dragging. Turn filter off then drag to increment.

  86. Suresh Kumar says:

    Thanks buddy:) It helps me lot

  87. chetan says:

    Thank you

  88. KRISHNA says:

    Hi, Tnx for the Post, it helped me a lot….

  89. Simon says:

    Thanks so much, I just to have it but it disappeared, it was diving me CRAZY!!!!

  90. test says:

    woww simply gr88

  91. Minal says:

    woww simply gr88

  92. Preetam says:

    Thanks very much saved lot of time

  93. Sam says:

    It works on 2007. thanks you saved my time.

  94. Thank you so much.. It turns off by itself, now I know how to undo it.

  95. Mukesh Mukundan says:

    Thanks a Lot mate:-)

  96. Pranuuu says:

    Thank you very much dude. It helped a lot.

  97. Harry Bijl says:

    Not useful for me. Just in one sheet, it doesn’t work (anymore). So this option was not even turned off, but as a test, turning it off and on again… it still does not work. In the other sheets… it works fine.

  98. Yogesh says:

    Thanks from PPC(PROGRAMMING) group.

  99. Aariz wasim says:

    I can say only one thing that most help full
    Thanks sooooooo much

  100. taru says:

    very helpful..thanks.

  101. sresubra says:

    Thank you, it helped me a lot…

  102. Chandrachoor Basu says:

    Thanks buddy, you really saved me a lot of searching time! I successfully restored the drag option!
    Just some additional information. I use MS Office Excel 2003 and this option is available under TOOLS > OPTIONS > Edit (tab)!

    Maybe you can update this in your blog. Cheers and Thanks again!!

  103. Deepak says:

    Thought i was the only one having this problem. It works now. Thank you

  104. Kumud Sharma says:

    Thanks a lot Ravi, I was looking for this info for ages

  105. Anwar says:

    Thanks dear

  106. Ramesh says:

    Useful post thank you

  107. MILIND says:

    Issue resolve. Thank you for this useful post.

  108. Chaitra says:

    very useful. Thanks!

  109. vishwavikalp says:

    Thanks Ravi

  110. Vikram says:

    got solution !!!

  111. Yasin says:

    Thanks. Works Well

  112. s.v.satyanarayana says:

    very usefull input , thanks for sharing , save my day in Excel

  113. Indu says:

    Struggled with this during the whole of mba 1st year. never googled. repasted every time. ther eis a day for everything!

  114. Grovie says:

    thanks a ton

  115. athira says:

    Am using excel 2007 how to do for that?

  116. prasant says:

    thanks..

  117. Bhavana says:

    Thanks

  118. Dan Bodimer says:

    Well, it was working all morning, until 30 minutes ago, then it simply stopped working. I think it has something to do with the format of the item in the cell (mine is ### A). Now it simply copies the cells (ie. 1a 2a 3a 1a 2a 3a 1a 2a 3a etc. instead of 1a 2a 3a 4a 5a 6a 7a 8a 9a etc.)

  119. Piush says:

    thanks

  120. suresh says:

    Excellent stuff… Thanks man…

  121. Shery Varghese says:

    thanks bro, this info really helped me

  122. Thanks a ton… 🙂

  123. rajani says:

    thank you a lot

  124. Jerry says:

    many thanks dude!

  125. JK says:

    Very helpful… Thanks Ravi..

  126. Will says:

    Thank you.

    After enabling macros for another workbook I realized that my drag-fill setting was toggled off. I think Excel has a bug where a developer forgot to retain this setting after enabling macros.

  127. Srikanth Gooty says:

    Thank you very much

    Actually I prepared to format my laptop for this option only. Really It helped me a lot.

  128. Abhishek says:

    Hi, I have the options checked as shown, but I still cannot use the auto complete function, I can see the + at the corner.

  129. Yashodhan says:

    Thank you very much for the post. It helped me a lot when urgently required.

  130. Mannu says:

    I confirm that the option is disabled as soon as you open the ITR excel utility. Try it yourself 🙂

  131. Mannu says:

    ITR Excel Utility has a disableCut Macro which turns this setting off.

  132. NIjo Thomas says:

    thanks…… its a useful tip..

  133. chetan bhola says:

    Very useful post. thanks for sharing it.

  134. Thanks you so much for your this post. It saved lot of trouble for me.

  135. murali says:

    Thanks allllllot boss i have been suffering from fever as i lost this option. you made me soooo happy with your tip

  136. hjalihal says:

    Thanks Dude . You made my day!!!…

  137. Rahul says:

    Thank you so much dude.. it worked.. 🙂

  138. mani says:

    thanks dude, its working fine

  139. Aditya says:

    Thanks a lot.. I was struggling to find this option..

  140. ken says:

    thank you friend ,solve my problem

  141. Pratap says:

    thanks

  142. Santosh says:

    Thanks a Lot, it really helped me to fix my issue.

  143. Rambrijesh says:

    Thanks for given solution .. lots of time spend but now working

  144. Sumanth says:

    It worked thanks.

  145. anup says:

    thank .. anup

  146. arun says:

    Really helpful. thanks a lot

  147. Malwadkar says:

    Thanks a lot.

  148. Thank you very much. You saved a lot of time for us. we were striking our head how it got disabled 😉

  149. Rik says:

    thank you

  150. Bryan D'Mello says:

    Hi thanks. This was awesome. Was suffering for a few weeks and this helped sort it out in a few seconds.

  151. Kalyan says:

    Thank you very much. This was a very quick help.

  152. neel says:

    How this got turned off is not known

  153. Narendra Singh says:

    Thanks for sharing the Function.

  154. siddharth says:

    Thanks alot!

  155. Sunil Kumar says:

    thanks a lot..

  156. Sushant says:

    Thanks a lot😊

  157. Mangesh Suryavanshi says:

    Thanks a lot

  158. Manish says:

    Thank you very much

  159. Tahir Khan says:

    Thanks a lot Bro.. God Bless you.. 🙂

  160. CA Dinesh Deswal says:

    Thanks sir, great help.

  161. Praveen Athghara says:

    It really helped me a lot. Thanks a ton

  162. ACS says:

    thanks a lot ravi… it helped me…. 🙂 8008166118

  163. Fred says:

    Thanks for the assistance

  164. Shiman says:

    Thanks it works

  165. Arshin says:

    thanks. it worked

  166. Morris says:

    Thank you..at least i can get back to work now..

  167. Anuj Singhal says:

    Thanks a lot. It really helped

  168. Goutham says:

    Thanks 🙂

  169. Lancaster says:

    Dude! you’re the man, this situation was driving me crazy! thanks a lot!

  170. Nithin Appa says:

    That’s simply superb, Made my day! Thanks a Ton Ravi Sankar!

  171. MG says:

    Thank you loads. I was wondering what went wrong suddenly. Thank you again.

  172. calebunleashed says:

    Amazing! Thank you!

  173. Nice…!!! But wondering without changing any options. How possible automatically my cells behaves like this. Anyways found error. Thank you for guide. Really working now after changing the options.

  174. APPU says:

    THANK YOU VERY MUCH. THIS WAS OF GREAT HELP

  175. DE says:

    Thanks!

  176. abdullah says:

    thaaaanx a lof,, help me a lot 🙂

  177. Joy says:

    Thank you so much! its really helpful mostly in work.
    God bless!

  178. Mannan says:

    great works thanks,

  179. Swapnil says:

    Useful post. Thanks.

  180. Parvaiz says:

    thanks a lot.

  181. sanjib K Hota says:

    Thanks really help full, It worked…

  182. Ankur Jain says:

    Man thanks, worked at once !

  183. Udayabhanu Jena says:

    very good post, thanks for sharing, i was facing this problem since a day, but solved within a minute with the help of your post. Thanks again.

  184. Sagarnil Dhar says:

    Thanks buddy for this post!! Helped me out.

  185. Vinay says:

    Thanks! this helped me with the issue I was facing around.

  186. Pankaj says:

    Thank You so much it was so simple

  187. SAMGANGULY says:

    THANKS A LOT FRIEND

  188. Sami Sameer says:

    Simple and excellent post…….Its working….:)

  189. Syafiq says:

    Thanks. Really helping me to resolve my problem

  190. amitesh says:

    thanks it works.

  191. Ravindra Borude says:

    Thnx man

  192. Ouma says:

    Thank you…. that was so helpful.

  193. Vijay Jangada,Pune,India says:

    It helped today.Thanx for the write-up.

  194. Krishna says:

    it helped me a lot Thanks

  195. Tarun says:

    Thanks it really helped me

  196. Vaibhav says:

    Thanks

  197. Akash D says:

    Thanks…. very helpful.

  198. Ajay P says:

    Thanks for article.

  199. Meena Naidu says:

    Thank you so much

  200. Neeraj says:

    Thanks you Ravi

  201. Antony Jissin says:

    Thank you so much

  202. Mayuresh says:

    Thank ypu

  203. no name here says:

    7.5 years later, you’re still helping us noobs figure this out… option randomly disabled it self and I had no idea this was even an option to turn on/off.

    fixed, thanks!

  204. Chandani says:

    Thanks a lot. Its really helpful.

  205. harsh says:

    Thanks

  206. Bhupinder Sachdeva says:

    Thanks… Simple fix.. Not sure how that check box got unchecked..

  207. Georgina says:

    Thank you – just suddently wouldn’t work. you saved me.

  208. Taddz says:

    Thank You Sir! much frustration alleviated! 🙂

  209. Mathews says:

    Thanks for the tip, which is working now!

Leave a reply to shino raj Cancel reply