Excel Unplugged

AI in Excel – Part 1 – Flash Fill

Welcome to the brave new world of Artificial Intelligence in Excel! In this article, we’ll look at a powerful new feature called Flash Fill. It works by recognizing patterns of filled data and filling out the rest. We initially have to fill in some data and then Excel does the work for us. Depending on the complexity of our data, we may need to do some trial and error tests before we get the desired result.

Let’s see it in action.

Flash Fill to join names and Last names

Let’s start with a simple table of first and last names.

AI in Excel - Flash Fill, Fill names
Starting table

We would like the third column to consist of the first name in capital letters and the last name as is. To start with, we manually write in the first Name – Last name combination.

As we start filling out the second name, Excel offers to fill out the rest of the table with the following entries. (Note that sometimes it takes more than one sample manual input to start working).

AI in Excel - Flash Fill, Fill names
Suggested answers based on the first row

Entries are correct so we can just confirm it by pressing Enter. We now have the exact right table we wanted.

AI in Excel - Flash Fill, Fill names

Two first names

What about cases where we have two first names?

In the table below, we fill out the first name. As we start filling out the second name, Excel offers to fill the data, but it’s not what we want. Some people have two first names, which is why we must provide at least one sample row for Excel to recognize what we want.

AI in Excel - Flash Fill, Two first names
Sample table where some entries have two first names

We fill out the first entry with two first names.

AI in Excel - Flash Fill, Two first names
We first fill the “odd” entries, so Excel can learn from the data we provide

We then select the entire column that needs to be filled and Home > Fill > Flash Fill.

AI in Excel - Flash Fill, Two first names
Flash Fill now works correctly

The result we get is now correct – Excel recognizes both one or two first names.

Results are not dynamic

Once we get our Flash Fill results, they don’t change if we change the original data. Let’s look at an example.

We have a simple table of dates. We provide the year for the first entry. As we start typing in the second entry, Excel provides the correct data. We confirm this with a press of the Enter key.

AI in Excel - Flash Fill, Not dynamic
Sample table of dates

Excel performs the Flash Fill and we get the column of years.

AI in Excel - Flash Fill, Not dynamic
Flash Fill fills in all the years

If we now change the year of the first date, the year in the year column (which was filled using Flash Fill) doesn’t change.

AI in Excel - Flash Fill, Not dynamic
The data that was filled in doesn’t change after changing the original data!

This is an important fact to keep in mind! Data won’t dynamically change after using Flash Fill.

Numbers from text

Let’s look at another example, which is a bit more complex. We have addresses, each containing the street number. The street number consists of a varying number of digits.

As we start filling out the data, we don’t get any suggestions from Excel until much the row 4. This sometimes happens when we have data that Excel finds complex. The solution is simple, just provide more starting entries and Excel will do the rest.

AI in Excel - Flash Fill, Number from text
Sample table of street adresses

How to use Flash Fill

In conclusion, Flash Fill acts as your friend that listens and tries to respond with the answer you want. It can hit or miss, depending on data complexity. This is why it’s important to play around with this feature and not just use it as is. Nonetheless, it is a brilliant feature worth knowing about!

Watch the tutorial

Meanwhile, you can watch the tutorial online on our new YouTube channel!

AI in Excel – Part 1 – Flash FIll

Please leave us a like, comment, and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out our brand new R Academy!