Excel for Beginners: Master Formulas, Functions & Cell References

Hey everyone, I'm Jamie and welcome. Are  you tired of manually calculating numbers   on your spreadsheet? Spending way too  much time with a calculator then typing   the results back in? What if I told you Excel  could do all that math for you automatically? In this video, we're going to cover the core  concepts that will transform how you use Excel.    We'll learn about formulas, Excel's pre-built  functions, and a magic trick called cell   referencing, including how to make your formulas  flexible, or how to lock them in place when you   need to. By the end of this tutorial, you'll  be able to build a simple budget tracker that   updates itself. This is for the absolute beginner,  so don't worry. We're going to take this step by   step. Let's get started. All right, let's dive  right in with formulas. Think of a formula as   your personal calculator built right into each  cell of Excel. The most important rule to remember   is this. Every single formula in Excel must start  with an equal sign. That tells Excel, hey, I want   you to calculate something. Let's select cell A1.   I'm going to put the equal sign in to start with,

saying, hey, you need to calculate something  here. And I'm going to put a simple formula,   10 + 5. And you can see right away it calculated.   If I click on the cell, I can always see the   formula that I typed in right inside uh the  formula bar up here. If I doubleclick on it,   it will show me the formula that's in it. So, if I  click off, it will go back to just what the answer   is. If you want to subtract, you would put your  equal sign in. Let's go 8 - 6. You can see it's   two. If we want to multiply, we're going to be  using the asterisk sign. So, we'll say= 8 multiply   5 is 40. And with divide, division, we're going to  be using the forward slash. So we'll go uh equals   9 / 3 = 3. And I do want to point out the order  of operations matter here. So if I say= 7 - 5   ultip by 2. Now in this case it's not going to be  7 - 5 multiplied by 2. It's going to be 5 * 2 is   10. And it's going to subtract seven. It's going  to be a negative answer here -3. So remember the   order of operations matter. Now, that's useful,  but where Excel really shows this power is with   cell referencing. Take a look at the table that  I have right now. If you're following along,   just pause the video and quickly type this out.   You can see that we have a place to put our total,

which would be the price multiplied by 3. I could  go to this cell and type in, like I said before,   we could put equals. I could put 199 multiply by  three, and I get my answer. But what happens if   the quantity changes or the price changes? Well,  then I'd have to go back and redo the formula for   every everyone. And this is where you want to  make sure you use cell referencing. I'm just   going to delete this. And this time again, we  need to start with the equal sign. We are still   going to do a calculation in here. I'm going to  say equals. And I'm just going to select B2. I   could type in B2 if I wanted to, but I'd like to  just select. And so then I'm going to say what   I want. This is going to be multiplication.   I'll type my multiplication, the asterisk,   and I'll click on quantity and I'm going to hit  enter. So I get the same answer, but the power is   this. What happens if the quantity here turns to  seven and I hit enter? It automatically updates.    Now you've turned this into a dynamic spreadsheet.   The formula we just made uses what's called   relative reference. Watch what happens when I  click the little square at the corner of cell D2,   the fill handle, and drag it down to D3. Excel  automatically changed the formula in D3 to   B3 * C3. The reference moved relative to the  new cell. This is fantastic most of the times,   but sometimes you need a cell reference to stay  put. Let's add a sales tax calculation. Now, if I

go up to F1, I'm just going to type tax rate here.   And in G1, I'm just going to give a percentage,   and I'm going to say 5%. Now, what I want to have  happen, and I'm going to put the amount in this   cell underneath tax rate. I want to multiply the  total here by the tax rate, 5%, and I should get   my answer. Let's give it a try based on what  I showed you. So, if we say equals, I'm going   to make sure I sell reference D2. We're going to  multiply. We're going to go to the 5%, hit enter,   and we get. 7. That worked out fine. Now, remember  what I showed you before when he dragged down the   uh formula by grabbing the handle here, dragging  it down, and notice something didn't work right   there. We have an error in there or let's see  what happens. So, if we double click on this,   it shows me the different cells that are that got  carried down. Now, this is correct. I wanted it to   move to D3, but notice here it also moved down.   So, it's under relative right now. I don't want   this G2 to be relative. I want it to be absolute.   So absolute is when you make it so it doesn't   move. So how we do this, I'm going to go and just  delete these and we'll do it one more time. So I   put my formula in. So we'll say D2. We'll multiply  it by the 5%. Now if I don't want this to move at   all to lock in place, I need to use dollar signs  on in front of the G and the one and it's not   going to move. A quick way to do this is to use  the F4 key on your keyboard. If I press it once,   it automatically adds the dollar signs to it. So,  I could go ahead and add the dollar signs to it   like by just by clicking on it. But the F4 key is  a little bit of a shortcut to do that. I'm going   to hit enter. Still got the same answer. Now,  if I drag it down like this, look, now we have   20 cents. If I double click on this cell, G1, this  is an absolute right here. So, this did not move.    But this one did move. If I wanted both not to  move, I would have to put dollar signs around   each of them. So, this is a really important  thing to know about when you're working with   uh different formulas in Microsoft Excel. Now that  you've mastered cell referencing, let's talk about

functions. Functions are pre-built formulas that  are right inside Microsoft Excel that do a lot   of the heavy lifting for you. Let's start with a  very simple common one, the sum function. And in   the case here, we're just going to sum up these  two numbers. Now, the old way you could do it   just as self-referencing, hitting the equal sign  and you select and you use your addition and so   on and get this. Now, with using a function, I'm  going to show you a few different ways you can   do this sum one. If I go ahead and put my equal  sign in, and this is the way I use the most, but   I'll show you others. I start typing sum. Here is  my function that I want, and you can see adds all   the numbers in a range of cells. So, if I go ahead  and select this one, I doubleclicked on it. Now,   we have the function, we have the parenthesis, and  inside the parenthesis, you're going to have the   arguments. And the sum one's pretty easy because  all we need to do is have a range. And the range   here is just going to be D2 to D3. So, I could  just type D2 to D3 in. So, if I had a long list,   I could type the top one and the bottom one.   And a lot of times when I start the sum,   it knows it will just automatically sum that  up. It will know the list. But I can go ahead   and hit enter. And that's how easy it is to use  the sum function. Another way you could use this,   and I tell a lot of way for beginners to go up to  here, is uh using this insert function. You can go   and do a search up here for a certain function.   And here's the someone. If I go ahead and open   this up, it walks you through step by step here.   So it kind of explains it a little bit more.    So as the functions get more complicated, this  is a great way to kind of break up the different   arguments that are inside the function. So in  this case, I don't have to change much that I   have the range here. So D2 to D3, that's going to  work fine. And then we have it here. So, another   way I could get to the uh functions, if I go over  to formulas over here, notice that we have lots   of different ones that we can go through here, and  I'll explain a few of these. But if we go to auto   sum, and I just click on this, it knew right away.   It grabbed all of them here. It gave me the range,   and I could just go ahead and hit enter. But  let's try a few more uh functions to just to show   you the basics. to get you started. If you're  learning to if you're wanting to learn more about

Excel, take a look at my other beginner Microsoft  Excel videos. I'll put the links to them down   below in the description and up above in the card.   But now, let's take the information that we've   used in Creator Budget Tracker. This is a pretty  simple budget tracker. You can pause the video   and just quickly type out what items that you feel  would work for you the best. I'm just going to use   the functions inside Microsoft Excel to quickly do  this. Now, so we have our cost here. I'm going to   go and do a total expenses. So, in this case, this  could be an auto sum or I could go to the sum,   but I'm under formulas. I'm going to click  on autosum. Is this the correct one? Well,   we have an extra one in here. I'm just going to  make sure I select the area that I want. And I hit   enter. So, right away, we have our total expenses.   Now, we can do other types of functions that are   easy to from this dropdown right here. Notice  that we have average. So if I click average, is   this what we want? No, we want to have this area  selected. So I'm using ranges in these ones. And   I'm going to hit enter. For the number of items,  we have a count option as well. So if I drop down,   you can see that we have count numbers. And this  will count how many entries. So you can see easily   that this is four right here. But if you had a  long list, this is where it becomes powerful.    These different functions will quickly take care  of these jobs for you with just a simple click.    Now, I showed you this quick way here. You can do  the same thing that I showed you before. We could   use the equal sign, start typing in sum. We select  our formula, select the argument, the range here,   and we can go through each one. So, even if I  start typing equal, then average, you'll see   that will be one of the different functions. I  can go and select the area and we can do more   and more. These are just a shortcut up here.   Now, if anything changes, this will all update   automatically. Here's an example. Maybe your rent  up goes up to $10,000. And you can see right away   the total expenses and the average expenses jump  right up. The number of items doesn't change   because we're still only listing four items. But  this is the power of using functions in Excel.

Every function has a name like sum followed by  a parenthesis. Inside the parenthesis are the   arguments, the pieces of information the function  needs. For some, it's just that one argument that   I showed you, the range of cells to be added.   But many functions need multiple arguments.    Let's look at count if. First, let's create a new  simple list in column A. And you can see I have it   typed out right here. And we'll look at one with  multiple arguments before we leave here today.    I have a very small list here of different fruits.   I only have from A1 to A6 as my range. But I want   to use a function to count how many times that  apple appears in this. And I know I can glance at   it and just do a quick count. I can see three.   But if you had a large list of thousands, you   start to understand the power of Microsoft Excel.   So we're going to use a function called count if.    And you're going to see multiple arguments. And  you could put this anywhere. I'm just going to   put this in B3 and I'm going to start my uh my  different function here. So I'll put my equals   in and remember the other ways that I showed  you as well. I'm going to start typing count   if and I can see it's right here. So the count's  the number of cells within a range that meets   the given condition. So I'm going to select this  and it's really important to pay attention to this   and follow the tips that they give you on this. So  what's the range that we want? Well, the range is   going to be this right here. A1 to A6. So, that's  going to be the first one. And then we're going   to put the comma in. And notice now it moves  to the criteria. So, what's the criteria? Well,   I wanted to count the word apple. So, I actually  have to type this in, but I need to use the   quotation marks here. So, I'm going to type apple.   And I need to type it the way that it's being   written in the list. So I'm just going to do this.   Now it's really important to get in the habit of   closing your parenthesis too. So if you have one  open, you can see here then the close will be   here. As you get uh further on and understanding  more Excel functions, you could have nested   functions and side functions. We're not doing that  in this uh tuto tutorial here today, but do check   out some of my other ones. So I'm just going to  hit enter. and it went through it went through   the range and the criteria was if it found apple  and it did three times. So there's an example of   one with multiple arguments. So go ahead and give  that a test. Start exploring trying to go through   piece by piece with the different functions to  see how you can work with them and adding the   information that's needed. So let's do a quick  recap. You learned that formulas start with an

equal sign. that relative cell references move  when you copy them and that absolute references   lock in cell place. You also learned how to read  function syntax using the tool tip commas and   parenthesis to unlock powerful shortcuts like  sum and count if. The best way to learn is by   doing it yourself. So try creating your own  simple list and practice these skills. Thanks   for watching this time on Teachers Tech. I'll see  you next time with more tech tips and tutorials.