When you have data in Excel that you want to use differently,
that is to combine it or extract just pieces of it,
Flash Fill might be the solution.
Now there are a number of functions that might get you to the same results such
as CONCATENATE to combine or the LEFT, MID,
and RIGHT functions to extract portions of the data.
And I hope you're not just retyping data to get it in a different format.
If you have the information here in Excel, there is a solution.
Hi, I'm Dawn Bjork, a Microsoft Certified Trainer.
Let's look at how Flash Fill works. It's simply looking for a pattern.
First, the data needs to be consistent.
You might need to do some data normalization or clean up the data to remove
inconsistencies such as in this example where we might need to remove a middle
initial used in the name or some sort of prefix or suffix.
Excel is looking for a consistent pattern. Now in this case,
we simply want to show the first name. Just pull it out. Notice what happens. Here,
I'm going to type the first name. Hit [Enter],
now start typing. The next entry. That's Flash Fill!
That's all you need to do. And so, if you're seeing the answers you want,
just press [Enter] to accept it. No other actions are needed.
The assumption is that the data is consistent and also the data isn't going to
change because this isn't actually a formula pointing back to the original data.
In fact, once you have the results you want, you could delete the original data.
Now,
one of the things that we see here in this feature is the Flash Fill smart tag.
You don't have to use this if you're happy with the results.
This option will let you reverse the feature or even highlight the cells in case
you wanted to copy this data to another location.
Flash Fill can be triggered in other ways. Now,
the reason why this worked is because the Flash Fill feature is enabled in Excel.
Now this is the default, but if for some reason it is turned off,
you can turn it on by going to File.
Choose Options at the bottom.
and then pick Advanced.
Here is the feature under the Editing options section:
Automatically Flash Fill.
This is a feature supported in Excel 2013 or newer versions including Excel 2016
and Excel 365.
So Flash Fill is available to us automatically through options in the Ribbon and
even with a keyboard shortcut
and I happen to love keyboard shortcuts.
That shortcut is [CTRL] + E.
Let's see how this works. We're going to try here now,
the last name, so I'll type that in.
We would not have to actually hit [Enter] and then either come back to this to
trigger the feature or look for it to be automatically triggered.
Instead of hitting [ENTER], I'll press [CTRL] + [ENTER].
Now what that does is it completes the entry but keeps me here in the cell.
Now press [CTRL] + E, the shortcut for Flash Fill.
And if it's able to find the pattern you have now filled to match that data.
With what you've seen so far,
you have now saved yourself so many hours with Flash Fill, but there's even more.
Now,
one of the challenges we have with Flash Fill is although I could continue to
another column,
there's a possibility that we might confuse Excel with other data columns,
especially if there's a lot of similarity in the information.
So although it might work with your data, what I'm going to
do here is insert another column and I'll put that right next to the original
source data. For this, I want to rearrange the data a bit,
although we already have the first name and the last name separately.
We could get another variation perhaps first space last. Let's try that.
And Excel works once again. So here is that Flash Fill.
Now, just so that you can see the idea here,
I'm going to [ESC] out of this.
That's what you would do if you don't want the feature to work.
And I'll move back to this original cell just so that you can see where Flash
Fill is on the Ribbon.
One place to find it is in the Data tab.
So in the Data tab then we have our Flash Fill feature.
You'll find it under your Data Tools group and so it's kind of hiding in here
for us and there is, once again, is that keyboard shortcut.
So we could press the keyboard shortcut,
[CTRL] + E or choose that option here from the Data tab in the Ribbon.
Let's look at another example. In this sheet,
we have a product code. Now sometimes we have this kind of information,
but it might not be formatted in the right way.
Perhaps we got it from another system or the data came from a vendor or are we
simply only want a portion of the data for sorting or filtering or even a pivot
table. In this case, all I want is the Alpha data in the middle of
this code.
So I'll type in that example and as we saw before, [CTRL] + [ENTER],
and then [CTRL] + E, the shortcut.
Let's look at another example.
Perhaps what's important in our system is to have this data set up with hyphens.
And so in that case, I'll type the example,
press [CTRL] + [ENTER].
Let's look at the Home tab here.
Go all the way over to the right to Fill and find Flash Fill.
So that was enough information to populate this extraction from the original
data. But as we've seen, it's all about consistency within the source data.
So we still might need to pull in handy functions when the data is variable.
Will this be helpful? I hope so.
Now we even have the option to extract from dates. In this case,
I'll create a few example entries to make sure that I'm teaching Excel what I
want from the data so it knows I want the month and not the day.
Three. Two. Two.
Three.
Now I'll highlight this and here is something called the fill handle.
You've probably used this as a way to copy content down or across.
But a handy shortcut that might be something you can use,
even when it's not for Flash Fill, is to move to that fill handle
and double click. Now the results don't look exactly like what we're expecting,
but let's look at that smart tag.
Excel is trying to help us fill the series based upon the pattern we provided.
Here is Flash Fill. Now, let's see what happens. Okay,
now it got smart. It was giving us what we wanted here. We just had to pick it.
Let's try this for the day. And once again,
just enough so that it's pretty smart here and we can highlight that.
Now we could go back to the Ribbon for this,
but we'll just do that Flash Fill again, and double click. And
once again,
we have to tell it what you're trying to be smart giving me that series,
but let's do Flash Fill instead. And so now we have the day.
Now the year will be easier. We shouldn't
have to build much of a pattern here, in fact,
it's probably enough for us just to do [CTRL] + E, our keyboard shortcut.
And sure enough,
we've been able to use Flash Fill to extract the data out of a date as well.
With Flash Fill,
you now have the power to use the data that you have in Excel in a way that
works for you. Thanks for watching.