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

Turn Off AutoFill for a Table

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 to how to turn off AutoFill in Excel by applying functions and VBA codes.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Quick Ways to Turn Off AutoFill in Excel

In the sections below, we’ll show you how to disable the 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.

Turn Off AutoFill for a Table

1. Use 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

Use the Excel Options Function to Turn Off AutoFill

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

Use the Excel Options Function to Turn Off AutoFill

Using the AutoFill tool, you may get all of the values in a column.

Use the Excel Options Function to Turn Off AutoFill

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.

Use the Excel Options Function to Turn Off AutoFill

Step 2:

  • Select the Options function from the list.

Use the Excel Options Function to Turn Off AutoFill

Step 3:

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

Use the Excel Options Function to Turn Off AutoFill

As a consequence, you’ll get the outcome with no AutoFill tool available.

Use the Excel Options Function to Turn Off AutoFill


Similar Readings


2. Run 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.

Run a VBA Code

Step 2:

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

Run a VBA Code

Step 3:

  • Save the program and press F5 to run it.

Run a VBA Code

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

Run a VBA Code

Therefore, you will get back the AutoFill tool.

Run a VBA Code

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

Run a VBA Code

Read more: How to Use VBA AutoFill in Excel


3. Turn 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]

Turn Off AutoFill for a Table

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

Turn Off AutoFill for a Table

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.

Turn Off AutoFill for a Table

Step 2:

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

Turn Off AutoFill for a Table

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

Turn Off AutoFill for a Table


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.

Please do not hesitate to get in touch with us if you have any queries. Please leave a comment below to let me know what you think.

The Exceldemy experts will respond to your inquiries as quickly as possible.


Further Readings

Bhubon Costa

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

Leave a reply

ExcelDemy
Logo