How to use Flash Fill in Excel 365 - 2013

How to Flash Fill in Excel with a button click or shortcut

In most situations, Flash Fill kicks in automatically as soon as Excel establishes a pattern in the data you are entering. If a preview does not show up, you can activate Flash Fill manually in this way:

1. Fill in the first cell and press Enter.
2. Click the Flash Fill button on the Data tab or press the Ctrl + E shortcut.

Extract text from cell (split columns)

Before Flash Fill came into existence, splitting the contents of one cell into several cells required the use of the Text to Columns feature or Excel Text functions. With Flash Fill, you can get the results instantly without intricate text manipulations.

Supposing you have a column of addresses and you want to extract zip codes into a separate column. Indicate your goal by typing the zip code in the first cell. As soon as Excel understands what you are trying to do, it fills in all the rows below the example with the extracted zip codes. You only need to hit Enter to accept them all.

Formulas to split cells and extract text:

• Extract substring - formulas to extract text of a specific length or get a substring before or after a given character.
• Extract number from string - formulas to extract numbers from alphanumeric strings.
• Split names in Excel - formulas to extract first, last and middle names.

Extracting and splitting tools:

• Split Text - a versatile tool to divide cell content into multiple columns or rows. Easily split string by any character, string, or mask.
• Split Names tool - fast and easy way to separate names in Excel.

Combine data from several cells (merge columns)

If you have an opposite task to perform, no problem, Flash Fill can concatenate cells too. Moreover, it can separate the combined values with a space, comma, semicolon or any other character - you just need to show Excel the required punctuation in the first cell:

This method is especially useful for combining various name parts into a single cell as shown in How to merge first and last name with Flash Fill.

Formulas to join cell values:

• CONCATENATE function in Excel - formulas to combine text strings, cells and columns.

Merging tools:

• Merge Tables Wizard - quick way to combine two tables by common columns.
• Merge Duplicates Wizard - Combine similar rows into one by key columns.

Clean data

If some data entries in your worksheet begin with a leading space, Flash Fill can get rid of them in a blink. Type the first value without a preceding space, and all extra spaces in other cells are gone too:

Formulas to clean data:

• Excel TRIM function - formulas to remove excess spaces in Excel.

Data cleaning tools:

• Trim Spaces - trim all leading, trailing and in-between spaces but a single space character between words.

Format text, numbers and dates

Quite often the data in your spreadsheets is formatted in one way while you want it in another. Just start typing the values exactly as you want them to appear, and Flash Fill will do the rest.

Perhaps you have a column of first and last names in lowercase. You wish the last and first names to be in proper case, separated with a comma. A piece of cake for Flash Fill :)

Maybe you are working with a column of numbers that need to be formatted as phone numbers. The task can be accomplished by using a predefined Special format or creating a custom number format. Or you can do it an easy way with Flash Fill:

To re-format the dates to your liking, you can apply the corresponding Date format or type a properly formatted date into the first cell. Oops, no suggestions have appeared… What if we press the Flash Fill shortcut (Ctrl + E) or click its button on the ribbon? Yep, it works beautifully!

Replace part of cell contents

Replacing part of a string with some other text is a very common operation in Excel, which Flash Fill can also automate.

Let's say, you have a column of social security numbers and you want to censor this sensitive information by replacing the last 4 digits with XXXX.

To have it done, either use the REPLACE function or type the desired value in the first cell and let Flash Fill auto fill the remaining cells:

Advanced combinations

Flash Fill in Excel can accomplish not only straightforward tasks like demonstrated in the above examples but also perform more sophisticated data re-arrangements.

As an example, let's combine different pieces of information from 3 columns and add a few custom characters to the result.

Supposing, you have first names in column A, last names in column B, and domain names in column C. Based on this information, you want to generate email addresses in this format: initial.surname@domain.com.

For experienced Excel users, there is no problem to extract the initial with the LEFT function, convert all the characters to lowercase with the LOWER function and concatenate all the pieces by using the concatenation operator:

=LOWER(LEFT(B2,1))&"."&LOWER(A2)&"@"&LOWER(C2)&".com"

But can Excel Flash Fill create these email addresses for us automatically? Sure thing!

1. Flash Fill results do not update automatically

Unlike formulas, the results of Flash Fill are static. If you make any changes to the original data, they won't be reflected in the Flash Fill results.

2. May fail to identify a pattern

In some situations, especially when your original data are arranged or formatted differently, Flash Fill may stumble and produce incorrect results.

For example, if you use Flash Fill to extract middle names from the list where some entries contain only the First and Last names, the results for those cells will be wrong. So, it is wise to always review the Flash Fill output.

3. Ignores cells with non-printable characters

If some of the cells to be auto-filled contain spaces or other non-printable characters, Flash Fill will skip such cells.

So, if any of the resulting cells are blank, clear those cells (Home tab >Formats group > Clear > Clear All) and run Flash Fill again.

4. May convert numbers to strings

When using Flash Fill for reformatting numbers, please be aware that it may convert your numbers to alphanumeric strings. If you prefer to keep the numbers, use the capabilities of Excel format that changes only the visual representation, but not the underlying values.

1. Provide more examples

Flash Fill learns by example. If it is unable to recognize a pattern in your data, fill in a couple more cells manually, so that Excel could try out different patterns and find the one best suited for your needs.

2. Force it to run

If Flash Fill suggestions do not appear automatically as you type, try to run it manually.

3. Make sure Flash Fill is enabled

If it does not start either automatically or manually, check if the Flash Fill functionality is turned on in your Excel.

4. Flash Fill error persists

If none of the above suggestions has worked and Excel Flash Fill still throws an error, there is nothing else you can do but enter the data manually or with formulas.