We hope you guys are ready for today's session it's gonna be on named rages in excel joe was super excited to do this session uh just a quick fyi there's gonna be no exercise files for this session it's going to be a build as you go so uh once we get started just open up excel or open up excel now before we get started and uh we'll get going there uh joe how was your three day weekend for you guys uh international we in the in the states we had a three-day weekend this past weekend how was your weekend joe yeah yeah it was you know i got that should i hang out with the kiddos so.
That's always the best um and then i was teaching uh teaching in europe so i had a ferrari monday i went to bed at like midnight and then got up at like 1am and it was so fun it really was they were they're they were so amazing uh the students and it was just a great time what about you i went to hawaii i went to the island of maui um it's my favorite place to go to out in hawaii i used to live out hawaii for those who don't know um and it was my favorite island to kind of get away.
From everything when i was at work and everything like that so it was a good time to go see all my old favorite places and buy this snazzy hat yeah that's that's that's a nice yeah uh anyways let's get to our mugs um joe do you have anything today uh no i have just this cup but i do have oh god this is not sponsored but i do have my nutella and go so oh i thought it was gonna be you again no not this time i ran out of yoohoo i gotta get more youtube for that for them to send us free stuff.
Well i did get something out maui i showed this to you earlier but uh this is i got this uh hand carved wood san francisco 49er thing uh it was by done by the artist is uh jean ray castillo uh lahaina underground on instagram if you guys want to check him out i was at the maui swap meet and he had a booth there and i picked it up he had a lot of uh great stuff there so if you guys are ever in maui check out the maui swap meet and uh check out gene's booth line underground does great work um and yeah support your local artists if you can um anyways uh just some quick.
Updates on the channel uh as you guys know tomorrow is gonna be sharepoint basics advanced essentially that's the part two video of the video that we premiered last week uh it's still gonna be going over the fundamentals of sharepoint so it's not like an advanced sharepoint class i just wanna clear that up for it for everybody real quick uh and then also a reminder uh if you guys have any questions on the videos that you're watching like you want to dive deeper or you're having trouble with something you can always go to offsite offsite's our online community.
For a more advanced uh sequel video um we i just got a confirmation from christina who did the video for us last time that she's going to be doing a sql reporting services class a more robust class and it's going to be six videos long so we're going to get six more uh sequel videos for you guys they're currently in production it's going to take a little bit to get those done so i would say probably about two months then we'll have all the videos and we'll get them all up and edited and then we'll get them on a channel but yes that those will be coming again if you guys let us.
Know what you're looking for we will definitely look into getting that made also i know there's been a lot of comments for uh m code m code uh we're definitely looking at getting made that one's gonna be a little bit more difficult because we actually have to make the course and then uh then uh get it made by an instructor joe maybe you can do the m code class i'm actually looking into um you know what what we're going to be able to do like from basics and see what it's running on and things like that so all the logistics are being worked on in the background but yeah i don't want to make.
A promise of when it's coming out but just know that we are working diligently to get that out to all of you yeah and then just for upcoming videos power automate power apps they're all coming soon those are gonna be coming up but i again i don't wanna say that we're gonna for sure do it because we've adjusted our schedule a lot and everything like that just based on the videos just based on kind of like the demand and stuff like that we check a lot of trends and stuff like that to make sure that we're always you know giving you guys stuff that's very relevant to your workplace but also in the comments and everything.
Like that please let us know and then you know before we get started i want to thank everyone again for all your all your support you're watching you're liking you're describing your commenting it all helps us out tremendously uh we're having a great january so far so we want to keep it going for this whole 2022 and then without further ado joe let's get started on named ranges in excel yeah name ranges all right so i the reason why this came up last week was because i i believe i'm always forgetting i think we did power query and then last week what did we do.
And uh yeah and it was someone was asking a question about more isolating some stuff and um i can't remember off the top i had either but because the weeks it kind of like clicks like when we're writing those huge formulas out what ends up happening is we have to take into account um absolute cell reference versus relative cell reference right so those are the dollar signs that we always have to add i don't do that anymore because i've been you know really studying on these formulas and these functions and how to.
Make myself you know more efficient in excel i learned that named ranges really help
Out especially when we're using them across the entire workbook so today's plan is going to be for me to show you exactly what name ranges are and then if we have time of course i always like to do like little bonuses at the end i might try to let you see the left function it's a new function with excel so you may not have it right away but it is being rolled out and the left function um is very similar to named ranges where.We're naming some sort of variable and that name sticks within the formulas and then we can use it in other formulas uh so yeah if we have time i'll show you the let function if not i do know that we are you know releasing a video that i did do with newer features um that's coming soon and i i already have the left function in there but let's let's get right into it all right yeah and then just to update for that for that video that's gonna be a little bit later guys it's gonna be uh for that one just uh fyi we do have like.
I said we do have a ton of content that we already have recorded that we already have scheduled but anyways let's get you screen going here and i will see you on the other side joe thank you ben yeah all right so if you want to follow along you're more than welcome to and then i don't know what just happened let me see it looks like your screen oh yeah your screen bugged out for a second there all right all right there we go okay so i'm going to just zoom in a little bit and if you.
Want to follow along feel free to just you know follow along with me open up a blank excel workbook and we're going to keep it very simplistic for this example but i just want to show you how name ranges will work for you let's say for instance that i am creating a ledger right we'll just do a very simplistic ledger i'm going to call it joe's coffee shop because i'm going to have some coffee with it we'll put in some items for that coffee shop we'll put in some prices for those items um and then of course i'm gonna do.
Week one and i'm gonna do a nice little auto fill here through week four i love my autofill see how it saves you so much time and then uh we'll do a total for the items sold right so we'll we'll call it total units sold and then we will do a subtotal of how much we made i'm just going to make that all look pretty and.
There we go let's do some cell styles i don't know i'm making it up as we go i think that'll look pretty okay so now what we're going to do is we're just going to put in a couple of items here i'll just say like coffee let's do some donuts of course with that coffee maybe we'll do uh i don't know maybe a bagel uh muffin i'm getting hungry uh iced coffee.
And some tea some people don't like coffee they drink things so just a couple of random items here i'm going to just change the formatting real quick by giving that a right click format cells and i'm going to make sure that it's a currency and then click ok so now my coffee is going to be uh i don't know a dollar fifty sure uh donuts will be 50 cents i'm going by jersey prices so.
In jersey there's always that little shop that just sells like 50 donuts um bagels will say a dollar for bagel for the muffin we'll say 75 cents for the iced coffee we'll say a dollar fifty as well and then for the tea we'll save two dollars because it's really good too now we'll just make up some random numbers uh to do this usually and and this is just from a training aspect so any people that are training on here you know for my fellow trainers they're facilitators if you're creating these uh.
Worksheets for practice like i usually do for these courses a little trick that i use is it's a function called rand between so it's random between it returns a random number between two numbers that we specify so i use this and i usually put in some you know from one to a certain number but since we're talking about like selling quantities i'm gonna do a hundred between a thousand and that should give us a nice range of just fake numbers.
That we're working with now when you do this it's gonna be random so all of us are gonna get different numbers and
That's okay that's not the point of this for you to have exactly the same numbers as me have some fun with this ledger just you know it's all made up so now i'm going to just autofill and autofill now the only problem is every time you make a change to something like for instance if i change t to uh heist.T you'll see all the numbers change you see how it just from from c4 to f9 everything changes and if i go back to just writing t they change again well we don't want that to change the reason why that's happening is because it's running the function every time so a way around this just to make these numbers stick is to highlight those numbers right click copy and right click but when you paste don't paste the actual function.
Paste them as values so now there's no longer a function there and there we go now this is actually just 649 486 779 so that's just how i do my exercises how i create numbers really quick instead of thinking oh what do we have to put in there um so yeah so now that we have our random numbers here what we're going to do next is just simply use a sum function so i'm going to do equals sum and i'm going to select my four cells here and press enter.
And then of course i'm going to just auto fill that down and you might be wondering what this little green thing does right here that that simply tells you hey listen there is something wrong in your cell like they're they're looking out for you excel's like joe something's not quite right but honestly sometimes excel could be wrong so right now excel is wrong all right the reason it's giving me this error is because it's saying joe your formula omits the adjacent cell so they're.
Saying well joe what you did was you summed all these together but you forgot this number and i'm like no excel i did that on purpose because we're only looking at week one through week four so i don't like their suggestions here so to get rid of this little triangle because they're wrong get more right you're going to highlight everything click the little drop down and just ignore the error and there you go so now for the subtotal we're going to.
Just do a quick subtotal which is going to be the total units times the pricing so i'm going to do equals the prices multiplied i'm going to use my asterisks the total units sold and press enter and there we go we now have our money so here's where named ranges is going to come into play let's say for instance we are business owners so we do have to pay well taxes right so since i have to pay those taxes what i want to do is i want to create a tax.
Rate to see how much i have to pay in taxes on each of these and we'll say that we're paying like 8.7 right now to do this a lot of people will do something like this they'll put the tax rate actually in the workbook they'll say something like tax rate and then we'll put our 8.7 now i'm going to put here taxes because i want to see how much i'm paying in taxes and you'll notice that formatting came over which is so beautiful i love when excel like helps us out.
So here's where relative versus absolute cell reference comes from if i were to calculate the taxes right it's going to be equals the subtotal multiplied by the tax rate and that's of course going to give you how much you have to pay in taxes 211 dollars the problem with this is if we try to get this done really quickly it's going to give us a bunch of errors and the reason for this is because of absolute cell reference.
See when we do autofill it's relative which means over here when i did my autofill it did b4 times g4 b5 times g5 and of course you can guess this is going to be b6 times g6 right because it's relative to the previous so it's just following a pattern here it doesn't work in our favor because it goes h4 times i1 right h5 times i2 no we didn't want that we don't want the i1 to move so what people do is they put a dollar sign.
Right in front of the i a dollar sign in front of the one so that it locks that value so that it never moves now of course you can do this but i find that really annoying especially when i'm working on huge spreadsheets where i have to constantly remind myself put the dollar sign put the dollar sign instead there is a good trick where we don't have to put the dollar sign and i'm going to show that to you right now it's called a named range so let's say right here i want to give this a name this 8.70.
I can actually name whatever i want for instance if i scroll down a little bit and i calculate from d12 i know i'm on d12 and i'm going to select d12 d13 and d14 so these three cells i'm going to change the name of them it's no longer going to be d12 d13 d14 selected i'm gonna actually call it joe right we could do that we can call it joe and to do this there's two ways you can either come to the name box over here and you could just type in the name joe.
And then press enter or whatever you want you can take pancake right whatever you want or you can go over to the formulas tab and in the formulas tab this is where you will find named ranges where you can define a name so either way you can use that or you can use this or if it's a huge selection you can use create from selection and i'll show you how to do this in a moment so right now i have these three cells.
Selected and i'm gonna call these three cells i'm gonna come to my name box i'm gonna call it yo and you don't have to write my name you can write your name and press enter to watch now when i click on one of them that's d12 that's the 13 and that's d14 but when i select them all it's gel how cool is that so now i can start to create this and this actually absolute references it automatically because forever these three cells will be known as gel so now when i come over.
Here and i'm like i wonder what's the sum right of joe i can actually write jo and look it already knows that it's those three cells so now i'm just saying what's the sum of job zero okay what happens if i put a one in there a two and a three the sum of joe's sex because it's referring to these three so we can use these in a great way like for our tax rate we could just come up here instead of calling it i1 let's call it.
Tax so now forever this i1 is now known as tax so when i use it in my formula i can do equals i could do the subtotal multiplied by the word tax press enter and then just autofill it works perfectly because now it's using absolute reference it's times tax it's times tax it's times tax and tax will forever mean i won.
So those names ranges are really important i use them all the time and i'm going to show you another example i'm just going to have you just look at my screen and watch this part because it's you know i just want to show you how realistic this is when you're using it in a real life situation so i'm going to open up one of my let's do yeah let's do this one i have so many work files for excel.
So let's say for instance i'm going to do this so i have this pair company quarter one expenses i have the division the category all the money i made from january to march quarter one and then the total expenses and what i want to do is i want to do a lookup which means that whenever i write something in here i want to look it up here all right so we can do this but it's going to be pretty annoying to.
Do because we're going to have to do absolute reference and we're going to have to keep selecting our stuff so for this one let's say i want to look up the total expense by category i'm going to use what's known as the sumif function so i'm going to sum it up if it says software i'm going to sum it up if it says telephone if it says copying whatever it says here i'm going to sum that up so i'm going to zoom in a little bit here so you can see this i'm going to do equals.
Sum if press tab on my keyboard to open that parenthesis and it's going to ask me for the range the criteria and what i want to sum up so the range is going to be where are you looking for the thing right so i'm going to be looking for it in the categories then it's going to ask me joe that's the range right there uh joe what are you looking for in the criteria so what is it that you're looking for well i'm looking for whatever's in here the word software in this list and then when you find it what do you.
Want to sum up i'm going to say well i want to sum up the total expenses so what this function is doing it's saying okay uh where are you software okay 5875 and then it goes down to the list the next time it sees software it's going to add that up so really cool function here the problem is is i'm gonna have to now select and click and drag all the way down so for my range i'm gonna have to go like this.
I know pretty annoying right and how many of you have ever like gone past you're like oops i didn't mean to do that right so i don't like doing this instead what i can do is i can name my ranges and all i would have to write is the word category so i'm going to show you how to do this the first thing you're going to do is select your data i'm going to use ctrl a i'm then going to go to the formulas tab here i'm going to go to the define names now.
Since i want to define multiple names like i want this entire column to be called division i want this entire column to be category january february and so on and so forth i can actually use create from selection so when i click create from selection it's going to say okay joe we're going to make your names ranges for you but what do you want to call them where where do you want to get the values for the names and well they're up top in my top row here so i'm going to select top brow and click ok now watch what happens if i select from.
C5 all the way down it's called look in the name box category how cool is that and if i select from b5 all the way down it should say division right so now what we're going to do is we're going to use these in our actual formula we're going to do equal sum if the range is going to be well category so i'm going to type in category look at that how cool and it's selecting the whole column.
My criteria is going to be whatever's in here right now it says software and what i want back is total expenses how awesome now when i press enter it gives me the total expenses and if i put in my telephone it tells me my total expenses how much should i spend in supplies my total expenses and now i can use these named ranges whenever i want right i can go over here i want to look up the average so this time instead of a sum if.
We're going to do an average if same exact principle i'm going to do equals average if and what is the range well it's whatever's in the category column so i just have to write category i don't have to select anything what is my criteria well whatever's here and my i uh i believe i20 so i'm just going to select i 20.
And then what is my average well i want the total expenses press enter and there we go and to take it even a step further with something like this just a couple other tricks that i would do you see how i have to write like supplies and then i want to see this i'm going to say supplies right that's kind of annoying to do so i'm just going to simply say that i want these to be the same i'm going to click here and say that.
This is equal to whatever this says and press enter so now when i type in rent types and run at the bottom too pretty cool taking it even one step further i'm tired of having to click and write stuff like telephone right i'm not a good speller so when i do this it takes a long time i can use data validation which we've gone over on this channel before but if you like what you see you know i could do another one for you next week but data.
Validation allows me to create lists inside of cells so i could just simply create a list here and go over to my data tab i'm going to go over here to my data validation you'll see a little check mark in the negative sign here and then i can say that i want to list inside of the cell and the list is going to be all of our different categories now you don't want to select all of them because then it will actually create a list that has technical support two.
Times only just select you know each unique one and click okay and now i have a little drop down list how much are we making copying how much are we making software how much do we make in telemarketing how awesome is that right so just by using those named ranges it's a lot easier to then use them in our formulas uh if i want to do two lookups i can do equal this one it's going to be some ifs with an s because there's multiple criterias.