How to Use Flash Fill in Excel 2013 and 2016

Watch Video – Using Flash Fill in Excel (Examples)

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:

Using Flash fill in Excel - Dataset

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).Using Flash fill in Excel - Select first cell
  • Enter the data (first name in this case).Using Flash fill in Excel - Enter name
  • Select the cell right below the one where you entered data (B3 in this case).Using Flash fill in Excel - Select second cell
  • Go to the Data tab and within Data Tools group, click on Flash Fill.Using Flash fill in Excel - Excel Flash Fill

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

Using Flash fill in Excel - result

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.

Note: Flash Fill is still not yet available in Excel for Mac versions.

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:Using Flash fill in Excel - example 2 dataset

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.

Using Flash fill in Excel - 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:

Using Flash fill in Excel - example 3 dataset

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.

Using Flash fill in Excel - example 3 result

Example 4 – Get User Names from Email Addresses

Suppose you have a dataset as shown below:

Using Flash fill in Excel - example 4 dataset

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.

Using Flash fill in Excel - example 4 result

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:

Using Flash fill in Excel - example 5 dataset

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.

Using Flash fill in Excel - example 5 result

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:

Using Flash fill in Excel - example 6 data

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.

Using Flash fill in Excel - example 6 result

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).Using Flash fill in Excel - incorrect

Excel Flash Fill Error

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

Excel Flash Fill - Error

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.

You May Also Like the Following Excel Tutorials:

  • Akash says:

    I have written a name MarkPeterson without any space in a column, can flash fill feature will work to part the name in first and last name

  • Jack Thomos says:

    Thanks

  • Adam Kopeć says:

    You can handle
    the middle name if you tell Flash Fill to insert, in lines where there is
    no middle name, a sign eg, an exclamation point.You will have exclamation points and middle names.
    Now You can remove exclamation marks, with the help of filtering for example.

  • >