How to Use Excel - A 3-Hour Path to Confidence and Skills

hi I'm Jamie and welcome to teachers Tech and  welcome to my beginners course in Microsoft   Excel so this course is developed to make  sure to take a person from novice with   barely any Microsoft Excel skills and bring  them to becoming skilled and confident using   Microsoft Excel so we're going to start  at the very beginning at the very Basics   and then scaffold it through so it's a nice  stepbystep progress till the end and you're   feeling confident about how to use Microsoft  Excel I have attached all the practice files   Down Below in the description so you can  download them and work along with me step   by step along the way let's get started on  how to use Microsoft Excel today on teachers tech let's go ahead and open up Microsoft Excel  now I have it on my taskbar down below because I

use it a lot and I like that quick access if you  don't see it there you can go to your search and   just start typing it in and it should pop open  right away if you want it on your taskbar just   rightclick on it you should have some options to  pin it to start or your taskbar I'm going to go   ahead and open it at this point right here and  when it pops up we're going to do a quick walk   around this screen we are going to be working  from a blank workbook today but I just want to   point out some things that you're seeing now here  are previous uh workbooks Microsoft workbooks that   I've worked on and I can just open them up again  I want to point out notice this one says one drive   and this one says desktop I can save my Excel  workbooks to the cloud to one drive and I can   be sharing them with other people and working with  other people at the same time or I could be just   saving them to my computer the other thing I want  to point out are these templates if you're seeing   these ones these are tutorial templates which are  great stepbystep ones that they have the data in   you just followed along just to let you know I do  have a lot of other tutorials on Microsoft Excel   and I'll put links to the description uh in the  description down below and up and above in the   card and make sure you hit that subscribe button  so when I do release a new video uh that you're   going to get notified when those come out so the  other thing here is more templates now these can   save you a ton of time if you know if you're  looking for to create something like a budget   or a calendar or an invoice all these different  ones after you're done with this Microsoft Excel   beginners class you'll feel pretty confident uh  manipulating these ones with the data that you   want to put in uh I'm not going to be covering  them directly in this class today so let's go   ahead we're going to create a blank workbook and  notice if I navigate on the side here from home

I can go to blank workbook here so I'm going to  go ahead and just open this up and we're greeted

with the typical Microsoft Excel sheet here let's  start with some basic terminology and a little bit   of a walk around the layout in Microsoft Excel the  workbook that we just created when we created that   workbook we get one sheet because it's a blank  workbook if we take a look at the bottom it says   sheet one we can create more sheets we can rename  these sheets and I'll go over that later but when   you first start you just have the one sheet inside  that one sheet you're going to have columns you're   going to have rows you're going to have cells in  ranges that you're going to be adding uh data to   so we can select these columns so if I go up to C  if I just select C I've selected the entire column   now so if I on my mouse just scroll down you can  see the numbers increasing as I go down and that   entire column is selected I can do the same thing  for rows so if I select this row right here now I   have the entire row of three selected and I could  go across and see the letters increase as I went   across that way where the two intersect where C  C and three intersect right here that's a cell so   when I just select one spot like that that's the  cell in this cell here is C3 if I take a look up   at the name box up here whatever cell I click in  I'll see that B3 D4 it changes so if you need to   quickly just see what cell you're in take a look  at the name box another important thing to know is   what a range is when you have a bunch  of cells selected so here's an example if I select   B2 and I'm going to click on my mouse and drag  and have a selection area down to here this is   a range right here it's just not one cell it's a  group of cells to I know what this range is   named it's going to be from this right here so  this is B2 and then you'd have a colon and you   go all the way to E6 so that would be the name  of that range so that's some basic terminology   some important basic terminology to know when  working with uh your worksheet on the sheet   here when we first get started let's move to the  top of the workbook here I want to talk about uh

the different things between tabs ribbons groups  and how to navigate everything here if we go up   you can see I'm under the Home tab so what we  see here are the different tabs as I click on   them if I click on the file right here it brings  me back to that original page when I just started   a blank one and it has all those options on it  now I can hit this Arrow to bring me back to the   workbook and continue working on it so these are  the tabs across the top underneath every tab we   have the ribbon so the ribbon is this area right  through here and this is all customizable too I   can change the ribbon and the tabs to add more  mine might even look different than yours and   I'll show you later on how you can cust customize  that but inside the ribbon so if we take a look at   this font right here where it says font this is  a group so this right here is a group inside the   ribbon and we can go one more step after this  because not everything is listed in this group   and whenever you see this right here I can click  on it and it gives me more functionality so under   the font I can see all these different things  through here that I can apply so this is   just a little walk around of what we're seeing  we have our tab across the top inside the tab we   have a ribbon inside the ribbon we have groups  and then we can expand for more functionality   whenever you see this now uh the thing I want you  to do before we move on is actually to save our

page before we start entering the data so let's  do it this way we could go from save up here but   for this first time I just want to go back to file  and at this point I'm going to go save as I could   go save as because I haven't saved saved  this workbook yet but if I go save as it brings   me to a few different places I could save it this  cloud is for one drive so I could save it into my   one Drive account if I wanted to do that I could  even save it into a SharePoint sites here and I   have other accounts connected to this as well if  you want to save it to your computer you could go   this PC or browse so if I go browse here and just  go to my desktop I have a folder beginner class   here I'm going to go ahead and open that up what  do I want to call this book so maybe this would   be I'm just going to call this class one just like  this you can call it what you want and I'm going   to hit save now as soon as I hit save we're back  on the page that we've been working on I want to   show you right up here so this is called class  one it's an Excel file and it's saved to this   PC so then I can drop down and get some uh more  information about this but remember you can also   save it to one drive and then you can access it  through the cloud let's move on now and let's   start inserting some data into this workbook so  the data that we're going to be entering in today

is going to be the top grossing movies of all time  if you took my previous Microsoft Excel tutorial   beginners tutorial you know that I use that too  but I have a feeling that the uh amounts and the   data has changed since then now I'm going to click  in A1 and I'm going to just type the First Column   heading that I want and it's going to be rank so  go ahead click in A1 and type rank I am going to   say I'll have uh all these workbooks that I show  you today I'll put the link Down Below in the   description so you can download it and open it up  in Microsoft Excel and you can follow along with   the data that I provide you so we have rank here  and then I can go over to be B2 so if I click in   B2 I can write the next one which is going to be  title so this is going to be title of the movie   so I'm just going to put title just like that now  I've been clicking with my mouse to go to the next   one you might want to use tab on your keyboard so  if I hit tab that moves it over I could use the   arrow keys to go around and to navigate so there's  different ways you can do this now in this column   I want to put worldwide gross so this is going to  be what the movie brought in uh with all you know   whether it be domestic or uh foreign everything  like that so this is going to be worldwide gross   and notice right away if you're new to excel it  looks like that I typed it in C and you can see

in the formula bar up here that it's there but it  looks like it's in D if I go to D and I'm going   to put my next one in it's going to be demestic  gross so if I type in domestic gross right here   and I better spell that correctly and if I click  off of it did I delete anything from here no I   didn't because the cells are separate so if I  click in C3 uh sorry C1 I can see worldwide gross   and domestic gross I can see it there now we can  change the width of our columns by going up and as   soon as we get this symbol I can click and hold  and stretch the column so notice I can make it   as wide or narrow I just like that a quick way to  do it though is to go up in between and then if I   double click it automatically adjusts to what's  written in there right now so if a title becomes   longer still and I do that it will adjust to the  widest part so I can see everything and I'll show   you what I mean as I move forward uh so if we go  to the next one domestic gross I could do that   and adjust so we have domestic gross and then I'm  going to put year now I'll be probably adding more   columns later and I'll show you how to do that  but that's what we're going to start with right   here I want to show you a quick way you can adjust  everything I'm going to make these smaller I can   select everything on the worksheet here by just  selecting this up here so if I click this button   everything on the worksheet is selected so if I  was applying a format everything would be changed   so if I said bold everything becomes bold even  these cell so if I enter something and later it's   still going to be bold if I turn it off but I can  adjust if I have everything selected and I double   click between any of these notice how it adjusts  everything to fit now again I might have to adjust   that later because as I start typing in different  amounts or uh titles that's going to be much wider   so now that we've got some titles in let's go  to our next step if you're finding the font a

little bit small and you want to adjust your view  without changing any of the font size or anything   you can do this in a few different ways uh down  in the bottom right hand corner we have the the   zoom so I can click up to get a different view I  can slide that and Under The View tab up top check   in the ribbon we have different Zoom tools here  as well to get it just the way you want to make   everything comfortable now let's talk a little bit  about formatting I'm going to go back to the Home   tab and different ways that we can change the look  here so if I click into a cell once I want to show   you the difference between one a single click to  a double click if I click this cell A1 and I start   typing something notice that it just defaults over  top it just deletes what uh what was there before   uh here's a tip you can use contrl Z or the back  arrow up here to undo your uh last step so I'm   just going to go contrl Z and if I wanted to just  uh modify what's in there already I can do this   in a couple different ways I could double click  and go in here and type ranking notice it didn't   delete anything the other way I'm going to just  go contrl Z again the other way I could do this   I can go right up into the formula bar and click  in here and then add there and now I've changed it   from there so I would have to adjust this just  like that like I've showed you before so I can   also go back and delete things so just some ways  to uh you know to make some modifications to what   you've already added now let's talk about changing  uh you know things from maybe you want a different   font to start with I can select one cell at a time  or range of cell to apply those so I could select   one cell like this I could make the change to uh a  font to a different one but probably in this if I   knew that I wanted it to be all the same I could  select and I'm just using my uh Mouse to drag it   across and I could go ahead and then select on  the drop-down of my font and choose the same one   and make the adjustments that I showed you before  if I select everything and then just adjust for   everything to fit I could make some changes  to color so if I was going to go and choose   maybe a red if I wanted red so the font becomes  a red I can change the background of the cell so   if I drop down is there a color that you would  like I I'll say green and then I've made some   adjustments we can do the same thing with borders  around these so I need to select the area that I   want to apply the borders I have some right here  they give you some quick ones so if you want all   borders do you want it just thick outside you know  thick outside around I can choose I can even draw   the border around so if I click it uh these don't  show up that well it wasn't that thick for what   I have but it's around the outside now if I don't  want them right now I could go back and drop down   and then all I have to do is look for no borders  and it's there now I want to point out if you do   change I kind of mentioned this before if you  select everything and then you apply let let's   say h a color to it so if I was going to say uh  we're going to make sure everything is going to   be this color here if I type anything at this  point and if I just type hello notice that the   color is applied to everywhere even if I go over  here since I had the whole sheet selected it's   going to it's changed everywhere so that's make  be careful if you're going to change everything   that way I'm just undoing with a couple control  Z's uh to get back to my previous step now how   I like to add titles and this makes it an easy  way is to be using cell Styles so if I go and   just highlight this again I'm under the home here  Home tab if I look in the ribbon look for Styles   and some of these other things are very important  like conditional formatting and format as a table

and we'll get that to the later on but if I go  to cell Styles look at the different ones I can   quickly pick so if I hover over you can see an  idea of what I have so if I if you just hover   over and if I find something that I like I'm going  to go with the blue right here and now I quickly   adjusted the style just by using this one cell  Styles so we have some information in here I'm   going to go collect the data and put it in here so  we can move on from there remember just download   the sheet down below uh in the description and  then you'll be able to have all the data that I'm   showing you here today so if you've downloaded  the Excel workbook this is what you should be

working with and this is where we're going to  continue from I have 15 so the top 15 here so   there's some formatting to do here that I'm going  to make some changes to uh the first thing uh what   I want to point out is notice that the title like  I mentioned before you're not seeing uh the words   the titles come up so I could make this larger  so if I click in between the two double click   and it stretches it this way so that's one way I  could do it if you didn't want it to stretch out   like that let's say you wanted it uh just to be  differently we could wrap text in a Cell so if I   go and highlight the information that I want to  wrap the text notice under home under alignment   we have wrap text so if I hit wrap text what  it does it took the it didn't uh expand the   cell longer wider it just WRA the text onto into  that one cell so it's wider the row height is a   here so wrapping text might be an option if you  want it that way I'm going to just uh go contrl   Z and I'm going to go with this method here just  to uh stretch it out because I should have the   room but I'm going to show you why I might want to  change this in a moment now the other thing what   I want to do is I want to add a title to this so  something above the top here and I   can insert more rows I can insert more columns or  rows what I'm going to do is just uh on top   of the number one when I hover over it I'm going  to rightclick and notice I have insert so I can   go insert and I just inserted another row and I  want to be able to call this the top 15 movies   of all time and I could write it in A1 but what  I want to show you is you can merge cells too so   there's all these cells right through here if I  go ahead and select this right through here and   click merge and notice there's more options here  merge and center merge and across so depending   and I can even unmerge if I don't want the merge  so if I say merge and center if I go ahead and   just double click in here the cursor is in the  middle then I could say top grossing movies of   of all time just like that so and I have this  title here that's centered across all the top   and I can go ahead and make the uh changes to like  I showed you before with the cell Styles so maybe   if I go and highlight both I could go back to cell  Styles and you can pick what you would like um as   one through here so whether it be one at a time or  uh is it just going to be that one and you get an   idea of what it would look like do you want it to  be the same so I'm going to choose this one right   here so you pick what you want so we've inserted  another row I do want to point out anything you if   I click on a cell right click on C here there is  the insert uh here as well let's do a little bit

more formatting with our dollar symbols now so we  have all our worldwide gross here in our domestic   and I want to format this with dollars I could  select one and format anything if I go up top I   can select my dollar and notice right away uh it  puts these number signs in like that if you ever   see that what's happening is there's not enough  room in that cell so for an example if I stretch   this out now it comes out so if you ever see  that the number signs across like that uh then   that's what's happening and you can just format  the column width and then it's going to fit and   see so I'm just going to undo because normally  what I would do is uh change maybe everything at   once rather than one at a time so I know these  are all dollar amounts and then I can go back   and click up here I still have the same issue I  can double click in between and it will stretch   out to remove that now I want to point out you can  choose different countries whether it be Canada US   UK all these different options and then we have  you know right now under accounting we have the   commas in there so if it was just number it goes  back to this one and currency you can see kind of   the different formats of each one as we go through  uh the different ones so I'll just leave it like   this and then the next thing I just want to  show you is what this would look like if you try

to print this and I want to point out this line  there's a perforated line between C and D this   shows me in the current way that is printing in  a portrait that this is where the cut off would   be so if I go over to file and I'm going to go to  print here and then take a look so this is where   this was at where the perforated line was this  is actually two pages so this wouldn't work very   well to print it like that one thing you could  do you could change the landscape or to landscape   orientation and then it's going to fit across so  you can make that quick adjustment if you wanted   to keep it in portrait you'd have to make some  changes to this to fit so if I was going to go   back you could try making this column not as wide  but you're going to lose some of this so this is   where maybe you want to try the wrapping so if  I click it once and twice and then I can make it   a little bit smaller in here and at this point I  notice the line is right here after E I could try   some other things like changing font size if I go  back to file and print now you notice everything's   fitting on that one page now there's lots of other  things under page layout under here so to set up   breaks and everything I have a different video  all about printing in Microsoft Excel so I'll   make sure I'll link that as well now I want to go  over basic Excel formulas I'm going to do this on

another sheet so let's first of all let's rename  this sheet rather than the sheet one I'm going to   call this top movie so I'm just going to go ahead  type that in like that if I rightclick on this too   I just want to point out you can rename and delete  sheets and you have different options even a tab   color so I could go ahead and change it I'm going  to go ahead add a new sheet and this is just going   to be a practice formulas so I'm just going to  type well I typed that in the wrong cell I got   to go be down here so for formulas just out here  and I could make it a different color if I wanted   to as well so I could toggle be between both back  and forth okay so formulas inside Microsoft Excel

the most important thing to remember is when  you're starting a formula you need to start this   with an equal sign so what I mean is if I go to uh  this cell and I want a formula I could use this as   a calculator but I have to put the equal sign in  and then I could say two and I let's say Plus + 3   equals 2 + 3 hit enter it's five but five isn't in  this cell if I click in the cell and look in the   formula bar this is what's in the cell equals 2 +  3 if I don't put the equals in it will not work so   just so you know that you always have to start  a formula with an equals now what makes Excel   powerful is cell referencing so what I mean by  that is so I'm going to go ahead and put a number   up here so this is going to be five and we'll put  six here and I'm going to start another uh formula   remember with the equal sign with Rell referencing  so this time if I put equals and click on C3 and   then say well let's go ahead and multiply so the  multiply is going to be the above the eight the   star here multiply what well I could put a number  in or I could click on another cell so this is   going to be C3 * D3 which should be 30 5 * 6 hit  enter and I have 30 if I click on the cell look   at the formula bar here and there it is so this  is what makes C makes Microsoft Excel powerful if   one of these numbers change so if this changes to  10 I type 10 hit enter this automatically adjusts   because it's cell referenced and if the cell  changes then the formula will adapt with the new   cell reference it's not really a number the number  10 isn't in this formula it's the cell that's in   this formula so that's just a little bit to know  about kind of the walkth through of working with   basic formulas so let's go back over to top  movies and add a formula now what we're going   to want to add is going to be the worldwide gross  minus the domestic gross and that will give us a   foreign gross so to do this though we need another  column well we could add the column here and but   I kind of want it right after so to do this I'm  going to insert first so I'm going to just select   this column e and I'm going to right click and I'm  just going to hit insert and notice to the left it   inserted this and this adjusted up top everything  stayed Center as well now this is where I'm going   to place my formula inside this so uh what do I  want to call this well this I said was going to   be forign so I'm just double clicking in there so  that this is foreign gross just like this so I'm   going to go and label it now inside of this I'm  going to also go to the uh Center just so it's all   the same and I'm going to go and start my formula  first thing you have to do is start with an equal   sign and we're going to be cell referencing so  if we go equals what's the first cell well it's   going to be worldwide gross minus so then we just  go ahead and select our subtract key is going to   be domestic grow and just like that we can hit  and we have an amount here now this is where   uh Microsoft Excel again is powerful we created  this cell uh formula so if we look up here we can   see C3 minus D3 I can copy this all the way down  without having to do that to each cell you could   do this in a few different ways you can drag down  like this what I tend to do is just double click   I hover over onto this green little square and I  double click and it automatically fills down and   knows where to place it so it made that formula  in every one so for example if I click on this   cell notice it's C4 minus D4 it changed this was  C3 minus D3 because it knows when I copied it down   that I needed to adjust which row I was talking  about so it always adjust so I didn't have to go   through to each one to create it now there is  something called absolute cell reference and I   will talk about that a little bit later because  sometimes you don't want it to automatically uh   copy down you might have a cell in one place  and you need to stay frozen to that and I will   show you an example of that in ad just a moment  let's go back over to our formulas page practice   page here and talk about uh just absolute cell  referencing and this is just going to be a little

example on it to help you get your mind around  it but I have a different video on formulas and   absolute cell referencing and I'll make sure I'll  link those as well so we have some numbers these   are just numbers no formulas or anything in them  and I'm going to create a formula right here I'm   going to say equals it's going to be this uh A7  multiplied by B7 just like that but I'm also going   to connect it to a different cell I'm going to say  uh subtract this cell right up here so let's hit   enter so 5 * 3 is 15 takeway six is nine if this  number changed this would automatic Al change as   well so now the problem is if I go ahead and copy  this down so if I just go and double click or pull   it down whatever way you want uh let's check if  these are right 4 * 2 is 8 subtract four should   be four and 3 * 1 uh is three take minus 4 should  be negative 1 so something's wrong here so if I   click into the cells so as I copied it down notice  this is D3 I need to make sure this is   always pulling from D3 it can't move so when I go  down to this next one it's pulling from D4 this   is D4 right here so it's pulling zero and that's  why four time 2 is 8 Min - 0 is 8 3 * 1 if we go   down now it's moved down to D5 because when you  copy it down like that it will keep moving like   I showed you when we did top movies what I need  to do though is turn this into an absolute cell   reference so if I go back to D3 here I'm just  going to click in here and I need to put dollar   signs around this so what I mean is the way I  do this is just to use my F4 key or function F4   you can put your dollar signs in front of each of  these in front of the D and the three and if I hit   enter looks like nothing changed but watch when I  copy this down now if I copy that down four And1   if I look at each cell it's pulling from D3 it's  staying connected to here so that's an absolute   cell reference again check the other videos that  I have about that I list in the playlist and   down below in the description now it's really  important that you understand the formulas and

functions are a key again to make Microsoft Excel  go to that next level and we're going to do a few   basic ones here I'll show you how to access uh  these but remember I do have lots of different   videos on different functions inside Microsoft  Excel so I do want to put a function down here   for build a formula with a function in it to Su up  our total amounts and find average uh first thing   though you notice how I when I scroll down that  this disappears all the time you can freeze your a   row or whether it be a column in place so you can  always see what's in it so maybe if it was names   if it was a long column over but in this case it's  going to be a row so if I select number three here   just this entire row I'm going to go and freeze  so anything above it will stay in place so I like   using this search too because if I go can't find  something under a tab I can just search for what   I'm looking for and let's say freeze pain and I'm  going to go freeze panes here so now what happens   is I can go and scroll down and this always stays  in place like that if you want it gone you can go   back and unfreeze it the same way just have it  selected and then go back and unfreeze it and it   will be gone okay let's move down to the bottom  let's try and insert just a quick H formula in   here or sorry a function inside the formula  so we're going to go and we're going to go   to formulas right here look on the tab and we're  going to show a few different ways how we do this   and this is just going to be a quick one with the  auto sum so if I click on it well what do I want   to sum I could click sum and it went through and  chose uh everything from this down to here and   it's it looks like that's pretty much right it is  choosing this one right here uh so whether or not   I don't want C8 I could go through and just hit  17 and it adapted here there was nothing in that   cell so if I hit enter it's now it's added just  like that so it's that quick and I can copy these   over so if I was just dragging this over now it  summed up domestic because it moved over notice   the columns D3 to d17 this is D so it's able  to copy those over if I go down here below I   could use these here is there an average that  I want I could hit average here well it's not   of that right here above it is going to be these  ones so I could go and select the uh range that   I need hit enter and now I have an average of  everything so that quick to use them now what I   like is they have the function live Library here  so you can they break it into categories for you   so you can see whether it be Financial text dat  and time all these different ways but we're not   diving into all those on this tutorial I want to  show you a couple different ways that you could   enter your formulas I'm going to delete these  for now so I just selected and hit delete on my   keyboard I tend to use a different method so you  could go from right here hit insert function and   you're going to you can search so if I was looking  for sum I could go and there's sum right there   could select it what's the range I want I could  put it in I see it's it has the right range that   I want I could adjust it or I could go ahead and  select my range just like I did before and then   hit okay after so that's another way you could  insert your function I'm going to delete this what   I tend to do if you know what you're looking for  I start my formula with that equal sign and then   I just start typing the function that I need need  and it's goes through alphabetically looks for it   here it is some I can double click on this and I  could put in my range I could type it in what I   want or I could select my range like I've showed  you every time before hit enter and there it is so   this is the way I tend to do it but if you're just  starting you probably want to either try under the   formulas tab or through here uh but then after a  while you'll probably notice that once you know   what you're looking for start with the equals  if I was looking for average I could go ahead   and then select select what  I need uh from there so I don't have to select   everything if it was averaging a couple I could  do that we can copy them over just like that so   we you see how you have a lot of these different  options with the functions and there's so many   and this is where it brings everything to the  next level using Microsoft Excel I want to talk

about sorting and filtering in Microsoft Excel  now and I just wrote some random stuff just just   as an example before I show it on the uh the list  here that we've been working on but with sorting   and filtering I can sort based on numbers or I  could go alphabetically and I'll show you some   different ways we can do this but let's say in  this case right here if I highlight these things   you can see 8265 and if I go up to if I'm under  the Home tab and I go to sort under editing here   and I wanted to sort maybe the smallest to largest  so number two should become the first notice Mouse   is next to number two if I select it changed it  so now I have 2568 but all of these changed they   stayed with it now I could also do it a different  way if I was going to go and highlight and go back   to sorting and filtering maybe do a custom sort  and I could go sort hi based on the cell values   this time and it's going to be also on A to Z so  if I hit okay notice now that it's cat dog fish   Mouse because that's the alphabetical order and  the numbers moved around to it so just a little   bit about sorting some data that way I'm going to  go and delete this right now another way what I   like to do is add a filter to this so I'm going  to go and highlight my uh row here just of the   headings and I'm going to go back to sort and  filter I'm going to add a filter to this so if   I select filter I get all these drop downs now  on all the ones that I chose I'm going to just   show you from year I could filter from smallest to  largest to smaller uh smallest so maybe I   want to see by 2023 coming out first so that would  be this one and they put 2023 and I can see right   now it's the Barbie movie for this one and it  sorts them all the way to Titanic in 1997 I could   also search for an individual year on this so if  I go back to my sort and right now everything is   selected I could hit select all and then look for  2015 and hit okay and now it gave me back all the   all the movies in the year 2015 that came out and  I can see uh the different ones through here so   it's not the complete list if I want to go back  and see everything again I could select all hit   okay and then I have so this really lets you drill  down on the data that you have I have a short list   here but imagine if you had hundreds or thousands  of entries and you wanted to start filtering it   for something specific in it this could save you  a lot of time now I want to show you how you can

chart inside Microsoft Excel uh this is just  touching the surface of it I have a different   video that I go through extended charting uh take  a look at that if you want to learn more about it   I'll put a link to the in the description down  below and up and above in the card uh but and   the other thing is to uh change your rank if you  still have your filter on you can uh show your   rank to the uh smallest to largest so filter  this one so we're then we're back to our top   now we don't have to create a chart for the whole  thing we're going to start by just doing the top   five so what I want to do is actually I'm going to  highlight from title over here to worldwide growth   so I'm just going to do this uh selection here now  then if I go up to insert and we look under charts   we have all these different charts here uh we can  go through these quick ones we knew if we wanted   to see the different types of bar charts in here  to the pi ones uh I'm going to go to recommended   charts so I'm going to select this and then it  gives you an idea if you go to all charts you   can see everything uh I'm just going to start  with this basic one right here and I'm going to   hit okay uh at this point the graph plops in here  into it I can move it around I can click and hold   uh when you click and hold it make sure you kind  of get into this area I'm just going to place it   right here now if I want to expand the data that's  going to be in the graph all I have to do is look   at this area and I can go and pull it over so if  I pull this over one more to domestic grow then   it's going to be added in here so it's an easy  comparison visually I can see uh which one has   done uh the best for domestic versus the other  ones I could even bring it over again to show   foreign gross and I can grow it the other way so  if I wanted it more than just the top five if I   wanted it to be maybe seven uh then I have even  more in here so I can make those adjustments very   quickly even after I started uh making the graph  so and now what I can do is even make some more   changes to this do I want some elements and you  can do it in a number of different ways uh right   now I don't have any chart title on and I can be  adding uh and I can make some quick style changes   up here so first of all let's try a Styles change  so if I go uh you know I like this one with a   contrast and I select it pops a little bit more  I can still adjust all this information but this   gives me a quick way uh just to kind of get get  started with the Styles and I'm going to leave it   there but I want to add some more things to this  if I go over to add chart element uh what do I   want to add to it do I want an axis do I want axis  titles on this do I want chart titles well let's   go with a chart title so if I click on this now I  have a chart title sometimes it defaults with this   depending on what you chose so I'm just going to  go I can click in here what's the chart title I'll   just say top grossing um movies here and I'll just  leave it just like that and I'll click off so I've   made that change notice when I click on the chart  I get all these different options under chart   design I get another tab that uh comes up so if I  click off of it it's gone click on it it's there   let's add something else and I'm only going to add  a couple things here but if I drop down and then   I go to let's say I want a legend I could choose  to have a legend on the right hand side and then   I choose it so right there now I have my Legend  here uh it's pulling it from this different series   names across the top here so I'm quickly adjusting  this as I uh go through I can change colors at any   time through here and I have quick layouts too so  if I wanted to still make more changes you could   try uh hover over different ones if you wanted  things to be just differently presented uh you   can see what works and what doesn't work when you  choose uh different ones try that approach if you   like maybe I like it at the bottom uh right here  and I'll leave it then I've adjusted it like so if   any number changes in here it will dynamically  affect what the chart looks like so here's an   extreme example here so for Avatar if this was  going to uh 5,000 this won't even show up anymore   when I hit enter uh notice it's gone right through  here because uh it doesn't even register for the   other one so I'm going to go uh contrl Z put it  back undid that last one and then it's back so   everything's Dynamic if this changes the chart  will change as well if you go through and double   click on the chart you do get formatting options  uh through here from The Fill to border I'm not   going to show you everything on here I just want  to show you where you can go through and play you   can change uh different areas so if it was things  like the legend uh what did you want to change in   it so I right now if it was no fill or automatic  you could choose maybe a solid fill and right   there the colors there but I could adjust it to a  different color just like that so I can make all   these changes uh to it and if you close out if you  rightclick on the chart you can see there's lots   more options that you can do uh you can change  the chart type from here uh save it as a template   move the chart you can save this as a picture as  well now I could go through if I was going to go   and check file and then print it gives me an idea  of what it's looking like right here CU I had the   chart uh selected on it so since this was selected  that's what I chose to print if I click off of it   into here and I go file print now notice that  it's I would have to do some formatting on the   pages how it's going through and probably I'd  want to change it from landscape and I probably   changed the size of the page and everything but  it gives you an idea of the different way so if   you just wanted to graph the chart printed just  select that and then hit print so that's a little   bit about uh charting inside Microsoft Excel the  last thing I just wanted to show you today and I

mentioned at the beginning was about customizing  your ribbon for Microsoft Excel I'm going to go   just up here and you can right click in a few  different places I could right click here like   this or up here you're going to get customized the  ribbon I'm going to select that I'm just going to   stretch this out so we can see everything and I'm  going to move this over so we can uh just compare   notice I'll shrink this we have home insert here's  the home here's the insert draw isn't selected so   it's not showing up page layout is there so that's  where uh these all these Tabs are showing up now   if I wanted to add a new tab I could go ahead  and click new tab and this top level one maybe   I wanted to rename it and I'll just call this demo  for today's purposes hit okay what do I want to   add to this well I have to add to the group so if  I wanted to add well I'm going to add cut and I'm   going to go through I'm going to add freeze  pane to this and insert uh picture so kind of   random but I added these things to this when I hit  okay look at that demo is there and then I have my   group that I created so you can create your own  ones if you just want that easy access rename   things the way you want I can go back rightclick  customize if you don't want this anymore I could   go back choose if I wanted to delete maybe remove  the group or remove the demo I can just go   through and do that the other thing I wanted to  point out we do have our quick access toolbar at   the very top above the tabs I if you this is all  that's on it right now but if I want it to have   maybe cut on it I can add it uh or maybe I want to  have we'll just add one more in here it's going to   be insert table add if I hit okay now across the  top you're this is how you customize your quick   access so you find those ones you just want to  go up and click on it will save you a bit of   time so now we've covered the basics of how to use  Microsoft Excel let's Advance those skills looking   at some of the powerful tools inside Microsoft  Excel to help you with data entry showing you   more functions and how important they are to use  and make sure you have the right spreadsheet open   you'll see all the tabs at the very bottom just  make sure you check that in the description and   open it and keep following along let's start  with a time saving tip how to make yourself a

little bit more efficient using AutoFill in the  workbook we're just on the first sheet so if you   take a look down below we're just on this one  that says autofill 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 o n 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 it 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 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 same 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 right click 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 this list you can see  I have other lists that are already in here 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 autofill 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 fi 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   roft 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 Livia and so on flashfill 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 now   when I'm looking and 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 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 wanted 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 CR e just like   that so you can see the timesaving 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 than 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 and 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 contrl 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 now I want to talk a little bit  about combining cells in Microsoft Excel I just   showed you flashfill and that is a quick way to  do this so if I was providing this example make   sure we're on the combining cell sheet uh now if  you want to follow along I'm going to go through   and just go contrl e it does The Flash Fill the  one way uh the one reason I don't like using   Flash Fill is it's not Dynamic so what I mean  is if Ned chooses to be called nety it doesn't   update over here so it stays as net darkk I would  have to do that again these other methods that I'm   going to show you with the Amper sand or the uh  these uh different functions you uh will be able   to dynamically change them if this list changes  here let's go and start with the Ampersand here   so I'm just going to go here and remember when  we start with the formula we need to start with   an equal sign uh just like what we talked about  in The Beginner's class so I'm going to go ahead   and put an equal sign uh we're going to sell  reference we're referencing this one so A2 which   has uh netti in it and then I'm going to go ahead  and put the Amper sand signed in so the Ampersand   signed is the above the number seven I'm going to  go shift and then I'm going to go ahead and click   on the next one which is B2 and hit enter notice  it says NTI Stark all one and I don't really want   that I want to have the space in it so you have  to do something um if you want the space you   have to do something a little different I'm just  going to click on the cell here uh if I go ahead   and double click in it brings me to this and I  can make the changes this way or I could go up to   the formula bar to make some changes but what I'm  going to do is just after the Amper uh sand sign   here I'm going to go and add a uh some quotation  marks just like this and actually I'm going to put   a space in between them like this and then add  another Amper sand sign and then hit enter now   I have the space so double click and you can see  what I did you need the Ampersand sign on both and   I put the space in between I could do something  differently so if I wanted to have you know hyphen   you can see how it changes like that but in this  case I just want to have the uh the space that I   had here now take a look at this if he goes back  to Ned and I just go and change this updates   uh automatically this can copy down so I can go  and drag this down uh just like other methods   and then this if I click on any of these here  you'll be able to see the Ampersand sign in here   now let's talk about uh some of these functions  that you can use and the reason I have these three   different functions here depending on what version  of Microsoft Excel you have I'm using the newest   version 365 version of it and I would  probably be using text join to combine it uh but   if you're on an older version maybe You' want to  be using concatenate and I'll show you how to use this if you have a version of Microsoft Excel  before 2016 this is probably what you're going to   use as a function to uh to combine cells I'm going  to go and use my insert function right here and   let's go ahead and look for it here so I'm going  to go to start typing and I'm just typing Co NC   hit go and these are actually the three different  functions that I'm going to be showing you here in   the next few moments but we got concatenate right  here I'm going to hit okay so what's my arguments   well my first one is going to be Ned over here so  A2 and if I click in here notice another spot is   put in I do actually have to use all three spots  in this way uh so I need to actually put the space   in between so just like before if I don't put this  it's going to be put together uh then I can click   in my last one and then put Stark in uh so then  or B2 and I hit okay now notice if I clicking it   uh you can see the formula here concatenate it's  going to be A2 and it put the space in between   and then B2 just like that and I can go ahead and  copy this down now with the concat so if you're   with the newer uh newer Microsoft Excel here we  can uh try this one is a step up because   it allows me to choose a range but it doesn't  quite allow me to have the spaces in it so I   have to kind of default to the other way so  what I mean is if I go ahead and if I put equals   this time and start typing concat you can see  there it is here if I was going to go I'm going   to keep typing notice concatenate it just shows  that it's out of date here you get that little   uh messaging here but this is concat I could go  and grab a range here and hit enter and it puts   back Ned Stark but I can't add the space in it  I would have to go back and do the same thing   that I did before so I could delete this I could  go put my con concat spell it correctly concat   right here select my first one uh use my uh use my  comma and then my spaces like so and then another   comma click on my second one and hit enter and  then it works kind of just like concatenate on   this one text join though is what I find if you're  at the newest version what you should is what you   should be using because then the range will work  and you can add your delim eliminators text join   fixes the issue that concat had with the lack of  spaces that I could add to this so let's go ahead   and start this so I'm going to put the equals in  here and I'm going to type in text join here it   is I'm going to select it so what do I want here  well the first thing it's going to say what's the   delimiter so delimiter is like a space or a hyphen  what do you want to go there I want a space so I'm   just putting in my uh normal W with the space in  between the quotation mark put my comma uh ignore   empty cells I don't have any I'm just going to go  ahead and ignore this so I'm selecting this one   put my comma in here this is what I like I can go  and grab a range now I don't have to go uh A2 then   B2 I'm going to grab a range so just by the A2  colon B2 I have a range I hit enter I can go to it   and then fill it in again if any of these things  change so if I go to netti here look which ones   Chang so that these all change right through here  so these are Dynamic and the Flash Fill wasn't so   those are four different ways besides Flash Fill  that you can join cells together in The Beginner's

Excel class I went through filtering and sorting  just kind of in a basic way now I kind of want to   dig deeper into it and show you can advanc feature  and also a function with filter in it as well well   but first before I do that you can see right here  how the formatting isn't quite right I want to   give you a little tip for a short cut to quickly  uh format your width and your row Heights and   everything on it so what we're going to do is if  you click anywhere in the uh data here and just   go contrl a so contrl a will select all now the  next thing what you're going to do is go to alt   and H and that goes to your home tab if you look  up top and it's giving me all these letters over   the screen it indicates what I want next I want to  go to format which is O and then I can adjust the   uh column width so you can see the column width  I want the autoc column width is I so I'm going   to hit I then it adjust it so I also want to do  any rows that aren't there so I could go back   to the alt H for home then go hit my o again and  this time what I want to have is the row one the   the row Auto height and that's going to be a so  like that everything is adjusted so if you get   to know those shortcuts you can quickly format  uh all your column widths uh and also your row   Heights on it now let's go and do some sorting on  this I'll do a quick review from what I did in the   uh in the first beginner's class before we do some  sorting let's just go ahead and add another column

and I'm just going to go and click on the E column  here and I could go insert under the Home tab and   insert a column this way or I can just rightclick  and insert this way and it adds the column we're   going to just double click in there and we're  going to call this uh foreign gross just like   that and we'll do a quick uh formula so we're  going to go equals and we're just going to say   it's going to be worldwide minus this one so we  get a total it doesn't fit we can just go correct   our column width by that double click here and  we can use the fill tool double click and fill   this in so it gives us a foreign gross here now  uh let's do some quick sorts what we're going to   do is just highlight everything we're not going  to sort it by rank because we can see already   it is there but if we wanted to change the order  of the rank if we do highlight everything we can   go under the Home tab over to source and filter so  we have sort and filter uh if we wanted to go from   largest to smallest because right now the number  one is coming up so that'd be the smallest but   let's go largest and it flips everything and all  the numbers adjusted to it so you can see Avatar   still has this and we could also rightclick on  this and we have the uh sort here we can go uh   this time we could go smallest to largest and  it puts it back to what we already had we know   they're in the correct uh ranking of these ones  but we could also do it different ways uh if I go   and highlight maybe if I just wanted to uh sort  it by alphabetically I could do that so I could   go and rightclick use my sort and then we could go  to uh sort zda there you go you can see everything   changed alphabetically so I could go through and  I'm just going to go contrl Z to undo that I could   go and sort by a certain column so if I chose  this one uh if I chose all of this information and   sorted it it's going to sort here now let's talk  about uh filtering so I like to use filtering more   than sort uh and I'm going to add a filter to this  so right across the top   uh to this area uh I could rightclick if I wanted  to and we have our filter uh I could go and filter   uh through things but I actually want to go up top  to sort and filter and click here and notice that

I have the dropdowns that come on each one now and  this allows me to uh sort from these so let's go   and fill filter uh let's go by year so if I was  looking at years I could go and turn select all   and then turn 15 okay now it only shows me the 15  here so sorting allows me uh here to uh go through   with the filtering to find exactly what I'm  looking for and then at this point I could even uh   filter again in it if I was just looking for one  of these I could just unselect this and hit okay   and then it will go even further down so I have  two different uh two different ones on if I go   back up to uh this I could go clear or reapply  you can see but if I clear it puts everything else   back to normal so let's go another step and look  at an advanced filter to use the advanced filter I

want you to go up to the data tab here and take  a look in the ribbon under sort and filter uh   notice that we have the same options that I showed  you under the Home tab or right clicking there's   always a number of different ways you can get to  these I want to go to the advanced here but I want   to do a little bit of stuff first I actually want  to uh go in this cell and I'm going to type year   in some uh years that I want  to filter so it's going to be and this is kind of   random I'm going to say 2019 we're going to say uh  2015 and I'll just add one more here and we'll go   2012 just like that and you could pick any three  that you want I'm just going to click inside this   information here it could be in any cell and then  I'm going to go to the advanced so there's right   here so I'm going to select it notice that uh  since I was in it and sometimes you don't have   to be within it and it will just find the data  here and so list range right here and now criteria   range is what I chose here what I wrote the thing  is though you need to make sure that whatever the   header that you place over here needs to match  one of the headers over here so you can see I   have year here and I'm just going to  go and hit the criteria range and it's going to be   right here and hit enter and I'm back to here you  could also go a multiple columns you just have to   make sure that again your header is going to match  the range from something within here too so I'm   just going to hit okay and now it's giv me back  those years so 2015 I can see 2019 and a 2012   uh if you want to clear your filter if you take a  look up top uh we can go up and just clear it and   it puts it back so I could go and enter some more  information so if I was turning this to a 21 here   uh then I could go back and uh do the same thing  that I did before and go to advance and uh choose   it based on those what I like next is I'm going  to show you the function filter uh that can do   I find I like it how you can kind of dynamically  change things from one cell reference so on this   spreadsheet let's go ahead and set up uh the space  we need to use this function this filter function

and I'm going to go ahead and select just the  headings here and I'm just going to go contrl   C and I'm going to go contrl V place them over  here and I'll just highlight all these columns   and double click in between to set the width  quickly that way and I also want to filter by   year again so I'm going to type year here and I'm  just going to put a year that I want to filter   by I'm going to change this but I'm going to just  type 2019 now I'm going to put the filter function   right in this cell here and to do it I could do  the equals and start typing filter but I'm going   to go up to insert uh the function here so insert  function and if you don't see it on your list you   can type filter hit go uh this is a Microsoft 365  subscription function so if you're not seeing it   that could be why if you don't have that so I'm  just going to go ahead and select this filter so   what do I need what's the array well the array  is going to be and I don't want to include the   headings is going to be this area right here  so that's my array what am I going to include   so this is the what I'm going to so I'm going to  click into the include this is going to be where   it's searching like down here so I'm highlighting  everything in here but I also have to add a step   here so I'm going to be adding the cell reference  so I'm going to put an equal sign in and I could   type the year 2019 but I want to be able to have  it cell reference so if I change that cell uh then   it will change automatically so I'm just going to  click on this right here and put it in and then   this last part here if it it's blank I just wanted  to return kind of blank so I'm going to go and   just put a couple of quotation marks there and I'm  going to hit okay so and I'm going to I can adjust   this uh more here so you can see this one and then  I could do some formatting on these uh as well if   I wanted to add the dollar signs but take a look  2019 so it's pulling from here what I like about   this method if I was going to look for a different  year I could just type it in here so 2022 and now   it's bringing me back 2022 this way you know it if  you set any of these up here versus the way when   we're just filtering if we were looking if you  had a list of thousands and you had to uh go and   deselect and select all the ones you want where  you can just go ahead and type it in here using   the function so just some tips about filtering  kind of from the simplest to the advanced filter   to even using a function so now I want to show you  how to create a table in Microsoft Excel and show

you the benefits of why you would want to use a  table uh we've been just using a range so far so a   range is when I select uh you know from different  points of cells and that's the range but now it's   a table I want and to turn this into a table it  can be done very quickly I'm just going to click   anywhere within this information this data set and  go to insert and you can see there's table right   here so if I click on table it comes up here uh  the range is there and then my table has headers   and it does have headers across the top I'm going  to hit okay and as soon as I do that it's   created this table and if you take a look up top  in the tabs we have table design and it gives us   all of this information and I'll talk about this a  little bit more all these different things in the   in the ribbon that we have but I want to show you  a couple things first if uh I want to turn this   ever back to a range rather than a table we have  this right here so we have convert back so I can   go do you want to convert the table B to a normal  range I can hit yes and notice those filters that   were automatically put across the top are gone  the formatting still exists but it's still just   a range right now I want to show you a shortcut  for this though uh if you're doing this quickly   you just use contrl T to do this so control T and  it automatically pops up with create a table I'm   going to hit okay and now this is back to a table  again and I want to point out too that we have a   table name so this is called table five right  now you can choose the uh different name for   this so if I wanted uh this table to be called  uh top movies like so and I'm just going to put   no spaces in here at top movies and now this is  called uh that table is called top movies now   some of the things that were created right away  we had these filters added that are uh you know   and I showed you how to work your filters you can  go through I can click in here sort all the same   ways that I showed you before uh we could also do  a uh do a numbered filter or even a custom filter   if you know you're looking for something specific  uh for example if I was going to go top one and   maybe I want to see the top seven here items hit  okay and it quickly uh shows uh right through here   all the different ones and then if I click back  I can go and clear my filter and it pops open   so it makes it very nice to quickly go through  through another benefit is if I go and ahead and   add anything to this table so if I was going  to go and use my foreign uh gross that I said   before so we'll just type that in it automatically  expands the filters there even though I don't have   anything in it and it happens the same thing so  if I keep adding more rows it's going to expand   this way as well the great thing is when I do a  formula and we'll do the same formula that we've   done before I want it right in here I hit equals  and we're going to do just the simple one that we   had the subtraction one from this so notice it's  not coming up as C2 this C became this worldwide   gross became a named range in here so worldwide  gross minus this right here so domestic grow and   watch what happens when I just hit enter it fills  and adjusted the width of this entire column and   filled it now another great thing that you can do  so if I if I'm uh going back to table design here   make sure you have this tab turned on I want you  to look at some of the options in the table style   options like we can you know whether a header row  or not you can see you can toggle click these on   or off if you want if you don't want the banded  rows or banded columns just like that turn them   on or off but what I want to point out is the uh  total row so I'm going to hit a total and I just   need to expand this it automatically came up  with a total down here but we have even more   options take a look at this so if I drop here  I can just quickly uh you know if I hit Max it   went and found the max there if I was looking for  uh an average I can just quickly uh change it so   you can see the things that you can do within  a table how quickly it allows you to do this and   with the other columns you can do the same thing  so if you're looking for uh a different total or   Max or men you have all these options under any  of them uh just do the drop- down and then you   could go through and sum them up and adjust  to everything you want and the other thing is   take a look at the table Styles if you do want to  change the table Styles you can take a look uh you   could go through uh hover over any of these and  you can get an idea of how everything looks   uh through here so our next step that we want  to do is in uh insert a slicer into this so a   slicer can help uh filter everything so I'm first  of all before we insert our slicer I just want to   add another column to this and I'm going to call  this uh studio and this is just going to be kind

of a fake uh just for adding information so we  can do some filtering and I'm just going to call   this uh Studio One here and I'm going to just copy  down uh to get three of them so the autofill goes   through and now what I'm going to do is just copy  or you could go write or contrl C and I'm going   to go contrl V and paste it down to fill up all my  spots here so I have a repeat of uh Studio One to3   so remember I could filter through different  Studios like I showed you uh like just normal   filters that go through just like that I'm going  just going to undo that but what I want to do is   add a slicer so a slicer uh if you take a look  and make sure that we're under table design we   have insert slicer here and we also have it under  the insert we have under filters the slicer so   multiple ways to get to it I'm just going to stay  under table design I'm going to insert a slicer   so notice I get some options what my slicer so I'm  going to slice this up uh or add my filter slicers   for this I'm going to look at studio and I'm also  going to look um at year here so I'm going to go   ahead and hit okay so I get these two slicers now  and I'm not diving into it in this one but I want   to point out uh that up top now I have a slicer uh  here so a slicer option in the tab and then I have   underneath of it I get all this in the ribbon and  I can go through and uh you know adjust the colors   of this and do the formatting and you know put  buttons and size and everything so if you're doing   a dashboard you can make it look exactly the way  you want but I just want to show the functionality   here so here's an example if I wanted to search  by Studio I could go and pick Studio 2 and it   filters it then I can see the three years that  they have and then I could even filter it down   to another level so I can go and clear these by  just hitting up here and it goes back and notice   as I do this all the totals are changing to it so  if I was going to look for 2015 and right away I   can see Studio 2 and Studio three come in uh then  I can even go to select Studio 3 after that so you   can do multi select too if I just uh I can clear  again from here but multi- select is with your alt   plus s on it so just making sure if you want to  select more than one of these so that's a little   bit about tables and slicers you need to have a  table to insert the slicers to it but you can see   all the extra functionality that you get if you  use tables with your data versus just the range   on it this is an extremely important concept to  understand cell referencing is especially when

we're talking about relative and absolute so make  sure you're on the correct sheet cell referencing   and we are just going to do some simple  formulas but we're going to be talking about   those two topics of uh relative and absolute cell  referencing so I need to create a formula right in   here that will give me the total of week one so I  have a unit price and I have sales for week one so   I just need to multiply these two that's a pretty  easy formula I'm going to go into this cell right   right here and start it so equals it's going to  be price per unit uh multiplied so multiplied by   this if I hit enter $13 that looks correct can I  copy this down I'm just going to autofill that yes   so it went through or did all the calculations  everything looks correct in here so what I want   to do actually is go to the formulas page and I'm  going to click show formulas uh you notice things   get expanded but just to show you what's happening  here so this is just relative what I mean by that   uh with the columns and rows is actually relative  relative if I go ahead and copy when I copied this   down it is relative depending to what row it's  in so as I copied this down this changed to C5   multiplied by D5 and I needed that C6 * D6 so  this is working uh correctly being relative if   I'm just using this one column like this I'm just  going to turn the show formulas off where I run   into a problem let's say I'd like to be able to  just oh copy this over so if I want to copy this   over this way $780 if I double click in here  it shows me what the two uh cells that are are   in here so 26 * 30 well that's not correct I need  it to stay on price per unit and multiply by week   two so this doesn't work when I copy this over  I needed to stay in this column of C so how do   I do that I need to make that column absolute in  my formula so I'm going to go back a step I'm just   going to delete this information and I'm going to  delete all of this right here actually I'm going   to delete everything we're going to recreate uh  the formula using absolute relative so I'm going   to go and start with equal and it's going to be  this one right here C4 what I can do is I can   add dollar signs and this is how I make something  absolute now a trick to this is just to use the   F4 and sometimes you have to hold down function  depending what your keyboard is but if I on M   function F4 I press it once it puts dollar signs  around both I don't want that because when I drag   it down I want the row to change this would just  lock into place and I'll come back to absolute in   a moment so this is absolute I'm going  to press F4 again now it's in front of the four   so this would be the row uh that won't move but  I want the row to move it's the column I want to   stay in place so I'm going to press it again now  the dollar sign is in front of the C and that's   what I want it to stay in absolute for that  column and I'm going to complete my formula now so   I'm going to just go uh multiply by this right  here and hit enter $13 so it looks the same and   if I double click on it I can see the dollar  sign is in front of the C nothing in front of the   four on it so everything if I go ahead and copy  this down everything else still looks correctly   so let's go back and show our formulas so if we  go formulas show formulas uh everything so C5   notice it's staying in that column that's what  we wanted and it worked the first time and but   now when we drag to the other side watch this so  I'll turn off my formulas and I just want to drag   this one over now it's $15 so if I double click  in this one C4 * E4 so C4 * E4 that's correct so   this was absolute and relative because oops I made  a mistake there by clicking out of it I'm going to   hit enter and I'm going to copy this formula down  and if I just click on this one so it's price per   unit times sales by week that's correct also and  so as I go down and keep checking these you can   see I was able to copy over to the column and then  copy it down and everything stayed correct because   that first one is locked into the column but this  second spot right here E6 is relative to the row   that I'm in so that is the first example now let's  say I want to be able to I can do this with rows   as well so I'm going to show you a little trick  here to uh for something else I'm going to go   and just copy this so I'm going to go contrl C  like this and I'm going to paste it down here   again so I'm just going to right click but what I  want to show you is this right here is transpose   uh so if I go to trans pose it changed uh the way  it was set up so notice now I have my days across   the top and then uh this across the bottom so if  I was going to make sure I want the row locked   into place versus the column it would be uh  slightly different so I'm going to go and just   delete this information out so if I was writing  this formula here I would be equals so it's going   to be multiplied by this here so what is it so B5  it's not the B I want it to move when I drag it   across this way it's going to be the 15 I need to  stay in place so rather than using the F4 I could   just go put the dollar sign in here as well so I  can just go Shift 4 and put it in front of the 15   and then I can do my multiplication and it's going  to be here just like that so I get my $13 so if I   drag this uh across here to fill it out everything  looks the same here I can see uh the same amounts   are coming up here so if I drag this down let's  see $15 that looks correct and then if I drag   this across like this everything is matching up so  if I go to show my formulas again you can see here   so with it in front of the 15 it's always staying  in that same spot of the 15 is staying the   same uh because I have it in front of the so this  would be relative absolute and this one up here   is absolute relative now I want to go to the last  example and that's going to be absolute absolute   because I don't want it to move from the cell so  if I go to show formulas and let's say there's a   discount I'm going to just quickly uh do a total  here so it's just going to be a sum right here of   uh these two here and we'll copy this down and  we're going to apply a discount to this notice   there's a discount up top here and if I was going  to say uh if I would just went through here and   say equals this and I'll do a multiply versus  this okay here's my discount that works fine   what happens when I copy this down well notice  I went to a blank and then value take a   look if I double click in here as I copied it down  it's trying to multiply it by this cell that says   product in it that doesn't make any sense I need  to lock it in that one cell it needs to stay in B1   so what I need to do to this and I'm just going  to go through and just delete everything and if   I go here so equals this multiplied by this spot  and this time I'm going to go back using my F4 so   function F4 and I'm going to hit it once when  I have it with absolute so the dollar   sign is in front of the column where it started  so the B and also in the row that's absolute   absolute I'm going to hit enter now when I copy  this down it works because if I click on any of   these double click this here stays at B1 notice  that this is selected I double click in here uh B1   so it always stays in the exact place so those are  some different examples from uh what we had with   absolute relative absolute and absolute  absolute depending on the situation how you're   copying those formulas around now we're going  to go through on how to use the IF function in

Microsoft Excel this is a very important function  to understand to create these if statements   the whole idea when you're creating these if  statements you're really uh going to get back   to results so if you're comparing a value You' be  saying if this is true then do this if not then   do this let's go ahead and create uh one right  here our first if statement in this cell uh D7   and we'll start it off pretty uh simple and then  we'll just add things and give you some different   options and carry forward some of the other things  we've learned in this class now I'm going to   go ahead and for this part here I'm going to start  the function from insert function here so we'll go   insert function if it doesn't show up right away  here do your search for if hit go should be right   at top so I'm selecting this one so we need to do  The Logical test what do we want to have happen   so in this case what I want it to have happen  is if it looks in this cell right here and this   number if it's greater than we'll say 700 just  like that and as I create this here take a look   at the uh formula being created up here as well so  you can just see kind of the structure of it so if   this value is true what do I want to have happen  well I want it to say uh doing good so I'm just   going to type in doing good right in here just  like that so the other thing that I want to have   happen so if it's false I just want it to  be blank so this is where again we'll put in our   double quotation mark like this and I'm just going  to go ahead and hit okay notice it's blank but   that's okay because this number yeah it should be  blank it's not above 700 let's copy this down and   see what happen so in this case right now so doing  good this is above so looked at this one said hey   if this is above 700 give me back doing good or  else give me a blank and so in this case it gave   me a blank on it let's move one step forward here  into this cell and we're going to create it   again but we'll do some cell referencing here so  let's go ahead this time and just put the equal   sign and type if just like this and select the  function if here and what's the first thing what   was our logical test well we we're still looking  at the sales here so it's going to be uh this here   and is greater than so we'll keep that the same  so it's greater than but let's go and reference   this so it's greater than 700 this cell and based  on the last lesson that we just did with the cell   referencing we're going to be copying this down so  this can't move so to do that we need to make this   absolute so absolute I'm going to just  use my function F4 like this to put the dollar   sign around each of it so that's the first that's  the logical test and we'll hit our comma and then   what do happens if this is true okay so we're just  going to again click in this cell right here and   we need to also make that absolute so  again F4 uh function F4 that I'm hitting on mine   and what's the last thing to have happen well I'll  put my comma and I'll just put my double quotation   mark here and then end my bracket and hit enter  nothing seems to be happening but remember these   are the same numbers I'm going to copy this  down is it matching up yeah it is the great   thing with cell referencing is that if this number  changes over here so if doing good turns to be 400   here hit return notice everything's there so if  this is dynamic now and making the changes   because we did that cell referencing I could also  uh change what I'm saying in here and it would be   reflected down here so let's move now to the next  column with an end also in it in this column we're

going to use the IF function again but we're going  to add the N function in this as well because I   want two different things to have it happen when  it looks up this number if it's above whatever   number we put in here so right now 900 it will  bring me back doing good as long as it's also   below 1,200 so let's go ahead and create this uh  with our formula so I'm going to put my equal sign   in we're going to start with our if again so if  but this is where I need to add the other function   right away is the end function so the end function  here now I can go and start putting my   my steps in so what's the first thing well when  this right here so C7 is greater than this right   here so remember similar to what we did before we  may need to make that absolute so I'm going to go   and put my dollar signs around it just like that  and put a comma in the other thing I need to have   happened if C7 here is less than so we need the  less than symbol this number again put the dollar   signs in again so I'll put those in here and now  I'm going to go and close my bracket put a comma   in what do I want to have happen well it's going  to bring me back doing good and remember to put   again we need to Absolute this one so we'll put  the that in here and the other thing it brings   back is going to be blank so if we put our blank  in here and we could have put something else in   here so if you wanted to put uh you know I'll just  put test just like that and we'll close this and   I'll hit enter and we'll copy this down so you  can see what are doing good and the other ones   are saying test but I don't really want it to  say test so what I can do is go back and I can   just delete that out of here just like that and  I'm going to go and copy this down again over top   and so I only have two that are doing good based  on that parameter so if I look at the formula here   I'll just go in here double click you it looked  up it said if it was greater than whatever number   this is that and less than whatever number this is  then bring me back this here otherwise a blank and   if this number changes so if I was going to go  400 you can see the different options now we're   even going to go another step forward you can see  all the different things that we're doing right   through here we're on the step three if t-shirt  cells are above 700 so if we're going to put this   number back in here and Below 12200 we want doing  good to come up and if it's in between but we also   want to have great month pop up if it's not now  on this one we had the nested end function with

this one we're going to have the nested n function  and then we're going to add another IF function so   we're going to have it one inside of it and to  kind of speed this up I could start typing this   all up but I'm actually going to show you how we  can copy our previous formula so we can just add   to it this spot right here I need I know this  is going to work this first part so I'm going   to go up here and just go contrl C to copy this  and hit enter and I'm going to move over to this   cell right here and go contrl V and paste it in so  you can see up top C7 uh I didn't want to drag it   over because that would have uh moved to the wrong  column I would have at C7 but this is all done uh   correctly but where I need to change it is here at  the end I don't want it to just uh show come back   blank at the end so I need to remove this and this  is where I'm going to put in another if statement   at this time so if I go ahead and start typing if  again you can see I get the option and I can go   ahead and complete this so what do I want to have  happen well if what if this right here is greater   then this cell now so above 1,200 that's currently  what we have set it up with and we have to make   the that fix or absolute so make sure you go ahead  and put those around I want it to come back with   this great month and again making sure where  the dollar signs are around it otherwise it's   going to come back with bad month with the dollar  signs on it so we get that all set up the other   thing we have to do with this is ADD one more  bracket even though we have one bracket at the   end you have to match your brackets so I need to  add another bracket so this bracket here and that   bracket match this bracket uh you can count the  the matches between each of these and how you're   closing up on these ones so we need that double  bracket so I'm just going to go hit enter so   they're saying this is a bad month is that correct  so it's below uh 700 so that's correct let's copy   this down and we have great month so great month  would be above 1,200 and you can see all these are   the doing good are in between and then we have  the bad month coming back and these remember   we can adjust these numbers here so if this was  going to be different and everything changes in   this column so that is kind of the beginning uh  point of the IF function here so we did it from a   basic IF function by itself in an if statement and  then we did our nested end and then we added our   nested and if in it so you can see how you can  add other functions within uh functions so let's   move on to our next part of this class now let's  create a drop-down list using data validation in

Microsoft Excel so take a look at this let's run  through an example under fruit notice when I click   in the cell I have the drop down here that I can  pick and I get to pick from these different fruits   so if I pick bananas there's bananas here's the  list right across here that it's actually pulling   from if I drop down to the next one same thing I  could pick oranges and so on now the thing that I   want to show you after we just do the basic setup  of a drop down I want to go to step a step further   and show you how to create another dropdown that's  dependent upon what you chose in the first one   like this so if I go to type notice when I drop  down now it's going to be all my choices from the   banan so I can pick a banana if I go to oranges  and drop down I see all my orange list here let's   try this one more time with apples so if I pick  apples there we go we have our Apple list and you   can see all the different lists that we have in  here but when I chose this first one this adapted   to the correct list and I want to show you how to  create that so make sure you're in the right page   we're on the data validation uh sheet so you  can follow along and create your own let's start   with a basic way just to create a simple drop  down before we get to the ones that I showed   you before now if I want to drop down right here  and let's say we're just going to pick a different   two different types of fruits apple and oranges so  if I want it right here what I need to do is look   up in your tab and go to data and then look for  the data tools right here so data validation I'm   going to just select it here and then the next  thing I want to do is go to list so right here   so list is right here at this point I could add  my type of fruit that I want so if I want Apple   and by comma I'll put uh oranges in this spot and  I'm going to hit okay at this point if I go over I   get the handle here for the drop down drop down  I have apple and oranges here so I could choose   either or very simply like that now I'm going  to go back up to data validation again and I'm   going to go and hit uh clear all Sor clear all  right here so notice I hit clear all hit okay now   it's gone even though there's something uh still  written in there that was the last thing I chose   there's actually the data validation is gone a  different way I could do this if I go back to   data validation I could go and choose my list here  and this time let's go here and I could select my   list across and hit enter and hit okay now I have  my complete list where I could pick different ones   so it's that easy to put in uh sort of drop- down  list and the nice thing with the drop down list uh   you can avoid typos uh when you're entering in the  data you can make sure that everything stays the   same especially if you're doing filters on it  after now this isn't the way that uh I created   what you saw previously we're going to do a few  more steps we're going to undo this step here   and start from scratch because we're going to do a  few other things here first the first thing that I   want to do is turn this data into a table and just  like earlier in the class we can use the shortcut   contrl t or go up to the insert and do the table  this way as well so this does have table headers   yes now we have a uh table here so the reason why  I made this a table was in case something gets   updated maybe I add another banana it will update  the list as well so uh that's why I'm using the   table here now the next thing I actually need to  do is name uh these ranges so this through through   here I want to have this named as a range called  apples this one called bananas this one oranges   and this one lemons to do this it can be done very  quickly if we go to our formulas tab take a look

at this we can create from a selection so I'm just  going to highlight this area create from selection   here and I want just the top row so that's where  the values are going to be and if I just unclick   the left column and hit okay doesn't look like  anything happened but take a look at this so if   I drop down over here I have apples bananas lemons  and oranges if I go and select oranges that shows   me the uh highlighted oranges part there so that's  a named range if I click a different one bananas   it moves over if I go to table 10 that's the  table that I created right there which I could   rename as well but I'm going to just leave it  there if you ever need to delete any of these   uh different names that you wanted you can go  up to the name manager and here they are apples   bananas if I wanted apples deleted I could delete  it just like that so just to point that out uh so   we have a table and we have our named ranges  the reason why I named the ranges is because   I'm going to use this indirect function uh that  allows me to uh direct it towards a range so uh   let me show you an example here I'm just going to  type in A1 here so A1 just like that and I'm going

to use the indirect function right here so if I go  in equals so I'll start typing indirect comes up   this one right here so I'm going to select it I'm  going to reference this one uh right here and I'm   just going to leave it like this so when I hit uh  enter on this one looks what look what happen so   hit enter it brings me back fruit order form it  didn't bring me back A1 it brought me this was an   address that it brought me back this spot so when  I'm using direct with the named ranges is going   to bring me back the list that I have so just a  little bit about indirect let's go ahead and start   our dropdowns and the first actually dropdown  isn't going to be uh anything special it's just   like what I showed you before we're going to use  data validation so we're going to go to data we're   going to use data validation here we're going  to use our list here let's go select our source   it's going to be across here so similar to what  I showed you before just going to hit enter and   then okay and then we have our first one so we  can pick our different ones right through here   and I can copy this down as well but I'll wait a  moment before I do some copying because I want to   do the type so when I choose this fruit the type  will change depending on what fruit it is so this   time when we use the data validation I'm going to  go ahead right back to my list here and I'm   going to actually put my formula right inside here  so I'm going to go equals and I'm going to type   indirect so indirect I'm going to it's going to  be based on this cell right here which is A6 so   I'm going to just type it in uh you can select it  if you wanted I could actually select A6 it comes   in with the absolute dollar signs around it so I  could F4 till they go away but I'm just going to   type it in like that so uh IND A6 so it's going  to look at this one Whatever is in this one let's   hit okay and we'll see what happens so now when  I drop down it is giving me this list because it   looked back at it's looking back at that range and  pulling from there so can we copy this down now so   if I just highlight these and drag them down and  if I drop down let's say we go to oranges and we   go to uh here so all oranges if I go to Apples  this should be all apples through here so you   can see how it's working uh very easily and we  didn't even do that many steps so at this point   we could go through and if this was an amount that  you're entering in for these or dollar sign you   could go through and finish this and remember it  makes it nice because you you're going to avoid   any errors with typles in here uh I was going to  point out why we did a table and everything watch   how fast notice that with Macintosh there's an  H missing here so if I go over here and just put   the H here and just click off of it if I go back  now and drop down and pick it the H is there so   it's already updated if I was adding another uh  example here I'll just type example can't think   of any more banana names off the top of my head  so if I type ex example and if I go over here   and pick bananas here and you'll see example is  there so I can quickly add more and that's why I   I did the table so that it automatically updated  that's how you create a drop-down list using data   validation and how you can create a dependent  drop-down list uh using the indirect function   in Microsoft Excel we're going to go and create an  entry form so we can keep filling in this list or

edit it or search in here so what I mean by this  is watch this so we have to turn this into a table   just like we've used many times before I'm going  to use the controlt to do this and my table has   headers just like that uh do notice I do have the  shortcut to create tables up top here you can add   that as well now I'm going to go and add a form to  this uh I'm going to go to the search and if you   type form it comes up looks like this right here  so I'm going to go and choose it notice here is   my data entry form I can do a few different things  if I wanted to go through I can uh just go through   and read all the different ones that I have here  I can go through and make changes so I can go and   edit too so if I go back and I don't want the name  in the middle there if I just delete that and hit   enter it's made the update if I want to go through  and maybe delete somebody I can do this I can hit   uh okay on this just like that and then that's  deleted so I'm updating the entry through here   I can do a search so if I go to criteria and I was  looking for maybe anybody in house dark and I hit   enter all the ones I see now if I go to next next  notice that they're all in house STK so I can go   through and it's keeping to that criteria and of  course I could add a new one so if I wanted to do   a new entry I could hit new uh maybe I put in Arya  here and I know his house dark and it's going to   be Macy here I probably won't spell it right but  oh well and I'm just going to hit enter and I have   a new one entered just like that so you can see  it adds another way that you can enter this uh I   want to go and show you how you can add a shortcut  for this as well so if I just hit close on this uh   we're going to put one up top in our quick access  here so I'm going to go and right click on this   and customize the ribbon and where we want to  go to is our quick access toolbar I'm going to   stretch this out so we see this shows me  everything that's on there there's the insert   table one I'm going to go to the commands not on  the ribbon and this is all in uh just alphabetical   order so I'm just going to go to uh F and you're  going to see the form right there I move it over   to have it over here hit okay so now at any point  I just have that quick access here that I can   click on and it will pop up and I can enter in  more data using a data entry form the last thing

that I want to show you today is conditional  formatting how to apply it and why do you even   want it so if you take a look at this right away  what you notice it changes it visually and to me   this makes it more functional too because you can  just quickly glance at the different columns and   you can see what's happening based on whether you  choose an icon or these bar graphs and in this one   I even have a form so I'm going to show you how  to apply these different types of formatting to   uh the data that we have we're going to go  through and apply a different conditional   formatting to each of these columns now before we  do that we do need to select what we're going to   apply the conditional formatting too so I could go  through and select here this way or we could use a   shortcut like control shift and hit the down arrow  and it will go all the way down to the bottom of   where the data is and just another tip if you  wanted to select everything uh we could be hold   just hit the right arrow and then it will go all  the way to the other end but in this case right   here I just need to have this first uh this First  Column selected without the header in it so once   you have that selected let's go up to conditional  formatting I want to apply uh datab bars to this   one we're going to go to datab bars and we can  see uh that we as we hover over there's different   colors here so we can take a look if we want the  solid fill versus the gradient and we even have   more rules so these more rules I could uh set  I'm going to come back to this in a different   one and create my own rule with a formula but  I just want to show you can get uh into these   so if you want to create your different colors  uh from here you can do that from here so but to   quickly add uh just our data bars here let's go to  this right here or you pick which one and I just   go ahead and select it and then visually right  away I don't really need to see the number as   much because my eyes will go to the highest one so  this is uh a great run with the data bar depending   again what you want to be showing and what the  data is but this really helps bring the eye to   uh the highest amounts and even the lowest amounts  are to see which ones are similar let's move over   to the profit column now and we're going to go  with a color scale so again we need to select this   so we'll select this column and we're going to go  up to color scales and we do have our more rules   like I showed you before but we can hover over the  ones here that will indicate kind of what you want   to show so you notice if I hover over here the  high ones are in are blue the bottom ones are   in red the more negative it gets the lower number  it gets redder so you can see what you would want   again change the rules if it's are the colors  to it through the rules that way so if I leave   it just like this I can quickly add that to it now  if we look at customer satisfaction let's try   an icon set so I'm going to select again this part  here and if I go back up the icon set right here I   have all these different things I can add to it if  I hover over you get the idea of it in this case   what I want to do is uh go to the stars because  this is customer satisfaction to me this makes the   most sense uh to show satisfaction of these icons  so if I choose it and I look at what it shows the   one that's Fuller gets the most satisfaction so  I don't even have to look at the number I can   just see the icon and go from there let's try  something else under days deliv to uh deliver   let's say in days deliver I want to highlight  anything that's taking over 15 days to show   that there's a problem I need to check into this  so if I was going to go and just highlight this   one and go up to conditional formatting I could  use the Highlight cell rules and look I have a   lot of different ones I can even be uh using ones  that are on dates the text that contains and so   on uh I'm going to use the greater than one here  so I'm just going to change this it's set at 14. 5   but I'm going to go ahead and type 15 in there uh  what do I want it to fill with you can see I have   different options whether it be uh red text custom  I could go to custom format if I wanted to uh set   it even more but I'll just leave it as this uh  one right here and it draws my eyes to ones that   are the problems of those plus 15 so just an easy  way to detect this and if I didn't want to have   any of these on it I can erase the entire SE all  these all this conditional formatting too so if I   was going to have this selected I could go to  conditional formatting and clear the rules so   since I have that selected I could just go clear  rules from selected and it will go back to uh with   no formatting or I could clear the entire sheet  from here too so just if you did if you did   want to erase things okay let's move over to  return and we're going to try the top bottom   rules so go ahead and then select it here we're  going to go up to our conditional formatting here   and here is our top bottom Rule and we're going  to highlight the top 10% of the return rates so   it's already done for us again we could adjust  it with more rules but I'm going to go with the   top 10% you can see if it's above average or below  average here I can go and choose this one what do   I want to happen uh do we want to it red or green  or you choose maybe I'll do yellow this time just   to show the difference and then hit okay and I can  see the only two that have uh come from there all   right so the last one that we want to do is add a  uh we're going to add a formula in here so we're   going to what we're going to do is take an average  and see anything greater than the average for this   one uh so we can see if anything's popular uh in  this would be able to give us this quick glance   to it we're going to add a simple formula to this  last column here uh before we do that I'm going   to insert a number right here it's going to be  40 so what I'm going to create a formula that   uh will look at this number and if any of these  numbers are equal to or larger than then it's   going to be formatted to the color that I choose  so let's go ahead and start this now so we need to   select our uh data that we're going to apply the  conditional formatting to drop down we're going to   go to a new rule at this time you need to go to  use a formula to determine what cells to format   and this is where we put in our formula so to  start with I'm just going to click out of this by   clicking here and selecting this top cell you need  to select this top cell that we're applying this   to so I'm going to select it and the other thing  is see how it has the dollar signs around it for   absolute we don't want that so I'm going to get  rid of these dollar signs and I'm just going to   hit F4 a couple times I want it just F2 uh so  I'm going to leave go back and what do I want   to happen well I want uh it to be so I said if  it was greater than or equal to so I'm going to   put the greater than and then the equal to and I'm  going to just select this and this spot right here   so this does need to be this would be absolute  and that's fine so I'm going to just select this   back there's no format set what do I want to have  happen here I'm going to go for format and I could   change you can see from number font border fill  let's just choose this purple one right here I'm   going to hit okay and okay so now it went through  and changed anything that's equal to there's a 40   notice that this is hard to read if I go back  again and uh let's go back and just go to   uh manage the rules here's the one I just created  I can edit this and I don't want that font to be   BL black there so I'm going to go format and I'm  going to go to font and here's the color I want   it to be white this time I'm going to hit okay  and hit okay and apply so I'll leave there and   now this is much easier to read so that's how you  go back and edit and you can check to see anything   that was equal to 40 or greater was highlighted  so that's how you add a formula with conditional   formatting play around with all the different  styles try making some different rules to see what   you get this can really make your sheets stand  out with just easy understanding of the data that   you have over this next section of this Microsoft  Excel beginners course we're going to take a look   at some specific functions we're going to look at  some if and some ifs coun if and countifs and date   and time functions in Microsoft Excel knowing  all these functions and how to work with them   properly will be an important progression in your  Microsoft Excel skills let's get started now with   sum if now what I want to do first of all is just  talk about the sum function so just like this uh   the sum function will total up the column up above  it or the selected range now what's the difference

uh if you're new to this to using the Su if or  some ifs well what some if will do for you is   it can look through this uh all these different  commissions here sorry sales and based on a criter   iera that you pick so let's say if I just wanted  to add up the north sales here and give that total   back I can do that with some if let me show you  how you do this so I'll just say what I want   again I want to be able to just look for the north  regions here and add it those numbers up so that's   where I'm going to do a sum if because I only have  the one criteria of the north so I'm just going to   go ahead and start my function my formula so  I'm going to type in uh some and you can see   some if right there I'm going to select it now if  I follow along here what is my range so the range   that I want to select from is right here so these  the different directions so I'm going to highlight   this now I'm going to put a comma and it's asking  me for the criteria and this is where I can type   in or select from a cell reference so in this  case I'll just type in and I'll I'm going to   type in North just like this and then I'm going to  put a comma in and this is where it's going to be   the sum range and what is the range where I want  things to add up from right here so I can go ahead   and close the bracket and so what's going to give  me back it's going to add up the all the Norths as   a total sum so I'm going to hit enter and I have  57,000 so just a double check if I look up here at   20,000 it should have chose at the 9,000 would be  29,00,000 and add the 28,000 would give me 57,000   I do want to point out just sell reference in this  example too so if I was going to go and do the sum   if again on this one and I'll just use a some cell  reference and rather than typing it in uh so I go   ahead and select my criteria here comma and this  time I'm just going to select the cell remember if   you type in anything it has to be in the quotation  marks uh you just can't type it without those so   I'm going to use that cell reference though and  uh then I'm going to put another comma and then   give my range again for the sum and then just  close it and I'm going to get the same number   there so go ahead and try those sum if now so one  of the reasons why you'd want to use your cell   reference would be it makes it really easy to make  some quick changes to what you're even asking the   criteria from so instead of North what happens if  you want South well I could just type South here   here and you can see how it quickly pulls out  the numbers for just south on this one now now   let's move over to some ifs and why would we use  some ifs so some ifs is for multiple criteria in   the case of some if it was just the one thing I  was looking at it was just the sales region but   what happens if I want to say pull from the house  here and how many new customers to add that up so   I'm going to add up add in another criteria so I'm  going to go to this place right here and I'm just   going to go to equals and this time I'm going to  put some ifs and go from here and notice that this   time it's a little different it's asking for my  sum range first so this is my sum range I'm going   to select this because this is what it's going  to be looking to add from and I'm going to put   a comma and what is criteria what well my first  criteria is going to be from the house so I'm   going to highlight this right here and add a comma  what do I want it to look for I'm not going to use   cell reference on this one I'm just going to type  it in and so I'm going to use my quotation mark   and I'm going to type in Lannister just like this  so I'm going to make sure I spell it correctly in   quotation marks and I'll put another comma what's  the other thing I want to compare it against well   new customers so I'm  going to highlight or select this range here and   comma and I want to look for anything greater  than 10 new customers so we have to use our   quotation marks again so we're add that in put the  greater sign in here and then 10 just like this   and I'll end it and end my bracket and you can  see 22,000 so what did it would looked at the   two different ones so there's only two Lannisters  and this is the only one right here that's above   10 because this one right here is only four so it  look through two different criteria let's try one   more and adding a third criteria so for this  last example I'm just going to be doing a sum ifs   on the commission so I wanted to add up certain  commissions based on the criteria I pick and   I'm going to pick three of them I'm going to go to  this spot right here to start my formula I'm going   to put in my equal sign then I'm going to type in  some ifs and continue from there so what is going   to be my uh the range that I'm going to be the sum  range is going to be this one and I'm going to hit   my comma and I'm going to go to my first criteria  which is right here new customers I want to look   for any uh any of them above 20 new customers so  I'm going to go ahead and do my quotation mark   here so I'm going to put greater than 20 like this  and close my quotation mark give a comma what's my   next criteria it's asking for well in this case  I'm going to say I want to see anything above or   equal to 20,000 so greater or equal than 20,000  I'm going to put my comma my quotation mark put   my greater than equals 20,000 like so another  and then I got to make sure that I end this and   then comma and what's the last criteria I'm going  to look for well let's say it's going to be based   on region again and so I'm going to highlight this  region here and I am use I'm just typing things in   but you could use your cell reference so I'm going  to say North I'm going to end it and hit enter and   I get 4800 4,800 so what did it find it found this  one from the north here this 2,000 and then this   one from the north at 2800 and both of them have  2922 this is $220,000 or above and this one would   match the criteria so you could keep adding more  criteria based on it you just got to make sure you   follow uh kind of the uh the operation of the the  formula that they're helping you with we're going

to switch over now to the count if function so  make sure you have that practice file open so   let's start with the basics of count if and I'm  just going to start with a simple example here   and that's with this region code I'm just going  to count up how many of a certain number there   using that function when we start any formula  we're going to go ahead click in the cell where   we want it put our equal sign to start and I'm  using the count if so I'm just typing count if   you can see I have the option right here I'm going  to go ahead and select that now I said I'm going   to look at the region code I want to see how many  times 1 2 3 comes up so I go ahead and select my   range like this add a comma and now I can say what  I'm looking for and when you're typing that in you   need to use your quotation marks so I'm looking  for 1 2 3 I end my quotation marks and then I   have to end my bracket here and hit enter and  you can see 1 two 3 comes up four times here's the   first one second one third and fourth and now  I just want to point out cell reference too so   I'm going to go to this spot here and I'm going to  start my count if formula again and you see there   comes up and this time I just want to use a cell  reference so I'm going to go ahead use the same   range it's going to be this range right here that  I want and I want to give a cell reference   where I'm going to type it in here so notice  there's nothing there right now but I'm going   to click on it and end it and hit return it comes  back at zero but now with a cell reference I can   quickly uh type something here that I'm looking  for so if I'm looking for 341 I can type 341 hit   enter and it quickly gives me the three back cuz  341 shows up three times here and I can change   this number very quickly and get it to return turn  so cell references can make it easier all right   let's go on to our next example with count if so  that's just with searching basic numbers how many   times it appears but I can also do things like  greater than or less than and I'll just give you   a greater than example so I'm going to go to start  my formula again I'm going to go with my equal   sign type in count if and it's right here and this  time my range is going to be in this one here so   now I'll say I want to look I could use different  uh operations I could use equal greater than less   than but I'm just using the greater than here so  I'm going to go ahead and put my comma in and now   it's asking for the criteria and this is where  I'm going to put my quotation mark and I'm going   to say greater uh greater than uh 15,000 here so  15,000 just like this and my quotation mark and   my other parenthesis and I'm going to hit enter  and now it finds f that are greater than 15,000   here so you can go ahead and count them up 1 2 3  4 5 now I just want to point out cell reference   and I'm going to do this again so if I go equals  and type my C my count if formula here make sure   my fingers are in the right place so count if  and I go ahead and start it this time so again   my range is here just like this but this time I'm  going to reference this cell again I haven't put   anything in yet and I'm just going to end it and  hit enter you can see Zero but if I want to find   let's say anything greater than 10,000 now I can  just type in my greater than 10,000 like this hit   enter and it gives me back seven that are greater  than 10,000 so I can use the cell reference for   that also now I also want to point out on our  next example here I've showed you with numbers   but we can be looking up uh up  uh different letters in here or words so if I go   to this again and hit my equals and my count if  and select that one and let's say we're looking   in this one my range is here my comma if it's not  a Sol reference I have to use my quotes here so   I'm let's say I'm looking for North then I type  in what I'm looking for again spell it correctly   and end it and end my other bracket hit return  and it can find 3 North in here again I'll leave   this for you to practice but you can go ahead and  type North in one of these spots and then use the   cell reference so you can even use operations  with the count if so if you want to add it up   multiple things in one column so in this case uh  I want to see how many one two 3s and 3 four 1es   I could add two different ctiv up so if I go  to this spot right now and use my equals and   I'm going to go to my count if here and this time  I'll go ahead select my range here just like this   and I'm going to just use and this time I'm not  using cell references but you could on this one   so I'm just going to put 1 two 3 just like this  here and end my quotation and then I'm going to   put uh in this bracket and I'm going to add  this time so I'm going to go ahead put just   the add sign and I'm going to do another count if  here so I'll go count if and select this and   what do I want well it's the same range so I'm  going to select this one again put my comma what   I'm going looking to add right here so I'm going  to use my quotation mark I'm going to put 341 in   and my quotation mark and now I should be able to  hit and it's going to be seven so it's added up   the four one two 3es and the three 3 4 1's and  it got a total of seven you can perform other   operations with this too so if I just go back  back and I'm just going to make a quick change   to this so this was adding 3 + 4 if I go back  over to here uh and just make a change to this   here I'm going to put multiply so now it's going  to multiply 3 * 4 and give me 12 because it's not   multiplying these numbers it's multiplying the  count of the two numbers that I did so you could   do this with different operations if you're if  that's what you're looking for all right let's   move over to wild cards and I want to give you  a couple examples wild cards can be a great way   to really speed up what you're searching for and  what they do is they kind of they take the place   of something and can skip over something and look  for something more specifically that you want uh   so for an example let's give you a simple one and  we're going to look at this name list and I just   want to see anybody that starts with the letter  A now if I go and start my count if uh formula   here and I want to see how many people start  with the letter A so my range is going to be this   inside here so I'll put my comma give my quotation  mark and I'm just going to put a now I need to put   the star after just like this because that will  say well look for the A and whatever comes after   does not matter so I'm going to go ahead end my  bracket hit return and I get one so this looked   through this list if anything started with a name  uh it gave me back one so let's just change this   a little bit and I'm just going to modify this  formula so I'm going to just take out the star   and the A and I'll type in Ned so you can see if  I hit return it brings me back zero there is a Ned   in here but there's a Ned Stark and I don't want  the Stark to matter in this I just want Ned so   if I go right into here add my star like this hit  return you can see now it's got It's over to one   all right so what I want to do this time and I'm  just going to go ahead and just copy this formula   here and go over to this cell here and I'm just  going to paste it in just like this so I'll just   paste in the formula you can see it's the same I'm  going to modify this one now at this case in this   case I actually want to look for last names and  I how many Starks there are so   notice that in this case the uh net and star how  it is if I just double click in here I'm going to   modify it from here so if I go and actually put  my star first this time so uh I'm going to get   rid of this put my star first and now I'm going to  type Stark like this and everything else can stay   the same make sure that this is inside your quotes  All I'm going to hit enter and it has three so now   it kind of bypassed the first letter here uh  in that star and now I could just write Stark uh   and then it found me three Starks one 2 three so  that's just some examples to for wild cards let me   show you one more another example of a wild card  that you could use would be a question mark So   if I was going to just type in I'll use the count  if here and this one over here I'll just use this   right here to kind of give a simple demonstration  this is my criteria here and if I go ahead and   pick what I want to be looking for so the question  mark So if I was looking for uh the put in the   question mark so I'm saying it to ignore the first  two spots and looking for the third one so in   this case if I was looking for anything that had  an H in there and ended it will give me one   back if I quickly go ahead and change this one if  it's looking for anything in with a c it gives me   two spots so the question marks here can tell you  to skip over uh a certain spot and then only look   for the one that you're left with and you can use  different numbers of question marks on it so the   count ifs are used if you have multiple criteria  if you're looking through two different ranges and   you could do two or three or more I'm just going  to show you with an example of two criteria here   and I will go to this cell right here and we'll  start with our equal and I'm going to type in   countifs and what I want to have happen this  time I want to look for anything above 22 in   this column uh that will give me back and compare  it to if it's there's a stark there so I'm going   to have to use wild cards too so my first is my  range so I'm going to go through here highlight   my first range and I'll put a comma and what  do I want to have happen so I'm going to put   my quotation marks and then I have to uh type in  what I want so I said greater than 22 here in my   quotation marks put another comma what's my next  criteria well this is going to be my criteria here   and so I give it another comma and now I have to  I'm going to use my wild card in this here too so   I'll put my equal sign and I just want last name  Stark so I'll put just like I showed you before   put my asterisk in there and I'm going to type  Stark just like this and my quotation marks and my   bracket so I should get looking at this I can see  there's only going to be one above the 22 that is   a stark so I should get one back so I'll hit enter  and I get one back on this one so if I was going   to if I saw if I change that number if I turn this  number to 21 hit return I get two back because   now there's another Stark right up here that would  fit in what I'm looking for so let me give you one   more example how to use this multiple criteria if  we go equals this time I'm going to go so we'll go   count ifs again and I'm going to be looking  at anybody in the north with sales above uh above   we'll say equal to or greater than 2,000 in this  case so I'm going to go to my first range here so   my first range I'll just start my formula here and  so this will be my first range and I'm going to   be looking for anything in the north so I'll just  go and type North and I remember you can use sell   references but I'm typing and you have to use the  quotation mark what's my next criteria well it's   going to be right in here so I put my comma again  and what am I looking for so I'm use my quotes   again I'm looking for anything greater than or  equal then we'll say 2,000 so I'm going to put in   my 2,000 here and end my quotation marks and and  my parentheses and I'll hit enter and I get two so   it was picking from this one right here and  if we were looking for anything above 2,000 from   the north you can see this one would be two this  one's below so I can go back and adjust these at   any time if I was looking for a different number  so if it was uh if it was going to be 2100 here it   should drop by to one because this wouldn't be in  what I was looking for so remember with multiple   criteria I could add more criteria too I would  just keep filling it out take a moment now and

open up the date and time function practice sheet  now let's start off with just something simple I'm   just going to click in today's date now I could  just I don't have to use the function if I want   today's date I could just simply type today's  date hit enter and it gives me back a date   and if you're new with using dates you can format  these so if I rightclick and format cells you can   pick a different format you can go through the uh  the list here so if I was going to just pick this   hit okay and you can see how it's formatted here  here all right let's uh but I don't want this   so I want to be able to have a dynamic uh date so  that if I open the worksheet tomorrow it's going   to change and for this I need to enter a function  and the function that I'm going to use so I just   deleted that cell is the today function and if I  just click in here there's a couple different ways   you can get to the functions uh on this so if I  click this little f up in here insert function I   could go up here you could type today and hit go  and it would find it in the list I could double   click on today and it just enters it in just like  this even though it looks exactly the same as the   last one I showed you uh it's different because  if I open this worksheet tomorrow it's Dynamic it   will say October 12th so it's automatically going  to update and remember I'll just delete this real   quickly you don't have to use that function I key  that I just showed you could just type this in   so if I was using today I could just keep typing  or use it at this point I just have to make   sure that I end I have to end that bracket there  and put it in all right so let what can we do with   this let's just try some simple formulas now let's  say hypothetically my birthday was December 17th   and I want to know how many days until my birthday  you can just create a formula using subtra   subtraction if I go ahead and put equals here and  so we're starting a formula I click on this cell   right here and I go ahead and minus so I went to  click the further one out first and then minus   this cell and then you can see it's 67 when I open  up this spreadsheet tomorrow this would be October   12th and this would automatically adjust to 66  because this first one's Dynamic and it would   change for it now let's see something else we can  do with this uh if I want to add something uh I'll   go to this uh cell reference here this cell and I  want to have a bill due after a person purchase it   and then we'll say 20 days out so what I'm going  to do is I'll start another formula I'm going to   say equals and we'll just go to this top one again  I'm going to reference this cell and I'm going to   go ahead and add so oops I'm going to go ahead and  just add my uh this cell right here so this grace   period one even though I have nothing in there  yet I can quickly adjust it and change the grace   period if I want so I'll hit enter it's the same  date even though the format's a little different   I'm going to click in here if I say let's say if  it was in 20 days like so you can see it adjusted   20 days now it's due on Halloween here if I was  going to say 30 days we could go ahead and will'll   adjust it very quickly with cell references I  could have instead of putting d8 so if you look   up here in the formula I could have put plus  30 and it would gave give me the same date but I   like using that cell reference because it's quick  to change on any of these if you want to put in   uh maybe the time you could do this in different  formats too so if I was going to put equals you   can use the not the new one you can use the now  and if I select this one and just end the bracket   hit return now notice it's giving me the uh just  the date this is because the way that this   one's formatted so if I go to format cells if  you take a look at the time one I could adjust   this and pick uh pick a time here and it will just  give me back a time to it so it depends on how you   want your cell formatted you could go through and  just pick these so you could go look at the date   and time uh if you want let's say go this one  so it's going to give me the date and the time   altogether you can adjust that I do want to point  out there's some shortcuts to the static ones that   make it kind of Handy if you just want to enter  a quick date in here so I'll just click and sell   the cell so if I hit control and then hold that  down and add my semicolon it will quickly add   the date and then if you do the control shift in  semicolon although like usess that kind of makes   it the colon if you hold down the shift but then  you can see how it enters uh some time so those   are just some quick short cuts so those are just  some simple things with time and date I want to   show you a little bit more advanced ones that  you can do too now I just want to go through a   number of different ways you can still add and  do different things and uh find the differences   between using times I'm just going to click in  this cell right here and I'm going to put use   my Now function that I showed you before so I'm  going to type in uh now on this one and I'll just   not new I'll go now and complete it so there's an  empty worksheet that you can just go to the bottom   and practice this as we go along what I want to do  first is let's say if we add we want to add a half   a day to this or 12 hours we can just simply do a  formula indicating this based on this time so if   I use my equal sign to start my formula and I go  ahead and click on this cell and go to my addition   I can add a half a day just by putting 05 for  half a day so if I go and put my 05 in hit enter   and you'll be able to see that this is 12 hours  ahead on this one so but let's say if we wanted   to be more specific than this we can actually  add another more like let's say 5 hours and 29   minutes to this but then we'll have to use another  function uh with it so if I go to this right here   and I'm going to just start my formula and I'll  go back to here clicking on this cell and now   what I'm going to do is add I'm going to start by  typing time and you can see I get this one right   here I'm going to choose this now all I have to do  is follow along so how many hours did I say well I   said five right and then you put the comma follow  it along how many minutes 29 now I could even get   to the second but I didn't say any seconds so I'm  just going to put zero and I'm going to end this   and hit enter and you can see now it jumped ahead  5 hours and 29 minutes so those are just some tips   of how you can be adding specific amounts  of times or hours to a date and I just want   to note anytime there's any changes uh to the  work sheet that this will change automatically   here it's Dynamic right so it's going to make  those changes and everything will adjust from there another cool function with date and time  inside Excel is the workday so I can actually   count I want to know how when is 30 work days  from a certain time so I typed in November 1st   right here and I want to know when is 30 work days  and the I can calculate this a couple different   ways and I could have it with or without holidays  depending on how the workplace works first of all   I'm going to do this uh without any  holidays in it if you look up top and I didn't   point out this yet but if you look under the  tabs and you look under formulas there's all   these date and time ones right here so I could  click down and take a look at all the different   ones here but if I go to workday right here and  just click on this I get this dialogue box the   arguments that I want to put in well I just put  November 1st here and I'm going to click on it so   that's the start day B6 how many days am I looking  at let's say 30 days 30 work days when will the   date be I won't put the holidays yet I'll just put  okay so then you can see that 30 work days from   November 1st would be December 13th now let's say  if I wanted to do with the holidays if I again if   I wanted I could do just type equals workplace or  go back up to date and time and sorry workday and   go up here so my start date is here 30 days again  is here this time I'm going to add holiday so I   wrote them over here so these are the two holidays  that in November I'm going to highlight them like   this you can see it gave that range I hit okay  and now it's December 15th so they took these   as holidays and now it changed the date so that's  just pointing out a work day counting work days   for you and also making sure that you see under  formulas you have all these different ones that   you can use so there's lots of other date and  time functions here from the library if you look   under date and time and I recommend you playing  uh around with those but the last function I want   to show you today is one you're not going to find  on this list and that is date diff and date diff   can calculate the difference whether it be days  months or years between two different dates and   I want to show you how that works so I'm going to  start with the formula in this cell here date diff   and I'll type you got to make sure you start with  the equal sign so I'm going to type date diff like   this and I'm going to go ahead and start it if I  click on this will give me some help on the   right hand side to kind of show you the argument  description and the units to use but how it works   is if I click on this first date where it says  December 2nd 2020 and then I just put a comma   and I click on the second date and put another  comma what I can do is then just in quotation   marks put D for day that's the unit and make  sure you have it in quotation marks and then   enter and you get the days now you can do the  same thing for months so go ahead and start it   and type put date diff like this and start it up  and click on the first one comma second one   like this and then making sure that you have the M  for month and end it and you get 10 months and you   could do the same thing give it a try with year  just putting the Y in there and you'll see the   different uh the different what it will come up  as a zero on it because it's not a full year are   you ready to keep advancing your Microsoft Excel  skills through this next and last section we're   going to be looking at three more things we're  going to look at pivot tables how to create them   we're going to look at the powerful function of  vlookup and then we're going to look at a couple   more functions with how to use filter and unique  to do a drop-down menu That's multiped dependent   so let's finish up with these powerful Excel  skills so we're starting with how to create a

pivot table and this is the data that we're using  here if you haven't downloaded this yet just hit   pause on the video go to that link I mentioned  before and download it and open it up now to do   an insert uh it's very simple you just go up to  the menu and hit insert and below in the ribbon   you're going to see pivot tables right away as I  go through these pivot tables I'm going to give   you some tips and tricks also and I'm going to  start with a tip right away and the first tip   is to select inside anywhere in the range or the  table if you're using a table uh before you go   and click on pivot table because what it does it  automatically finds the range for you so here's   an example if I go up to Pivot tables notice that  they have some recommended pivot tables here I'm   going to cancel and click out and I click on  recommend it again and it doesn't know now I   could still select my table or rrange from  here and it'll be fine but just as a quick tip   if you select inside of it and pick pivot table  or recommended pivot tables it's going to know   where the data is coming from now I just want  to mention a little bit about the recommended   pivot tables uh this is a quick way to uh to  create them uh usually they're pretty accurate   with probably the main ones that you would want  but you can see where it goes some of unit cost   by region some of unit cost by item it does some  recommendations based on the data and the titles   that they think that you would want so uh you  can always start with recommended pivot tables   and choose one of them uh but we're going to be  going from pivot table on it so uh and after we   go through pivot table you you'll understand  after you insert the recommended pivot tables   you can still make the same changes so I'm just  going to go ahead click pivot table and you can   see that right away since I'm clicked inside  of the uh range here that it has the sheet and   the range from A1 to g44 here now uh where do you  want this pivot table to go so this is important   do you want a new worksheet or do you want it to  be on the same worksheet uh so I'm going to go new   worksheet uh and so it's going to open up another  tab you can choose what you want to do on that or   you can set a different location here so you can  also choose whether you want to analyze multiple   tables and I'm not doing that in this case but I'm  going to go ahead and hit okay and what you get if   I look down at the tab at the bottom you can see  I have a new sheet right here uh and now they're   asking me to start building my uh my report by  selecting uh my different fields to put onto it   so now we have to be selecting our pivot table  fields and if we look over on the right and it   can be done very quickly so if I go ahead and I'll  just click on sales rep and what you notice is it   gets put into rows and now the names are going  to go down this column and then the data as I   select it would be put into rows you can quickly  change this so I could dra drag it into columns   and now the names are going to go across the top  and uh we are going to have our information below   it in the column now we can uh quickly turn this  off and on the sales rep so if I click off of it   it goes away we can drag these down too so if I  drag this into a spot I can drag it around into   columns you can see how easy it is to manipulate  so I have another tip here that I want to give   you uh so this is kind of the default setup here  with the fields uh down below where you're drag   into if you go up to the gear here and tools  and drop down you can pick a different way to   view it so I prefer this one right here uh  I'm just going to select it and we get these uh   on the side here with their different fields that  we can just drag across now that's up to you what   you prefer and the other thing I want to point out  with if we go back to it you can sort your Fields   A to Z uh so if you go ahead and select it you  can see how it changes it uh now this if you had   a lot of different fields this would just make it  easier to find them so just remember those couple   tips right there okay let's add some more things  to this now so we're going to have our sales rep   here and I want this in columns and I'm going to  place it here so you can see uh some of the ones   are hiding right now and the other thing I want  to add so let's go ahead and add our uh totals   and you can see as I click on different ones how  quickly it starts to change so I can click on and   off uh I'm going to go ahead and click on units it  shows all my sums of units here but what I want is   to have items so when I click my items now I do  want this in rows it places them down here so now   if I pick my units it fills in so I can see each  person so if I look at Dwight's I can look at per   binder per desk and all the different items and it  gives me the grand total there so these are just   some different ways you can get some quick uh your  report being built by selecting the fields so did   you notice if you click out of the data that the  pivot table Fields go away so C click back in and   they reopen here now I want to point out just  if you're adding more Fields let's try uh I'm   going to add a region here so if I click on region  you can see it added it to the rows at first and   then it's going to be breaking down this way it  breaks down the binder and based on the Central   north and south where it was selling being  sold in different places now if I bring this over   to colums you can see now that it's going to  go across the top it's kind of breaking uh it   between the different uh salese so in the case of  someone like Aon you can see that she sold in the   Central and North while Dwight only sold in the  central here so go ahead try different things try   adding uh you know by selecting different ones  uh selecting uh seeing how it works to get the   different reports on it and then moving from rows  to column to see the ways you can adjust it now   another way is to sort and if  I look over here you can see that we have a drop   down and this is going to help me sort so in this  case this is going to sort my column labels which   are the uh sales wraps here and if I drop down I  can go ahead and select individual ones so if I   said okay let's deselect and I'm going to go and  let's compare Dwight compare Jim   and hit okay and you can see that we have the  two right here and I can look side by side to   see their totals of their items being sold if I  wanted to see total of amounts in different ones   I could pick a different uh thing to add from over  here to add it and you can see how quickly it can   adjust so if you drop down again I could select  everything hit okay and everything goes back on   I can select this way too so if I wanted to sort a  little bit more uh if I didn't want to see all the   different items maybe I just wanted to see paper I  could hit okay and now I'm just seeing paper and I   see the salesperson so it makes it easy uh to look  at that way so go and try a few different ways to   sort now I want to show you how you can actually  build a individual report and it will create a new   tab down below for each sales rep or depending on  what you're creating and it's done only with only   few clicks so before I show you how to create a  tab for each salesperson I wanted to point out the   use of filters so let's say in this uh data with  the units I just want to see uh by region also and   I like to filter through there so if I go and take  region and put it in filters you can see up top I   can simply now drop down and I could be looking  for a certain region or zone so if I wanted to   see in central I hit okay and I just get those  re results back you can also see that you can   select multiple so if you want it more than one  or switch it back uh it's easy to add more than   one filter so or even move from different ones I  could uh take this sales rep here and move bit up   and now I can sort by region and sales rep so if  I was looking for uh let's say in the north and   I'm going to also choose a sales rep I'll choose  Pam hit okay I can see in the North she sold this   many if I was going to go back and check out a  different region uh at this point you can see   now she hasn't had got anything in the South so  I just wanted to point out uh the use of filters   but now I want to show you how you can use the  filters to create a tab for each sales in person   uh in this example so I'm going to go ahead and  deselect the items that I have here and I'm going   to build another report and remember I want a new  tab for each one for each salesperson so I do need   a total and this is going to go into the values  here I do want the item there so I can see uh   the item beside the total here and we're going to  need the sales wrap and I'm going to actually add   this to the filter so I'm just going to drag it  into the filter like this now uh something   else I want to point out is to format your  numbers here so if I go and just select in here   I can rightclick and number format so maybe you  want it in accounting uh or currency you can see   the different examples that goes through so if I  wanted two decimal places and to look like this   I'm going to go ahead and hit okay uh and you  can see how it formats it you can choose the   format that uh you would like uh the other thing  I want to show you is if I right click on this you   can sort this so maybe I wanted from smallest to  largest or smallest or some more I'll   just go largest to smallest just like this so  some things that you can do uh to it now what   I want to do is generate the individual reports  on each tab if I go ahead and drop down and go to   options right here and you can see show report  filter pages so when I select this we're going   to get show Port uh filter Pages if I go ahead  and hit okay now uh it's going to go ahead and   if I look at the bottom so if I look there's Andy  there's Dwight's there's Aaron I'm going to get an   individual report for each uh for each salesperson  and you could adjust it depending on what you want   so I wanted to make sure you understood how you  could do that uh very simply inside the pivot   table Fields so I'm just going to go back uh you  can see as I go through these different ones I'm   just going to go back to the uh original PIV table  that I was working on here and I just want to show   you a couple other things and some more tips now  let's say I was curious uh where this 23514 number   uh came from if you double click on a number so  I'm just going to double click on this number it   opens this up and shows uh how that number was  created by all of this information so that was   by uh double click it opened up a new sheet when  I did it you can see there's a sheet 16 uh beside   it now so just try double clicking on a number  and it's going to show you how it's even broken   down more now some other things I want to show you  uh with formatting and usually things can be done   in a few different ways I'm going to just show  you if I go down to values here right here and   just drop down uh you can see that there's value  field settings you can also get to Value uh field   settings if you right click on them too you can  see value field settings right here so if I go   ahead and choose uh value uh this settings here  value field settings let's say I could you know   I could say an average account uh if I was doing  a count hit okay well the dollars don't make any   sense anymore but it's just counting up uh here it  would be 15 rather than $15 so depending on what   you uh were uh trying to show in your pivot tables  remember that we have value settings and you can   change uh what you want right through here so I'm  going to leave this as sum now another thing I   want to show you when you're right clicking uh  is if you rightclick you can show values as so   I find this is a cool way to show percentage so  do we want you know try a few different ones so   if I say percentage of row total column total let  say column total I can see that the binders make   up 48% uh the pens will make up 21% uh and it  just shows you in a different way so all these   different reports can be generated and remember  you're right clicking uh to get to more of these   here so I just go back to no calculation and it's  gone so now I want to show you how you can apply   some conditional formatting to your pivot table  numbers here before we get into the graphing and   charting uh so let's say if we want this to be  like a datab bar through here so what I need   to do is go back up to the Home tab up top here  and look under conditional formatting if I   drop down uh you can see we can apply all these  different things but we're looking at the datab   bars datab bars and if I hover over notice it's  just doing the cell that I'm selected in and I   want it to be applying to all of them uh in this  case just go to more rules right here and you can   see apply rule to only that cell no we wanted  to apply to that uh a cell showing the sum of   total values here so if I select this one here  uh that will do the trick now the other thing do   you want the numbers to be shown so right now it  show both or do you want to be just the bar only   so I can go and pick a color now and it will  preview down below so maybe I pick an orange   just like that uh and I'm going to go ahead and  hit okay and now I have a data bar applying to   uh these different ones so just different way to  show uh you know show your reports that you can   add conditional formatting to them okay let's move  over to graphing or charting our pivot tables now   so let's say you want to chart your pivot table  report just to make it look a little bit better   a little bit easier for people to just take that  quick glance and know what's happening uh so I'm   just going to select inside of this here and  I'm going to go up to insert and take a look   we have pivot chart right here uh we do have the  drop down I'm just clicking on it here and right   away so we I'm it defaulted to the column I can  pick I just get an idea maybe of different ones   how it looks here uh just by going through I'm  going to keep this simple I'm going to create a   piie chart here uh you can see up top if I wanted  to see a different view I can hover over to get   the view uh to try any of these different ones  I'm going to just keep it to the pie right now   okay so now just hit okay on it and I have this  in here I can move it around I can size it uh if   I wanted to differently if you wanted it larger  here now I want to point out this is dynamic so I   can take a look at different things so if I wanted  to take the look at the sales rep here uh maybe I   wanted to compare different ones I could I select  multiple maybe I just wanted to pick one so if I   pick Michael Scott I can see just the one person's  here so it's going through finding the information   and breaking it down for me uh you can see the  items are over here uh so if I go back I can go   and select all and puts it back again so you can  sort using the chart to give those quick looks I   can dropped out here also and if I wanted to uh  maybe deselect if I just wanted two things here   so if I had paper and desk hit okay and it just  compares the two different ones so you can do all   these different things within the chart still  just like we were doing uh just with the data   before so but you can apply this uh to the Chart  so now what I wanted to show you too you can make   this customized this look if you just double click  in it if this wasn't open this format chart area   uh just double click on it and it will open it  up uh you can see I can quickly start to change   things whether I want it to be maybe a gradient  fill uh you can see how the background changes I   can adjust my colors uh down here if I want to add  different ones if I wanted a pattern fill   you can play with all these different options we  have our text options uh also so uh here so if you   wanted to change different fonts and colors you  can adjust all those things so if I go ahead and

select maybe I wanted this to be differently I can  even drop down uh so that's the chart title or if   I wanted maybe the legion here you can see how it  selects different things so I could go ahead maybe   I'll add a I can change the fonts like this if I  wanted the legend to be positioned differently I   can start to change things all around so all these  different things that you can play with uh to get   the chart looking how you want uh you can move  these inside too so if I wanted to place these   in different places again you can size your chart  differently to uh get it the way you want so I   hope you like this uh this intermediate uh pivot  tables I'm going to do more about pivot tables uh   in future lessons uh just because there's more  that you can do with this I thought this would   be a good start for you if you kind of  just maybe touch the surface to them before or   maybe haven't used them at all either so now we're  ready to move on to our next section so now I want   to show you how to use vlookup in Microsoft Excel  vlookup is a very powerful function that you can   use to look up something in a column vertically  that's where the V is going to come from so it   looks down a column that you set because it's  going to be the furthest left and then it returns   uh some item in a that corresponding row so in  this case here I already have V lookup in   this cell already created I'll create a new one  here if I just go ahead and select this cell you   can see in the formula bar it's already there but  what it's going to go through so it looks at GOC   right here and it finds it I told it to go down  the furthest left of the selection and this is   this uh vertical column here it finds GOC then I  told it to look up the uh in the in that row this   salary here so it gave me back 68,000 so let me  just change this so instead of C I'm just going   to go put I here and it returned 58,000 so it went  over to the furthest left here of the range that I   selected and it went down and it found goti right  here and it went across and found $58,000 so this   is a small data set here uh and you if this was  a large one you would probably see how much more   powerful uh this is but if you had thousand of  employees and you're looking something like this   with this formula you can simply just do those uh  searches really quickly now uh something I   would just wanted to point out here with v lookup  I was just taking a look at the X lookup function   because X lookup has a few more capabilities  to it uh with v lookup you can only look to the   right uh but X lookup you can actually look left  or right and with X lookup you can kind of do the   H lookup or V lookup together so it's something  I would recommend if you have a new newer version   of Microsoft Excel I do have a video tutorial  on that and I'll put a link in the description   down below and up and above in the card so let's  go ahead and start our vook up and build it for   this sheet and if you haven't got this sheet open  hit pause download it and follow along so we're   going to go through the vlookup function a couple  times couple different examples and two different   cells the first place we're going to put the  vlookup function is right in this cell so what   I want to have happened is it's going to look up  an employee name and bring me back an employee   designation so it needs to go over to this column  here and then look down this column here to find   uh the designation in the same uh row that it  found the employee name so we're going to start   in this one the first time we're going to go and  just go insert function up here so open up that   one insert function if you don't see V lookup just  do a quick search you don't need to type the   whole thing here I'm just going to hit go and then  double click on it so we get our four different   arguments here so the first argument is going to  be the lookup value well the lookup value is what   the name is that I type into this cell so this  is going to be the lookup cell or lookup value   in this cell right here so I'm going to click in  I6 and you can see it put I6 into there the next   is the table array so I'm going to click in this  spot and the table array is going to be this ction   and remember it's going to be the furthest left  column of that array that is going to search down   so it's going to look for a name here uh and so I  need to make sure this is the furthest left column   and it is so I'm going to select here and I need  it to go to at least go to employee designation   um it could go all the way to salary if I wanted  but it doesn't need to uh but I'm going to leave   it right there for this one now the next one is  the column index and this is how this is how   it works so in B this is the first part of the  table or range here so this is column one even   though I have a here there's nothing in it uh this  doesn't count so B is number one C is number two D   is number three e is number four and F is number  five so we told it to it's going to look down the   furthest left of my array that is this one and I  needed to look down what's the column   I needed to search through well it's going to be  d right here and that's three because it's 1 2 3   so I need to put three in here just like that uh  so the next thing what I want to do is uh type in   a true or false I could leave it blank too but in  this case I'm going to type false and that means   an exact match if I put true it's an approximate  match but I wanted to find an exact match in this   case so I'm going to type in false like so and I'm  going to hit okay and it came back notice it came   back with uh Na here uh that's because I don't  have anything typed in so let's just type in John   here and we'll type John and it returns software  engineer so went to the furthest left of the array   that I showed that I selected and then it went  to column 3 and found what was in there so that's   software engineer so if I go ahead and type brand  here it went down this column the furthest left   and then it searched column three and brought back  analyst uh so this is working quite well you can   see it doesn't take a lot to create the vlookup  function one problem is if you're using uh faults   I'll show you the problem if we go to Brand here  and I'm going to just click in here and let's say   there's some spaces just like that notice it  went to na it's because we are looking for an   exact match uh and you got to be careful not if  you're using exact match not to have spaces so as   soon as I get back I delete that again I and hit  enter you notice it works again so I just wanted   to point this out okay let's through run through  vlookup one more time so I'm going to go ahead   and recreate the top one from up here for vlookup  now the reason I'm going to do this because it's   a little different on how to count the column so  I'm going to go uh what do I want to have happen   well the V lookup is going to go into this cell so  it's I 10 and I'm going to start my function this   way this time just by putting the equal sign then  V lookup and I'm going to choose it right through   here so what is my lookup value going to be well  the lookup value is going to be whatever I put   into this so this is going to be the code and I'm  just going to go ahead click on that cell I9 and   comma what's going to be my table array on this  one well my table array this on this one is going   to go from the code up here to the salary one here  so there's going to be four different columns uh   in there and then at the next point I'm going to  put my comma what's my column index and now this   is where things have changed so remember last time  I said B was one now that the array is starting   here this is going to be one so C is 1 D is 2 e  is three f is four uh so it's wherever the array   is going to be starting on it so I just wanted  to point that out so what do I need uh this is   going to be 1 2 3 four so I have to put four for  my number on it because this is not selected in   it so I'm going to go ahead put my comma and then  put my exact match again and that should be all I   need I'm just going to finish off with the bracket  and let's try this out so if I just put in goti   goti like so and you can see that goti here was  $58,000 here so I just wanted to point out when   you were when I was doing the V lookup function  this way is making sure that you understood when   you count your columns it's wherever the array  starts uh that's going to be number one and then   you move over from there uh and then if you have  to go back and correct anything uh you can just go   back uh you can either correct it up top here or  double click into the cell and it will open up so   if you wanted to change this to true or false  you can move those back and forth or change a   different range too all right let's get ready to  move on to the next section and that's going to   be creating the multiple dependent drop-down list  in Microsoft Excel and I'm actually going to use a   vlookup function in that one too I'll add that  at the end all right let's go to the next one

so in this section I want to show you how to  create a multiped dependent dropdown list in   Microsoft Excel now there's different ways that  you can do this I actually have different videos   showing different uh functions and formulas to  create this I'll put a list of those down below   and you can check those out to see if those work  better for you in this one here today that you'll   be working through we're going to be using the  unique function and the filter function so what   we're going to do I'm just going to give you a  little demo here to start with and what it means   is when we have a dependent dropdown list what I  choose in here will influence what i cho get over   here so if I drop down here it shows the different  departments these different departments are being   pulled from the this column over here so if I  pick Finance here then my list will change here   so it will change on what I can pick so there's  only accountant in here so I'll show you if I   picked a different one if I went to uh security  and then drop down you'll see that I get head of   security or the guard so if I was picking uh guard  and then drop down I only have one person that's   listed as a guard but if I was going to go back  and pick maybe let's say marketing and then I was   picking marketing assistant and then drop  down notice that two people came up for marketing   assistant and then I could pick one of them and  then their salary comes up based on what I picked   so these were all dependent upon what I chose  before and that's what I want to show you how   to create today in Microsoft Excel so the first  step here is to be able to pull out the individual   Market names here you can see that there's two  sales here uh three sales there's uh multiple   marketing I don't want it to be written each time  I just want sales to be once and finance to be   once uh so I just want to be able to have all the  different department names so what I'm going to do   is use the unique formula to begin with and I'm  not going to put it in this spot right here I'm   actually going to just put it to the side here and  I'm going to start my uh formula here I'm going to   be putting all my uh my unique and filters down  in this row and then I'm going to hide it after   so you can't see it uh so I'm going to go equals  and then I can start typing unique you can see   as I start typing it's closer and closer here's  unique I'm going to select it all I need to do is   Select what I need to be searching and it's going  to be this right here so I'm just using a ranger   if you were using a table uh you would be able to  select that column uh by The Columns name too but   I'm just using a range so I'm actually going to  just hit enter and I get my unique uh different   department names now this is what I'm going to  be putting in Department over here and this is   where I need to use data validation and where  you find this is if you go up to the data tab   up top and then looking in the ribbon find your  data validation so I'm just going to click on it   and what we need to change it to is list so allow  list just like this and what is the list going to   be so I'm just going to click on this it's in  this spot here this is where the list is coming   from and I'm just going to hit enter and I'm going  to show you something I'm going to make a change   to this so it works better in a moment I'm going  to hit okay and watch this what happen so I drop   down notice it only says sales on this um what  I could do I could have selected everything so   it would have shown everything but what I like  to do is and I'm just going to click on uh data   validation again and open it back up if you add  a hashtag at the end it will always to how many   spaces uh is in that list that it's pulling so if  I hit okay now and go back you can see that all of   these from here are now in my drop- down list here  so this is the first step that we're going to do   so the next step that I want to have happen I want  to be able to once I choose sales then I should   be able to only look within the sales job titles  that are there so it should look down this list   and there are repeats of different ones you can  see there's sales assistant and sales assistant   and sales manager so I should it should find two  different things so I am going to be using unique   but I also need to filter it and I need to connect  it to this H2 cell so let's go ahead and start our   unique function formula over here and to do this  I'm just going to put equals and then start typing   unique and I can see it pop up but rate away I  need to uh add the filter function so if I start   uh typing filter and I need to add this and  what do I need to be looking at the array so I'm   going to be comparing uh this and I'm just going  to select the whole column here and then I'm going   to put a comma it's going to be uh comparing to  this one here but when this one equals what well   this is where I need to put the equals sign in  not the plus equals this spot here so it's going   to look down C to see when this connects to here  and then that's the end so that's one bracket we   have to add another bracket because we have uh the  the two opening ones there too so I'm just going   to hit enter and you can see since I have sales  now it brought back sales manager and a sales   assistant if I drop down and go to marketing it  brought back these two now we're going to do the   same thing what we did before we are going to put  uh the data validation up here so   I go to this spot I click on data validation and  what am I going to use well I'm going to use a   list again and I'm what's my source going to be so  I can uh click on this and I'm just going to click   in this cell here and I'm going to add the hashtag  right away hit enter and hit okay so now we should   have this marketing manager marketing assistant  it's pulling from here so if I change this to   finance and look what we have there should only  be a accountant and you can see it reflected over   here we'll make one more change to make sure it's  working security and now we should have this drop   down between head of security and guard okay let's  move to our next spot so now we want the names to   populate down here based on the guard so like if  we had this selected it should just choose the one   guard that we have the name list so how do we do  this well the names are all unique already so we   don't have to use the unique uh function in this  one we'll just use a filter and connect it back to   the uh H2 spot or H3 cell that we already have so  I'm going to go back to my J column and I'm going   to put in equals and we'll use our filter here  and what is it that we're going to be needing   well I'm going to need this column right here and  what's going to be the next one it's going to be   the job title so it's when the job title is equal  to what well equal to   whatever is in this cell right here uh so now I  can go ahead and just close that and you can see   there's the one name so let's see what happens up  here as we choose if we choose marketing and we   choose uh let's say marketing assistant we should  have two names right here uh so it's pulled the   list so now we have to use our data validation to  put it back into the drop-down list here so I'm   just going to select this cell make sure you're  under data go to uh data validation drop down   choose your list choose your Source it's just  going to be this spot here I'm going to add my   hashtag hit enter and hit okay so now I should  get those two here so we have one more step here   that we want to add uh and we want to be able to  connect their salary here and we're going to do   this actually with VL lookup sort of similar to  what we did in our last lesson now let's use our   V lookup function to finish this off by getting  the salary connected to the name so our function   is going to go in this cell right here in H7 and  we're going to start this with our equal and we're   going to look up for v l and there it is I'm going  to select it so what are we looking up well we're   going to look up what name is in this cell right  here so I'm going to select it that's my lookup   Value Place my comma what's my table array well my  table array is going to be going from uh this part   up here the beginning of the names down to the  uh end of the salary here now our next point is   our column index now remember since employee name  is the First Column this is number one so this is   the first column in the array this is one 2 3 4  so we're going to need to have the column index   number be four so I'm going to put in number four  like that and I'll put another comma and I want   it to be false because I want the ex exact match  and I'll just end this and hit enter and let's   try it out a few times so if we're dropping down  here and if I was looking in sales drop down see   the maybe I want to see sales manager and drop  down uh here and I can see Walter White gets $186,500 J we do have hide here so it didn't  delete it just hit it there so now people   won't see uh the information coming up there  and then you have your uh multiple dependent   dropdowns working right through here and then  you have the V lookup function getting that uh   salary from reading this last cell so like I  said before there's many different ways that   you can create multiple dependent uh dropdowns  in Microsoft Excel I do have other ones I like   the X lookup one too you don't have anything  to hide in sell so take a look at that one   and I have some other ones too I'll put the  links to those as I mentioned uh down below   and in the cards this brings us to the end of  this Microsoft Excel beginners course 3 hours   packed full of lots of lessons there let me  know what else you want to learn in Microsoft   Excel I always have weekly videos coming  out on Excel and many other topics thanks   for watching this time on teachers Tech I'll see  you next time with more Tech tips and tutorials