Bhubon Costa
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.
- 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.
- Press Enter to get all the results with the help of automatic Flash Fill.
Step 2: Turn Off Flash Fill
- Firstly, click on the File tab.
- Select the Options.
- Then, click on the Advanced option.
- Finally, uncheck the Automatically Flash Fill option.
- Click on OK.
Step 3: Result
- Start typing the email, but this time no suggestion will be displayed.
- Click the Data tab to operate the Flash Fill manually.
- Then, click on the Flash Fill.
- As a result, the following emails will be filled as the image shown below.
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.
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.
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
Step 3: Check the Advanced Option
- Go back to the Advanced option.
- Therefore, you will see that the Flash Fill is turned off.
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.
