Use Flash Fill to fill out data based on an example. Flash Fill typically starts working when it recognizes a pattern in your data, and works best when your data has some consistency.
Flash fill is also available for Excel on Mac as of version 16.13, but it does not automatically trigger. The user needs to manually trigger it.
Use Flash Fill
You have information in Excel that isn’t formatted the way you need it to be, such as this list of names, and going through the entire list manually to correct them is daunting. Here, Flash Fill can help.
-
Type the first name the way you want it.
-
Start to type the next name, and Excel provides a preview of the names formatted the way you want.
-
Press Enter, and Flash Fill fills in the names for you.
Want more?
Fill data automatically in worksheet cells
In the first video, you saw how Flash Fill can automatically fill in and format a list of names.
But, it can work with many different types of data.
I am typing phone numbers the way I want them formatted. When Flash Fill recognizes a pattern, it provides a preview.
To accept it and fill-in the column, I press Enter.
To use Flash Fill, I need to be in a column next to the data I want changed.
I also used unique formatting on the top row.
This helps Flash Fill understand that the top row is a header row, so it won’t use it in determining a pattern.
The Phone Numbers use the General format.
This column is formatted as a number. Flash Fill is not triggered automatically for data formatted as a number.
There are only 10 unique characters, and it is too easy to come up with random patterns, like this.
The dates aren’t correct. For example, this date should be 05/27/1974, not 02/27/1974.
Providing an additional example will help Flash Fill understand the pattern.
We’ll add another date the way we want it formatted. We’ll select the cells.
We can use the Fill Handle, or the Flash Fill button on the DATA tab. And now, Flash Fill accurately fills in the dates.
In the first video, you saw how Flash Fill can help you combine and format names.
In this example, we’ll do a more advanced version of that.
I type the Last Name, First Name, and the middle initial with the desired punctuation and capitalization.
When I start typing the second name, Flash Fill makes suggestions for the rest of the names.
It is not perfect, but it is pretty close. So, I press Enter.
In this case, Flash Fill wasn’t sure what to do for names that don’t have a middle name, or initial.
By providing Flash Fill with an example of how to handle these names, it fills in the other name.
You can also use Flash Fill to split up a column of data.
Type the First Name, start to type the next, and Flash Fill makes a suggestion for the rest of the first names.
They look good, so I press Enter.
We do the same thing for the Last Name.
Now, you have a good idea about how to use AutoFill and Flash Fill. Of course, there’s always more to learn.
So, check out the course summary at the end, and best of all, explore Excel 2013 on your own.
