Microsoft Excel Tutorial - Beginners Level 5

hi there jamie keet here today at teachers  tech hope you're having a great day today   this is level 5 microsoft excel tutorial  for beginners so if you're not familiar   with my tutorials for excel i'll put the  playlist down below where i walk through   from the very beginning concepts in excel and  leveling up each time as i go through with a new   video this video is going to have a couple new  concepts in it that i'll go through everything   will be time stamped down below so you can jump to  different parts of the video if you're looking for   something in particular but let's get started  today with level 5 excel for beginners this tutorial is going to have two main  topics and the first one is going to be   about pulling data from the web now a lot  of times when i'm teaching i need to have   data and i like to just pull it from the  web and this is a fairly simple process   to do so if you ever just want extra data to  practice your excel lessons you can go to a   web page with a table and pull it right in there's  a few other more reasons that you might want to be   doing this if you're building an excel sheet that  needs to have live data maybe from a stock market   or even the weather and this can be simply  brought into your excel worksheets the other   thing i want to focus on is about tables taking  your data and turning it into tables and showing   you all the different things that you can  do when using tables inside microsoft excel

so let's say i want to bring into my excel this  table here this information from box office mojo   of the top grossing movies of all time of course  inflation and adjusted to this too so you can see   i have this large table here from this from this  website so you could google this too if you wanted   to look this up and to follow along but what  i'm going to do right now is go up to the url   and i'm just going to copy this so i'm going  to copy like this or you could go ctrl c   and now i'm going to go back over to excel and  open it up now what i'm going to do with that   url is go up to data right here so go ahead  click on that and then i'm going to put get   data right here so click down and we're going to  move to go from other sources   at this point from the web then you're going  to get a box that will pop open here just like   this and now this is where you're going to put  the url so you can right click and paste it or   go control v and then hit ok it will take a  few seconds to kind of load this up until the   next step and then so you can see it's picking up  right now it's loading connecting to that website   at this point here what you need to do is you  have some options to select i'm going to select   table and then you can see the table comes  right up here so you also have table view   and you have the web view so you can make sure  that you're pulling it from the right space here   i'm going to go to table view and i'm going to  move down drop down and go load two so at this   point load two i get this other uh box that opens  up where do i want this go to so i'm gonna leave   it as a table because i do want this as a table uh  and so select this first one and i'm going to say   i wanted to go on this first sheet right here so  i'm going to just uh select existing worksheet and   you can see i've selected an a1 if i clicked and  then it's going to go to a different place but i   want it to be brought in starting at that point so  you set this up the way you want and then go ahead   and hit ok so it's you can see it's getting the  data and boom it's brought into your spreadsheet   just like that i'm just going to bring in a second  page here into my excel worksheet and i'll create   another worksheet from this and this is just going  to be the markets with the currency so you can see   that this is a table here i'm going to go back  over to excel i already put in the information   and did the exact same thing i'm selecting table  table view and i'm going to import this on to a   new worksheet so again i'm going to go to load  load 2 and uh this time i'll go new worksheet   and just hit okay so the reason why i'm doing this  is just to kind of point out that whenever these   whether it be the movie uh gross topmo grossing  movie or this one there's a connection made to   the web inside excel and there's actually going  to be live updates now with movie one it's pretty   static it's not going to change very quickly so  no need of refreshing it all the time but when   you have something like currencies that do change  throughout the day you can actually have   a refresh built in so it always has the current uh  correct uh commodity prices or sorry at the prices   of the currencies on it if you take a look at  the right hand side of the worksheet you can see   that we have this queries and connections open  up this shows the different connections of the   two different uh tables that i have connected to  this top one is to the movie that i selected and   this is the currencies here now as i said this  updates it has live changes to it because that   connection to the internet but you can also force  it if you're clicked inside the table take a look   as soon as i click inside query uh becomes on the  shows up on the ribbon i'm going to select this   and i'm just going to go refresh now if there's  any changes you can see it just changed it got the   current market data on the currencies some other  things that you can do to even force it and set up   how you want it is to go over to the table just  go ahead and right click on the table and go to   properties and i can name it here so if i was  going to get rid of table 0 i can just call it   currencies like this and i can go through and  pick what i want when do i how often do i want   to refresh you can see it was at 60 minutes but  maybe i want it to be every i can go through and   just pick on uh one minute every one minute i want  this to refresh now the other thing is you could   refresh when opening the file so if this file  gets closed down you want it to have the current   numbers there so you can select that and then  just go ahead and hit ok you can see the changes   the name has been changed here now every minute  i would get a refresh on this so remember you   can pick other tables like weather and different  things to try on your own when you're importing   these but i'm going to close this down and open  it back up to show you what excel will ask you   so i've closed it down and i'm going to go and  open it and i just named it web and tables and i'm   going to go click on it and you'll see right away  that you have this security warning it's saying   external data connections have been disabled i  need to enable that because they're just saying   hey we're connected to the web are you sure  this is what you want and then we just have   to make sure that we accept this on it so  that's about how connecting uh web data to your   microsoft excel so maybe you want some stuff to  practice with those numbers without having to   input it yourself you can go to a place on a  wet on the web pull it in and start practicing   away let's move over to our next section  talking about tables in microsoft excel now

so in this part i want to talk to you about the  functionality of tables in microsoft excel so   a lot of times when we have our data here's  an example of just the nfl quarterbacks uh   this is a range right now and just to let you  know i did bring this in i imported it through   the web but i did take it away from table form and  i'll show you how i did that a little bit later   so i turned this into just a range a lot of times  when you work in excel this is how you're working   with all the different data now i'm just going to  go over to a blank sheet i have in sheet one here   and i just want to point out how to make a table  if you're starting from scratch you can go ahead   insert so if you don't have any data already  you can see under insert there is table right   here now we'll say create a table and right now  i only have the one spot a1 selected so i'm going   to go back actually and select a range just for an  example and go to insert and table you can see now   the range what size the range is from here i want  my table to have headers and go ahead and hit ok   and now i have a table but i don't have any data  set up into this what i'm going to do is actually   just start it from that nfl passing week one that  i showed you so before i'm going to go back over   to here and i want to turn this into a table to  show you the different the functionality that   you can have so if i click anywhere in this range  here so i'll just click here and i go to insert   again and i'm going to go to table and it's going  to try to guess what the range is since i'm click   clicking on it i can see it highlighted right  around you can see all the blinky lights around it   and i'm going to hit ok because that selection was  correct hit ok now it's turned this information   into a table and i'm going to start to show you  all the different things that you can do with   this now so as soon as i turned it into a table  you notice that there's some more functionality

to this if i go up to the top to the where the  headers are here i have filters next to them   and this comes in really handy to do some quick  sorts with the data inside the table so let's   say passing yards here i could drop down on here  and when this opens up i have some options how i   want to sort is that going to be smallest to  largest to smallest or a custom so   maybe if i want to go to here and say greater  than i want to see all the quarterbacks that   passed over 300 yards this week i is greater than  300 hit okay and i quickly get that information   sorted in this table so you can go through any  of the columns with those filters so i'm just   going to go and undo that with control z on it now  right now you can see i'm clicked out of the table   but as soon as i click inside the table take  a look at the ribbon up top and now i have   table design so i click on table design this is  where i can name the table right now it's called   table three so i could go through and i'll just  call this qb uh for quarterbacks for short and so   i have a name for it so you can call the table  which would match what you want now some other   quick things i want to show you is just in the  styles uh there's this is the default here you   can see this one is highlighted right here but as  i hover over any of these you get this live change

in it to kind of show you what it looks like if i  drop down i can even see more uh different styles   i can choose from on it and then you can see the  new style tab and clear right at the bottom too so   uh just to point out where those are now the  other thing i want to point out are to the left   of these styles take a look at all the style the  table style options here now uh if you wanted if   i check on this first column you can see how it  just highlights or bolded everything in it so you   can make some quick changes to it now you can turn  off i like the filter option on the on this table   before how i can sort through it but you don't  have to have the filter option you can click it   toggle it off and it goes away i'm just going to  leave this on for here now i'm going to put on a   another one total row and you can see as soon as i  did that take a look you can see total at the very   bottom now it put that in and i have 435 but i'm  going to show you a few more things that you can   do with the total in a bit i just want to point  out some of these other options here i banded   columns so what we could do with that so if i put  banded columns notice that it's going to put every   other one highlighted so that might get a little  confusing that way you could turn off banded rows   but i kind of like that banded rose in order to  just easily read across the information in the   table all right let's take that let's take  let's take a look at some more options here   the totals option down below  that i added on to the bottom of this table here   and you can see how it added the total in here and  then 438 what it did was a quick sum of everything   in here but when i click in here take a look you  get this little drop down here so if i click on it   i could change what i wanted there so if i didn't  want a sum i could say uh what was the max and   then it would put that in it would go through this  list and find 56 you can see at the very bottom so   let's say if i wanted to know the average of the  yards passed in a week if i go over to this one   even though it's empty now but if i click on this  and i said average so i could click on average oh   i went to wrong one that's the yards per tempt and  i click on average and you can see i can do any of   these 303 yards average per quarterback on it so  it gives a quick way to add these to the bottom   of the of each of these columns now the other  thing that i wanted to show you see the different   the header right across nice thing with tables as  if i scroll down it changed rather than saying a b   c d across it just put the headers in those place  so if you have a really long list it makes it easy   to kind of you don't forget what column is what if  you roll back up you can see how it just changes   right back in so that's another nice feature of  having it with a table and you can make a quick   change here too so if i go back to the styles and  maybe i wanted something uh to have the totals   highlight at the bottom you can change it and  now i have a total one uh it's doing the header   and the total of it just to kind of quickly change  the round and that's up to you what you would like   now i want to show you a couple other really good  features inside using tables here so i'm selected   inside the table and remember when that happens  i have table design take a look at there's remove

duplicates and this is a handy feature now this  for this example probably won't come up uh but if   i click on it and i'm going to go unselect all  here so take away everything because the only   thing i want to check to see if i have a duplicate  of a player in here let's say for some reason   that there might be a duplicate it will search  everything but imagine if you had a long list of   different numbers you could go through each column  here so the only thing i'm concerned about is the   player but if you had a part number or different  things like that you could select what you want   to search for so i'm going to hit ok and it's  saying hey it found one duplicate value found   and i added one to this so i if you took a look at  the side it was going to be two justin herbert's   here so i'm gonna say okay and it went through and  deleted that to it so the duplicates are removed   from there now so that makes a quick way to look  for those another thing is so when i brought this   in i did connect it to just the nfl page of the  stats and i brought it in and i converted it to   just a range so with any table you can convert  it back to a range so if i'm clicked in it   and select convert to range uh do you want this  to convert enabled to normal table to normal range   and hit yes and it will go back so even though the  formatting i was still there this is just a normal   range so i can go through and change this too so  if i just hit normal you can see now it's back   to where i started so that's what i did when i  pulled it in from the web source i just took away   and turned it into a range just to show you when i  started here today i just want to quickly show you   how you can add some more rows or column to your  table inside microsoft excel i if i go to the very   end let's say i'm in the last cell and i just  hit tab notice a new row with formatting and   everything will be added and same thing if i go  to a column and just type and i'll just type test   notice when i click out it automatically adds  that to it and it has the filter it becomes part   of the table when you're inside the table if  you notice on the right click you'll have some   options to where you can insert table columns  to the left or rows so if i click rows you can   see it adds another one so if you do need to  add any of these to it it's really quickly to   do with those couple of options now i want to end  this video today with just giving you some quick   shortcuts inside microsoft excel i'm going to give  you three of them i have to hopefully help you   just kind of speed up some of your things inside  microsoft excel so here are the quick tips to end

this tutorial here today the first thing is about  tables since i just talked a lot about tables you   can quickly create them with a shortcut so if you  click in the data range and then just hit control   t on your tate on your keyboard and you're going  to get this shortcut and you can select that it   has headers hit ok and then you have your table so  that's tip number one just the shortcut for that

now the next thing what i want to show you is  i'm gonna go to sheet three here let's say if   i wanted to copy this information onto each uh  onto other sheets and then i don't want to have   this here i want to have it just so i can enter  it individually for each sheet but i don't want to   go through the process of copy pasting it on each  sheet i can do multiple ones at a time and this is   how you do this so what i'm going to do first of  all is just select this right here and i'm going   to copy ctrl c or you could right click and copy  to but i'm using control c now if i select sheet 4   i could paste it onto here and that would work but  i can select multiple sheets to paste it across   if i hold shift down i can select each at the  time just like that and the other thing is i   can do is i'll select them off if i select  the first one so if i was going to sheet   four and holding shift and select the last one  it selects everything in between two so now what   i'm going to do is go to a1 and ctrl v paste it  and i'm going to at this point i'm going to now   show you each of these sheets so if i go to  sheet 5 sheet 6 you can see it's all there   it copied across so i'm going to select  them all again and notice that they're   pretty small so i want to work on them a little  bit larger i can zoom up so i'll just use the   bottom here and i'll zoom up to 180 percent and  now if i check across the sheets they're all   zoomed up on the other thing if i wanted to delete  the data or data in between it so if i go to here   and select this area right here hit delete because  if each sheet was going to be different and maybe   i just had to change the area now if i go on each  sheet this time notice it's all gone and i can   just make the one change so this can save you  a lot of time if you're dupl if you're copying   across sheets rather than having to input it  sheet by sheet the last quick tip i have for you

here today is about formatting an entire sheet so  let's say for an example right now you can see my   font is at 11. maybe i wanted it at a 16 font and  i don't want to have to change it one by one or   our selection what i can do is select the whole  sheet so if i select everything like this and   then turn it to 16 we'll say now all these cells  in the future if i click and add anything to these   will be that 16 size font so just select  everything in the sheet and then you can make that   up with that adjustment and then you don't have to  go back and change it one by one after so i hope   you like those three little quick tips and the  other information about importing the data into   your excel from the web and also on tables let me  know what else you want to learn in excel thanks   for watching this week on teachers tech i'll see  you next time with more tech tips and tutorials