welcome to this Excel tutorial on five ways that you can use flash fill this is computer tutoring
hi okay so flash fill you might have seen it on the top of your ribbon in Excel maybe if you've
gone to home and you've clicked on the fill drop down menu just here you can see the flash fill
icon just there so you might have seen flash fill just there you might have right clicked and drags
and there's various ways and how to use it we're gonna look at five ways that's what we're gonna
look at five ways to use this flash fill and I've got these ways down at the bottom here so you can
see these the five various ways just down here so that's good as you mean here we go the five
ways here ways one and two got raised three and the fifth way okay since the fifth column here
or the fifth sheets brilliant so the first way so just imagine this scenario here you need to
separate somebody's first name and their last name now if you quickly want to do this then
flash fill is probably the best way especially if their last names are double-barreled as well so
flash fill will look at patterns so what I mean by that well let's have a look at this example
here so I've got this first name just here let me just zoom in a touch so you can actually just see
here so if I just zoom in there we go Joshua Charles notice as well some of these have got
double-barrelled names and I've been made it even more difficult and I'm not putting a little dash
in between the two last name so let's give that when I go so I'm gonna go to Joshua type Charles
I'm gonna click in there I'm just gonna type in Joshua and press tab I'm going to type in I'll
just do Joshua first okay so I can press click on Joshua here and then what I can do is I can drag
down here like so let go and I can choose flash fill just here and you can see it's automatically
picked up the first names looking good alright then so let's go on to the last name shall we
so if we go to the last name we've got Charles so I'm gonna type in Charles in here gonna press
ctrl + Enter which is great ctrl + Enter keeps the focus on the same cell good practice to get
into you know go down to here drag down here at the right mouse button let go go to flash fill
and you can see it's automatically filled these up but notice there is a little problem if you
have a look at Derek James Roberts you can see it's only come up with Roberts here so I'm just
gonna double click on that cell go to the very beginning just before the R and type in the word
James now just a little gray that's appeared over here so I'm just typed in James in here and press
Enter and you can see it's picked up the Harvey Oswald and it's picked up the Carlos Johnson so
there we go so one way of using flash fill let's move on very quickly to another way so if you look
just over here on the right hand side you can see a little problem here it's quite a common problem
dates you've got the dates in the wrong order you know it's gone month day and year now for
our us friends I do apologize but this can be a real bone of contention for us over there in
the other side of the Atlantic and basically if you look at thatmy excel formula i've got these
as dates he in fact is as either left aligned they're actually generally formatted so it's
not even recognized as a date so how do I sort it well we can you bring flash fill comes to the
rescue so I just go to you not UK date here type it as I expected it the 20th of March not the 3rd
of whatever that month is I don't know but it's the 20th of March 2020 if you're going to do ctrl
enter to bring that one in here and I'm going to use the keyboard up at the top here so V M
ribbon at the top so on the Home tab on the ribbon so if I just scroll back there and give you just
a quick yeah so that's the Home tab on the ribbon you can click on this drop-down list here and you
can choose flash fill from that drop-down list so if I do that there we go and go to flash fill and
you can see it's all filled up just make sure all the dates are right looking good brilliant okay
so let's go to Third Way so if we click on the waste three and four and I'll provide the sheets
as well of course I will down below so you can click on that and you know and follow along next
one's going to be the old problem off I need to get everyone's email addresses now I know we've
got their first name and their last name for people with double-barrelled names it's their
first initial it's gonna be their first initial it will be and then their last names together okay so
what I'm gonna do is I just click on the first one here I'm gonna type J dot Charles at UPS make sure
I smell Charles right at computer tutoring co dot uk' okay it's gonna do a different way for flash
fill at the moment but what I'm gonna do is hold down control and enter to enter that keep control
button held down and we're gonna press the letter e on the keyboard and you can see it automatically
fills that down whoa looking good isn't it so as we look down he goes great excellent ah here we go
we want to take the first time so if I'm looking down this list here I see the first time it's not
really working is with Derrick James Robert so just be careful with this here in fact I'm just
gonna make this a little wider here we go because I don't want to double click on this because it's
gonna take a male - Derrick James Roberts that email address doesn't exist so don't worry you
know if you click on it Derrick James Roberts shouldn't be getting an email okay so we're just
going to double click to the right here and I just want to put the word just before the are
basically or the cursor just before the are in Roberts type in James and compress enter and you
can see the flash fill is automatically matched the Harvey and the Carlos there it is starting
to be my bestest friend great excellent so let's move on to another example the for for example was
a for example there so if we go across and it's just zoom in here so we've got the wrong date I
see people typing in dates like there's all the time as I go out and conduct training courses
they put in full stops instead of forward slashes just if you're watching this and you're typing in
a date please type in a forward slash type in a forward slash you won't have this problem but
if you do or you've received a spreadsheet that somebody has typed a forward slash in well you
can click in here and you can type in the date as it should be 0 6 /d Row 3 forward slash 2020
brilliant I'm going to use my new favorite just shortcut ctrl enter to enter that and then ctrl
e to flash fill it have a look down and now we've got proper dates excellent just one thing quick
thing as we're going through before we move on to the fifth way and I bet you can't wait for
that but just one thing just to note is if you just have a look you see if you click on here
and then just look at your formula bar here at the top you'll notice absolutely zoom in you'll
notice that is actually not with dates when you put in it in it's not a formula of any type it's
not I'm using left or concatenate or anything like that I took no it's just putting that in it's it's
static it's not dynamic it's a static and if you just like you typed in the text yourself that's
what it is they're great let's go to the fifth one let's click on that 5th way there at the
bottom mailing address so the idea is is I want to have the first name we've got the first name
and last name separating we've got addresses here we can see that there's some gaps here as
well and let's see if flash will come pick that up as well you'll also pick up a little tip as
well here let's start shall we so if we go into HM just gonna click on H start typing so I want
Joshua space Charles now I'm gonna hold down the Alt key on the keyboard and press ENTER to go to
a new line within the cell so make sure it's alt + Enter then we can type in the address for this one
here so one Priory streets okay I'm gonna do alt + Enter bring down to a newline lewisham alt enter
London alt enter se3 8 j EU so on a press ctrl enter to get that entered in you can see that's
great should automatically hopefully have adjusted the heights keep the ctrl key held down and tap e
on the keyboard and you can see it automatically put the addresses so this is a mailing address
that I could use for whatever I wanted to really now as again when I don't have an address - here
I've got a space so let's see how good flash fill is I'm gonna go here just click just before
the essence in Ives back up one just gonna press control enter to the enter that one in there and
then you can have a look and it's got a couple of them in there it's actually done let's just
go and try another one sometimes you have to get a bit more of the pattern so I think Cambridge is
the next one down let's see if we can do this one here so just backspace up and press Enter
just enter that one in here see if that one's that yeah so it's taking a couple for it to pick
up that pattern so there we go five ways to use flash fill hope this has been helpful give it a
thumbs up if you have done if you haven't already done so as well please click on the subscribe and
the notifications button I just wanted to say to everyone thank you so much for watching you