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|
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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