# How to Turn Off AutoFill in Excel (3 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

We may use Excelâ€™s AutoFill tool to fill a list of similar values or a sequence of consecutive entries. Itâ€™s generally shown at the bottom-right corner of a selection. However, you may want to disable it to ignore data redundancy. In this tutorial, we will show how to turn off AutoFill in Excel by applying functions and VBA codes.

In the sections below, weâ€™ll show you how to disable AutoFill in three different methods. To complete the task, we will first use the Options function and then run a VBA code. Weâ€™ll turn off AutoFill for a table thatâ€™s important to know subsequently.

## 1. Using the Excel Options Function to Turn Off AutoFill in Excel

For example, suppose you have a data collection of various best-selling items, each with its profit and quantity. Now, you want to find the total profit in cell E5 by multiplying the profit by the quantity.

You will get the result by applying the formula below.

=C5*D5

The AutoFill tool will appear on the bottom-left side of the screen at that instant, as presented in the figure below.

Using the AutoFill tool, you may get all of the values in a column. This will help you to autofill a column in Excel.

However, you want to turn off the AutoFill. To do this task follow the steps below.

Step 1:

• Go to the Ribbon and click on File.

Step 2:

• Select the Options function from the list.

Step 3:

• Then, unmark the check box tagged with Enable fill handle and cell drag-and-drop.
• Finally, press Enter.

As a consequence, youâ€™ll get the outcome with no AutoFill tool available.

Read more: How to Repeat Formula Pattern in Excel

## 2. Running a VBA Code to Turn Off AutoFill in Excel

You can use VBA codes to make it work in addition to applying functions. To complete the work, follow the steps given below.

Step 1:

• Firstly, press Alt + F11 to open the VBA Macro in your worksheet.
• Click on Insert.
• Then, choose Module.

Step 2:

• Paste the following VBA
``````Sub Disable_Cell_Drag_Drop()
Application.CellDragAndDrop = False
End Sub``````

Step 3:

• Save the program and press F5 to run it.

As a result, youâ€™ll see that the AutoFill feature has vanished from your current worksheet.

Notes. To turn on the AutoFill again, simply replace the previous VBA code with this one.
``````Sub Disable_Cell_Drag_Drop()
Application.CellDragAndDrop = True
End Sub``````

Therefore, you will get back the AutoFill tool.

Additionally, you can fill the blank cell with the same formula by applying the AutoFill tool.

## 3. Turning Off AutoFill for a Table in Excel

The preceding approaches will fail if the data set is formatted as a table. Because, after typing a formula in a column, the cells will fill automatically.

For example, we have entered the following formula in cell E5.

=[@Quantity]*[@Profit]

Each cell in the column is filled by itself when you enter the formula, as illustrated in the figure below.

But now, to turn off AutoFill, go through the procedures below.

Step 1:

• Firstly, select the Options function from the File
• Choose the Proofing
• Then, click on the AutoCorrect Options.

Step 2:

• Click on the AutoFormat As You Type option.
• Finally, unmark the leveled option in the below image.

As a result, youâ€™ll notice that when you re-enter the formula, it wonâ€™t fill automatically.

Read More: How to Auto Number Cells in Excel

## Conclusion

To conclude, I hope this tutorial has shown you how to turn off AutoFill by using functions and VBA codes. Examine the practice book and apply what youâ€™ve learned. Because of your support, we are willing to repay projects like this.

## Related Articles

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF