How to disable or stop auto fill feature in Excel?
In Excel, Auto Fill feature is useful for us to fill a list of same values or a series of sequential values. Normally, it is visible at the bottom-right corner of a selection, but sometimes, you want to turn off it to avoid some accidents as following screenshot shown. Have you any ideas to stop applying this dragging and dropping feature in Excel?
|Enable Auto Fill feature||Disable Auto Fill feature|
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Get into the Options dialog box to turn off the auto fill feature, you can do as follows:
1. Click File > Options in Excel 2010/2013, or click Office button > Excel Options in Excel 2007.
2. In the Excel Options dialog box, click Advanced from the left pane, and uncheck Enable fill handle and cell drag-and-drop option under Editing options section in the right pane, see screenshot:
3. Then click OK button to finish the setting, and you can’t apply the fill handle feature from now on.
Here is also a simple VBA code can help you to disable the auto fill feature.
1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Then click Insert > Module, and paste the following macro in the Module Window.
VBA code: Disable the auto fill feature in Excel
Sub Disable_Cell_Drag_Drop() Application.CellDragAndDrop = False End Sub
3. Then press F5 key to run this code, and the dragging and dropping feature is disabled now.
Note: If you want to enable the auto fill again, please use the following code:
VBA code: Enable the auto fill feature in Excel
Sub Disable_Cell_Drag_Drop() Application.CellDragAndDrop = True End Sub
Recommended Productivity Tools