How to Turn Off AutoFill in Excel?

by

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.

Autofill in Excel

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.

  1. Once you’ve opened a workbook, go to the File Tab and then select Options.
Click on Options
  1. Using the Excel Options Panel, select Advanced.
Click on the Advanced option
  1. In the Editing options section, uncheck Enable fill handle and cell drag-and-drop.
uncheck Enable fill handle and cell drag-and-drop
  1. Click Ok.
  1. 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.
Autofill disabled
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.

  1. Go to the Developer Tab, and in the Code Group, click on the Visual Basic option.
Click on Visual Basic
  1. Go to the View Tab and select Immediate Window.
Click on Immediate Window
  1. Type the following line of code in the Immediate Window and press Enter.
Application.CellDragAndDrop = False
Enter the code in immediate window

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.

  1. You will find that AutoFill has now been disabled programmatically.
  2. 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).

Excel table

To turn off the AutoFill formulas in the Excel Table, follow the steps below.

  1. 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)
Entering a formula in an Excel table
  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.
Enter column is automatically populated
  1. To turn this functionality off, go to the File Tab and choose Options.
  2. Using the Excel Options Panel, select Proofing.
click on proofing
  1. Click on the AutoCorrect Options… button.
click on the autocorrect option
  1. In the AutoCorrect Dialog Box that opens, select the AutoFormat As You Type tab.
select the AutoFormat As You Type tab
  1. Uncheck the Fill formulas in tables to create calculated columns box.
Uncheck Fill formulas in tables to create calculated columns
  1. Click Ok and then Ok again.
  2. Now clear column C.
  3. 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.
Autofill disabled in Excel Tables

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment