Flash Fill in Excel – Shiksha Online

Flash Fill is a powerful and time-saving feature in Microsoft Excel that can transform how you work with data. It allows you to extract, combine, or reformat information quickly and efficiently. The article highlights the use of flash fill in cleaning and managing huge data sets. Learn how to use flash fill in Excel through examples.

 

Flash Fill is a highly flexible tool integrated into MS Excel 2013. It offers a quick way to enter and manage new data. In order to effectively use Flash Fill, ensure that –

• The source data has a consistent pattern.
• You refresh the Flash Fill results every time you update the source data.

Content

• How Does Flash Fill Work?
• Flash Fill Keyboard Shortcut
• Adding Flash Fill to the Quick Access Toolbar
• Extract Data – Flash Fill Example 1
• Join Names – Flash Fill Example 2
• Separate Names – Flash Fill Example 3
• Reformat the Numbers – Flash Fill Example 4
• Limitations of Flash Fill
• How to Turn Off Flash Fill

How Does Flash Fill Work?

Flash Fill is used to fill in values automatically. All you need to do is enter a couple of examples you want as output in the column you want to fill in. Excel will automatically detect the pattern in the data and fill the rest of it. You can use FlashFill to extract data, join names, reformat the numbers, and much more.

Flash Fill is among the most straightforward Excel functions. Here is how you can use it: 

• Enter your data set in the spreadsheet
• Next to your data, enter what you aim to achieve using Flash Fill.
• Go to Data, and under the Tools section, Choose Flash Fill to complete the list.

 You can run it manually to Data > Flash Fill or use Ctrl + E as a shortcut.

Read MS Excel Tutorials

Flash Fill Keyboard Shortcut

Adding Flash Fill to the Quick Access Toolbar

Add the Flash Fill button to the Quick Access Toolbar to make the work more convenient.

The Flash Fill button is now added to the quick access toolbar.

Extract Data – Flash Fill Example 1

Flash Fill is used to extract a defined string from a cell – In the dataset below, we have employee codes from which we have to segregate the employees’ names, phone numbers and user IDs. We will use Flash Fill to extract these specific strings from the cells.

To segregate the names mentioned in Column A, start typing the names in Column B. If you drag the name to fill the rest of the columns below, then this won’t work, and the name in the B1 will get copied to the rest of the Columns.

Go to the Data tab and click Flash Fill.

You will see that the rest of the columns are now filled with the first name data.

We will follow the same process for the rest of the columns and sort our data.

Join Names – Flash Fill Example 2

Flash Fill in Excel is used to join the names and create email addresses for the rest of the cells. We have the first name in Column A, and the last names in Column B. Note that we have used lowercase to create the email address in C2. Flash Fill will read this pattern and apply the same format against the rest of the names.

Click Flash Fill to generate email IDs for the remaining cells.

Separate Names – Flash Fill Example 3

You can also perform the vice versa action using Flash Fill. It can separate names from the given email addresses. 

We will pick the first and last names from the email and write them down in Columns B and C, respectively. First, we will select B2, click on Flash Fill, and then select C2 and Flash Fill.

If you select both B2 and C2 simultaneously, you will see that the Flash Fill option gets deactivated. So choose them separately.

Reformat the Numbers – Flash Fill Example 4

Flash Fill can reformat the numbers. Here, we want to convert the given numbers into social security numbers. We will follow the same path – Apply flash fill and achieve the desired number format.

Click Flash Fill.

We now have the re-formatted data.

Limitations of Flash Fill

It is an easy tool, but Flash Fill still has certain limitations. In the example below, each statement in Column A contains a data point. We aim to pick those data points from the text strings and place them separately. To start with, we will mention the data from A2 in B2.

Apply Flash Fill.

You can see that Flash Fill did not correctly extract the decimal numbers. It could only pick digits after the decimal point.

You would need to provide a little help to identify the pattern. Change the first instance of the incorrect data, and Excel will automatically correct all the remaining instances. In this case, we changed the entry from 0.74 to 74, updating the instances where Excel picked data incorrectly. However, you can still see that values remained unchanged for B5 and B10.

How to Turn Off Flash Fill

Sometimes, you don’t want the Flash Fill function to interrupt while working with your datasets. Even the previews sometimes interrupt. 

In such cases, you can disable or turn off Flash Fill. Please note that you can enable or disable Flash Fill anytime you want. Here is how to turn off Flash Fill in Excel.

Go to the Ribbon, and click on File —> Options

Click on Advanced. You will be redirected to a new Dialogue Box

Locate Advanced and click it

Scroll down and uncheck the “Automatically Flash Fill” box. Click OK.

Conclusion

Flash Fill offers great flexibility when it comes to managing data. However, it does limit you if there are inconsistencies in the data. Another point to remember is that it will not automatically update your results in case your source data changes.

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel