What is Flash Fill?

Flash Fill is a built-in feature within Excel that detects patterns in data and helps eliminate manual work by auto-filling rows. This can be an incredible time-saver, as it doesn’t matter if we are working with 50 rows or 500. Once the pattern is detected, every single remaining value is then filled in!

Some examples of the types of data that Flash Fill can help with include:

• Splitting first names and surnames
• Combining names
• Converting names to initials
• Extracting a zip code from an address

Flash Fill works really well in any scenario where there is a clear pattern that can be followed.

How to Use Flash Fill

There are two different approaches to using Flash Fill: a manual method and an automatic method.

Manual Approach

To use Flash Fill manually, you need to first fill in a single cell in the new column to show Excel the logic you want to apply. Then, under the Data tab, simply click the Flash Fill button.

For example, let’s say we want to extract surnames. After entering the surname in the top row of the new column, every subsequent value below is filled in using the exact same logic to extract each person’s second name.

Automatic Approach

The second approach to using Flash Fill happens automatically! As you begin typing and Excel detects a clear pattern, it will prompt you with a greyed-out series of cells as its Flash Fill recommendation.

For instance, when filling in a surname, as soon as Excel detects the pattern, a recommendation will appear. All you need to do is press Enter to accept the suggestion and watch the rest of the values fill in.

Additionally, a pop-up menu will appear next to the cells, allowing you to undo the Flash Fill or accept the changes. This is useful if you accidentally Flash Fill and need to revert the changes.

Examples

Let’s look at a few more examples that demonstrate the power of Flash Fill:

Combining Names

Let’s say we want to combine first names and surnames into a full name. While this can be done using a formula like CONCAT, Flash Fill is obviously much quicker and easier!

Converting Names to Initials

To convert names to initials, we’d typically have to use formulas like LEFT and CONCAT. With Flash Fill, though, simply typing in the initials for the first name will automatically fill the rest of the dataset in the same format. This becomes especially useful when the logic is more complex than just taking the beginning or end of the data.

Limitations of Flash Fill

One very important caveat to Flash Fill is that it isn’t perfect. When working with messy or inconsistent data, it may not always pick up on what you’re trying to do. A common example is when working with names that include titles like Mr., Dr., or Mrs.. In such cases, Flash Fill might treat the title as part of the name and fill it incorrectly.

Here’s an example: If we’re extracting first names but the database contains titles in some entries, Flash Fill might capture the title, leading to incorrect results.

While not perfect, Flash Fill is still extremely useful when working with slightly messy data. If you’re dealing with over a thousand records and Flash Fill accurately fills 900 of them, your manual work for the remaining records is significantly reduced.