Flash Fill in Microsoft Excel

Flash Fill in Excel is one of the features available in Microsoft Excel 2013 and above to automatically extract or combine data when a pattern is recognised. In this post , let us look at what exactly is Flash Fill , some of examples of how you can use the flash fill in excel , limitations of flash fill and how you can turn on or off the feature.

What is Flash Fill in Excel ?

Excel Flash Fill is a feature where excel analyzes the data that you have entered and fills the data in the cells based on the pattern that it has identified.

This feature was initially introduced in Microsoft Office 2013 and is made available in all the later versions of excel like Excel 2016 , Excel 2019.

How to use Flash Fill in Excel ?

When using excel , the Flash fill starts automatically based on the adjacent or previous cell. Below is an example 1 of how we can insert flash fill in your excel.

1. Assume that you have a excel work sheet with the values 560-102, 560-001, 500-003, 712-821 in the cells B3 to B6.
2. Enter a value 102 in the cell C3 indicating to excel that you are extracting the value after – from the previous cell value.

3. Navigate to Data Tab from the Data Tools group and then click the Flash Fill button. Alternatively you can use the shortcut key “CTRL + E”.

You will notice that excel identifies the pattern and flash fills the data on other cells accordingly.

Note that Flash Fill starts up automatically as soon Excel identifies a pattern of data based on what you have entered. You can always use the above steps if it does not kick started automatically.

Microsoft Excel Flash Fill Options

When the Flash Fill is applied to the cells by Excel , you will also see the Flash Fill Options button which appears nears the auto-filled cells. it provides you with the following options

1. Undo Flash Fill
2. Accept suggestions
3. Select all 0 blank cells
4. Select all 6 changed cells.

What are the limitations of Excel Flash Fill ?

1. The results of the Flash Fill donot update automatically – Excel Flash Fill is not the same as formula. The changes made to the original data will not be reflected in the results of the Flash Fill data.
2. Pattern identification may not work always. For example , if you have formatted the numbers differently , Flash Fill may get confused and might not identify the patterns as expected.
3. There are times when Flash Fill might convert your numbers to string format. You might end up using the excel format feature to sort some of these issues.

How to Enable ot Disable Automatic Flash Fill in Excel ?

By default , the automatic Flash Fill is enabled in Microsoft Excel. You can turn this off if you don’t want excel to automatically apply Auto Fill to your worksheet.

To do this , follow the below steps

1. In your Excel Workbook , naviate to File -> Options.
2. In the Excel Options dialog , select Advanced from the Left panel and then uncheck Automatically Flash Fill option under Edition Options.

3. Click OK so that the changes are saved.

If you need to re-enable Flash FIll in Excel , select the checkbox “Automatically Flash Fill” again.