How to turn off flash fill in Excel

Excel is a powerful tool for organizing and analyzing data. One of its features, Flash Fill, is a time-saving tool that can automatically extract and manipulate data based on patterns. However, if you find Flash Fill distracting or want more control over your data, you can easily turn it off.

In this tutorial, we learn how to turn off the Flash Fill feature of Excel by following the simple steps below.

Method – 1 Manually turn off Flash Fill.

Step – 1 Open Excel Options:

• Click on the File tab.
• Open Options.
• Excel Options menu will appear.

Step – 2 Turn off Flash Fill.

• In the Excel Options Menu click on Advanced.
• Under Editing options uncheck the box with Automatically Flash Fill.
• Done! You have turned off Flash Fill.
• To turn it back on. Follow the same procedure mentioned above and check the box corresponding to Automatically Flash Fill. This will enable the Flash Fill again.

Method – 2 Turn off Flash Fill using VBA Code.

Step – 1 Open VBA.

• Click on the Developer tab on the ribbon.
• In the Code group click on Visual Basic.

Step – 2 Add Module.

• After you click on Visual Basic a window will open.
• On that window click on the Insert tab and then click on Module.

Step – 3 Type the code.

• After adding the module type the following code.

• After typing the code, click the arrow pointing towards the right to run the code or press F5.
• Macros dialog box will appear, you can change or add the macro name and then click on run.
• FlashFill is turned off now.

Code explanation:

Sub: 

“Sub” stands for “Subroutine”. A subroutine is a block of code that performs a specific task or set of tasks.

Single Quote( ‘ ): 

It’s used for adding comments in the code. The program will not run the line of code that starts with ( ‘ ) as it is considered in a comment.

Application.FlashFill = False: 

This sets the value of the “FlashFill” property of the Excel application object to False. Setting the “FlashFill” property to False turns this feature off.

End Sub: 

This line marks the end of the subroutine.

Conclusion:

Flash Fill is a powerful feature that can help you save time and effort when working with data in Excel. However, if you find it distracting or prefer to have more control over your data, you can easily turn it off using the steps outlined above.