How to Use Flash Fill in Excel

Flash Fill helps you to enter data much more quickly in Microsoft Excel. It watches for patterns in the data you’re entering and makes suggestions to complete your entry, filling whole columns with relevant values. Keep reading to learn how to use Flash Fill in Excel to increase speed and accuracy in up data entry.

Flash Fill works similar to the way that Autofill works in a web browser but is much more intelligent. Excel’s AutoFill works somewhat differently, allowing you to copy formula’s and values across horizontal or vertical arrays in a spreadsheet. Excel actually uses machine learning for Flash Fill, which allows it to make clever suggestions for what you might want to be entering.

How to Use Flash Fill in Excel

There are two ways to use flash fill in Excel. It is always working in the background and will make suggestions as you type into any cell. The simplest and most common way to use Flash Fill is to accept these suggestions by hitting the Enter key. The second way to use Flash Fill is using a command on the DATA tab of the ribbon to automatically fill a number of cells at once.

Flash Fill is currently quite limited in its capabilities. It only works when the column you’re entering data into is directly adjacent to the column containing the source data. The source data is the data containing the pattern that Flash Fill will use to generate values. Somewhat strangely it only works with columns and not rows, so you can’t automatically populate a row of data.

Using Flash Fill As You Type

While you’re typing, Flash Fill is always working in the background. If it thinks it can see what you’re trying to enter in the column, it will make a suggestion for how you could instantly complete the whole entry. Used in this way, you can think of Flash Fill as an extension of Excel’s AutoComplete. While AutoComplete makes suggestions for what you might be typing into an individual cell, Flash Fill allows you to complete whole columns of data.

To use Flash Fill while you’re typing, follow these steps:

1. Insert a new column, next to the column with the source data.

2. Type the required value into the first cell of the new column.

3. Start typing the value into the second column. If Excel detects the pattern then a preview will appear showing what values it will use for the remaining cells.

   

4. Hit the Enter key and the values are inserted automatically.

   

5. That’s it, as simple as that!

You can continue to use Flash Fill in subsequent columns to extract different items of data. As long as the column containing the pattern isn’t separated from the column where you are entering the values, Excel should detect the pattern. Continuing the previous example, the names could also be extracted.

Using Flash Fill From the Ribbon

Where Flash Fill detects a pattern, it usually shows the preview while you’re typing. Hitting Enter to accept this suggestion is normally the easiest way to use Flash Fill. However, it can also be used via a command on the ribbon. To use it in this way follow these steps:

1. Insert a new column, next to the column with the source data.

2. Type the required value into the first cell of the new column.

3. Select the DATA tab on the ribbon.

4. Select the Flash Fill command.

   

5. If Excel has detected a pattern, it will enter its suggested values into the remaining cells.

Combining Data From Multiple Columns

Flash Fill isn’t only able to split values into separate columns. It can also be used to merge columns using any pattern you choose. This can be a lot quicker and more intuitive than using concatenation formula. For example, to create a single address column from individual address fields you could follow these steps:

1. Enter each address field into a separate column.

   

2. Create an adjacent address column.

3. Enter the first address in the required format.

4. Start typing the second address and check Excel’s suggested values.

   

5. Hit enter to accept Excel’s suggested Flash Fill.

Note that in this example commas were used between the street, city and country but not the number and street. Flash Fill should work with any format you choose to use.

Other Capabilities of Flash Fill

Flash Fill can simplify many data entry tasks. As well as splitting and combining columns, Flash Fill can remove leading zeros, where only some entries have them. It can be used to format text, number and dates. It can replace part of a cell content, perhaps creating a secure version of sensitive data by replacing some numbers with XXXX. Different functions such as combining values and adding characters can even be combined.

To perform any of these functions, the process is the same. You follow these steps:

1. Enter the raw data that needs to be cleaned or reformatted in one column.

2. Type the data as you want it in the first cell of an adjacent column.

3. Start typing the second value and Flash Fill will suggest how it can complete all the other values in the column, following the same pattern.

Once you start to get to grips with what Flash Fill is capable of you’ll find it an extremely powerful and useful tool. If you’re interested in another really useful tool to simplify data entry, check out how to use AutoFill in Excel, this can copy formulae and values in an intelligent way across a spreadsheet.