Flash Fill is an amazing tool that was added in Excel 2013 and is available in all version after that. You can use Flash Fill in Excel to make data entry easy. It’s a smart tool that tries to identify patterns based on your data entry and does that for you. Some simple examples of using Flash Fill could be to get the first name from the full name, get name initials, format phone numbers, etc.

Using Flash Fill in Excel – Examples

Here are some examples where flash fill can really save time by identifying patterns and making data entry easy.

Example 1 – Get the First Name from Full Name

Suppose you have a dataset as shown below:

Until Excel Flash Fill came into existence, the way to get the first name from the full name was to use Text to Columns or use Excel functions. But now, you can instantly get the results using the new tool on the block – Flash Fill in Excel. Here is how you can use Flash Fill in Excel to get the first name:

Select cell adjacent to the cell that has the data (B2 in this case). Enter the data (first name in this case). Select the cell right below the one where you entered data (B3 in this case). Go to the Data tab and within Data Tools group, click on Flash Fill.

Excel Flash Fill would automatically fill all the cells with the first name from the dataset.

It’s done in a flash (and hence the name Excel Flash Fill). The keyboard shortcut to use Flash Fill in Excel is Control + E.

Example 2 – Get the Initials from the Name

Since Flash Fill works by identifying patterns, you can use it to get the first name and last name initials. Suppose you have a dataset as shown below: Here are the steps use Flash Fill in Excel to get the initials:

Select cell adjacent to the cell that has the data (B2 in this case). Enter the first name initial in the select cell. Select the cell right below the one where you entered data (B3 in this case). Go to the Data tab and within Data Tools group, click on Flash Fill.

Excel Flash Fill would automatically fill all the cells with the first name initials.

Example 3 – Concatenate Names in a Different Format

While you can use Flash Fill in Excel to split data (into first name or initials as seen in Example 1 and 2 above), you can also use it to merge cells. Suppose you have a dataset as shown below:

The intent here is to combine the names in the following format – Last Name, First Name. For example, Tony and Stark would combine to give Stark, Tony. Here is how you can use Flash Fill in Excel to combine names:

Select cell adjacent to the cell that has the data (B2 in this case). Enter the cell with the combined name in the desired format. Select the cell right below the one where you entered data (B3 in this case). Go to the Data tab and within Data Tools group, click on Flash Fill.

Excel Flash Fill would automatically fill all the cells where the names are combined in the specified format.

Example 4 – Get User Names from Email Addresses

Suppose you have a dataset as shown below:

The intent here is to get the username from the email id (for example, to get ‘tonystark’ from tonystark@ironman.com). Here is how you can use Flash Fill in Excel to get the user names from email ids:

Select cell adjacent to the cell that has the data (B2 in this case). Enter the cell with the username from the email id from the cell on the left. Select the cell right below the one where you entered data (B3 in this case). Go to the Data tab and within Data Tools group, click on Flash Fill.

Excel Flash Fill would automatically fill all the cells where the user names from the respective email ids.

Example 5 – Format Phone Numbers

You can use Flash Fill with text as well as numbers. Here is an example where you can format the phone numbers using Excel Flash Fill. Suppose you have a dataset as shown below:

The intent here is to get the phone number in the following format: XXX-XXX-XXXX. Here is how you can use Flash Fill in Excel to get the phone number in the desired format:

Select cell adjacent to the cell that has the phone number (B2 in this case). Enter the cell with the phone number in the desired format. Select the cell right below the one where you entered data (B3 in this case). Go to the Data tab and within Data Tools group, click on Flash Fill.

Excel Flash Fill would automatically fill all the cells with the phone numbers in the specified format.

Example 6 – Extract Specific Data Points (such as City from Address)

You can use Flash Fill in Excel to extract a part from a data set. Suppose you have a dataset as shown below:

The intent here is to get the state code from each address. Here are the steps to get the state code from address using Flash Fill in Excel:

Select cell adjacent to the cell that has the phone number (B2 in this case). In the cell, enter the state code from the address on the left. Select the cell right below the one where you entered data (B3 in this case). Go to the Data tab and within Data Tools group, click on Flash Fill.

This would instantly extract all the state codes in column B.

Similarly, you can extract pin codes as well.

Limitations of Flash Fill in Excel

While Flash Fill is an amazing tool, it’s important to know it’s limitations before you use it on large and important datasets:

The result of Flash Fill is static. This means that if you change the original data set, the Flash Fill result would not change. Flash Fill ignores cell with a space character in it. If you’re using Flash Fill, make sure the cells where the result would come don’t have any space character in it. If it does, Excel Flash Fill would simply ignore it. In some cases, Flash Fill may falter in identifying the pattern giving incorrect results. For example, in the case of extracting the middle names, if there are names without any middle name, Flash Fill would give the incorrect results (as shown below).

Excel Flash Fill Error

If Flash Fill is not able to recognize a pattern, it may show an error as shown below:

In such case, you can fill a couple of more cells so that Flash Fill can recognize a pattern, or dump the idea of using Flash Fill and use Excel functions instead.

How to use Fill Handle in Excel. How to Create an Excel Drop Down List. 10 Excel Data Entry Tips You Can’t Afford to Miss.