Excel Essentials

1. Introduction: Hello, everyone. It's Timothy Taylor and I'm back with a new course. The title of this course is Excel Essentials, and it's going to be all the essential tools that you're going to need to know when using Microsoft Excel. I've been using Microsoft Excel since about 2006 or 2007 on a regular basis. All the things that I've learned over those years of using this application, I'm going to teach you within this course. There's no more need to be afraid of using Microsoft Excel. The workbooks, the worksheets, the charts, is not going to scare you anymore because you're going to have all the tools that you will need to be effective and productive when using this application. 2. Microsoft Excel Definition: Before we get into the course, I would love to explain exactly what Microsoft Excel is. Microsoft Excel is a software program created by Microsoft that uses spreadsheets to organize numbers and data with formulas and functions. It's very similar to what Google has with the Google Sheets. Everything that you're going to be learning over here, you can also use majority of that in the Google Sheets. I love using Microsoft Excel because you can use it when you're offline, and that's very important in certain situations. 3. Navigating the Workbook: The first thing that you want to learn when you're using Microsoft Excel is how to navigate within Microsoft Excel. I'm going to show you right now how to navigate the workbook. One thing that we want to look at is worksheets versus workbooks, rows, columns, and cells, and then also saving and organizing files. So let's talk about navigating through the Workbook. So one of the most important things about Microsoft Excel is just knowing Microsoft Excel, knowing the basic things about Microsoft Excel. So the first thing that I want to teach is what a worksheet is. This is a worksheet. Down here, you see it says sheet one, sheet two, sheet three, sheet six. The only reason why I went up to sheet six because I deleted four and five at one point, and it's going to always just start with the number that's next seven, as you can see here. Even if I delete seven, if I hit this plus sign, it's going to go to sheet eight, it's very important to know that. All these sheets that we have here all combined to create a workbook. It's just like a notebook that you have that has 120 pages. Those 120 pages are all individual sheets. That's your worksheet. If you combine the 120 worksheets, you have a workbook or a notebook. Same thing here. Now in regards to columns and rows, if you look up here, you have the letters. It goes in Alpha order, A, B, CD EF G, so on and so forth, then the numbers here go in chronological order 123-45-6789, so on and so forth. The letters represent columns. It goes north and south or vertical. The letters are columns, the rows goes east and west or horizontal. The rows are represented by the numbers. That's the difference between a row and a column. A cell is just these individual rectangles. That's it. If you ever need to save anything, obviously, you hit the file and then save as, and then just continue in that fashion. You want to organize things, you go down here to your files and you just organize your different workbooks that you saved previously. That's the basic just quick introduction to Microsoft Excel. 4. Data Entry and Formating: The next area that I want to cover is data entry and formatting. The things that we're going to be going over now will be entering text, numbers, and dates, formatting cells, and number formats. The next thing that we're going to be talking about, obviously, I'm just showing you how to use data entry and formatting. If you want to enter a text, you just click on one of the cells, you start typing. And obviously, you're going to be hearing a lot of clicking because I'm using my mouse. All right. So that's it. That's how you type. You just start typing letters into that cell. Same thing with the numbers, same exact thing. Now, let's just say you put FPL the cost of FPL your electricity in Florida, the utility company, I'm sorry. Let's just say it's $200. You're going to pay it on January 1, 2025. That's the date. Here, it's going to be shown as a short date. If you go up to the top in this number section, you can click on here and it'll show short date or long date. Long date is going to have the day of the week, and then it's actually going to spell out the month. The short date just going to be numbers. Here, you can click on this dollar sign is going to do accounting. If you click here, you can go to currency, watch the difference in accounting in the currency. The dollar sign is actually going to move when I click on currency. It moves closer. That's the difference between accounting and currency. If I just wanted it as numbers, I would just go to general. This is all formatting. If you wanted to change the color of a font, you would go here to font color, make it black, blue, purple, green, whatever you would like. You can also change the size of the font by selecting that cell and changing the size here. You can also change the type of font. You can also right click and do the same thing. You see the types of fonts change? Regards to numbers, you also have percentages here. You can increase or decrease the decimal points. Then I'm going to put all this back to normal. Another thing that is important is using borders. So when you first start out, you're just putting in numbers, and it doesn't look that good. But if I had, let's say I had I wanted to show my expenses for the month. I have my utility. Let's just say I have my car insurance. I have my mortgage. Let's just stay there for now. What I could do is I can highlight these cells by clicking in D one, holding, sliding over to F one, quickly, how do you know which cell you're in the name of yourself. You just click in the cell right here it shows you D one. Right here, it shows you E one. Right here, it shows you D six. It's always going to be column row, letter the number. Highlight, select, go to merge and center. Then here, I may go to center the words within the cell. Here I'm going to go to currency. Let's say it's on the third of the month. This is going to be on the first of the month. Mortgages 1,200. Going to highlight those cells and then we're going to put in currency here. Then we're going to put some borders around all of this just so it's easier for you to see. We're going to do all borders. Then we're going to do thick border here. If I right click on one, it shows insert. It'll just insert another row so that you can see this a little bit better. Here I will color I would also make this bold and then I will color this also, but I will choose a different color or maybe just a different shade so that you can differentiate the two. That's what I would do. That's just a basic way of formatting your information or your data when you're using Microsoft Excel. 5. Basic Formulas and Functions: The next thing that I want to go over are the basic formulas and functions of Microsoft Excel. We're going to work on introduction to the formulas. We're going to work on some average min and max functions and then also cell references. Relative and absolute. Let's go into an introduction into formulas. If you have a large sample here and you want to find the minimum car insurance, max car insurance, average car insurance, the total, we'll go to the top and we'll go to formulas. Here, if you click here, you'll see the most commonly used formulas. Sum is going to be the total amount from your cell range. Average is going to be average amount, the average number for your cell range, count number is going to be the count. MAX is going to be the highest number within your cell range and then min is going to be the lowest number within your cell range. Here if I look at this sample size, go to Min Car Insurance. If I click this cell here, click here and go to Min. Remember, Microsoft Excel is a calculator and it's very smart. It's going to do whatever you're asking it to do. It's going to perform that task, but you have to ask it to do it. You have to tell it to do it. Microsoft Excel will guess. Sometimes it guess right, sometimes it gets incorrectly, as of right now, it's guessing incorrectly. We don't want these cells. We want cell B four all the way down to cell B 57. And for some reason, I got dropped off of it. But I know it is, I'm just going to type it in. B four through B 57. Hit Enter. The lowest car insurance payment is $120. Same thing with the MAX. I'm going to go here and I'm going to click on MAX. Remember, I know the sales now, B four through B 57, hit Enter. $813 is the maximum car insurance payment out of this survey. Average car insurance, you're going to go here. Remember it's going to be B four. Through B 57. That's the only thing that you're going to be that you're going to have to change. This is the average car insurance payment. Then if you want to know how much has been paid towards car payments this month throughout this entire survey, you go here, go to S, and before through B 57. Hit Enter, $22,720.17. That's a basic introduction to formulas. Obviously, as you use it more and more and then you have different things that you want to see or different things that you want to find, obviously, you'll learn about more formulas and functions. If I go over here and I like these two charts and there for different reasons, obviously. If you have a company and you have some employers or employees, I'm sorry, you have John making $20 an hour, work 40 hours per week, you want to know exactly what his paycheck amount is going to be. If you go here, I'm going to go I'm actually not going to do some. I am going to do a sum function. I apologize. If I go equal sum up parentheses, I can do my calculation within the parentheses. I want to take this cell, which is C three. I want to multiply it, so that's going to be the asterisk symbol. I'm going to multiply it by this cell and hit Enter, it's going to be $800. It's already in format of currency. Now I want to find out the paycheck amount for the rest of the people here. I take this small box at the bottom right corner, I click on it, hold that left click down. And I drag it down. Now I know what everyone's going to be getting paid. I didn't have to do the formula over and over and over. It's already there. I did it once and then I drag it down. Now, if I keep this here and I go back to formulas, at the top right hand corner, it says show formulas. If I show the formula, you'll see exactly what each formula is. Now, this is where the relative and absolute cell reference comes from. Here you have the relative cell reference. For this formula, it's going to take C three multiplied by D three. When you tell it to perform the same task down here, it automatically thinks you want C four multiplied by D four. Down here, it automatically thinks that you want C five, multiplied by D five, you're taking the relative cells. That's the relative cell reference, and this is okay. We're going to take off the show formula and we're going to go over here. Now you're a teacher. You have seven students in your class. They just took a test. These are the amount of correct answers that they've received or they've earned. Now, how many questions were on the test? Let's just say 30. I'm going to put 30 up here. For score, again, I'm going equal sum on the parentheses and then I'm going to do my calculation within the parentheses. Now we have 25. We're going to go divide it by 30, hit Enter. Comes up to 83.33% and it's already in percent. If it wasn't in percent, it would have showed up as a number, could have showed up as general, but now you put it in percentage. This is what it is. You can actually increase the decimal points here. Now remember, with this formula, you always perform um your multiplication, your division, whatever it is, it's going to be based on the cells and not the numbers. It's H three divided by J one. We're going to do the same thing we did over here. We're going to go to the bottom right hand corner. We're going to drag that formula down. We run into an error. This is an error, something happens, something isn't correct. In order to find out what it is, we're going to go back to formulas and we're going to show formula. If we show the formula here, if we show the formula here, it did H three divided by J one, which is what we wanted. For the second one it did H four divided by J two, nothing's in J two. For the third one it did H five divided by J three, nothing's in J three. This is relative, just like over here, the relative cell reference. But we didn't want it to be relative. What we wanted to do was want to take this number divided by this. We want to take this number, I'm sorry, this cell, divided by this cell, this cell, divided by this cell. In order to do that, we're going to delete these because we don't need it. Then over here, we're going to put $1 sign in front of the J. What does that do? That locks it in this column. That's going to lock it in this column. But guess what we know already, it's going to stay in that column regardless. Because Microsoft Excel is smart. It's going to stay in that column regardless. But we want to put $1 sign in front of the one. We want to put $1 sign in front of the one because the row will move. Remember, it moved down here, here, here. Just like over here, it's moving down, it's going to start moving down here. But it's going to stay in that column, but that row will change. So we want to lock the row. So we're going to lock the row at one. This is row one. It's going to be J one, but the row is one. We're going to keep it there. So we hit Enter. Then we do what we drag it down. Now each formula is H four divided by J one, H five divided by J one, H six divided by J one. They're going to stay here. That's an absolute cell reference. Let's take off the show formula and then let's actually see what these numbers look like. All right. Here you can see the students scores. What automatically gives it away that is correct is if you go to Susan, Susan got 30 correct answers out of 30, so you know she has 100%. That's how you That's the difference between relative cell reference and absolute cell reference. Now, you wouldn't want anybody know that you did it, so you go here and you can actually hide this number by just giving it a different color. Give it a white color. No one knows. The font color is white, no one knows because obviously the sheet is white. 6. Sorting and Filtering Data: Now that you know how to input basic formulas and functions, we're going to now talk about sorting and filtering data. We're going to talk about sorting alphabetically or numerically. Then we're going to also talk about using filters to find data quickly. This will all be useful, especially when you're using large worksheets with a lot of data in it. You want to easily be able to use this information. Sort and filter information. If we have a large sample size like this and we want to say, Hey, who's the person with the lowest car insurance or what's the lowest car insurance payment that we have throughout this entire sheet? The first thing I'm going to do, I'm actually going to for right now, I'm going to remove this because it's a merged cell. And because it's a merged cell, it's going to give you an issue. I'm going to remove that for right now. Going to highlight all the car insurance payments. Go over here to the top right, click on smallest to largest. It's going to asks, Hey, if you do that, we're going to change all of this. Meaning if Number 14 is the cheapest car insurance. Then number 14 is going to be at the top, and then all of number fourteen's information, the mortgage and the card note is also going to be there, and I'm fine with that. I hit SRT, it's actually number 46. They have the cheapest car insurance, which is $120 per month, and then their mortgage is here, then their car note is here. I can do the same thing and say, Hey, let me get the let's go the other way around. What's the most expensive? Let's go in that order. And it's number 45 has $800 in car insurance payments. We have that. Going to actually go backwards and undo all that information. Now, if you want to filter, let's just say we said, Hey, I just want to see what the person in slot five, the person in slot seven and the person in slot ten, what is their car insurance payment, their mortgage and their car note. I will click on this row. I will go here to sort and filter, click on Filter. Then I'm going to find those numbers that I just said, and I forgot what they were, but let's just say five, seven and ten. Hit okay, I will only show their information. This really comes in handy when you have a large sample again and you're looking for something out of that entire pile. If you have thousands, if you have thousands or hundreds of different data or data selections, then trying to find just three, it's going to be trying to find a needle in a haystack. This makes it a lot easier for you. Remember, Microsoft Excel is to help you to assist you in what you're doing. The easiest way that we can do it, that's what we're going to look for. 7. Charts and Visuals: Now we want to talk about charts and visuals. We're going to talk about using the information that you have and creating a bar line, and pie chart. Then also, how do you customize these different styles within the chart? Microsoft Excel is all about how can you present data in the most efficient manner. If let's go back to the other page. I like this one a little bit better. So if we had a class size and we have one, two, three, four, five, six, seven, eight, seven. We have seven students here and we wanted to make a better visual because we're presenting this to someone. Let's say we presented this to the team or your Math team, your ELA team, your administrators, and you just wanted to show them a chart because a chart is a lot easier to read and instantly grabs your attention. Or graph, I should say. Charts graph, all these are very easy to read. This is easy to read also. But if we wanted to use this information, to get a pie chart, a column chart, a bar chart, we have to know what information do we actually want on the chart. The first thing I think anyone would do or anyone who's not familiar with Microsoft Excel as of yet, they will probably highlight this section, go to insert, and then go recommended chart. This is a column chart. Double click here, this is how the column chart looks. Now, we see the student names, the scores down here, and then it says the correct answers. How many correct answers do they have? Now, we don't need that information. We don't need to know how many correct answers this dudess have. We want to see the score. What we have done should have done is, let's get out of this one. Going to delete that. Actually. To get out of that chart. I want to know the students. We're going to select those by clicking here, holding down your left click, going down to April. Then you hold your Control button on your keypad and then do the same thing here. This is the information that we want the student's name and their scores. Now we go over the inserts, we go to recommended charts. Here's a column chart. Now you can see it. I think this makes a lot more sense. It's more aesthetically pleasing and it's easier to read. You can instantly see that Susan has the highest score, followed by April and then Stan. What you can do in here though is from here, you can actually change the chart styles by clicking up here at the top. Change it to whatever you think is the best option for reading the information that's here. You see where SCOR is here? You can actually put that at the bottom by switching row column right here. Now score is at the bottom. With this, you can also go over here and change colors. You can do a multitude of things with the chart. But that's the basic way of creating a chart. You can do pie charts. Now, with a pie chart, this wouldn't be very helpful. In a pie chart. One, it doesn't go in well, and then it wouldn't make any sense anyway. But a bar chart and a column chart, those make the most sense in regards to the information that we have right now. But remember, Microsoft Excel, we're all about taking data and being able to present it. By creating a chart or graph, it makes it a lot easier to present the data that you have. 8. Conclusion: Thank you all for completing this course of Excels essentials, throughout this course, I hope you've picked up valuable information on how to use Microsoft Excel. Throughout this course, we went over several different things that you should know about Microsoft Excel, including navigating the workbook. Data entry and formatting, basic formulas and functions, sorting and filtering data and charts and visuals. As you continue to use Microsoft Excel, you will pick up on many more tips and techniques and using Microsoft Excel. Remember, the goal of using Microsoft Excel is to make your life a lot easier when manipulating, using or reviewing data.