Excel Formulas - Basics and Beyond | Thomas Fragale | Skillshare

# Excel Formulas - Basics and Beyond

Play Speed
• 0.5x
• 1x (Normal)
• 1.25x
• 1.5x
• 2x
3 Lessons (51m)

13:08

19:08
• ### 3. Excel formula basics Part 3

18:51
• --
• Beginner level
• Intermediate level
• Advanced level
• All levels
• Beg/Int level
• Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

49

Students

--

Projects

## About This Class

Hello. These 3 videos will show you what you need to know about building basic Excel formulas, and then they will go go beyond the basics. Topics include:

• Getting started with formulas
• Doing Math in Excel, including the mathematical order
• Autosum
• Copying formulas
• Relative Formulas
• Absolute Formulas
• Introduction to built-in formulas, AKA Functions
• Linking formulas from one sheet to another
• IF Functions
• Vlookup Function

## Meet Your Teacher

#### Thomas Fragale

Microsoft Certified Trainer - 2152801073

Teacher

## Class Ratings

Expectations Met?
• Exceeded!
0%
• Yes
0%
• Somewhat
0%
• Not really
0%
##### Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

# Your creative journey starts here.

• Unlimited access to every class
• Supportive online creative community
• Learn offline with Skillshare’s app

## Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

## Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best. ## Transcripts

1. Excel formula basics: in this video we're gonna talk about, have to do basic math formulas in Microsoft. Excel math and formas is a very important part of Michael about Microsoft Excel. So you want to make sure that you have this master. So let's take a look at rule number one Rule number one. All formers start with an equal sign. So I'm gonna go ahead and click on that blank cell and I'll type in the equal sign and I'll type in 34 plus 98. So if we take a closer look at that the former this is equals 34 plus 90 it I'm gonna go ahead and hit the enter key. Now you can see how the result of the formula shows up in the actual cell and the formula shows up in the form of the bar over here, as we can see, let's try a subtraction so I'll type of equals 98 minus 43 again. We'll take a closer look and this is equals 98 miles. 43. That's how I always start with the equal sign. And there's not gonna be any spaces in your formulas. In most cases, when I hit the enter key. That's gonna do the math for me. So rule number one all former star with the equal sign. Now let's take a look at Rule number two. After you type in the equal sign, you can either type in the number you want, or you can click on this cell on the spreadsheet that contains the number. So let's try that out. I'm gonna try to add those two numbers together so I'll see equals 1 32 plus 55. And that will certainly work. When I hit the enter key, you can see it's gonna do the math for May. And, of course, when I click on that cell, the form that is up here in the form of the bar. But here's a better way. I'm gonna type in the equal sign. And instead of typing in the number 1 32 I'm gonna click on the cell that contains the 1 32 right there, which happens to be Cell C two and I'll type in the plus sign and then click on the cell that continues to 55 which happens to be cell C three. So now the formula says equals C two plus C three, which means use the numbers in those cells. When I hit the enter key, I'm still going to get the 1 87 But here's the difference. That formula is pointing to those cells, so if those cells change the form that will be had. Quite so. In other words, I'm gonna come over here and I'll type in 63. Now when I do that, this number right there is going to stay constant because the numbers are hard occurred it . This number down here should recalculate, and you can see that it did now, says 1 95 So it re calculated because it's pointing to Cell C two and C three when C three or C to changed than the formal. Also re calculate it. So take a look at rule number two. After you type in the equal sign, you can either type in the number that you want, or you can click in this cell on the spreadsheet that contains that number. That actually is the better way when you click in the cell so that when the cell changes, it will recalculate. So let's take a look at rule number three. You're gonna tie things together with mathematical operators and the formula filed a mathematical order. Now I put I put these in the mathematical order over here. So let's see some examples of some of these expressions. Sometimes you might use Prentice's. And I'm gonna show you a good example of The Apprentice use in just a second. If we look over here, says equals open parentheses, four plus five close parentheses, times six. So because of the parentheses, it'll do that plus sign first. So the four plus five equals nine times six is gonna give you 54 you can see the result will be 54 now. Sometimes you might have to use an exponents, and exponents is going to this little up arrow and you get that we're doing a shift of six on your keyboard so they might say something like Equals two up arrow for I believe that's called a carrot and then you can see it took to the fourth power. So perhaps you might have to do exponents and use that little carrot the way you get that is, but doing a shift six on your keyboard Multiplication is going to be the asterisk. So in this case, I have equals seven asterisk eight, which means seven times eight and you can see that's going to give us 2 56 Division is gonna be the slash. Now, this is the slash This on the question mark key of your keyboard. So in this case, it says equals 99 slash 11 and you can see the real results gonna be nine here. Obviously, the plus sign is going to for addition. Here's an example like time and obviously the subtraction will be the minus sign. And here's an example of that. Now let's see what happens when we have more than one mathematical operator in the expression. So I'm gonna say equals for plus five times six. Now you see, it's gonna follow the mathematical order, which I have listed over here. So it's actually going to the multiplication first, five times six is 30 and then come back and add before, as you can see, it's gonna give me 34. Let's take a close look at that formula. I'm going to go up to the form of the bar Over here equals five plus sex so because of the mathematical order, it did the multiplication first. And then it came back and added before, and that's what we got. The 34. Now, the way I control that is I can use to parentheses. So now I'm gonna double click on that cell that she used that sell. You could either change the cell, appear on the form of the bar, or another way to change a cell is to double click on it. So what? DoubleClick right back. And now I'm gonna put in open parentheses before the four and up close parentheses after the five. Now, because of the parentheses, it's going to force the addition to go first. And now we have a different result that we have the 54. So your math, your math, is gonna file the mathematical order, and the way you can control it is you put parentheses around the part that has to go first . And of course, it can get much, much more complicated than that. But it's basically gonna follow the mathematical order if you um if you put something in parentheses in that part of the form that is going to go first if you have more than one set of parentheses, then it will take the parentheses from left to right. However, sometimes there's parentheses inside of other parentheses. And if there's parentheses inside of other parentheses, and it will take the inner ones first and then work its way out and then going across it will still do the print that she's left to right. All right, so let's put all that together. All formers will always start with an equal sign. Then, after you type in the equal sign, you can either type of the number that you want or you can click on the cell on the spreadsheet that continues, that the number that you want and you're gonna tie things together with your mathematical operators, and it will follow the mathematical order Now, when I learned this a long time ago, and maybe you learned this this way as well, they gave you a way to remember the mathematical order. If you look and column I. I wrote down the first center of each of those words p for parentheses. Be for exponents em for a multiplication D for division A for addition and s for subtraction. So they gave you a new monitor phrase to help you remember the mathematical order. Maybe this rings a bell for you. Please excuse my tear. And Sally knows how it starts with those same letters in column I, which were the same letters that these words and Colin F start with. So it's just a pneumonic device. Please excuse my dear. And Sally, So what possibly could? And so we have to do with Excel. Well, it's going to help you remember the mathematical order. Okay, So when you have a longer mathematical expression, it's gonna follow the mathematical order, which I have listed right here. And also, the way you can control it is you're gonna put parentheses around the part that you want to go first, like in this example over here in south G three. So you might want to print of screen out and, you know, have the spire side that is your starting to build your formula. So let's try another example. In this case, I say equal seven plus nine times a divided by three minus six. Now, what's gonna happen here the way it is now? It's going to the multiplication first, then it'll did the division. Then we'll do the plus. And then it will do the minus. So I don't know what the answer is gonna bay. All right, so it's just 25. So let's take a look at that form of them. I'm gonna double click on that cell again. So let's break it down. Nine times eight, we're going to get 72 divided by three, right? And then house, I forget to the method. So then we'll do Set. That's going 24. Plus the seven is going to be 31 minus 26 And now I can see how they got to be the 25. And I know not Make sense to me. Now I'm gonna double clicking that formula again. What if I wanted the plus toe happen first? There. Now, this is gonna make a much bigger number, I'm sure. So I'm gonna go ahead and, um, do the parentheses around the seven and the close parentheses after the nine. Now, I'm sure it's gonna do a much bigger number because it's going to seven plus nine, which is 16 times Teoh. Yes. See, now I got to be 36 instead of being 25. So those parentheses really do make a difference? Let's take a look at that. And in this case, it actually did the addition first because of the apprentices. So we got 16 times a divided by three, minus six, and therefore it became a much bigger number, as we can see. So the Prentice's are going to make a difference. Everybody now, in this case, what if I want it to the what if I want this attraction to happen next? Then we put the parentheses over here around the three and around the sex. Then it's actually going to the addition first. Then it will do this attraction than it will do the multiplication. And then it will do the division. And you could see now we have even a different number. All right, so hopefully you got some ideas of how to work with your formulas, but just about every formula will fall. This this algorithm, where you always start with the equal sign after you type in the equal sign, then you can either type in the number that you want or you can click in the cell on the spreadsheet that continues the number that you went and then things will be tied together with your mathematical operators. Now percent, we'll handle percents in other videos. But here's some quick ways to do percent. I'm gonna type in 400,000 and I would like to get 10% of that number. Well, there's two quick ways I can do that. I c equals 0.1 point one is gonna be equal to 10%. Then I said times or the asterisk, and I'm clicking at 400 which is really salty 10. And we get the number 40. So once and it could Dio is, you can say equals 0.1 times you 10 and that means 10%. And when I hit the enter key, I get the number 40 there. Another way to do 10% is just to use the percent side. So he also equals and percent times the 400. So take a closer look at that formula. Announces equals 10% times e 10 and that's also going to give you the number 40. As we can say now, if I changed that right number right there, then you can see I'll change it to 900 and that cheese is melted, his numbers. So I'll see you in the next episode, and we'll start to go further with our mathematical formulas here in Microsoft Excel. 2. Excel formula basics Part 2: Now let's start to do the math in real life application. So I'm gonna go down to Ah, sheet 2014 down here at the bottom. Good. Now, all of this information, I would have just typed in. Normally, as we're looking at this spreadsheet, there's no formulas yet Everything was just type then and then here we just have a column of numbers. Now, one thing I want to show you that's very popular in excels called the owner some. So I'm gonna go ahead and click on that cell, and clearly that's where I want my total toe appear. Then we're gonna, um, pick on the home menu of top and then on the home menu. Gonna come way over here and we'll Pippen this icon. It's called Auto Some. So when I click there, typically it puts the proper formula in their formula. And in this case, it did. It says equals some C four through suit 10. Now, when I see that Colin right there, that represents a range of cells. So that way you read, that is some see, 4th 1st you 10. I'm gonna hit the enter key here, and it's going to get me now whenever we see the pound signs in the Excel spreadsheet or these days in my pickled hashtags or pound sides, right, That simply means you have to make that column bigger. Watch how I'm gonna do that. So I'm gonna go ahead and movement mass between that Air Sea and other D c my mouse between never see another D And then I'm just going to double click on that black cross when we double click. That makes the column bigger, and then you can see we have the total. So let's do the auto some again. I'm gonna click on that cell and delete it with my delete K. So now I'll pick on the home menu of Top will come way over here on the right hand side, and we'll pick on the word Odo. Some right there and now knows what happens. They usually puts the proper formula in there for me, not all the time, but many times it does. When I hit the enter key, then you could see as the proper form of in there. If I look in my form of the bar, then there's the former. I wish I could change if I wanted Teoh, and then I could see how the number is in my spreadsheet. Now, if I change one of those numbers, let's say we're gonna change that to 30,000. Watch the bottom total. It is going to change because the formula refers to Cell C Fourth Bruce, you 10. So if any of those change, then the former Recalculates. So the auto some is really gonna be, Ah, great addition. So it's way to add up a column or at open no ah, row of numbers. Now next, I'm going to show you how to do a speed fill with a formula, and this is going to talk about something that's called the relative references. So these will be relative formulas. So at this point, let's say February is gonna be a 10% increase over January. Mathematically, if I take any number and multiply it by 1.1, then it'll give us a 10% increase. So I'm gonna start the form with an equal sign, as always. Now I'm gonna click on that 30,000 remember, from Rule number two and it says cell C four, and then I'll say times which is the asterisk 1.1. So that means take the number that's in cell C for multiplied by 1.1, and you could see it gives us to result there 33,000. If you think about it, 10% of the 30,000 would be 3000. But when I add that back onto the 30,000 we get 33,000. That's very a 10% increase. Now, if we take a look at the formative there, I just double click that sell. It says C four times 1.1. Well, I want to go ahead and copy that right down the column. So watch what Will Dio. We're gonna move to that cell, and we're going to get the Black Cross right in the corner. You have to be right on the corner. The black crosses called the Speed Phil, or the auto fell. Now I'm gonna go in and try Get down to the rest of the numbers in that column and let's see what happened. Each of those numbers is really a 10% increase over the previous number and column C so, but let's take a look at the form of the More importantly, I'm gonna double click on this cell and the former Mrs C four times 1.1. That's the one we tied them. I'm gonna double click on this cell. By the way, of course, the former There's also showing up in the form of the bar, right? But if you double click on the on the cell, you see the former there also. Now, this one says C five times 1.1. So it made that adjustment. For me. That's called a relative formula because it changes relative to the cell. Try again. I'm gonna double click on this cell, and this is C six times 1.1. I'm sure the next one says C seven and so on. So I was able to type in the form of one time appear C four times, 1.1. Then when I got the Black Cross and dragged it down, it made those adjustments for me automatically. That's called a relative formula, because it changes relative to the do sell that's going to see a lot of time in most people's larger spreadsheets. It's hardly ever the case where you type in the former's one by one. Whenever they can. They do that, they type in the top one and then track it down or drag it to the right, and then it fills in those things. For me, that's called a relative formula. Let's see another example of that. Let's say March is gonna be a 10% accuse over February, and April is going to be a 10% increase over March and so on. So this is what I'll go. I'm gonna hide it theirselves with the White Cross, see the White Cross in the middle. Then we'll get the Black Cross in the corner and I'm gonna drag it over to the July column . Now Each of those numbers is a 10% increase over the previous number. But let's he would happen to the formula. This one says C four times 1.1 As to when I typed in. Originally, this one says D four times 1.1. This one says E four times 1.1 knows how it's always the cell to the left of the current cell. That's called a relative formula. So I was. I was able to type in one time that I could drive that formula down with the Black Cross that we could drag it across, and very quickly our spreadsheet is filling in eso. Um, that happens because of the relative formula. Really helped you build your spreadsheet a lot quicker. Now, one should learn that technique gonna use it over and over again. The way we did this turtle a few minutes ago, we did the order some. So that one says some C four through C 10. So I'm just going to click on that cell. We'll get the black cross in the corner and will drag it over. And now each of those totals is there. So we see the pound signs for the Excel spreadsheet. Now, in this case, gonna make all those columns bigger at the same time. So ah, pick on that R D and ah hah! Away from that I d over to the June column for the to the July column. So they're all selected. And then I'll get the black cross between one of those calls and double click. And now that should have made the columns bigger. And you could see that it did so. The first total said something. C four through C 10. The second total says some D four through d 10 That made that adjustment form. That's called Evra Relative formula. And the next one, I'm sure, says some E four through 10. So that was a really important part. I want to zoom back out for you so you could see the bigger picture. Now let's do another order. Some over here. I would like to get the road total. So I'm gonna pick on the cell where I want my total toe a PR and then we're going to use that same auto Some right there knows this time it says some C four through I for so usually I guess is the right thing for you not all the time, but many times it does. When I hit the enter key, then it was ableto get the road total for that row and then I'm gonna get the black cross again. It's there and then I'll drag it down. I'll give a drag it down to Row 11 and now I even have a grand total there as well. This one is zero. Because if you think about it, there's nothing in that row. So when I see houses, some see 10 through I 10. So whenever I need to see that zero, I can delete that with my delete key. So now we have a grand total as well. So typically, we can get our column totals and our road totals with the auto some that's under the home menu. As we can say Now we have a pretty decent looking spreadsheet. Now I'm seeing that I don't have a total for the July column, so I'm just going to click in that cell and just drag it over with the Black Cross. And now we have a total there as well. Of course, I met the column bigger again. Anytime that receded pound signs, it simply means we have to make that column bigger. And now we're looking pretty good. So so far, we did the relative formulas. Then we did the auto Some. These are things we're going to use all the time to help you build your spreadsheet, so you might want to watch the video over again. Toc these techniques until you go, you can get it down now. In this case, I want to show you something that's called an absolute reference. You see, for the percent of total, it's gonna be the total for that row divided by the grand total. And I want all those two divided by that saying Grand total. So first I'm going to do it. The foreman of the Incorrect Way. We'll see why it's incorrect, and then we'll do it the right way. So when you zoom in for you, okay, so I want to say equals this cell, which is Sell J for Divided by the Grand Total, which is J 11. So says equals J four, divided by J 11. And when I hit the Enter key now I had that self four minute like a percent, so it comes up is 24.19%. You would think that I would just be able to try that down, So I'm gonna go ahead and get the black cross in that cell and we'll drag it down to the rest of the wrist. Knows how, says Division by zero. So let's see what happened. This is the important part, this one, said Jay, for divided by J 11. That's when I type them, but because we did a relative reference because they're still relative formulas. This is, says J five divided by J. 12. As you can see, there's nothing in J 12. That's why is his division by zero. But I don't want the vibe i j. 12 there. I still want to divide by J 11. This one is even getting further away. Now it says J six divided by J 13. You can see there's nothing in J 13 and that's why it says division by zero. But again, I don't want that to say Jenny 13 there. I still want to say j 11. So here's what we're gonna dio. We're gonna make this form of it into what we call an absolute reference. And this is when we use the dollar sign in the formula. So let me show you an example. I'm gonna double click on that. Of course, I could be changed these up in the form of the bar as well. So here I'm gonna click before the J, and I'll type in a dollar sign there, and I'll click before the 11 and I'll type in a dollar sign there when we see the dollar signs in the formula like that. We're not talking about money. What that means is it's gonna be what we call in absolute reference. So when I copied it down, that part of the final will not change. It will still say J 11 Now, you could put the dollars on just before the letter. That means the column will stay the same, but the road could change. You could put the dogs on just before the road number, and that means the road number will stay the same. But the column could change the way I have it. Where the dollar sign is before the letter and the number. That means it's always gonna be that exact same cell. So we're gonna hit the enter key now. It's not gonna make a difference until we re copy that down. So I'm gonna get the Black Cross and drag it down. Now, all of a sudden, they have real numbers. Remember, I have that column four minute, like a percent. If yours might come up as a decimal place that you just form it, this has percent. Now, I'm gonna go ahead and double click on that cell. Now, this one says J five, but it's still dividing it by J 11 Because of the dollar signs, we call it an absolute reference. This one says J six, but it's still dividing it by J 11. So the first part, that formula is relative this and said J four and then J five and then j six. I'm sure this one says J seven, but they all are dividing it by J 11 Because of the dollar signs, we call that an absolute reference. Let's see another example of that. Now here I let the calculate the sales tax. Now I'm recording this from Philadelphia, Pennsylvania, in the United States. And actually our tax rate is very high. So the tax rate in Philadelphia, Pennsylvania, where I live, there's 8%. Can you believe that? 8%. So let's see how we're going to calculate the sales tax over here. We're going to start a formal, of course, with the equal sign, as always, and I'm gonna click on the total for that row, which happens to be sell J for, and I'll type in the asterisk for a multiplication. Now I want to click in the 8% which is really sell em to now as you're building your form of them. If you know that, that so is going to be the absolute reference. Here's a keyboard shortcut. You can hit the F four function key on your keyboard, so I'm gonna hit four and knows how. That puts the dollar signs in there for you so you could type in the dollar signs. But a quicker way is to hit the F four. And you would do that on this cell in the former that you want to make into an absolute reference. Now let's see what happened. If we take a look at the four minutes is J four times dollar sign em dollar sign to now. What's gonna happen is I'm going to copy that down and I'm gonna go ahead and get the black cross and drag it down. And now let's see what happens. This one now says J five times dollar sign em dollar sign to this one, says J six times m two. So the first part that formula is relative. Assure the next one says j seven, but they all are pointing to em two because of the dollar signs. So Let's say that tax rate changes. I'm gonna change it to say that's a 4% and then watch that column with the sales tax. And that is how they all changed because they're all pointing to that cell and I'll go back to 8% now and now you can see how they went back to the higher numbers. So those airways, we can use the absolute absolute reference here in Microsoft Excel. That's called an absolute reference for the dollars. Now the next thing I want to show you here is another way to use the auto. Some over here I wanna type of your average and just be able to type that in and go to that cell. Now there's lots of ways to calculate the average, but here's what one that's built right in. We'll go back under the home menu and on the right side. This time we're going to click on the pull down where this is older, some and then you can see there's other calculations that you might want to use. Now I'm gonna have another video in the future. They'll go into more of these functions, but in this case I just pick on the average. Now, when I picked the average this time it did not pick the proper range. See how it wants to go from C for toe l four. Well, what you're gonna do is you're gonna highlight what you want to take the average from, so I'm gonna highlight from C for over two i for I'm just dragging it over when I hit the enter key. Now I have the average for that cell. As you can say so let's take a look at the formalist is equal. See, for me equals average C four through I for So let's see how we did that again. I'm gonna go and click on that cell and I'll delete it so I could do it over again. So on the home menu, when the right side will quicken the pull downwards his auto some and we'll pick on average now, this time it did not get the proper range for you Other. Sometimes the order some does. So what you'll do is your highlight what you want to take the average from. So I'm gonna highlight from C for over to the dry column and then we'll hit the enter key. And now I have the average for that row. Of course. And you could see the former Mrs equals average C for through I. For now, what we'll do is we're gonna copy that down. And now that we have the average for the rest of the Vryzas Well, now those all changed. Now it's a C five, the i five. This one says average seasick. Step I six, those cheese. And I wanted those to change. So I did not include the dark side in the form of this just regular relative references. 3. Excel formula basics Part 3: Now, the next thing I want to show you is how you can do Ah, range, name, and use a range name in the form of a swell. He's come up quite a bit. So I'm gonna go ahead and click on that 8%. Now, you can either pick one cell or you can have it multiple cells. First of all, it means him out for a second. So I clicked on that one cell. Now follow my mouse, and I'm gonna click on the the name bar up here, and I consume back in and over there. You type in whatever name that you wanted, Teoh. Except the name doesn't like spaces, for whatever reason. So I'm gonna call that tax rate with no spaces. Now, you could use an underscore there instead of, um, but just don't use the space. You could use an underscore if you wanted to. So now hit the enter key. So now that cell that we were on, So m two now has the name of tax rate so we can use that in the formula. So I'm just going to have another sales tax column here and watch how will use that name when it started for me with the equal sign, of course, and I'll click on the road total for that red, which is J four, and I'll say Times tax rate now knows when you start to type it in, it will show up in this left, so you can either type it in or you can double click that and in those with that tax rate, refers to it as you can see his point that 8%. So I should. You had a name, a cell. And then I showed you how to use that cell in a foreman on those houses. Jay four times the tax rate. Now, one benefit of using that is it might be easier to remember the name of the cell. The is to remember the cell reference, especially if that sells way off to the side somewhere. Another benefit is when I copied that down, I'm just gonna get the black cross and drag it down. Each one of those says there were a tax rate in that part of the formula, as you can see, so it's like using an absolute reference without having to use the dollar sense so that was a nice way to do that. So I should you have to use have the name. A cell is the name box that also could have been more than one cell, by the way, then I should have used the name in a formula. And then once you have that, if you copied down to the rest of the column the rest of those will use that same name. So it's like using an absolute reference without the dollar signs. Many people use those those range names. Now, if I'm gonna form of the and maybe this is the first time I'm looking at this, I want to know what the word tax rate refers, toe. So here's what you would do from there. You're gonna go back to the name box over here on the right, skipping over here on the left, and I want to click in that pull down, and then the range names would show up right there. I'm gonna put under our tax rate, and then you see, it points to the cell that the tax rate is naming. So if you ever see these form of this like that and you're like. OK, well, what this tax rate mean, you go back to the name box, and then when you click on that, I'm gonna zoom out for a second so we could see the bigger picture. I'm gonna go ahead and, um, zoom out. So I'm gonna click in the pull down for the name box. We'll pick on tax rate, and then you could see how it points to that cell. Now, when you have a lot of range names, let me show you where we're going to go. We'll pick on the form of this menu and then under formulas, I'm gonna come over here and took on the name manager, and then you can see that would be a list of names that would be associated with that website with the page. I mean, and then you can list them. You can make a new one, you can change them, or you can beat them. So when you have a lot of range names, you might want to use the name manager. So you get that is, you pick on the formulas menu and then you come over here. If you pick on the manager. Now, let me show you another way to associate the names. We can use the dames there on the left side of the screen or in the top row. So I'm gonna highlight from the word January all the way over to I nine. And that is how the first show that I have highly is in fact, the column headings. We can use those as range names by doing the following. I wanna pick on the former this menu. Then I want to come over here and I'll say, create from selection means create from the selected data. So clearly my name's air on the top row and I've seen it before in the Left column. Now I don't remember seeing the names too much in the bottom row or in the right column, but I'm sure that can happen. So I want to pick on top row that my name's Aaron. The top row. Let's see what happens when I click. OK, now, if I click in that name box now, all of those names are there, or if I pick in the form of this menu and then the manager now all of those range names are there, so let me show you how you would use this. If I come over here, I could say equal some just type of January, and it will know what that IHS question just gave it a name. All right, So I showed you how to name your cells with the name box. Also shows you haven't need the cells by selecting them and say create from selection. Why should you have to use the names in the formulas? And then, you know, you can use the main box to go back to those names. If I put on the road tax rate knows how once again it points to sell em to. And then there's also the name manager. Those range names do come up quite a bit. I wanted to talk about that. Now, um, I'm gonna show you how to link your sheets together. By the way, I'm gonna get rid of this form of it right here, just going to click on it and then to lead that I was just an example. So knows how I have she 2014 and down here I have she 2015. Well, on the 2015 feet she I would like to see the totals for 2014. Let me show you two ways. Toe link your sheets together. So that means if I cheese, 14 4050 will also change at the same time. So now I'm back to she 2014. And I'm gonna highlight all of these numbers, and I'll just do a normal copy. So there's numerous ways to do a copy. Of course, I want to highlight those cells and we'll do our copy, and this case will pick on copy. Now, I'm gonna go over to shed 2015 and right on this cell. All right, click and we'll do what we call a paste special. Now I'm gonna click on the word pay special. These icons did the same kind of thing, is the next screen that we're going to see. But I'm just usedto this screen, so I'm gonna pick on the word pay special. And then over here they have a choice. Is called paste link. Ah, pecan paste link and knows how we have the same numbers from she 2014. But more importantly, look at the formula. It says 2014 Exclamation point c 11. Let's zoom in on that, says Best. A sheet name 2014. The extra. The exclamation point separates the sheep name from the cell. So that really means if cell C 11 changes on sheet 2014 then this will change as well. They're linked together because we did a copy from one sheet. Then we came over here and we said Pay special and then paste link. So let's go back to shoot that 2014. Right now she could see it says 124,000. Now, if you have those blinking cells like Ideo, I'm gonna go ahead, hit the escape key and put those away. Now I want to change one of these numbers. Change that to say 25,000 and knows how the bottom total change. I'll change that number to say 24,000. And the bottom number says 138,000 and now I go over to she 2015 knows how change there as well. So one way to link your sheets together is to do a copy from one sheet and you go to the other sheet and it's a paste special and then paste link. By the way, that'll work between two different workbooks. I can copy from work one workbook, go to a completely different file and say paste special and then paste link. So if I change the one workbook, the second will change. Even if the 2nd 1 isn't even open. The next time you open it up, it'll have the newest results because he said paste special and then paste link. By the way, I see that that cell has the pound signs. Of course, I'm just gonna make that column bigger. So go up to that between those two palms, get the black cross and double click. A lot of times when I see that I try to fix it right away because otherwise help. Forget about it, quite frankly. All right, so I showed you how the liqueur sheets together. That was one way. Here's another way. Now I'm gonna start the formula on she 2015. And as I'm building the former that I'm gonna point to a cell on she 2014. So I'm gonna type in the equal sign. No. So how much she 2015. As I'm building the formula, I'll go to my other sheet 2014 and I'll pick on the grand total, which is right here. Now. I can keep on doing more math for that assed, much as you want a toe, but that is really using that cell on the other sheet. That's how the form that is putting over here. So if I hit the enter key right now, it's gonna go back to she 2015 which is where I started the formula and you can see that has the grand total from she 2015. I mean, 2014. So what I did there was I started to form the with the equal sign on she 2015. Then, as I was building my formula, I clicked on the cells on the other sheet. So that's another way to link your sheets together. You can either copy and paste. I mean copy and pay special and then paste link. Or you can start to form that on one sheet and azure that the U format. You click on cells on another sheet, and there's a great ways to link your sheets together. I'd like to talk about with these with these spreadsheets and the formulas. If I go back to she 2014 those house says January, February March going across there. Well, I'm sure when I originally built that, here's what I did. I'm gonna delete those for a second. I'm gonna click in their January and get the black cross in the corner and then just drag it over and we call that the speed fell and knows how it works with the month knees, Right? Nicely what you get? I'm gonna undo that on a pick in the world. January, we'll get the black cross in the corner. That's called the speed fell. Or the auto fill them with a straggle over to the right. Let me show you other examples of when that could be very helpful. I'm gonna get a sheet five here. You can also drag it down. So knows here I have the month names going down. That was the same exact thing here I use Monday. I got the Black Cross and dragged it down, and then we have the rest of the dates I could do 12345 Let me show you how you do. 12345 I'm gonna actually get rid of those entries and share that one again. I'm gonna click on the one. I'm gonna get the black Cross. Try Get down on those How It says all ones. Right? So what you do is you're gonna click in this little box at the bottom of your speed fell and then you pick on when it's called Phil Siri's. And then it says 12345 Okay, so that's a nice one to know about. Watch again. Time to start the one, Get the black Cross and drag it down. Then I'll click in the box every year, and I'll say, Feel serious now. This is 12345 It can also work with the dates going to give you a keyboard. Struck out right now. If I do control semicolon, control of semi Colon will give us today's date. And that is the date that I'm recording it. June 5th of 2016 if you're watching this, So I'm gonna go ahead and get the date and dragon could get the Black cross and drag it down. And I guess he filled in the rest of the dates. Now here's something interesting. Will you do with the dates? Perhaps I would like to do the weekends only. As a matter of fact, when I'm recording this, it is a weekend. So the fifth is a Sunday in the sixties on Monday. So what if I just want to do the weekends? I mean the weekdays only going to click in the snow box. And then we'll say, Phil, Week, days. And if you notice it actually moved the Saturdays and Sundays. So let's try that again and I pick on that and I'll pay Phil weekdays, and then you could see how it only shows the weekends only. So when you do that, you're going to click on the pull down and you pick on Phil weekdays, and that is how it then shares only the weekdays only. So goes from like the 10th to the third teeth and the 17th to the 20th and so on. So that's a nice from the know about. Now you can also do some of the deaths. Have I typed in team won? Then I'm gonna go and get the Black Cross, drag it down and it fills those in. That had been any word I could have said Patient Juan, or student one or anything like that. And then we would be able to throw that serious him One more. I want to show you here if I want to get every Friday. So let's go with this. Friday's the which is going to be the 10th. So I say, six slash 10 slash 2016. Then I'll type in next Friday's date, which will be the 17th 6 slash 17 slash 2016. You're gonna type in the 1st 2 of your Siri's. I'm gonna highlight both of those cells with the White Cross. Then we'll get the black cross in the corner and just drag it down. And now I have every Friday after that. So when you want something that's different than sequential, you type in the 1st 2 of your Siri's there have at both cells to get the black cross and then you drag it down. Usually it's smart enough to figure out where you're trying to dio. Let's see another example of that. I like to say 5 10 15 20 is what happened? Five and then 10 hallowed both cells with the White Cross. We'll get the Black Cross, drag it down and now we have 5 10 15 20 So that's how on she 2014. That's how I originally built where it says January through July. Now, to wrap up this wrap up this video, I want to show you an example that comes up quite a debt. We wouldn't do something that's called the percent of Change. So in this case, I'm to go to a different sheet. Ah, that's good. A sheet sex. And you can see how I have the 2015 totals and the 2016 totals. And now down here if I want to get rid of those totals so I could just get rid of this by deleting them now there I would just do an auto some. So I put on the home menu and I pick on all those some right there. You can see that's going to sum up that column now, In this case, I don't want to include 2015 So I'm glad I saw that I actually wanna go from C three to C two C nine. So if the auto some gives you a different range than what you want to use, then you could just hot at your own range before you hit the end. Okay, good. So now this point I just copied over with the speed fell. And now I have the column from the column total for both of those as well. Now let's see how we can get the difference in the percent of difference. The difference is just gonna be equals. This number D three minus C three equals D three minus C three and then we'll have the difference there. Now, of course, I'm gonna go ahead and get the black cross and drag it right on down. Now there's even a negative number. You can tell it's a negative number because, uh, negative numbers will show up in red or in parentheses, and you can see we have a negative number there, and that makes sense. 2014 total of 14,000 and 2016 had a total of 12,000 so actually went down by 2000. So also did there waas, I said, equals D three minus C threat. Now, to get the percent of change, we're going to take this number and divide it by the first total. Some people will divide it by the second toe, but that's not really correct. To get 2% of change, I want to take the difference and divided by the first number. So I'm gonna say equals this number divided by the original Total e three divided by C three. And now I have that column already for a minute as a percent. So if you do that and it's four minute like a decimal, that you would just change had to be four minute as in percent. And now I'm just gonna copy that down. And now we have the percent of change for each one, and obviously that one has a negative. A negative change now knows how both of these numbers are the same in the accounting department. So they're difference with zero, and the percent of change was zero. So the calculated percent of change, first of all, we had to get the difference. So I took um, if you noticed the former this is D three minus C three and then Once I had that, I took the difference and divided by C three. So is this equals e three, the bodies divided by C three. So that's a real life example that's called a percent of change.