Microsoft Excel Intermediate Class: Elevate Your Skills ⬆️ 📈

hi I'm Jamie and welcome to teachers Tech and  welcome to my Microsoft Excel intermediate class   in this Microsoft Excel tutorial I want to carry  on from where we left off in The Beginner's class   and make sure that we enhance your skills  I want to go beyond the basics of Microsoft   Excel so that you can save yourself time and  effort with becoming more efficient and all   these things together will help your resume even  stand out more where you can be thinking about   career growth with these important Microsoft  Excel skills if you'd like to follow   along with today's Microsoft Excel class Take  a Look Down Below in the description I'll put a   link to this workbook we're working through today  with all the different activities so you can just   click on the link download it then open it back up  in Microsoft Excel then you'll be able to follow   along very easily also in the intro I mentioned  a beginnner class that I did earlier I'll put a   link to that too if you wanted to start from that  point and it'll be up above in the car to so you   can just click on that and check out that video  as well let's get started with today's learning on   Microsoft Excel let's start with a timesaving 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   the pattern is needed and 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  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 Aon like this m Mo 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 a lot of time I've been   going vertically but you can go horizontally on  this too so notice over here January so if I was   going to drag this way January Monday 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 rightclick up here and then go to customize  the ribbon this pops out so I am in customized   ribbon but it brings you to all the Excel options  as well I'm going to go to Advanced and just drag   this all the way down to the very bottom and  we're going to go edit custom list right here   so just click on this now I could go through and  I could type things like uh apple and I hit enter   banana I could make my list like that but rather  than even typing it out maybe you already have it   once in Excel like this if I go here and just go  and now grab the list and I can go back and just   click this again and hit import there they are  just like that so now I have 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 Lots  move over to flashfill now so now it's time to

switch the sheet so at the very bottom just make  sure you click on flashfill 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 autofi 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  Excel now I want to pull out the name the first   names from this down on this side right here and  I don't want to have to go through and type each   one Emma Noah Olivia and so on 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 when I'm looking so it's so fast   this way if I just Flash Fill and click on it if  you notice it said it didn't like that because   I didn't have the cell selected what I wanted to  Flash Fill so if I go back to it and type it again   Flash Fill 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 control 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 contr 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   then month so if I click in the cell contrl e and  it will copy it down we can also do things like   take from two cells uh so if I have this cell  and this cell here and I write Charlotte Clark   and 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 control e again and  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 Flash   Fill and that is a quick way to do this so if I  was writing 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 contr   e it does The Flash Fill the one way uh the one  reason I don't like using FLV is it's not Dynamic   so what I mean is if Ned chooses to be called  Netty 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 Ampersand 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 Ampersand  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 just typing C 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   double click in 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 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 conat 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 work 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  wave 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 nety here look which ones change 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 kind Advanced feature   and also a function with filter in it as 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 shortcut 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 control a so R 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 row autle 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 begin 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 sort 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 can 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 we 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 filter uh let's go by year so if I was looking   at years I could H 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 and  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 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 uh 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 given 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 for 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 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 want   it 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 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   to a normal range I can hit yes and notice those  filters that were automatically put across the top   are gone the formatting still exist 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   contrl 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 uh 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 number 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 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 worldwi gross became a named  range in here so worldwide grow 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 H 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 Min 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 control 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 Two   3 so remember I could filter through different  Studios like I showed you or like just normal   filter is it 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 two and Studio three come in uh then I  can even go to select Studio 3 after that so you   can do multi select to 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 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 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   multiply 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 in   here so 26 * 30 well that's not correct 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 need 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 C 5   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 clicking  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 too 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 transpose 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 to 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 B15  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 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 uh 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 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 up 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 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'd 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   d 7 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 uh 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  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 wanted 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 happened so in this  case right now so doing good this is above so look   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   we're going to create it again but we'll do some  self 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  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 this 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 the SE 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 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 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 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 happen  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   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 H go in here double  click you it looked up it said if it was greater   than whatever number this is that and less than  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 Sals are above 700   so if we're going to put this number back in here  and Below 1,200 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 n function with this one we're going to  have the nested n function and then we're going to   add a 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 in ahead and complete  this so what do I want to have happen well if what   if this right here is greater than this cell now  so above 1,200 that's currently what we have set   it up with then 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 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 end 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 drop down 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  banana 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 list 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 wanted 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 simp 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 sorry 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 dropdown list uh  you can avoid typ POS 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 control 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 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 happens 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 indirect with the named ranges it's 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 AC cross 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 to dependent 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 indirect 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 if   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 uh 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 an automatically updated   that's how you create a drop-down list using data  validation and how you can create a dependent   dropdown 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 control T 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 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 delete it 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 notice that they're all in house  star 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 ARA here and I know it's how STK  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 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 column and you can see what's happening   based on whether you choose an icon are these bar  graphs and in this one I even have a formula 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   to 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 want 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  data 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 h 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 uh color scales and we do have  our moreal 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  and 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 start 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  toiv 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 that 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 we 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 and 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 it happen here  I'm going to go 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've don't want that font to be 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   so we're in at the end of this class here today  we covered a lot of different material here uh   let me know what else you want to learn I do plan  on creating an advanced Microsoft Excel class one   coming up uh so let me know what you would like to  learn Down Below in the comments thank you so much   for watching this time on teachers Tech I'll see  you next time with more Tech tips and tutorials