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.

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).

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

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.

We fill out the first entry with two first names.

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

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.

Excel performs the Flash Fill and we get the column of 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.

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.

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!

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

Follow us on LinkedIn.

Check out our brand new R Academy!