AutoFill is a handy feature in Excel that automatically fills in data for you (given that Excel recognizes the pattern that you specified).
This feature is turned on by default. It is denoted by a small fill handle seen at the bottom-right corner of a selection.
Some users don’t use the Fill handle and may find it a bit distracting. If you’re one of them, you may want to turn off AutoFill in Excel.
In this tutorial, we will cover the main ways to turn off AutoFill in Excel.
Turn Off AutoFill Using Excel Options
To turn off AutoFill using the standard method, follow the steps below.
- Once you’ve opened a workbook, go to the File Tab and then select Options.
- Using the Excel Options Panel, select Advanced.
- In the Editing options section, uncheck Enable fill handle and cell drag-and-drop.
- Click Ok.
- Now you won’t see the AutoFill handle, and Excel won’t populate the even number series if you attempt to drag down the column.
Also read: How to Turn OFF Flash Fill in Excel
Turn Off AutoFill Using VBA
We can also use a simple one-line VBA code to turn off the AutoFill option.
The Immediate Window in the Visual Basic Editor allows you to execute simple VBA statements and single lines of code. You will get an instant result upon pressing Enter.
To turn off AutoFill using the Immediate Window in the Visual Basic Editor, follow the steps below.
- Go to the Developer Tab, and in the Code Group, click on the Visual Basic option.
- Go to the View Tab and select Immediate Window.
- Type the following line of code in the Immediate Window and press Enter.
Application.CellDragAndDrop = False
You can also copy this line from above, paste it in the immediate window, then place the cursor at the end of the line and press enter.
- You will find that AutoFill has now been disabled programmatically.
- Simply set the property to True if you want to turn on AutoFill again.
Turn Off AutoFill in Table Formulas
Excel Tables have a special feature that automatically fills a formula down when you create a calculated column.
You can turn this feature off, though.
Let’s see how this works.
In our example below, we have an Excel Table which has three columns, namely Product ID, Standard Product Price, and the Price After 10% Discount Applied.
The third column will be a calculated column (where we would enter a formula, and it will populate the entire column).
To turn off the AutoFill formulas in the Excel Table, follow the steps below.
- Start by entering the following formula in cell C2 to calculate the price after the discount has been applied..
=[@[Standard Product Price]]*(1-0.1)
- When you press Enter, you will see that Excel automatically populates the rest of column C with the same formula. This is due to the Table AutoFill option, which is turned on.
- To turn this functionality off, go to the File Tab and choose Options.
- Using the Excel Options Panel, select Proofing.
- Click on the AutoCorrect Options… button.
- In the AutoCorrect Dialog Box that opens, select the AutoFormat As You Type tab.
- Uncheck the Fill formulas in tables to create calculated columns box.
- Click Ok and then Ok again.
- Now clear column C.
- If you fill in the discount formula in cell C2 again, you’ll find that the rest of the column will not automatically be populated.
In this tutorial, we have gone over methods of customizing the Excel interface to your needs by showing you how to turn off AutoFill in Excel worksheet, and in the automatic Table formula input in Excel Tables.
Other Excel articles you may also like:
- How to Autofill Dates in Excel (Autofill Months/Years)
- How to Turn Off Autosave in Excel?
- Excel Fill Series Not Working
- How to Turn OFF Scroll Lock In Excel?
- How to Clear Cache in Excel?
- Excel Shortcuts Not Working – Possible Reasons + How to Fix?
- How to Remove Dotted Lines in Excel
- How to Turn Off Error Sound in Excel?