Excel basics - Microsoft Excel Tutorial

- [Instructor] Some of you may be Excel experts, others you can get by with your Excel skills, and some of you may be pretty new to Excel. So before we get started let's go through some very basic Excel functions. Mostly these are things we're going to be using throughout the course. So this course I'll be recording on a Mac. Some of you may be on a PC. There are some slight differences from time to time. In some cases, when I say the word Command, for example if I wanted to use the Command + D function on the Mac that might be the Control + D function on the PC. But let's go ahead and move forward. In this case here what I want to show you is the power of the equal sign. So the equal sign allows us to do a number of different things. For example, if I wanted to figure out what was in a different cell, so I can click equals, and then I can, let's say I wanted to copy what was in cell B3. So this right here, so I can type in B3. And there I go. And if this value were to change to 100 notice it changes what's in cell F3 as well. Let's change this back. So the other thing that we can do is we can add cells together. So we can create little formulas. So in this case here, I want to add what's in cell B3, and I want to add that to what's in cell B12. So that's the 10 in B12 and the 1 in B3. And that should give me 11. Just like I was expecting. Again, if this value here were to change to 25 that should change the value of what we have in that particular cell to 26. Let's change that back to 10. Now the other thing is adding up all of those cells together, B3, B4, B5, all the way down to B12. That's going to take a while to type into Excel. So what I can do is see if there is a formula. And so there is a formula here, the SUM formula. So I'm going to type SUM, and notice it's already starts to populate down here. So I can click on SUM. And now it's asking me, well, what do you want to sum? And I can, again, click on these individually. But the other thing that I can do is I can say, well I want to add everything from B3, colon, down to B12. I close my parenthesis, and there we go. It just added everything from B3 to B12. And once again, if this value were to change to 100 that should change the values both here and here. Now what we want to do here is we want to figure out what is the tax on each sale. So there's an 8% tax rate. And what I'm going to do here is I'm going to go ahead and I'm going to say, well the tax on this particular sale of $349 is going to be the $349 times the 8% tax rate. So that gives me $27.92. Now the problem is that if I want to take that formula and I want to copy it down, I can go ahead and do that. But you'll notice this doesn't work out very well. So instead, what I'm going to do, and the reason this is happening is because the 8% is in this cell right here, B3. And Excel thought, well, since you want B3 here you probably want B4 here and B5 here and B6. So that's what happened. Now we want to make sure that it locks in on B3, 'cause it's always going to be the tax rate of 8%. So I'm going to go ahead and do that. I'm going to make sure that it's locked in by going towards B3, and I always want it to B3. So I'm going to type in Fn + F4. That's going to create an absolute cell, an absolute value. So it's not going to change from B3 ever again. And if I want to, I can do this manually. But I can also do Fn + F4 and it's going to change it for me automatically. Now, it didn't change the value here, but watch what happens when we decide to copy down. And notice what I can do here. I can click over this little handle. I'm going to click on that, drag it down. And notice in every case it's taking E5 plus the same tax rate. E6 plus this tax rate. And if the tax rate were to change to let's say 9%, all those values change. The other thing I want to show you here is that while we don't want to do it necessarily as copied to the right, you can do that as well. And notice it's still locked in on B3. To find the actual total, the sale amount plus the tax, we would take this value here, add it to this value here, that gives me the total. And then once again, I can drag it down. But Excel is pretty smart. Notice what I just did there. Instead of dragging it down, which I could have done, I went to the handle and I double-clicked and it knew that I wanted it to fill out the rest of my table right there. In this case, what we want to do is we want to sum the pounds of potatoes, and then we want to move that over into a different table. So this is easy. We can type in SUM, and then I can take the sum of all of those values. And there we go. There's 70 pounds of potatoes. But if I copy this and then I paste it over here, you'll notice it's not 70 pounds anymore because it believes, and you'll look at the formula here, it's trying to say, well if you wanted to add these five values, you must want to add these five values over here. Which is nice in some cases, but in this particular case I want to take the pounds of potatoes and put 'em there. So instead, what I can do, there's a couple options here. One is I can Copy. And then I can do a Past Special. And I can just put the value. This now changes this, and notice up here in the formula area, it changes it to a hardcore 70. You can't change it anymore. And the unfortunate thing about that is if we decide to change this to 20, there's actually 80 pounds of potatoes, and notice this is locked in. So we don't want that. So I'm going to undo this. And instead what I can do is I can either say, well the potatoes is going to be equal to whatever this cell is always. Another way is, remember we just looked at absolutes. So I can say I always want it to be B4 to B8. So if I click on this, again Fn + F4. then I go to B8. Fn + F4. that is locked in this formula. So it'll always be the sum of B4 to B8. I can now Copy. Paste. And if this number is to change both this number and this number will change. The other thing you can do within Excel is you can turn a table into something a little prettier, an actual table. So by just being somewhere in the area of the data I can hit, in the Mac, Command + T. And now it asks me, "Is this the area you want to be your table?" It is. And it also asks me whether or not the first row right here are my headers. Yeah, these are going to be my fruits and vegetables. This over here is the amount of pounds I have. So I click okay, and you'll notice it turned it into a nice little table. And I can change the formatting up here as well. Lots of different options. Sometimes we're going to see that our data set is really big, and moving around it could take a little bit of while. So scrolling down and scrolling up, that could take a while. So instead, what I can do is I can start using my Command and my arrow. So notice I'm all the way on this far left side of my data set. What I can do is, well, how many rows are in this data set? So I can hit Command and the Down Arrow, it takes me all the way to the bottom. Oh, we're down to row 93. I can also use Command to the right, takes me to the right far end of my data set. Command + Arrow Up takes me all the way up to the header. And Command arrow to the left. Sort of takes me to just about where I started. Now by adding in just the Command + Shift, the Command Shift and then the Right Arrow it allows me to highlight the entire first row. Let's go back to the beginning here. I can do Command + Shift + Down. It now allows me to highlight the first column. And if I want to grab all of the data I can hit Command + A and it allows me to grab everything. Now, if I don't want that header, you might say, well, is there a way around that? So I can do Command + Shift + Right Arrow, then Down. And now it's gotten all of the numbers in the data set. One last thing I want to show you is this cool trick that they call FlashFill. Notice, in this case, I've gotten the first person on this list. I got their first name, I have their last name, and then it was put together in one particular cell. I want to do this with the rest of the names. And even though this is a short list, it could take me a while to type all of this and put it all together. So what I can do is I can see if Excel can figure this out for me. So I'm going to delete this here. And we're going to hit Control + E. If I hit Control + E, hey Excel do you know what's going on here? And look at that. It just figured out that I wanted the first name and last name put together with a space in between in the full name column. There you go. All right, we're all set to use Excel.