How to Turn Off Flash Fill in Excel (2 Easy Methods)

When Excel recognizes a pattern or sequential value, it suggests filling in the data to make our job easier. When the Flash Fill functionality is turned on, this happens. However, you may need to disable the Flash Fill option on occasion. In this tutorial, we will show you how to turn off Flash Fill in Excel.


Download Practice Workbook

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


2 Handy Approaches to Turn Off Flash Fill in Excel

We’ve provided a data set of a person’s name in the image below. We’ll send out some emails with the appropriate names. We’ll show the difference between filling the cells with Flash Fill turned on and off. To do this, we will use Excel’s Advanced Option as well as a VBA code.

1. Use the Excel Advanced Option to Turn Off Flash Fill in Excel

In the section below, we will use the Excel Advanced option to turn off the Flash Fill feature. Follow the steps below to accomplish this.

Step 1: Make Sure Flash Fill Feature is Turned On

  • Firstly, type an email in cell C5 with the corresponding person’s name.

Handy Approaches to Turn Off Flash Fill in Excel

  • As the Flash Fill is turned on, while typing in the next cell, it will show the suggestions in light grey, as shown in the image below.

Handy Approaches to Turn Off Flash Fill in Excel

  • Press Enter to get all the results with the help of automatic Flash Fill.

Handy Approaches to Turn Off Flash Fill in Excel

Step 2: Turn Off Flash Fill

  • Firstly, click on the File tab.

Handy Approaches to Turn Off Flash Fill in Excel

  • Select the Options.

Handy Approaches to Turn Off Flash Fill in Excel

  • Then, click on the Advanced option.
  • Finally, uncheck the Automatically Flash Fill option.
  • Click on OK.

Handy Approaches to Turn Off Flash Fill in Excel

Step 3: Result

  • Start typing the email, but this time no suggestion will be displayed.

Handy Approaches to Turn Off Flash Fill in Excel

  • Click the Data tab to operate the Flash Fill manually.
  • Then, click on the Flash Fill.

Handy Approaches to Turn Off Flash Fill in Excel

  • As a result, the following emails will be filled as the image shown below.

Handy Approaches to Turn Off Flash Fill in Excel

Read More: Creating Email Addresses from a Single Column with Flash Fill, TEXT Formulas & Commentator’s Text Formula Suggestions


2. Run a VBA Code to Turn Off Flash Fill in Excel

In addition to the traditional approach, you can also turn off the Flash Fill feature with Excel VBA. In the image below, you can see that the Automatically Flash Fill box in checked means the Flash Fill is turned on. Follow the outlined steps below to do this.

Sample Data

Step 1: Create a Module

  • Firstly, press Alt + F11 to open the VBA Macro.
  • Click on the Insert tab.
  • Select the Module option to create a new Module.

Sample Data

Step 2: Paste the VBA Code

  • Paste the following VBA code to turn off the Flash Fill.
  • Save and press F5 to run the program.
Sub TurnOffFlashFill()
'Command to turn off Automatic Flash Fill
    Application.FlashFill = False
End Sub

Sample Data

Step 3: Check the Advanced Option

  • Go back to the Advanced option.
  • Therefore, you will see that the Flash Fill is turned off.

Sample Data


Conclusion

I hope this article has given you a tutorial about how to turn off flash fill in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

Please contact us if you have any questions. Also, feel free to leave comments in the section below.

We, the ExcelDemy Team, are always responsive to your queries.

Stay with us and keep learning.


Related Articles

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