How To Use Flash Fill In Excel
Flash Fill is an incredibly useful and often looked over function in excel which when used properly can save a ton of time when working with large data sets.
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 can include:
- Splitting first names and surnames
- Combining names
- Converting names to initials
- Extracting a zip code from an address
This works really in any scenario where there is a clear pattern that can be followed.
How to use Flash Fill
There are two different approaches we can take here. The first involves using a specific function button, while the other actually happens automatically.
Let’s start with the manual approach.
To use Flash Fill, first you need to fill in a single cell in the new column to show Excel the logic we want to use, and then under the data tab simply click the Flash Fill button.
As we can see below, after entering the surname in the top row of the new column, every single value below is then filled in using the exact same extract of each persons second name!
The second approach for using flash fill is actually not taking an approach at all! Chances are if you are manually filling out cells like this you will be prompted by flash fill as once Excel has a second value being filled out and detects a clear pattern being used it decides to help us out by recommending a flash fill. As we can see below, as we start filling in the second name a greyed out series of cells appear with Excels Flash Fill recommendation:
If we stop typing and simply press the enter key it will auto populate!
As we can see above, when filling this out a little pop up menu appears to the right of the cells allowing us to undo if we accidently flash filled (which can happen if you press enter quickly without realising!), while also selecting the changed cells or accepting the suggestions that were put forward.
Examples
Let’s look at a few more examples which help show off the power of this handy function.
Starting off with the opposite of what we did before let’s take a first name and surname and combine them into one:
Pretty straight forward. You can do this with a concatenation of columns B and C with a blank space in between as well, but this approach is obviouisly much quicker and easier!
Now lets convert these names into initials. This is where formulas start getting more tricky as there is a bit we would have to do using combinations of things such as LEFT and CONCAT. If we simply type in our logic into the top name the rest will follow:
More advanced examples like this are where the function becomes even more convenient as we arent simply taking the beginning or end phases which could be done via a quick formula.
Limitations
One very important caveat to Flash Fill is that it isn’t perfect. When working with messy or inconsistent data it isn’t always smart enough to pick up on what we are trying to do. A good example here is when working with names. If we are extracting first names but in a database someone has a title such as Mrs, Dr and so on then it’s likely Excel will just pull that in as it was the ‘first’ phrase in the cell. See below for example.
While not perfect, it is still work using with slightly messy data as we can still save an absolute ton of time. If we are working with over a thousand records and it accurately fills in 900 of them then our manual work for those remaining records is significantly reduced.
This sums up our quick guide on how to use Flash Fill. For more handy guides on working with Excel, be sure to check out the rest of our tutorials here.
