All language subtitles for 6. Splitting or Combining Cell Data Using Flash fill

So now I've spent the last two lessons showing you different ways that you can split up data.

I'm now going to show you the method that I use 99 percent of the time.

And the reason why I use this method is because it is so much easier than those other methods.

However, it's not something that you can use in every single scenario, so it's good to have a backup

of using either text functions or text to columns.

Now the function that I'm referring to here?

Well, it's not actually a function.

I should say.

A utility in Excel is the flash fill command, and they introduce flash fill in.

I think it was Excel 2013.

It might have been 2016, and it was like a revelation.

When I'm training excel, this is always the tool that gets the most gasps because immediately people

can see how this is going to be useful and how much time it's going to save them because they're no

longer having to create these complex formulas to break up that information.

So let's start out with a basic example so we can see how it works, and then we're going to apply it

to our sales data worksheet.

So once again, I just have a list of employee names, and all I want to do here is I want to split

up this employee name.

So I have the first name in one column and the last name in the second column.

So take a look at this.

All I need to do here is type in the first one.

Now I'm going to do control enter to stay in the same cell.

Go up to the data tab.

And in the data tools group, we have a button called flash fill.

Click like a magic.

It copies those down.

How brilliant is that?

Now there is a couple of different ways that you can invoke flash fill, so I'm going to delete all

of those out and just get rid of those and do it one more time.

Now what I could also do here is I could type in memory, go to the cell underneath instead of clicking

that flash fill button if I simply start to type in the next one.

Excel is going to ghost down what it thinks.

I want to type into these cells, and if that is what I want, I can simply press enter to accept that

again, super quick.

The third way I can do this is to type the last names.

Let's type in the first one here.

So McCoy Control Center, I could highlight all the cells that I want to flash, fill and then use the

flash fill, keyboard shortcut control e to flash fill those names down.

So three different methods for invoking flash fill.

But I think we can all agree that is so much easier than using text functions or text to columns.

As I said, one of my favorite things in Excel.

So now that we have that under our belt, let's use this in our sales worksheet.

So once again, we're back at this stage with this worksheet where we have the country and the product

combined.

So I'm going to add another new column control shift plus and let's add another one control shift.

Plus, let's call this one country this one product.

So now I can use flash fill to fill these down.

So we're going to type in the first one control.

Enter up to the data tab.

Click Flash.

Feel like magic.

It fills them down.

Now what about with Kensington?

Because that's in brackets.

That's typing Kensington control.

Enter, let's click Flash Fill.

It still works, so it hasn't brought across those brackets.

All that's left to do now is delete our column A. And then literally ten seconds, I've managed to break

up that one column into two separate columns.

Now it's also worth noting that you can use flash fell not just to split up data, but also combine

data.

So if we go back to our example, if I wanted to take these two and combine them into one to give us

the full employee name again, if I just type in full name over here, we can use flash fill in the

same way.

So you need to do is provide flash fill with the format for the first one control and then I can click

the flash fill button.

Now notice this is an example of when flash file doesn't work, and that is because there is a blank

column in between my data.

So if you have this kind of set up, this is where you might then need to resort to using text to columns

or text functions.

But it's worth noting, but I wanted to show you that just so you can see one of the drawbacks of using

flash fill.

So what I'm going to do instead is let's just move this across, and now I should be able to flash fell

down, which I can because the columns are next to each other.

So really important if you have a blank column in the middle.

Flash Fill is going to struggle to know what to fill down, but otherwise this is a brilliant utility.