How to use AutoFill & Flash Fill in Excel - Tips for Beginners

hi I'm Jamie and welcome to teachers Tech  today I want to show you how to use autofill   and Flash Fill in Microsoft Excel if you're  new to Microsoft Excel and just beginning to   learn all the different things about it these  are two important things that you must know to   help speed up your data entry so let's get started  on how to use autofill and flashfill in Microsoft   Excel if you want to follow along download  the worksheet Down Below in the description   and let's get started now how autofill  works is it knows it can recognize a pattern is

needed in some cases they just know the pattern  and it can just help populate so in this case   of January I don't want to go down and have to  write in this next one February and start typing   out all the months you can use autofill for this  so if I click on a cell like this and I just go   over to where I see the fill handle this green  little square at the bottom and hover over I'm   just going to drag down watch as I start dragging  here so February March April May and when I   Let Go it populates with those months uh and I  can do the same thing with weeks or sorry days   just like this so if I click and drag down you can  see the days populating here even if I abbreviated   the days from Monday just a month like this m it  recognizes the pattern and does this right away I   could do the same thing if I was going to do the  January as Jan February Feb it would recognize   that as well too now with numbers it's a little  different if it's just one number so if I go ahead   and try to autofill with the number one and copy  down it just copies one again and again   I have to help it out a little bit here so what I  need to do as I'm going to just go contrl Z to   undo that last one I need to increase the pattern  so I'll put a two here just like that and now if   I highlight both and grab and drag down it will  know the pattern and will just keep filling it   out if I was going to go ahead and maybe wanted  to do go up by fives I could put the next one   as 10 and do the same thing so get make sure that  you highlight both of those and then I could drag   it down and the pattern will be recognized you can  see how this can save you a lot of time I've been   going vertically but you can go horizontally on  this too so notice over here January so if I was   going to drag this way January Monday say thing  everything works across vertically as well so if   you're putting column headers this can save you  time doing that uh I want to show you though if   we go to the left so in this case if I was going  to go back and start dragging to the left notice   December November it will go in reverse same thing  with the days this will become Sunday here now if   I go to the numbers here I can continue on just  like I did before but if I go the other way take a   look at this as I drag you're going to notice that  it starts to go zero and then it goes even into   the negatives so a little technique using autofill  can save you a lot of time where you don't have to

be typing all this information in I want to show  you how you can create your own autofill now and   we're going to use this as an example here I  have apple banana orange and let's say we're   typing out this list Lots or we want this list  and we want just be able to have it recognized   in an autofill what we can do is go to file and  take a look at the very bottom we're going to go   to options right here so options it opens this up  here I'm going to show you a shortcut I'm going   to close this if I just rightclick up here and  then go to customize the ribbon this pops out   so I am in customized ribbon but it brings you to  all the Excel options as well I'm going to go to   Advanced and just drag this all the way down to  the very bottom and we're going to go edit custom   list right here so just click on this now I could  go through and I could type things like uh apple   and I hit enter banana I could make my list like  that but rather than even typing it out maybe you   already have it once in Excel like this if I go  here and just go and now grab the list and I can   go back and just click this again and hit import  there they are just like that so now I have have   this list you can see I have other lists that are  already in here so Sunday Monday Tuesday   Sunday Monday so all these different ways so  if you know you're using something over and   over again uh you could uh change that or add  them just like I'm doing so if I go ahead and   hit okay and I'm going to hit okay again so let's  go over here now so if I started typing uh Apple   just like this it should recognize that this  is part of the autofill so if I drag this down   notice it populates the list right away so again  that can save you a lot of time if there's a list   that you're using over and over again so autop  pill is a great feature let's move over to Flash

Fill now so now it's time to switch the sheet  so at the very bottom just make sure you click   on Flash Fill now and you should see this data  that we're going to do a little bit of practicing   on now what's the difference between autofill  and Flash Fill well autofill kind of works in a   linear chronological way just to fill in the list  and I showed you how you can create your list uh   with flash field I would consider it smarter it  can find the patterns and the data that you have   and then fill in all the blanks that you wanted to  so let's start with a few examples to show you how   this works let's start with this list right here  on how to use flashfill in Microsoft Excel now I   want to pull out the name the first names from  this down on this side right here and I don't   want to have to go through and type each one Emma  Noah Olivia and so on Flash Fill can do this so   much faster for you if I go and do the way that  I showed you with autofill and just grab the uh   autofill handle here and drag down it just copies  it doesn't recognize any pattern it just put Emma   over and over again but if I go to this here the  autofill options take a look at Flash Fill just   select it and it said hey I think I know what  you're doing you want to have the first name   of these two in here so just like that it did the  list for me now I'm going to show you a shortcut   for this too that's way faster if you have this  first cell selected try this on your keyboard   control e so control e it will just populate that  list a lot faster and save you even more time you   can also find uh Flash Fill Up in the uh in  the ribbon I like to just search for things   now when I'm looking so it's so fast this way if  I just Flash Fill and click on it if you notice   it said it didn't like that because I didn't have  the cell selected what I wanted to Flash Fill so   if I go back to it and type it again flashfill  it populates it like that so I don't even uh I   can do more than just pull out the first name so  here's an example if I delete all this I could   go uh if I wanted the first initial and last name  so if I type Johnson and you have to type things   correctly or else it won't recognize the pattern  so if I put E Johnson like this and go contr e   it pulled out the first initial from the first  one and then the full last name if you want it   just initials I could go ahead and put EJ in the  first one and then use my contr e uh to uh just   fill it crol e just like that so you can see the  time saving advantage of Flash Fill let me show   you a few other ways you can use it if we move on  in this case right here so MIA Thompson I created   this I just rewrote this once like this I want to  put emails to this so if I click in the cell and   you'll probably see this pop open I'm just going  to click off of it because I don't want to send   an email I'm just going to go contr e and it takes  it this and knows what I want and populates that   list just like that now I could go ahead and do  something like this if the dates were formatted   this way with month then day maybe I want it day  then month so if I click in the cell contrl e and   it will copy it down we can also do things like  take from two cells uh so if I have this cell   in this cell here and I write Charlotte Clark in  this cell and I use my control e it will know to   pull that down and again I can make different  combinations if it can recognize the pattern   here's one that can save you time for formatting  you know how hard it can be to read phone numbers   like this I just rewrote it once like this put  the uh just to break it up so it's easier to read   click in the cell controll e and it goes down  and Flash Fill even if I have uh three columns   like this I could go and type Zoe Patel 1992 and  we'll go ahead and just use our contr e again on   each case we have the first name last name and the  year after it so lots of different ways that you   can use flashfill to save you a lot of time I hope  this video has provided you a better understanding   of autofill and Flash Fill in Microsoft Excel  two things that can really help speed up your   data entry let me know what else you want to learn  in Microsoft Excel or other Tech products thanks   for watching this time on teachers Tech I'll see  you next time with more Tech tips and tutorials