How to Turn OFF Flash Fill in Excel

by

Excel Flash fill helps quickly fill our Excel data by identifying a pattern and using the same pattern to fill the data in a column.

For it to work, you need to manually specify the result you want, and Flash Fill can fill the remaining cells in the column by following the same pattern.

For example, if you want to remove dashes from cells, you can enter the result you want in an adjacent column and then use Flash Fill to get the result for all the cells in the column.

However, it sometimes finds wrong patterns and makes incorrect suggestions.

Because of these reasons, some Excel users prefer to turn off Flash Fill in Excel.

In this article, I am going to show you how to turn off Flash Fill in Excel.

Method 1 – Use Excel Options to Turn Off Flash Fill in Excel

The Flash Fill function in Excel is activated by default.  Sometimes Excel is unable to find the correct logic or pattern in our data collection.

Then Excel will make the wrong suggestions. In such cases, it is best to turn off Flash Fill.

I have a name list in Column A of the below table. I want to enter the first name of each person in Column B.

Dataset with names

So, when I am starting to type the first name of each person, Excel suggests auto-filling that column as follows.

Flash fill shows suggestions when entering data

I want to enter Ben as the first name for the third person on the list. Excel Flash Fill, on the other hand, suggested that I enter Dr. To avoid making such blunders, I’d like to disable the Flash Fill.

I can turn off Flash Fill in Excel by the below steps.

  1. Go to the File Menu.
Click the file tab
  1. Go to the ‘Options’
Click on Options
  1. Select ‘Advanced’ from the Excel Options Dialog box.
Click on Advanced
  1. Go to the ‘Editing options’ section and uncheck the ‘Automatically Flash Fill’ box.
Uncheck the automatically flash fill option
  1. Click the ‘OK’ button in the Excel Options dialog box.

Now, when entering data for columns, I will not get autofill suggestions from Excel.

Using Flash Fill When it’s Turned Off

When you disable Flash Fill, it can still be used when you want. Disabling it only means that it would not show you the suggestions automatically as it does when it’s enabled.

Below are some ways you can still use Flash Fill once it’s been turned off:

Using the Excel keyboard shortcut

Control + E

I can type the first name of the person in the first line. Then go to one cell below and press Ctrl + E. It will automatically fill the entire column using Flash FIll.

Using the Flash Fill Option in the Ribbon

  • Go to the “Data” tab and click the “Flash Fill” icon.
Click on Flash Fill icon in the Data tab

or

  • Go to the “Home” tab. Expand the Fill options and select the “Flash Fill”.
Click on Flash Fill in the home tab
Also read: How to Turn Off Autosave in Excel?

Method 2 – Use VBA Code to Turn Off Flash Fill in Excel

Sometimes, multiple Excel users are working on the same worksheet. Some Excel users choose to turn off Flash Fill, while others do not.

Then, constantly turning off and on using the above method is time-consuming.

The ideal option is to create a button that immediately turns off Flash Fill.

We can do that by using the below VBA Code.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub TurnOffFlashFill()
Application.FlashFill = False
End Sub

To create this VBA code in my Excel file, I have to follow the below steps.

  1. Press “Alt + F11” to open the VBA Editor. If the function keys are locked, we have to enter the Fn key also. In other words, if the function keys are locked, we have to use “Alt + Fn + F11” to open the VBA Editor.
Open the VB Editor
  1. Click the “Insert” tab.
Click on the Insert option
  1. Click “Module” from the expanded list.
Click on Module
  1. Copy the VBA code that I have given above and paste it.
Copy paste the VB code in module code window
  1. Click the “Save” button.
Save the code
  1. Click “No” for the Microsoft Excel information box.
Click on No
  1. Save the Workbook as an “Excel Macro-Enabled Workbook (*.xlsm).
Save the file as macro enabled
  1. Insert a new shape and type “ TURN OFF FLASH FILL” inside the shape.
Insert new shape
  1. Select the shape and right-click it. Then, select “Assign Macro…” from the context menu.
Click on assign macro
  1. Select the Macro name from the “Assign Macro” dialog box and click the “OK” button.
Select the macro to assign to the shape

When I click the “TURN OFF FLASH FILL” button, I can quickly turn off the Flash Fill. Now, I don’t need to go to Excel options and manually uncheck the “Automatically Flash Fill” box.

If I want to turn off the “Automatically Flash Fill” for the entire Excel user group of my Business version of Microsoft Office, I can do it using the Group Policy Management Console (GPMC). You can find the instructions to do that on this forum post.

Also read: How to Turn Off AutoFill in Excel?

Reasons You May Want to Turn Off Flash Fill

Flash Fill in Excel is a helpful feature that can save you a lot of time when dealing with large data sets.

It recognizes patterns and tries to autofill the rest of your data based on what it has learned.

However, there may be instances where you would prefer to have it turned off.

Here are some reasons you might want to consider:

  1. Accuracy of Data Entry/Parsing: If the data set isn’t consistent or the pattern isn’t straightforward, Flash Fill may not always get it right. This could lead to inaccuracies if you don’t double-check the output. And if you have a large dataset, it is more likely that you may miss an error by Flash Fill.
  2. Control Over Data Entry: If you prefer complete control over how your data is entered and manipulated, you might prefer to turn Flash Fill off.
  3. Performance: While generally not a significant factor, for extremely large datasets, Excel’s attempt to constantly look for patterns and make suggestions might slow down performance slightly.
  4. Unexpected Results: If you’re not aware that Flash Fill is turned on, it might autofill data in ways that you didn’t expect or want. When you turn off Flash Fill, you can still use it, but it won’t happen on its own and you will have to run Flash Fill by either using the keyboard shortcut (Control + E) or by clicking on the Flash Fill icon in the ribbon.
  5. Specificity of Task: Flash Fill is great for generalized tasks. But for highly specific tasks, it might not fill the cells as expected. So, for complex tasks, manual control might be more beneficial.

Remember that you can turn Flash Fill on and off as needed, so it’s often useful to utilize this feature when it suits your needs and disable it when it doesn’t.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment