Microsoft Excel Tutorial for Beginners - Full Course

Hi, and welcome to this course called Excel from the beginning. In this course, we're going to learn how to use Excel using six projects that you can use in real life. So my name is shad Sluiter, and I've been a teacher for computer science and computer applications for several years, what you're about to see are applications that I've used in my classroom, and have been successful with other students in the past. So I really appreciate the opportunity that Free Code Camp has given me to present this to you. I have used Free Code Camp in

my own classroom, with success with students from high school to the university level. And so we're going to see part of what I've done in class. And I'm glad to contribute back to help you as well. Let's take a look at some of the projects that we'll build in this course. So Excel from the beginning is going to use six projects, the first one, and it'll be the most basic will be to set up a payroll. So imagine if you were a company and trying to pay your employees and keep track of their overtime. So

that's what we'll do in this first project. The second is to set up a gradebook. And so we'll be doing computing on percentages, we'll be finding who's in the top of our class and who's not. Also we'll have a factor decision tree factoring program. So we will try to decide what career would be best based on what we prefer, what the pay is and other benefits of a job. And so a spreadsheet will help us determine how to make a decision. Another application we'll create is a sales database. And so I will give you a

bunch of data and we will sort that we will determine who are the best salespeople what their commission is, and make some charts, we'll also do what's called a car inventory project where we'll create what's called database actions, we're going to have a large number of data again, and we can show you how to concatenate fields, how to split them, and how to make reports with this. And then lastly, I'm going to reserve this section for six different problems that we're going to solve. So the first five projects are more tutorials to show you how

something is done. And then I'm going to give you some challenge assignments at the end, Where I give you a half of a solution. And then you use your creativity in what you've learned in the previous courses to see if you can solve the problem. And so both we have a tutorial section, as well as a practice where you can put things into service. Now let's take a look at some of the things you'll learn throughout here. So some of the terms, you'll learn from the very beginning how to enter data, how to navigate through

a spreadsheet, and how to save it. Also, I'm going to show you how to set up formulas because using math formulas, simple formulas to solve problems, such as how to compute overtime, or how to compute the percent that a salesperson should get on commission, or to simply do averages and to find the maximums and minimums. So those are basic formulas will create charts. So you'll have pie charts and line charts, and you'll have scatter charts, Excel is really easy and creating charts, you basically have to choose what kind you want. And it does it for

you. Also, we're going to do Excel with some more advanced features. So relative and absolute references is used if you have some kind of a factor that you Want to use and other parts of the spreadsheet. And so that's another feature that we'll see. We're going to do imports and exports of data. So CSV is a common file you find on the internet. So maybe your bank statement comes to you in the mail, or at least online, I guess. And it says we produce these items in CSV format as if you know what CSV format

is. So I'll show you how to use this. And so you can interface with other programs on the internet, we're using something called v lookup. So v lookup is a powerful tool that is like searching through a menu to find the right item for your choice. Also, we're going to use pivot tables, pivot tables are summary reports. So if you have a large amount of data, and you want to condense it to a very readable decision, this is kind of a big data tool. So the last one is to split text and concatenate text. And

so you can modify things according to how you want them to be. So that's some of the features that were ahead of us. If this looks interesting to you, then please let's continue on with the first lesson. So here's the first lesson, we're going to work With something called payroll. And you can see that we're going to set up a group of employees, we're going to give them some fake numbers, their hourly wage, and calculate once again, with fake numbers, how many hours they worked, then we're going to take those numbers and use some simple

formulas to fill in these columns of data where we can see how much they get paid. Also, I'll show you shortcuts so that large numbers of columns and rows get rather tedious but there are shortcuts in Excel to make it work fast. So let's get started with our first lesson ever an Excel so this particular lesson on Excel the beginning parts of how to use Excel has been seen over a million times. on my YouTube channel, so welcome. If you haven't seen it before, this is a great way to start using Excel, we're going to

use Excel to create a spreadsheet for our business. We're going to launch the program and start a blank workbook. Now since this is the first assignment that we're going to do with Microsoft Excel, we need to go through some of the things that you see in any spreadsheet. First of all, the spreadsheet is designed in organized according to Cells. Just like in the game battleship where you have columns that are letters and rows that are numbers, you can identify cells such as this one by such as G six. To enter in information into a cell,

you simply click it and start typing. And so I'm going to type employee payroll, press Enter. And you notice that this cell, even though it extends into column B, really, it is only in a one. Now let's use Excel as a payroll, we're going to keep track of the hours that our employees worked this week. And we'll keep track of their wages. So first of all, we need to put some column headings in. So I'm going to put in the title, last name, and then first name. And then I'm pressing tab between these keys so

that it moves, the selection moves to the right, hourly wage. And then I'm going to put in the date, let's say January 1, and press enter. Over here, I'm going to say that this is the hours worked. And this is their total pay. So I'll just put the word pay. Now you notice you can expand a column by clicking between the column headers like between D and E. Now you can see the whole word. The next thing you need to do is you need to invent some names. So you get to come up with about

20 names, maybe 15 for your employees. I'll type them in now. Now you can see that I've invented names, last names and first names. And now I'm going to give them an hourly wage, each person makes a route $15 or so. So I'll type in some numbers here. You notice when you type in numbers, they are right justified when you type in the person's name in your spreadsheet, that they are left justified. That's just a convention that Microsoft Excel uses so that you can identify letters versus numbers. Now one of the items that you will

most commonly use in Excel is formatting. These are all hourly wages, I'm going to select this range with my mouse by clicking and dragging. The first square is actually highlighted even though it's white, the other ones are gray. But what I want to do is change them into a currency. So I'm going to click on the dollar sign up here. And you notice that they all become dollar signs. They all are the hourly wage for each employee. Now let's invent a number for each of the employees for how long they worked. The average workweek for

a full time person is about 40 hours. So I'll create wages or sorry, I'll create the number of hours that each person worked in this column. After giving each employee a number of ours, we're going to start working with formulas. Now, over here on this line, this square in e4, I'm going to create a calculated number a calculated cell. If a person gets $15.90 an hour, and they work for 40 hours. How much do they get paid? Well, fortunately, the calculations in Excel are quite easy to do. on my keyboard, I'm going to press the

equal sign. And you notice up here in this area called the formula bar, we have an equal sign, as well as the equal sign in the cell. Now I'm going to click on the cell that has the wage in it 1590. When I click there, the letter C four is entered into the formula bar, c four is the wage $15.90. Now I want to multiply 1590. So I'm going to press the shift and the 80, which is the multiply sign. And then I'm going to click the 40 the dot the number of hours that this

person worked. So my formula is equals to c four times the number that's in cell D four. When I press enter, it'll tell me that this person gets $636. If you go and change some of these numbers, such as we added an hour here and I type 41 and press enter. That calculation is automatically updated. When you double click on a cell that has a formula, you will see that the colors correspond to the cells that you clicked. So c four is in blue, which is this number And D four is in red. In a

press ENTER again. And it shows the numbers again. Microsoft Excel has a nice feature where you can copy and paste formulas. If I right click on this cell and choose Copy. And then I click on the next cell and choose Paste, it'll automatically calculate the next line. You can also highlight a range and choose right click and paste, it will fill down where all of those numbers are, why is this one luckily look like railroad tracks where a bunch of pound signs, it's because the value is actually too large to be held in this in

this cell. So I need to make the column a little bit wider. And now it becomes visible. Another way to fill down if you want to, you can go to the corner of a cell with this little green dot. And if you click exactly on the corner and drag your mouse down, it will also fill down the values in that column. And so now I've quickly calculated the pay for every single employee. One more thing we're going to do with this formula is we're going to add a few lines at the bottom. I want to

know who is the maximum, press enter, who is the minimum, press Enter? What is the average pay, and what is the total pay these numbers max, min, average and total are just labels. But over here in this column, I'm going to put in a corresponding formula. Microsoft Excel has hundreds of formulas that are predefined. Here's one of them, you type in the equal sign. And then you type the word MA X, you notice that these automatically are drop down menus that show that this is one of the formulas that Excel knows about. After typing Max,

I use the open parentheses, which is the shift key and nine. And I'm going to drag my mouse across a range of cells till they come to the very top, release the mouse button And press Shift and zero to do a close parentheses. So what this will do, it will give me the maximum of all of these cells, the maximum person is getting $45 an hour. And so you can see in this line here that Trent man is making 45 an hour. Now I'd like to know who the minimum is. So I'm going to do

the same process type equals sign, am I N, open parentheses and drag my mouse across a range. And I will tell it what the minimum is. After I press Shift and zero, enter, and the lowly person in my office is getting $6.90. It looks like it's Paul Smith. Now what's the average employee? Well, I can type in equals and then Av. And you see there's a bunch of formulas for average, I just want this most simple one, I'm going to choose average. I'm going to select the range, close parentheses, and press enter. The average employee

makes $16.48. Okay, let's take this range here of three different formulas, highlight it, right click it with the mouse copy. I want to click in the next column over right click And pastes. And now you see that we have the maximum, the minimum and the average for the number of hours that are being worked. Now you notice that these values are listed as dollars, they're actually not dollars, they're number of hours that the persons are working. So I want to change these formattings back to general numbers. Up here in the ribbon, where you will find

the number formatting area, there's a small arrow that if you click it will bring up a dialog box that shows all different types of formatting for numbers. I'm going to select general and click OK. Now, why does this number have so many decimal places in it? It's because it's because we are computing an average. It would be nice if we would just round these off to maybe the 10th the 10th place or the hundreds place. So let's go to these zeros here. And we're going to decrease the decimals that we're rounding to so to the

nearest 10th is good enough for us. So now we have the number of hours maximum minimum and average for our employees here in column D. for column E, we could also find the min the maximum the minimum and the average as well. So let's highlight this group, I'm going to select this bottom right corner, drag to the right. And it fills to the right now and shows me that these are the maximum dollars that someone made the minimum dollars and the average. Once more, these are dollars figures, these are not hours. So let's change these

to the dollars format. So I'm going to click up here on the dollar sign. And now we have the average the maximum and the minimum in the salary for this for this week. So now we've created the maximum the minimum and the average for each of these columns, column C, D, and E. We haven't done anything yet with a total. For the total, let's find out what the total number of hours worked was. And let's find out what the total number of wages that we've paid is. So for the word sum, we type equals su

m, and that will give us the total. Let's do shift and nine. And then I'm going to select a range here. I'm going to select all of the hours worked, close parentheses and Enter. And you'll see that there were 695 hours worked in my shop. Now I'm going to fill this to the right, and shows that I made a lot of more paid a lot of money For my wages this week, I'm going to change this to $1 sign format. And it shows now $11,532 The last thing we should do is put our name at

the top, so maybe c one is a good place, type in your name, press enter, save the spreadsheet, print it. And you're done with assignment number one for Excel. Welcome to assignment number two for Microsoft Excel, we're going to extend assignment number one using the payroll spreadsheet that we had created earlier. What I'd like to do in this assignment is add a new formula using the if formula, and also adding the idea of paying overtime to our employees. So you notice here in column D that we have the number of hours that each employee worked,

the first employee worked 41 hours, we're going to give him a bonus for his extra hour of overtime. So I'm going to start by inserting a column here in column E. If I click the column header, the entire column is selected, I can right click on the column header and choose the Insert button. And it will now give me a new column to work with. The first column, column D was the hours worked. Now I'm going to say this is the overtime hours. The overtime hours is a calculated field. So let's come up with a

formula that will tell us how many hours extra that this employee worked. At first, it would seem simple, we would just say equals the number of hours the person worked, subtract 40. And that will tell us how many hours of overtime this person has. It works great if you have 41. But there's some errors. If you have less than 40, I'm going to fill the column down. And you will see that when we come to Paul Smith in row number eight, he actually gets a negative one hours, it's a negative hours that he's worked, that

doesn't seem to work too well. What we would rather give is Paul should have zero hours of overtime, not a negative number. So let's revise the formula a little bit. So let's go up to here. And this time, I'm going to type in an equal sign with the word if if is a command that says let's take a logical test, and we will a logical test is either an equal sign greater than or less than, and then we'll give it a value depending on if that test is true or not. And if it's false, we'll

give it a different number. Watch how this works. The question is, if his hours worked, is greater than 40. And a comma, the value if it is greater than 40 should be his hours worked minus 40. Comma, and if he worked less than 40 hours, then let's just give him zero for this column, which would be zero hours of overtime, I close parentheses and press rest press return. So he still gets one hour of overtime. But now when I fill the formula down, you see that the people that worked less than 40 hours gets zero

in their form in their formula instead of negative numbers. And so this is the correct formula for calculating overtime pay. Now, how about the pay? What does that do? Let's go back and review this here. If I double click here, you will see that this formula takes the number of hours hourly wage and fills it in column F. That's great, but now we give them a bonus for working overtime. So let's create create a new column and call it overtime bonus. Now in this formula, we're going to calculate a bonus for the number of overtime

hours they worked. So I'm going to say equals point five Use the time sign Point five times their hourly wage, so we're going to give them half of their hourly wage times the number of overtime hours they worked. What that does is it gives us a formula to give them time and a half, or one and a half times for each hour that they worked overtime, press enter. So since john Kern earned one hour of overtime, we give him his full 41 hours of pay, plus an extra 50% of his pay for one of those

hours. Let's fill this down and see what we have. For the rest of the people. Some of the people have zero. This one got no extra time bonus, because he worked 39 hours. Some of these people worked 40 hours and still got zero time. Why? Because the formula is about how many hours beyond 40 that we're calculating. So they got paid their full wage for the first 40 hours. Now, how about the total pay, let's put in a new column, the total pay is simply equals to their regular 40 hours of pay plus their overtime

bonus and a return. And let's use the fill down option. Some of the numbers are too big, so we will expand the column and this is their total pay. Down here at the bottom, we calculated maximums minimums, Averages and totals. For all of these formulas. Let's move these to the right. Calculate the totals now and see we can see what everybody earned including their overtime. Welcome to assignment number three with Microsoft Excel. What you see on the screen before you is a final version of the payroll spreadsheet, you notice that it's got a lot of

cells, don't worry, most of these are copied and pasted. It's an extension of assignment number one and two. In the gray area here where you see hours worked. In the previous assignments, we only were concerned with one week of pay. And this assignment, we're going to add four more weeks of pay. And we were you will use calculations to find out how much they earned their overtime bonus, and then here in the blue area, their total pay. So we'll start from an assignment here that we did before. And we'll simply extend it to look like

the one that you just saw. Well, first of all, what we need to do is add some new columns. So starting in column E, I'm going to right click and choose Insert and clicking on the column header. Let's do this a few times. And will give us some space to work with for other weeks in the month. Now starting here in column E three, I'm going to add seven days to this date. Notice the first date that we chose was January 1, you can either type in January 8 for the next week, or we can

use a calculation, let's use a calculation I'm going to say this equals this square d3, plus seven. And now it starts at January 8. Now if I use the fill command, I'm going to create several more columns. And they're automatically calculated. Next, I'm going to invent some more hours. These are numbers that should be approximately 40. They can be a little less, they can be a little bit more. But it doesn't matter really what the numbers are just so that we have some data to work with for each employee. Now we've reached the end of

our data for the number of hours for each employee. It looks like I've created one extra column here in column II, so I'm simply going to remove it by choosing right click on the column header and delete. Now let's go to the overtime hours. We're going to put a date here for the week, January 1. And once again, I'm going to insert some columns. You can actually insert columns more than one at a time. If you highlight four column row headers and choose Insert, you get four new rows. Once again, I'd like to add seven

to the date that's in January 1, this plus seven and fill to the right. Now let's add some overtime hours. Now I want to calculate the hours of overtime for each week. Well we've already done this in the previous assignment with this famous if formula, if d four is greater than 40, then give us the value of d four minus 40. Otherwise give us zero, I can take this entire block and copy it, we're going to right click on it, choose Copy. And I'm going to right click on the next square and choose Paste. So

now it's calculating the overtime hours for the week of January 8. Let's double click on this cell. And you notice that we are now calculating the overtime hours from this blue square the 42 hours from January 8. So the formula automatically adjusted for the Column letter. This is using cell e4. I'm pressing escape now double clicking on this one. And you notice that this cell was using this cell D for as its source for its numbers. So when you copy and paste, Excel automatically assumes that you are relatively addressing columns somewhere else in the spreadsheet.

So if this is one cell to the right, then the next one is one cell to the right. I'm going to copy and paste again. So I'll copy this row here. Copy and paste it. Copy and Paste again. And paste again. So now we have overtime hours calculated for every week. Now when you start to get a lot of cells on a spreadsheet, it can get confusing looking at so many numbers. So Excel allows us to paint the cells in certain colors. These cells here that I'm highlighting now are all related to the number of

hours that they worked. Let's give them a color. Let's paint them all something gray. So now you can see that they all belong together as a block. For overtime hours, let's paint them a different color. Let's choose, You can choose whatever color you like. But I'm going to choose some kind of a salmon color. And now we're going on to calculate their pay. This pay here really is the pay for January 1. So the week of January 1 is what they got paid here. Now I'm going to calculate the pay for every week. So let's

insert about how many three more columns. And let's do once again equals the previous date plus seven. And then fill that formula to the right. Looks like we need one more column. I'll fill this one to the right. Now, how do we calculate pay, we simply took two numbers from their hours and the wage hours worked, which was 41 times their wage, we're going to find something new here. If we just simply copy and paste these formulas, we're going to get the wrong result. I'll show you what happens in a minute. And a copy this

formula, paste it for the next week. Now why in the world is everyone making over $1,000 some up to 3300. What happened? Well, let's double click on this cell and find out where it's coming from. First of all, you notice is very difficult to see the other side of the world here, we can actually zoom out on our spreadsheet. And we can make it easier to see the whole page. So let's zoom out to 50%. We can see the whole spreadsheet now but the numbers are a little small. Let's choose something else. I'm going to

select the part that I'm interested in just the cells. Click on zoom and choose fit selection that will fit this highlighted area on the screen. So now I can see hourly wage all the way to the last formula. Okay, now let's go look in this cell here. What's going on in cell number Oh, four. It says take the cells D four and multiply by E four and give me the results D four and e4. Remember great while we were in the previous week, we were taking the hourly wage times the number of hours. Well now

the relative referencing is saying well let's take the first two cells and multiply them together. So we need to make a modification This actually should be referencing c four, and pressing escape On the keyboard. And I'm going to delete all of this here, I'm going to right click on these and choose clear contents. What I need is what's called absolute cell referencing. So I'm going to modify my original formula here. It's telling me in this formula, that we should use C four times d four. And I know that I'm going to copy it to the

right four times. Well, what I really want to do is keep referencing the hourly wage, because that's how you calculate pay hourly wage. In C four times the number of hours worked, which is going to be D, E, and F and G and H. So is there a way to tell Excel not to use relative referencing instead absolute referencing, that's what we're going to call it anyway, I'm going to go up to this formula bar and modify the letter C, I'm going to simply type in $1 sign in front of it, that doesn't mean a

value dollars, it just means that every time that you think about sales, C, or C four, you're going to always use column C. For the first week, nothing changes, all of the numbers should stay exactly the same. But now when I copy this range of cells, and paste it into the next column, Let's take a look at what this reference is. I'm going to double click here, you notice that it is still referencing cell C four. But now the other cell is relative referencing says let's move to the right one every time. And so it's

now multiplying the hourly wage times the hours from January 8, or the 42. I'm pressing escape on the keyboard. Now I'm going to highlight this whole range. This time, I'm going to instead of copy and paste, I'm just going to use the fill right option, using the little square in the bottom right corner. It feels right. And all of the wages are calculated, let's double click here. You see it's using their proper range, it's number of hours. In this case, it's 30. But it's still referencing c four. And so now their overtime, I'm sorry, their

their regular pay work is all calculated correctly. Well, let's give this range a separate color as well. This is our regular pay. So I'm going to highlight this section here. Go back to home and choose a color from the bucket. Let's see it's time I like green, green for pay. Let's move to the right a little bit. Now we need to calculate their overtime for each week, their overtime pay. Well, we've done that for the first week. But we need to have some more weeks. So let's insert four new columns. Choose the insert command. Let's

put in a date for each of these columns. This one was January 1, this one's going to equal the first cell plus seven and then fill it to the right. And so we have all of the weeks for January. Once more, this is going to be a problem. If we don't have absolute cell referencing right now. The overtime pay is correct for the first week. If we fill this to the right, we're going to have a problem, we're going to have numbers that are way too high, like this one. If I double click here, you

see it's referencing overtime hours. But way back at the beginning, it is referencing not the wage that we're expecting. It's referencing cell D four, so we're going to have to change this formula to use absolute cell referencing again. So I'm going to Clear these highlight and choose clear contents from the menu. I'm going to double click on this formula. And instead of C four, I'm going to reference it as dollar sign c four. And I'm going to copy this formula all the way through the block. Here's another way to copy and paste through the entire

block. I'm going to copy just one cell and then highlight the entire place where I want to use this cell and choose Paste and all of the formulas now show the overtime bonus formula for each week. Okay, the last thing we should probably do is give this its own color. So let's use the overtime pay as a color such as I don't know what Pick something red, something blue, how about blue, blue is looking good. Now for the total, what in the world is a total gonna look like? First of all, I need to zoom

out a bit. Let's go to zoom to 50% total wages. Well, the total wages right now is calculated using, it looks like pay plus the overtime bonus looks great. Let's put in a date over this, This is going to be January 1. And once more we need to calculate equals this cell plus seven. And we're going to use a few more weeks of this. Now this time, we should be able to get away with using relative cell referencing five, highlight this and I'm feeling to the right. I'm going to double click on that cell titles

so that they all adjust to the proper width. Let's check this one out. If I double click on this cell, what's it adding together looks like it's adding the pay from the first note from the second week, times the overtime bonus for the second week. That's exactly what we want it let's see a try another one here, I'm going to pick this one randomly chose one cell, it looks like it's heading the proper cell. So in this case, we don't want absolute cell referencing the usual default settings for relative referencing work just fine. Let's add

one more color. And we'll call it a day. Let's go to a darker gray. One more thing that you might want to do is use the formulas across the bottom. I'm going to actually highlight this section here. Fill it all the way to the right. We calculate the totals the maximums the minimums for every week. One more thing you might want to add here is the total pay for all weeks. Let's say January pay. And I'm going to put in this equals sum, formula equals sum, and then add up all four of these. five of

these with a closed parentheses. Let's zoom in a little bit so you can see better. Double click again. So we're adding up all five weeks that were paid in January. And then fill this down for everybody. You can see now who is the top pay earner in the company, I'm going to copy this range, paste it over here. The maximum person is this one, earn $8,000 this month that looks like this line right here. Second from the last employee, let's scroll to the left and see who that is second to the last employee. The name

is Trent man. Now when you print, you're going to have a difficult time fitting all of this on one page. Fortunately, Excel gives us a nice way to print all things on one sheet. Let's go to the File menu and choose Print. Now down here, it says no scaling, we are actually using five different Sheets of paper to get everything printed. It's going to be very wasteful, don't do that. First of all, we can turn the paper sideways. So let's change the orientation to landscape. That helps a little bit now it's only four pages wide.

It says scaling here. We can say this, we can fit all of the columns on one page or fit all the sheets in one page. Let's try that. Okay, it's very small. It seems to work though. Let's save it and print it as this size. The next lesson we're going to do is called grade book. And you can see that the grade book is not just a simple grade book, but we're going to show what's called conditional formatting. That's what those little colored dots are. They're going to show who are the top students in your

class and who are the bottom. We're going to do percentiles, as you can see on the right side. And so several things about a grade book and formatting data that we haven't done before and you'll have a very Nice looking chart when you're finished. What you see on the screen in front of you is a grade book for employees, We're going to give them a series of tests for the company, a safety test, a company philosophy tests, a financial skills test and a drug test. And then we will give them employment based on their testing

level. So let's assume these are all new employees. And they are testing after their job interview. So this is the completed spreadsheet. Let's start from scratch and build it one place at a time. So I'm going to file and choose New and a blank workbook. Let's start by giving this a title and the first cell, let's call it grade book. And we're going to keep people's names in here. So we'll put last name here and first name. Now you can see that I've typed in the names of the employees that are used from my payroll

spreadsheet, you can just copy and paste these names in or if you'd like to re type different names, you can, but we're going to use up to line number 20, for those that are going to be taking the company test. Now you notice that I put the titles of each test in C one, D one, e one and f1. The first test is called safety test. The next one is called the company philosophy test. The third one is the financial skills test. And then finally, the drug test. Notice that these words all run into each

other. Let's do a format on these cells to make them fit better. I'm highlighting all four cells. And up here on this button. It's called orientation. I click it. And let's choose rotate text up. And now, each of these cells is written vertically. So it allows us to make the columns narrower. Now, how much is each test worth? Well, let's fit in here and B two, and let's put in here, points possible. That'll show us what the maximum grade is for each of these. Let's say the first one is worth 10. The next test is

worth 20 points, the financial test is worth 100. And the drug tests we're just going to have as a pass or fail. So it is worth one point. Notice that I need to make the column E a little bit wider because 100 doesn't fit in the square very well. Now, all we have to do next is starting with john Kern is invent a number, how many points did john earn and so on? Now, you can see that I've entered numbers for every one of my Employees. So the maximum is 10. Nobody has more than 10

points do they owe Trent man got an 11. But then over on this side where it says drug test, you notice that everybody has a one or a zero. So there were two employees that got zeros, that means they failed their drug test. Next, let's calculate the percent that they earned for each of these tests. I want to take the titles and copy them. So I'm highlighting all four of these, right click on one of them and choose Copy. And let's put them in the next area over how about column H. We'll start with column

H. Now what would be the formula for a test and the percentages? Well, you would take the equals, and we'll take this number 10 and divide it by the points possible divided by 10 and press enter. This turns out to be a one. Actually I wanted to say 1%. So I'm going back to the ribbon and choosing the Home tab. And you'll find in here the formatting for percentages. Here's a percent sign. So on the safety test, john Kern earned 100%. Now I'd like to copy and paste this formula so that All the percents are

shown for this assignment. Let's just fill it down and see what happens. Now there's a problem. This one says divide by zero error. This one says 80%. This one's is 90 90%. And this one says 100%. It should say 90, so it's not working like we thought it would. Let's double click on this 100% do you see what's being divided, it says take C seven and divided by C five. Really what we want to do is take C seven the score that Wendy received and divided by the points possible. And so this error is what's

called a relative referencing error. It's counting back two cells and dividing by two cells above it. We need to use absolute referencing here to get the correct results. So I'm going to clear these and try again going to right click and choose clear contents. What I really want is to take equals the cell here of the points that john earned and divided by this number with an adjustment I want to absolutely choose row number two every time. So I'm going to put $1 sign in front of two. And so now when I fill down, it's

going to always reference row two for all of these assignments. And so Indeed, we get the correct answers, there is 110% listed here. That is correct, because Trent earned in 11 points. Now, I should be able to just copy and paste these cells, or use the fill command and calculate all of these numbers, immediately. Notice the drug test is either 100% or 0%. And so now even though the tests are all worth different points, we scored each of them with percentages. Now, it would be nice to tell at a glance which of these students are

which of these employees is doing extremely well and which ones are failing. Let's highlight a set of test scores. Let's do the first one here. And I'm going to do conditional formatting. Conditional Formatting will color the cells according to the numbers that are inside. So on my ribbon, I look for conditional formatting. And in this case, I'm choosing icon sets. This set here is like a set of traffic lights, it's got red, yellow, and green, and then a black one. Automatically, it puts an icon based on who's in the top set percentages and who's in

the bottom. So you can see that the green Lights show up immediately, with the top test scores. There's a red light for somebody that's failing, and then there's a black one for an absolute miserable score of five. Let's try that with the next row. Let's highlight the next set. And we'll choose conditional formatting, and icons and choose the traffic lights again, and make the box a little bit wider. So you can see once again, that Karen is having difficulty she received a six on the company philosophy test. You have to do these all individually, because

each one of them has its own set of scores. If you try to format them all at the same time, you'll get different results. Choose the traffic lights, again, for our financial skills test, and see who sorts to the top. This time you notice right away there's a black line, or a black.on blessing, she's not so good at financial skills. And for the drug test. The last one, it will format it again. Under icons and feel free to experiment with the others. There's color scales, and there's data bars, interesting things you can see on each

of them. We're just using icons for these. And so now we have little traffic lights, showing us quickly who is doing well, you can see there's four green lights for john, are some people that have mixed results in between. Now let's also make a rule that we want to sort out people that receive less than 50% on any test. So a quick way to find out who that is, would be to highlight all of the test scores and their percentages. Let's go back to conditional formatting. Now here's a nice option, the first one called highlight

cell rules. Let's choose the one that says less than. So in this in, we want to find out who is receiving less than and let's put in 50%. So I can put point five in here. And then the options here are light red filled with a dark red text, you can choose different options, but we'll just leave it as the first and click OK. And right away, you can see that at a glance, there are some people that have problems, less than 50%. For Karen, less than 50% on a drug test means you failed it

you got zero, less than 50% on financial. And so we have a few of these people that are problematic. Now since I have a concern about who should be fired, I'd like to create a another line called fire employee with a question mark. And then we're going to say should we fire them or not? We're going to use a formula that asks this question. Are any of these scores less than 50%? If so, then we should fire them, you should at least pass with 50%. So the formula we're looking for is called the or formula.

he typed the equal sign and type or, or in a parentheses. This means that we're going to ask a series of logical questions is some number less than another one? And if so, then we're going to return true. Watch this, I'm going to say or is this score here the safety test. Less than 1.5 and I'll put a comma. And then I'll ask another question. Is I for less than 50%? Another comma? Is this less than point five? And then finally, the question is, is, comma is this one, less than point five. So I have

four questions in a row, all of them asked the same thing is this number less than point five. And if I press enter, It says false. None of these scores are less than point five. However, when I fill down, you will see that some of these scores are less than point five. Here's a true and a true and a true. Once more, we can see from this side from this line, Who should we fire? Who should we dismiss? Let's do a conditional formatting on this one. Let's go highlight the cells choose conditional formatting. And this

time, I'm going to ask the question is equal to is this question equal to true, I can spell true correctly. and press Enter. And so now all the ones that are true are highlighted in red. So we know who we should fire. Lastly, let's put some numbers at the bottom of the chart. Let's go down to here and use our famous for max min, average. Let's just use three max min and average. So in this cell, we ask equals this is going to be the maximum of the range that's above it. So maximum of all

of these scores with a parentheses. And let's do the same for minimum equals min, parentheses. And we'll ask about all of these Parentheses, what is the average score equals average, and then the cell range. And then we can use the fill right? option, show what the averages is for each of these. We're going to copy all these and put them on the other side, where the percentages are. Now since these are percentages, it would make sense to format them with a percent sign. Let's create a chart that shows all the graphs of each of these

scores from the safety test. I'm going to highlight safety tests. And now let's go to insert. And let's choose a chart. Here's called a column chart, the column chart, we'll just click it, and we'll choose clustered columns. Slide it over to the side. Now we're missing some things on it, we need a chart title, double click where it says title, we can change it to call it safety tests. We also need to know who are the people taking the test, we just have numbers at the bottom. So it'd be nice to change that. After we

have the graph on the screen, it would be nice to show the actual names of the people as well as the numbers. Or instead of the numbers right here, We just have employee numbers at the bottom. let's right click on the graph, and choose Select Data. Now when it says here, horizontal axis labels, let's change that, edit that. Now it's asking us what is the label range, it's asking us for a range of cells, we can either type it in or we can just simply go over here and click on Kern and drag down to

Underhill. You'll notice as I do that, it's telling me that from sheet one, the range a four to a 20 is going to be used. And click OK. And click OK again. And so now you see all the people's names at the bottom. So we've created a test, graph the safety test. Let's do another graph. Let's do the company philosophy tests. So highlight the scores. Go up to the Insert button or the insert options. And let's choose another chart. Another column chart and slide it down below this one. Let's give it a new title. Let's

call it the company philosophy tests. Once more, we need to add these labels in so that they are in the names instead Of the numbers. So let's choose Select Data where it says axis labels, we'll edit that. And we need to slide over to select the people's names. So from under Hill, up to Kern. And okay. And Okay. Let's close this menu here. And you can see the company philosophy test. One more graph, the financial skills test. Let's create that one. Same process, insert the charts and choose a column chart. Give it a name. And

let's give the names at the bottom something instead of numbers. And let's slide this one in place below the other two. Okay, that brings us to the finish of this of this gradebook test. Let's save our document and print it once more when you print. Be careful that you don't print on 1000s of pieces of paper that says we're using six. So maybe I'm exaggerating. Let's change a few things. Let's orientate it, so it's sideways. Let's see if we can fit this to one one page. Once you have one page listed here, you can see

all the graphs neatly arranged and your data on the left side, you're ready to print. In this lesson called decision maker, we're going to create a scenario where you're supposed to pick a job. And we're going to weigh different factors based on your opinion. So pay the amount of jobs that are out there in the job market, how much you enjoy it, how reliable the job is to you and various factors. And then based on your opinions, Excel will give you an answer on what career you should choose. Welcome to excel assignment number five. This

is a decision chart that we're going to create using Microsoft Excel. Let's assume that you're going to choose a career based on several factors, we're going to consider several jobs here on the column A, and several factors that would lead you to decide to go into that field such as your pay in that job, the job market, which would mean the likelihood that you'll be hired the enjoyment factor How well do you like this job, your talents, how well are you at doing the job. And then finally, another factor might be schooling, how much time

is invested to get to this career. And so we'll create a chart that will lead you to a decision that probably is one of the most important in your life is which career Are you going to work at. So let's start from scratch, I'm going to choose a new workbook, a blank one. And let's start by giving it a title. Let's call it career decisions. Now down here, we're going to call this the job column. And you invent some jobs that you think you might do. So I've placed a few jobs here in column A,

from McDonald's manager to an NFL player. Now let's have a large variety of types of things that you could possibly choose, you might have different ones. Now let's put in the factors that would lead you to decide on one of these, obviously, pay is an important factor. I've placed other factors into the chart as well. Not only pay but job market, the enjoyment your talents and your schooling that's required. So now, under the column pay, let's assume that a five or some number large is the best that you can do in the career and a

one is the worst. So a McDonald's manager that's put him down at near the bottom and doctors probably doing pretty well. Let's give him a for NFL it's as soon as the best it's a five engineers probably Pretty good as a three and a truck driver might be a three as well. And then for the others job market. Likely you probably can get a job there. A doctor once you get your school you're probably guaranteed a job. NFL Probably the worst of the factors because hardly anyone gets that gets to that level. Engineers, they've got

very good job prospects and truck drivers apparently are doing well as well as well. How well would you enjoy doing the job, maybe McDonald's, not so much, doctor if that's your passion. So make up some numbers based on your own preferences. And so I filled in each of these categories based on what I think about each of the each of the each of the factors. Now, it would be simple justice to sum up the lines here. So I'm just going to put su m, and then add up all of these together. And then we'll fill

down to see what kind of results we get. So this is telling me that number 19 is the highest, I should choose engineering to be my job. However, there's more to the decision than just adding up all of these factors. This assumes that Every factor is equal, such as the amount of pay you get is just as likely to influence you as the job market. Well, in fact, job market might be more important, it's nice to have a high paying job. But if you can't get it, then maybe job factor is a more important decision

than you thought it was. Let's add some new columns. In between each of these categories. We're going to insert a new columns. So I'm clicking on the title, the letters at the top the column headers, and right clicking there and choosing insert. What I want to do next is add what I would call an importance factor for each of these categories. For instance, job market, I would put that as the highest of the factors, if I can't find a job, then there's no sense in going into it, employment or enjoyment, I should probably enjoy it,

I'm going to give it as a for my talents. So I think I can learn whatever it is. So I'm going to say that's not as important schooling, it's not important, I'm willing to do as much school is I have to, and then pay I'm going to count that as a medium factor. Now for each of these, let's take a formula to multiply the relative importance, so equals the relative importance of this times the factor for each career. Now this is going to require a absolute reference. So where it says dollar sign see for I'm

going to put $1 sign in front of the four, because I'm going to reference this number in the blue every single time after I fill down. So filling down here shows now the importance of the NFL 15 is somewhat moderated now, it's got less of importance than maybe the job market would have. So let's copy this section here. Let's paste it into the next area and paste it into the next area. And continue on. Now let's readjust this when I do the final tally, I just want to say equals and I'm going to add up

each of these factors. So I'm putting a plus sign between each cell. And then I will fill down in the last row to give it a title and call it total. So once more 65 shows up as the most important of the jobs at least according to my criteria, a 65. One more final touch might be to color code each of these areas so that they stand out distinctly one from another. Now looking at the last the totals, let's do a conditional formatting on these numbers. So highlighting the numbers, I'm going to choose the top

10% that'll show basically the first highest ranked item. And so 65% 65 is the highest number in the list at shows which one comes out on top. Engineering, by the way, is what the computer decision chart recommends for me. Let's see what you have prints your work, put your name on it. And you have a decision now of what your career will be. In this lesson called sales report, we're going to summarize a large amount of data we're going to have lots of different sales items will calculate who the best sales people are in our

department and create this pie chart when we're done. We're going to use some data from some sales figures to do data. work in Microsoft Excel. What you see on the screen in front of you is a report from perhaps the company's Database. on sales for a store, you can see that there's a lot of columns and a lot of rules. So you don't have to type all these things in, I'll provide you with the spreadsheets so that you can just manipulate what's already there. So you can see each title is at the top and row

number one, there's a few blanks that we're going to use formulas for. And then let's scroll to the bottom and you will see that has, I think it's 172. Lowe's opens a sales transaction. So all the way to December here, you can see the sales of each item in our pool supply store. And a little bit of data about each one. The store cost and column E is how much the item cost wholesale. Sale Price is what we sold it for. And then we need to calculate a few items such as the profit, the commission,

and then we'll do some reports on each salesperson to find out who the best salesperson is in our store. Well, let's do some of the formulas here that we will have to encounter when we work with sales data. Over here in column L, I've put some notes on the formulas that we're going to use and some of the techniques that you'll see in this lesson. Text to columns, which we'll use to split these names, you can see that there's a first and last name and column I we want to split them into separate columns. The

second function is the if formula. You've seen that before. The next function is called the sum if, which means you can pick certain areas or certain items to add together based on a criteria that you choose. This is a database really more than a spreadsheet. And in databases, we do a lot of sorting, and filtering, a new concept that you'll see as a pivot table, which will give you a summary of like the number of sales that each employee makes. And then finally, we'll review charting by making a pie chart. Let's start by making the

title up here in row one more readable. Right now they're all compacted together. So let's highlight these. And let's go to text wrapping. text wrapping allows us to see each one of these in its full text. So now you can see in column B, C, and each that there's more text that we didn't see before. Let's start with the item called text to columns, what we would like to do Is to split these columns so that they have first name in one column and a last name in the other. So to do that, we're going

to need to insert a new column to give it some space. So right click on column header j, and choose Insert. And so now we have a blank column to work with. Let's go to column I. And we're going to the Data tab here, and then choosing text to columns. What this will do, it will allow the computer to parse the data that's in column I, there's two options if you use fixed width. That means the first column might have five characters, or seven or whatever you want to choose. But in our case, we want

to split it based on the space that's between these names. So I'm going to choose delimited. When you select delimited, it says what are your delimiters a delimiter is a divider. And so if you select any of these items here, a tab, a semi colon a comma or space, it will automatically divide the words on what you choose above. So I'm choosing space. And so a space character between the words gives us two columns. Now let's change these headers now since they make more sense to say first name, And then last name for each of

the salespeople. And so we've added a column. Now, the next item is to calculate how much profit was sold. For each of these items. For instance, transaction 1001 was a pool cover. The product code is something in our inventory that we just use is based on the maybe the manufacturer's code. But how much profit do you make if you sell the item for $98 and it costs you $58 to buy it from your supplier. It's a simple formula to say equals this square here, which is F to subtract E to and press Enter. And you

can see that we made $40.10. Now you might have to format that using the key right here under the home button to get the dollar sign. Now how about commission? How much money are we going to give to Charlie Barnes for selling this pool cover? Well, here's the rule above 10% commission for items less than $50 by If he sells an item that sells for more than $50, we'll give him 20% of the profits. So let's go and make a formula using the if command equals, if my rule says, if this sale price, I'll click

here is greater than $50. Comma, then let's give him more than More profit than the less. So the rule says give him 20% of the sales. Let's make it 20% of the profits actually. So let's take the profit and multiply shift an EIGHT and a decimal two, so that's 20%. But if it's less than or equal to 50, comma, then let's take the profits and multiply by point one, or 10%. So that's the rule that tells us the commission is based on the value of the item that was sold per center. So the commission for

this item is 20%. It costs more than $50. So we give him 20% of the profits of the store. So Charlie burns earn $8.02, which is 20% of the profit on this item. Now, let's just highlight these two squares, and fill down so I'm going to grab the little corner and drag down. That's a little bit hard to fill down when you have 172 rows. So here's another way to fill down. I'm going to hold the shift key on my keyboard, after selecting the first two rows, and then slide to the bottom of the spreadsheet.

Now I'm going to hold the shift key keep holding the Shift key and click at the last area, you notice that the whole zone or this whole region Is selected, I'm sliding back to the top and sliding back to the bottom. Now once I have the region selected, where I want to fill these items, I'm going to look for an AI command called fill down. If I click on home, way over on the right, you will see an item called fill. If I click here, I have down right up and left is my options I

want to fill down, I click here, it automatically fills the entire range. So sometimes that's quicker than trying to fill it down using this little item in the corner. So I'm going to click to unselect the range. Now you can see in some items such as this one gallon of muriatic acid, it costs the company or cost the customer $7. The profit for the store was $3. And Doug Smith earned 10% on that sale, so less than $50 item, you get less commission. scroll back to the top. Now the next item that we're going to

look at is called sum if some if allows you to add together a range of items based on a condition. Let me show you something more specific. Let's go to the bottom. And you can see that I have a few formulas here, Three different sums, I want to do the sum of all items, the sum of all the items that are valued more than $50. And the sum of items valued at $50 or less. So let's go to the cost here. Column F, which is the profit or that that is the cost to the customer.

The easiest formula is just the sum formula. So I'm going to type equals sum. Now what's the range, I'm going to type the range this time since the cells are so so many, we're going to type the letter F and two, and a colon and then the word the letter F 172. And you can see on the screen, that there's a blue rectangle surrounding the range, close the item with the parentheses and press enter. So in this year, the store sales were $17,110. Now what my question is, is how many of those items were valued

at $50 or more? What is the sum of all those items? The new formula that we use now is called sum if So type equals sum if and that let's take a look at what I can put in here. It's called a range and a criteria. So the range is once again f two colon f 172. That gives me this the range of all the sales and the price to the customer. Now a comma, the criteria, I'm going to have to put a quotation mark and then are greater than 50. Another quotation Mark, what that

will do is it will sum only the items that are greater than 50. So press enter, you can see that $16,088 is the sum of all items that are costing more than $50. Well, let's use the same formula. But this time, let's choose any items that are valued at 50 or less. So that is equals sum if the range will be the same f two colon, f 172, and a comma. Now this time, I'm going to put in my rule as less than or equal to 50. And quotation and a parentheses. So now I can

see that the vast majority of my sales are for items that are $50 or more, hopefully, these two items, these two cells add up to the entire sum. Okay, let's scroll back to the top and see what other items we have to do. two items that you do in most database work is with sorting, and filtering. So let's go see how that works. Go to the Data tab. And right here you see sort and filter, sorting and filter exactly how you would think it would work. Let's choose sorting first. What do we want to sort

by, we can sort by The first, we can sort by the last name of an employee. And click OK. And so now all the items have been rearranged so that the last name is alphabetized. So all the Barnes items come up first, and then Hernandez starts at row number 35. So we've sorted by column j, basically, you can resort again, and this time, choose a different item. Let's go back to sorting by the sale location. So if you're looking for all the items that are alphabetized, according to column K, you sort by sale location. And

so all the Arizona sales are first, then the letter C comes next New Mexico. And then finally, the last items that show up on the list are Utah. So sorting, let's see it one more time, I went to sort back to the transaction number, which was the original way that the spreadsheet was sorted. So the last item, and the first item now are back in order. The next item is filtering. If you want to filter some items to show only certain values, use the Next button filter, what are we going to filter. soon as you

click that button, all these Titles automatically have a little arrow next to them. So what happens if I choose one of them such as sales location, I can unclick certain items. If I only want to show one state such as New Mexico, I leave a checkmark there and click OK. And so now it looks like my spreadsheet is much shorter. It's only one screen full. All the items have this in common they have n m in this column K. However, don't be fooled, the other items are still there, they're just hidden. Look at the row

numbers, we start with to jump to eight 913 18. So there's lots of rows that are hidden. Just because we sorted or we filtered by the sales location. Let's go back and select all of them. Click ok. Try filtering by other columns. Let's filter by, let's say by the first name. Let's see, I only want to see Helen. I'm looking at her sales. And now I see just Helens results. Yeah, remember, the other items are still there. We just want to hide them temporarily. So that's filtering, sorting and filtering are useful when you're doing analysis

on a bunch of data like you have here. The next item that I would like to use is called the pivot tables. Pivot Table is a way to summarize a large group of data. So I'm going to select just the data that I'm going to work with here. I'm going to select from cell A, and highlight all the way down to the other corner of my spreadsheet. So I'm holding my mouse button down, moving all the way to the bottom to row number 172. It's important that I limit my my pivot table to only this

data I don't want to include the summary at the bottom, nor do I want to include anything that's over on the right in column M. So after I have selected all items, I go to the Insert tab and I'm choosing a pivot table. Pivot Table So up next says what columns are we going to use, you can see the selection here is from a one to K 172. That's what I've just selected, it's going to create a new worksheet. Up until now we've only used one worksheet, it's always been called sheet one, as you can

see at the bottom. But when I click OK, you will see now I have a new sheet. This one says sheet three, yours probably says sheet two. Now what are we going to put in a pivot table? First of all, in a pivot table, you think of what You're going to add together or make a summary of? Well, all I care about now is the sales figures for each of my salespeople. So I'm going to select last name, and the sale price of each item. You notice that we have a little summary here, Barnes sold

$6,000. When I click those, the computer guessed what I wanted to do. It says I'm going to use the rows of the last name and choose the sum of the sales item. There are other ways you can add these together, you can count them, you can average them put the maximum. Most often, you use some, you can experiment with other items, you can drag these around and get strange results. You can do filtering. There's a lot to do with pivot tables. But for all our purposes today is I want to leave this as the sum

of the values and the row labels as the last name of each employee. Let's format these as dollars figures because that's what they are. And now you can see that the best salesperson in my store is Barnes. Smith comes in a close second, Hernandez needs some help. To show this visually, we can highlight these numbers and create a chart. Let's go to insert insert. And a pie chart would be an appropriate type of sales figure here. So I like three dimensional pie charts. Let's put this next to the other. And you can see the last

name of each salesperson and the percentages of the total sales. You might want to show some data on this graph as well. You can right click this and you can choose Add Data Labels. So you can click here. And it will show the actual number of sales that each employee earned. Adjust the colors and the formatting to your style. Now the last thing we should do is print to worksheets. So let's put your name up here. And then print this page. When you go to print, make sure that it fits on one sheet. So I

choose Print. And you can see in the preview that I have two pieces of paper here, it's not really necessary to have two. Let's make this a horizontal landscape. And let's condense this to one page. And click Print. In this lesson called card database, we're trying to get some more advanced features of Excel, you can see that we have literally a database of lots of cars, We're going to find out how many miles they each worked. We're going to do some formulas with text. So we can combine two fields together and split them apart. And

we're going to do some averages, and create some charts as well. So let's get started with a car database. Welcome to excel assignment number seven. This is a database of all the company cars that you own in your business. This is a rather extensive spreadsheet. So we're going to split the lesson into three parts. You can see the lesson contents on the screen. First of all we're going to use importing to create a text file into a spreadsheet. The second is we'll introduce three new formulas left, middle and right. We'll use v lookup formulas to

create value out of a table. We'll review the if formula. We'll use the concatenate formula to put cells together, we'll work on a pivot table. And lastly, we'll import our documents into Microsoft Word. Your company fleet manager might have a computer system that does tracking and he's asking you to do some analysis now on the cars that are in your company. Usually when a person gives You a database, you have a few options. You can modify it in a database programs such as access, or perhaps what you're more familiar with is with Excel. And so

we're going to use a database aspect of how Excel works today. You'll see that they have the spreadsheet on the screen shows the cars that we own in the company with some IDs some miles their cars and years and their makes their numbers the principal driver and so we're going to do several steps. With database functions using Excel. First of all, you won't get a spreadsheet. When you ask for inventories, usually what you get is a text file. And so what I have on the desktop here is a spreadsheet in the form of a text

file. If I double click this, it looks ugly, you see that it is not in a spreadsheet format, at least not yet. This is what you get from reports from sales data from your Amazon account, or your insurance company or your bank. These formats are called text formats, they don't have any, they don't have any spreadsheet, graphs, they don't have any colors. It's just straight text. But Excel knows how to work with these very well, you notice all these commas here. These are all separators, they show the different columns that will come in the spreadsheet.

So I'll show you how to work with this. Now I'm going to close this first and start Excel. Now instead of starting by working with the file itself, we have to import it. So I'm going to the word file. And I'm going to open a document. And I'm going to choose this text file, I have to go find it first. So I click on computer. And let's browse. And let's navigate to the desktop. That's where I have this file saved. And it doesn't show up. Why not? Well, remember, the extension on a txt file is

txt. And Excel is just looking for spreadsheets right now. So I'm going to change the filter here to show all files on the desktop. And then scroll through it again. And there it is. There's the car inventory. txt. Now when I open it, it's going to ask me some questions. How do I handle this? What do I do? Are these like, text files that are equal sized cells each? Or are they delimited? Well, these are all separated by commas, as you can see in the preview. And so we're going to Stick with the delimited idea.

Choose next. And how are they delimited? Is the next question. Are they separated by tabs, semi colons, commas, spaces or something else? Well, these are all separated by commas. And as soon as I click comma, you notice all these columns seem to line up as they are intended. And so that looks like I'm on the right track. I'll click Finish. And so now I have the start of my spreadsheet. Each comma creates a separate column in the spreadsheet, let's take you through some steps here, we're going to have to follow this pretty closely. There's no

room for creativity on this assignment, just follow exactly how it's presented here. First of all, let's change the columns so that the column headers so that they can show the entire title. So we'll use text wrapping, the first thing I would like to do is introduce some new formulas that are able to handle text, you can take pieces of a field and create new fields. For instance, whoever invented this car ID for the company was trying to squeeze as much information into the ID as possible. And so we used a code like fd for Ford

06 for the year, M T, G, four Mustang. And 001 is the car number. That's not normally recommended to try to squeeze data into a field like this, but that's what he's doing. So we need to come up with a few ideas on how to separate these fields. Let's go to where it says make. I want to take the first two letters off of each of these data items. So the first two letters is going to be using a formula called left. And if I chose if I choose that, you notice the options are. First

of all choose text. Well, I'm going to choose this as my text. And the next after this comma is the number of letters. Well, in my database, it looks like the first two letters are the manufacturers names. So let's use a close parentheses. And sure enough, you can see that it's just slicing off the first two letters of the database. Well, let's fill this down and see what we have here. All the way to the bottom, how many are there looks like 53. And so you can see we have two letters for each of the

make. Now what do those mean? Let's Let's Okay, let's create a new formula. And we're going to put in column C. The manufacturers name I'd like to put in just the word Ford. Or down here General Motors t y must stand for Toyota a Jo is Honda car is Chrysler and ah y is Hyundai. We're going to introduce a new function here called a lookup table. Let's take these items here, like CR and h, y and t, y, and h, o, and GM, and f, d. These are all our manufacturers. And let's actually put the

real name here. Okay, you can see that I've created a small table that coordinates these abbreviations with the full name. To make this work, though, I have to have these in alphabetical order. So I'm going to highlight these, just these cells, not the whole table, and sort them. So I look for the data command, and sort. It says, What do you want to sort by I want to sort by the first column, and click OK. If you don't put them in alphabetical order, this next process is not going to work. Now that I've got these

charts here, I'm going to look them up. I'm going to look up f d in the chart and put in the word Ford, The computer function to do that is called equals v lookup. That means vertical lookup, it's going to look through a table vertically, look up the words f d, or the letters f, d, and then return the second column, which is Ford. So let's type in the whole command here lookup. And let's follow all these options through First of all, the first item it's looking for is the lookup value. So fd is the

lookup value, put a comma. And then the next item says table array, the table is the little set of data at the bottom, you're going to scroll down this table, and then a comma. Then it says column index number. That means which column in this table contains the real word that you're looking for not the abbreviation but the real word. So the second column contains Chrysler, Ford General Motors. So I'm going to type a two up here and a comma, and then actually, this is the only option that we need, we can close the parentheses

Now press Enter, and scroll to the top notice it says forward. Looks like it's working. Be careful, there's still one error with this. But we'll discover that in a moment. If I fill this down, you're going to notice there's a relative reference error problem. everything stops working after the third row. It has this na, which means not available or not accessible. And so what's going on? Well, if I double click on the second item, you'll see that this table is looking at rows 57 through 62. And you notice down here that it's been shifted down

one, that should send an alert to your mind to say, Oh, the computer is using relative references instead of absolute references. So if I go back to Ford, and double click this, I'm going to modify this. So I'm going to put in $1 sign in front of 56 and $1 sign in front of 61. So that means that it's always going to use those row numbers, every time I look up a value. And now it looks like it's creating the results I want. So let's go all the way to the bottom. And you see that

we've created a new field in our database that shows the manufacturer's name. Now the model is the second item. While the model is found here, this is the the letters empty G. So we can just use the left command like we did for the manufacturer. So we're going to use a new item called mid command. So in this case, we have an a Ford Mustang, empty g stands for Mustang. So let's, let's pull out some letters. Well, to pull out the manufacturers make we use the first two letters and so we use the left command.

Well, we want to pull something out of the middle of the text. So this one is called the mid command and my D. It says here that it's going to return the characters from the middle of a text string, given a starting position and the length. So let's put a parentheses. The first item that it's looking for is the text. So we'll click here. And then a comma, the start number where does this m start? That's it position 12345. So it starts at position five and the string and then a comma. How many characters do

we need? Well, these all have three characters in each The model numbers. Okay, close parentheses and Enter. And now we see MTG. So let's fill these down a ways and you can see that it's pulling out other items. FCS must stand for Ford Focus, and then a gender General Motors si MRSLV. Let's see what some of these others are. Scrolling all the way to the bottom and filling down, you can see that we have some of these others. Now the meaning of all these models can be interpreted as follows mt G is for Mustang. FSC

is for focus. CMR is a Camaro SLV is a Silverado, Toyota Camry and a Toyota Corolla are next. And let's see a civic looks like a Honda Civic, Honda Odyssey. And what's next here, a PT Cruiser, Christ's their caravan, and then the Honda Elantra. So these are all different models. So at the bottom here, we were going to do the exact same process of creating a lookup table. So I'm going to copy down all of these manufacturer model names, and then put the real name next to them. Okay, I'm finished with typing in all of

these car models and their full name. Remember, a V lookup table only works if it's alphabetized. So I'm going to highlight these squares, choose sort, and sort by the first column, which is D. Click OK. And so now the this line is in alphabetical order. Once we have that done, we can create a lookup value. So I'm going to Use the exact same lookup as my model. I'm going to copy this and paste it over here. It doesn't seem to be working right? It says, A lantra is the first item. What's going on, I'll double

click here to find out. We're looking for D 56 through e 61. Now that's almost there. The only problem is, this table is longer. So I'm going to extend this one all the way to the bottom, and press enter and go back to the top. Now it looks like it's finding Mustang correctly. Since the other one was created with the absolute references, we have the dollar signs in the in the top, it seems to be working right, so we'll fill down and we'll have the model name for each of these cars. Almost there. Let's open

this a little light wider. We have an error down here what's going on. This one says not accessible or not available. What's wrong with this, we have a 00 D, somebody in their stupidity, typed in a zero instead of an O back at the beginning here. So let's fix that. Up here at the top, we have the NA h o and then the year We have an extra zero in there. So let's delete that zero and press Enter. And now it appears that that typo was fixed. So when you get text from another company, sometimes

you have to realize that people didn't enter the data correctly. Lastly, let's put in the manufacturer year. This is going to use the mid command again, we're going to pull out the year from this square. But a comma, it's going to start at position three. And we're going to take two text items, two letters, and we get 06. That one seems a little simpler. So we'll scroll that down. And now we'll pull out just the year, a two digit year for each of these cars. Now we need a formula to calculate the age. How old

is our car? Well, let's put in an equal sign. What would you do? Given that I'm creating this spreadsheet in the year 2014. I'm just going to take 14 and subtract the years that come before and press Enter. And it says eight. So a 2006 car is eight years old. seems to work. Let's see what happens when I feel down. Most of the time it works except for here. What's this says there's a problem with it. Now if you look closely at this, this is an O six. It's literally an O six. We want 06.

So once more, somebody thought that it was an O six, and in the database, they put a o letter O in here. So let's delete that Oh, and put in a zero. And so that fixes that value. The same happened here. Let's go back to this row 15. I'm deleting the Oh, and I'm re typing a zero. Let's continue to fill down and see what other errors might show up. Oops, there's another one another value that says it cannot be read. So let's go to here. Ah, oh, and then a 05. So we've had to

do some corrections to our data. Now, this formula is very simple. It's actually too simple. This car is negative 84 years old. Where did that come from? Well, that was called the y2k bug where computers just held two digits for every year, it worked until we switched over to the year 2000. So a 98 is actually 16 years old. And it gives us a negative 84. That's because it's taking the formula 14 minus the value in cell B nine f 19. Well, it's a correct number according to the computer, But it doesn't serve our purposes,

we want to make sure that the number comes out correctly. So let's readjust our formula. Let's erase this one. And I'm going to ask a question, if if the difference between 14 minus this gets less than zero, then I want a formula for all those 90s and 80s. Cars. Well, I just simply say I'll take 100 minus the year. And that'll get us the distance between 98 to 2000, that would be about two. And then I'm going to add 14 to get to our current value. Otherwise, we'll just use the previous formula, we'll say 14

minus this here. And so now we have two different cases, for those that are less than 2000. And those that are greater than 2000. If I press enter, I still get the same results for the first. And let's scroll to the bottom and see what happens. And so sometimes, a simple calculation needs to have a few cases. So yeah, this is correct. Now, a 1999 car in the year 2014 is 15 years old. So the formula seems to work. Now let's go over to this formula, miles per year. Well, that should be pretty simple. least

at first glance, we want to say this equals the total miles divided by the years driven, which is the age of the car. And so that shows 55,000 miles. Let's fill this down. And let's see if it works. Almost all of them work. There's a few errors. There's an error, it says divide by zero. Why in the world are we getting that error? That's because over here in the age, it says this car is zero years old. It was made in 2014. Is it really zero years old? Well, it's less than a year old. But

it's not zero. So it's probably six months old, maybe three months old. Let's make a slight adjustment to our formula. If I double click on this formula, you'll see that I'm dividing eight, the age divided by the miles. Well, the age and some of these was zero, we can't really divide by zero. And this car really is not exactly eight years old, it might be eight years and six months, because a car that is zero years old really has a few months already on it. So let's just round a little bit. Let's add a little

bit of a of a number. Well, let's see, we want To add something to G two. So I'm going to have to put a parentheses before g two and add point five let's add let's add a decimal point five. So that that year is really a year and six months. Why parentheses? Well, you should know from your order of operations in your algebra class that you're supposed to add and subtract after you divide and multiply. And so we want to make sure that we add first and then divide. So that's why the parentheses are there.

If I press Enter And then fill this formula down, we should get better results. So the mileage calculation is based on the age plus six months. And we have no divide by zero errors. Let's format these two columns, they could probably use some commas to make them look more like miles, it's easier to see when you have 1000s. If you have a comma in there, let's move, let's keep moving. The color of this is black, and white, green, etc. That's fine. We'll use that in a few minutes. When we get to the new car ID.

The principal driver shows the last name of each employee. How many warranty miles are there on this car? This is a 50,000 mile warranty. And this car is just barely under the warranty, it still has 10,000 to go. Well, the question is, is this covered under warranty, that's the formula we're going to do next. And so we'll do an IF calculation. If if the miles are less than or equal to the warranty, and yes, it is covered. And so I'm just going to put the letter Y for covered a comma. So if it's not covered,

I'm going to write the word not covered. And then a quotation and a parentheses. So now this says yes, it is covered, I get a why. If I fill down, I will see a different value for each of these cars. And some are covered and some are not covered under the warranty. The last item that we're going to fill in here is called the new car ID, your boss gives you this requirement. He says take the original ID. And I want you to squeeze in three more letters in between the manufacturer and this last three

digit number. And I want to I want to know what color is the car. So once more, this is a bad idea to try to squeeze as much information into one field. But your boss Said so. So it's the truth. Let's do it. How do we how do we how do we combine fields, the formula you're looking for is called concatenate co n. And then you'll see concatenate shows up here. I click it, double click the format that you're supposed to fill in is simply says text one, text two, etc. So we're going to concatenate

a bunch of fields, we want to put first of all continue using f d, comma, and then we want to have the manufacturers year comes, as you can see, that comes second in the list. And then we want to continue on with the model. And then a comma, and now your boss said put in the colors. So we want to put in black, we're going to adjust that because he only wants the first three letters of the word. So we're going to delete this two squares, I want to know just the left three letters of

black. So I left left three of j two, so j two comma three, and a close parentheses and a comma. So we've just added the left three letters of black. And now what we need is the last three from here. So we use the formula right Click here, comma, three, double close parentheses and press enter. So now we have the new car ID. One more change that we need to make is that this shows lowercase letters. Your boss didn't tell you but he forgot to say all IDs in cars have uppercase letters only. Now there's

a nice formula to put in there. If I want to isolate these letters and change them to uppercase, the formula they're looking for is called upper. So I put a parentheses around the part that I want to be uppercase and press Enter. And now the new car ID shows bl a as black. I'm filling down and now we have a new car ID. Okay, so those are a lot of different database functions that you can use to manipulate text. The next item in our checklist of things that we're going to see on this assignment is

called a pivot table. a pivot table allows you to summarize data. For instance, your boss might ask of these drivers Who has the greatest amount of miles. There's some formulas you could work with. But here is one of them. It's called a pivot table, I'm going to insert, and under the insert command, you see pivot table. It says, What's my range that I'm going to work with, and it automatically selects the entire spreadsheet. Click ok. a pivot table creates a new sheet down here, you notice this one said car inventory. And this sheet is called

sheet one, I'm going to select the driver first. put a checkmark next to him. And then I want to know about the miles on the car. So I click on miles, automatically, the computer assumes that I want the sum of the miles and the driver. And so now we have a chart that shows each driver in the number of miles that he created. Well, you could also put in a chart and create a list of all the drivers and their miles. And so Smith jumps out right away from our graph. Whoever Smith is he's driving

a lot. Let's go back to our car inventory and see what Smith is up to Smith, what kind of car does he have, oh, here's a Ford Mustang. No wonder he's driving so much, I would drive a lot to have somebody gave me a Mustang. And so Smith shows his miles As the most of anyone in this series. Another type of graph that we haven't worked with yet is called a scatter chart. A scatter chart allows you to put a specific data points on a graph. So I'm going to select here, the age of the

car, and the miles, just these two columns, I'll click on their column headers, and select the entire column. I'm going to insert a new chart. And the chart that I'm looking for here has a bunch of points on it. It's called a scatter chart. And as soon as you select it, you can see what it's doing. It's pointing each of these graphs, each one of these dots on the graph shows that the years across the bottom are showing from zero years up to looks like about 18 is the oldest car and then the number of

miles each car has. So it almost looks like a straight line. Well, there is something called a trend line that we're looking for. So I'm clicking on the plus sign up here. And I'm selecting trend line. And now there's a dotted line that goes up and down through the middle of these dots, we could probably make these access titles a little bit easier to read. So I'm going To select those as well. deleting the word access title and putting in the word miles, miles driven. And then down here on this axis, I'm changing this to

the age of the car, age of the car, and then in parentheses, years. And so let's Park this off to the side. And slide over. So now we have a chart that shows the miles in our inventory. Some of these are outliers. Some of these are right on the line. one more way that we could do some analysis is we could find out which cars are being driven more than others. So let's select column I. Let's go to conditional formatting. Let's try out something called the color scales. It's pick one of these color scales, there's

blue and green and red doesn't matter which one you choose. But when you pick one, you will see that some colors are highlighted in darker and lighter colors, they show you the extremes. So like this 35,000 shows up as one of the highest. Let's sort this spreadsheet based on the miles driven per year. The first thing we have to do is select just the range where our data is stored. We're going to Ignore these lookup tables at the bottom. So I'm highlighting all the way down to row 53. And then going to the Data tab

and choosing sort. Let's sort by column. What column is this column I and let's go from the largest to the smallest and click OK. And so you can see that this car here this particular car has 35,000 miles per year. On average. It only has six months and the guy has already driven at 17,000 miles. We scroll to the bottom you'll see that the minimum Drive driven car is this Ford Mustang here, it's eight years old and it has 40,000 miles on it. The last item I'd like to do is to create a report. So

let's go back to Microsoft Word. And we're going to copy and paste a few of these items from our spreadsheet, the title of a report is going to have our name on it. Next, I'm simply going to put the top drivers by miles. And then I'm going to go back to excel. Go back to my sheet, copy this graph, so I'm going to right click it, choose Copy. And you can see you can go back to word right click it and choose Paste. And so these two programs integrate with each other. Press Enter a couple

of times. And I'm going to show the next is the scatter chart for the car inventory. So it says the scatter chart for the car inventory miles on each car. Now I'm going back to excel. And I'm going to copy this graph here that has the scatter chart. So right click on it, choose Copy, go back to word, right click, and choose Paste. And so I have two charts that came from my graph. let's print these and call that our final assignment. One more thing when you go to save a document that in Excel that

was created using a txt file, you can see up here it says car inventory. txt. When you go to save that, you're going to get an error message. It says you're still in the tab formatted text. Do you want to keep using that format, you should say no, we want to save this using a new format. Instead of tab delimited, or text delimited, we're just going to choose Excel workbook. And now all those pretty blue and all the formula and all the other things that were created in the charts, they will Stay with your spreadsheet,

a regular tab or text delimited file cannot possibly save these items. Okay, in Microsoft Word. Let's go File and print. And we'll call our document done. In this section, we're going to talk about problem solving. And so you can see that Susan in front of us has a problem. She wants to take a vacation and wants to spend as little amount as possible. And so our options are to go on a Caribbean cruise, go to Orlando, or go to Chicago. Each one of these requires flights, car rentals, and hotels admission fees. And so Excel is

a great tool to figure out which one of these would be the least expensive. And so I will give you a partial solution. But that by this point in the course though, you're supposed to be able to solve some of these problems on your own. And so it will stretch your thinking and reinforce the skills that you've learned before. So problem solving is going to start with a simple problem. And then gradually, I'll give you several until you get to the most complex problem. When you reach the end, you will be considered a person that

can use Excel To solve real problems in your personal life and in your job as well. Hi, in this video, I'm going to show you how to calculate interest on a simple interest payment plan for four loans with different interest rates and then make a graph. So here's the final product that we're going to arrive at at the end of this video. And so you'll see that we have a $10,000 loan with four different interest rates. And then we have monthly payments at the right column. Let's start up a new spreadsheet. And then we're going

to put in the data labels. So the first thing I'm going to do is fill out the column titles for our loan. So the first column is principal, the interest rate, the months that we have to pay over the total interest paid the number of dollars that the total loan will be and then the monthly payments. In the first column, let's put in labels here for loan A, B, C and D. I'd like to make sure that these column headers are correct. So let's double click on the little line that's between each of these dividers.

And that will separate the column to be exactly the right width for the label. So let's zoom in and let's check out what our payments will be. So let's make up a principal number for our loan. Let's say we're buying a car and I'm going to spend $10,000 that I don't have. So I enter in 10,000. And you can see that the numbers don't quite fit now. So let's expand the column a little bit. And if you don't have dollar signs, you can go up to the accounting area and you can change this to $1

sign if you want. Now the interest rate if I put in a number here, let's say 9% and I use the percent sign It will calculate the number correctly. However, if you don't put in there 9%, you can put in 0.09, which is the decimal equivalent of 9%. And then if you choose the percent sign, it will show it correctly. Now the number of months, let's say I have a 12 month loan, and so I put in a 12. So these three columns here are going to be numbers that we enter, and then the last

three columns are going to be the formulas that help us solve our problem. So let's start with the formula interest paid. So that's a formula where we type in equals, We want to take the principal, which is B two, and I will multiply by C two, which is the rate. So you can either type in equals b two, and use the star key, which is the shift eight. And that's for multiply. And then finally, type in z two, C two, and press Enter. And that will tell us that the total amount of interest is $900,

over the price of the loan. So then the next column is the number of dollars paid total. So we have to pay back our money. Let's go with equals, and we'll click the 10,000, then we put in a plus sign, because we have to add the number of dollars interests that we're going to pay as well. And so that's 900, and press enter. So the total cost of your car will be $10,900. Now you're going to pay this over the life of the loan, so we're going to take this total amount and divide it by

12. So we'll take divide by the number of months that we just agreed to pay. So that's f two, divided by E two, and press enter. So your monthly payment for this car is $908. Now you can adjust this quite easily if you want to change something. So let's click in here and let's say I have a $20,000 loan for my car. And you can See the payment automatically is recalculated. Now you're shopping between different banks, and you want to see what the competitive rates are. So let's take our 20,000. And I'm going to select

and fill down. So let's see I'm selecting this whole bracket of looks like 12 different cells, I'm using the Ctrl key and D on the keyboard. And that is for fill down. Now I want to calculate on different rates. So one bank here decided to give me an 8% loan. Another one is seven and even 6%. So I shopped around, and I found four different rates. Now the number of dollars that I pay is equal and the number of months that I pay is equal. Now let's take the last three columns, and we're going to

fill down so I'm going to select those three columns, and drag from the small dots in the corner. And this will calculate my interest based on the 12 month payment. So let's see here, the interest rate is better for the 6%. And then the total monthly payment is slightly lower. Now I'd like to visually present this. So let's select the total monthly payment, and we'll make a bar chart out of it. So let's see. Let's go To the Insert tab here. And I'm going to select a chart for bar chart. Let's choose this first one.

Now there's my chart. And let's see, I'll zoom out slightly. Let's put in a chart title. And I'm going to call this monthly payments for $10,000. All right now at the bottom, you can see that I have these 1234. I would like to compare the interest rates, that's the only thing that changes in here is the 9876. So let's do a right click on the chart here and choose Select Data. In the column here where it says axis labels. I'm going to edit this and it says please give me a range. So I can just

simply select from the chart, here's 876789. And then it puts in the correct columns are c two to C five. And that is exactly what I'm looking for. Click OK. And then it says here, this is what I'm going to display now, which is 987 6%. And click OK. And there you got your chart. My name is shed Sluiter, and my youtube channel is called tech Made Simple. I'm a university professor that teaches programming, computer software development and security, all kinds of technology, including what you just saw here with Microsoft Excel. Welcome to excel problem

solvers. This is a set of examples of how you can use Excel in solving real world problems, problems that are too complex to solve with a regular calculator, or to solve in your head. So we're going to look at Susan and Tim. Every time we get a new page in this booklet, we're going to see that they have a different task. And so here's what we're going to do for each page. First of all, we'll listen to Tim and Susan and tell us about their problem. We'll build a spreadsheet with proper formulas. Now I'm going

to show you a partial solution. For each of these solutions, we're going to create a spreadsheet with a little bit of a design. But I'm going to assume that you've already done some Excel work and so that you can finish this with a little bit of collaboration with your friends and a little bit of thought. And then finally, every one of these solutions will have a graph, it'll be a bar chart that will show a comparison. So let's see what these different problems are. The first problem we're going to look at is a school shopping

list. And so Susan has a lot of things to buy. And she has Three different stores to shop that Tim also has a shopping list that's slightly different. The second problem we'll face is, should we buy a cat or dog. And so we'll look at all the different costs associated with buying each of those. The next problem that Susan faces that she wants to take a vacation. And so we're going to give three different options either a Caribbean cruise, Orlando or Chicago, Tim's going to do the same vacation, but he has a different number of

people. The next problem is that they're going to purchase printers. And so there's three different kinds of printers to choose from. And the cost of ink for each is different. Number five is more complicated yet, we're going to untangle the cell phone bill. Number six is we're going to choose from three different cars, a very economical car and a luxury. In this first Excel problem, we're going to go on a shopping trip. And so we have a shopping list. Susan says, I'm Susan, would you help me pick one of these three stores to buy my

school supplies. Here's my shopping list. And so to the right of the shopping list, you can see all the items that Susan would like to buy. Her choices are Walmart, dollar trap, and office repo. Each price is listed below. And then her number of items that she would like to buy are listed on her shopping list. So for instance, three ballpoint pens. at Walmart, it's going to cost two or three times 50 cents, or $1.50. Now this is a large complicated shopping list with many items. So this is a perfect way to solve a problem

using an Excel spreadsheet. Inside the Excel spreadsheet that we're going to create, we need to add a list of all of the items in the shopping list, and then a column for each store. And so pause the video right now and type these in just as you see on the screen. Next, let's go back to the shopping list. Each price listed at each store has to be put into the spreadsheet, and then put them into this column under Walmart. Pause this video until you have the numbers typed in. Now you might say these look like

dollars. So let's make $1 sign each of them. Open the currency, add $1 sign. Let's add the prices for dollar trap and also for office repo. So pause the video until you have all the prices listed for each of the stores. Now I'm going to help you a little bit further with the problem, but not completely. Let's start by looking at Susan's shopping list for each item for the ballpoint pen. We know how many she needs such as three. And so I copy down the number of items that she needs. Now, how would you figure

out the amount of money that she would spend on ballpoint pens. Let's put that formula right here. ballpoint pens, you know that she's going to spend $1.50 if she shops at Walmart, three times 50. Here are some hints for the formula. In this square, which is G three, we're going to multiply the quantity times the price. So enter your formula once you figure that out. After you figure out the first formula, you'll be able to fill down and calculate all the rest of the prices as well. At the very end, let's put in the total.

And so you'll know what the total cost is for purchasing supplies at Walmart. Repeat this same process for another column, we're going to figure out the price for The entire shopping list the dollar trap, and then we'll figure out the price again. And obviously, once you finish, you should have three total prices listed down here. After you add up all of these numbers close. The last thing you'll need to do is put a chart on the graph. And so copy the labels from row two down to the bottom. And when you find the totals like

I've mentioned here, you can highlight the entire two rows, create your chart and it will be labeled correctly. Now for the other fellow, we're going to have to make a another chart so copy everything you get from Susan and then you could probably move it over to column K or so and paste all of the information Just change the shopping list so that Tim's list will be hidden and shown instead of Susan's, then you'll be finished Good luck. In this video, in Excel problem solvers, we're going to look at a problem that Susan and Tim

are facing. They want to purchase a dog or a cat. And so let's see what Susan says. She says, Hi, I'm Susan, would you help me choose a pet. I love both cats and Dogs equally. But I would like to spend as little money as possible each year. compare these expenses in a spreadsheet and show me your answer and create a graph. First of all, notice that she's talking about a year. And so at the bottom down here, when it says hints, it says use a spreadsheet to calculate the total cost of ownership for owning

a pet for one year. So we'll just assume one year is the length of our expenses, pets, adoptions, dogs or 50, cats or 90. All expenses include all of the vaccinations, spaying and neutering. However, there are more expenses than just buying the cat or the dog. First of all, this is a list of what we consider necessary accessories. The cat has three items, the dog is four, so you have to buy this one time. The second box shows the ongoing expenses, a box of cat food, kitty litter, and the small print says buy two of

each of these for my pet each month. And so we'll double that expense. Each month. The dog has some expenses to his dog food is more money, and his dog treats her Also an expense. Here's a good pattern for setting up the spreadsheet, you can see that I have two boxes, and I colored these boxes. So that way you can distinguish that there's a section called initial costs. So these are just one time costs. And these are monthly costs. Then we have a column for the dog, and a column for the cat. When we're done,

we're going to have a total of how much it costs to keep the dog for the first year. So we'll be adding up all the numbers above it. What are the initial costs, while the purchase price for the dog, we have to buy a collar a taggable leash, and then the initial total will be the sum of everything above it, the cat will have some of these expenses. I think the cat will have a zero here because it doesn't need a leash. For the monthly expenses, remember, there's going to be food litter and treats. So

these will be subtotal. And then we're going to multiply by two because we need to have a monthly total, each one of those items was bought twice a month. For the one year costs, we're going to have to take 12 times the monthly total, And then add it to the initial. So think through how each of these costs need to be added in to get an accurate price for a dog and an accurate price for a cat. When we're done, we will highlight these two lines, create a chart and see you'll have a bar chart

that shows the cost of a dog compared to the cost of a cat. So there's a good outline for you remember, this is just a partial solution that I'm offering you. You have to come up with the formulas and use the numbers to get an accurate result. Welcome to the next lesson of Excel problem solvers. This is number three, where we're going to take Susan and Tim and ask them what they want to buy. We'll evaluate this with a spreadsheet, and then hopefully come up with a graph. So Susan and Tim are both going on

vacation, and they're going to explore either Caribbean cruise, Orlando, or Chicago. First of all, she says I'm Susan, would you help me pick one of these three vacations, my husband and I like all three vacation ideas, but we want to spend as little as possible. We will fly stay five nights and four days. Tell me which one of these will cost the least. And so we're going to build a spreadsheet to answer that question, which one of these will cost the least? Let's look at the details of what kind of vacations they are. The Caribbean

cruise is the simplest, it's $555 with almost everything included. The only thing that does not include it says here is airfare. So we have to buy plane tickets to get to Miami. Orlando has theme parks. And so they're going to spend four days and look at each theme park. So for her, it's going to cost twice the price of each ticket. They're not going to rent a car. It says in the small print. They will spend money on a hotel and they estimate $50 per person on food. Each day. Lastly, we have Chicago. Chicago is

a city of museums. And so they're going to go visit the natural history, the Museum of Art, the Science Museum and the Broadcast history museum. And so each of these prices is per person. In Chicago, they have to rent a car, their hotel is a little bit more money, and they estimate about $50 per day for food. And this box over here, we show the costs of the airlines. So Miami, Orlando, and Chicago are all different costs, and those are per person. Now let's look at how we would build a spreadsheet. First of all, we

have a column for each city. So we have Chicago, Orlando, and Miami, at the bottom of the page, we're eventually going to have a total for all of these cities. So we'll find out the answer down here. And then we'll make a graph. I've divided the costs up into per person expenses. So if you have more people in your family per person expenses will go up. And then hotel expenses, assuming that all people stay in the same room, it will cost the same for two people as it will for four. And so the hotel is

more of a fixed cost per night rather than per person. Now for some of these, we won't have a number. For instance, if we look at Chicago, we will have no money for Disneyland, so we can just put zero in there. Also for like the museum, the Science Museum, we will not have a science museum cost in Orlando, Some of these costs will be just zero, it appears that there's something missing. I have hotels and tickets, but there is also car rentals. So you might want to include another car rental expense, somewhere down the bottom,

and then add that into the subtotal. So calculate all of the subtotals per person expenses, hotel expenses, car expenses, add them together, compare the three cities and give Susan her answer with a bar graph. Once you have finished with Susan, then you can use her work to copy and paste a new chart, a new bar graph, and you'll have Tim's answers as well. Remember, he has two children, so his per person expenses will be higher. Good luck and helping Tim and Susan pick their vacation. Welcome to excel problem solvers. So Susan, and Tim, both would

like to buy a new printer, Susan says would you help me pick one of these three printers, I plan to print about 15 pages each day, so that if days per week, I want the total cost to be as low as possible, I expect the printer to last two years, you'll see that the epsilon printer is only $29. And so you might think that Is the best price at the beginning. Until you look at the supplies, you'll see that ink is $40 a cartridge. And it will add up quite a bit. Tim, on the other

hand has a different request. He said I'm Tim would you help me pick one of these three printers, I plan to print about 500 pages each day. And so his printer will probably be different than Susan's. Let's look at the fine print. First of all, you'll notice that each cartridge on the epsilon will only print 200 sheets. While at the other end, the zero printer will print 11,000 pages for a cartridge. And so the initial price on the printer, even though it's high, may end up being less expensive per page. Here's a suggestion for how

to set up your spreadsheet. We'll do columns B, C, and D for each of the printers, we'll put in a line for their initial purchase price. And then we'll put the yellow section for the cost of their print per page. And that's important because even though inkjet cartridges are less than LaserJet cartridges, they don't print nearly as many pages. And then for each of these people will have to Compute their expected pages per day. So Susan is only expecting 15 per day. This is the number she will actually print per year. And so you'll need

a formula to calculate that number. Once we know the pages per year, we can estimate how much it will cost per year because we have a and then the total printing costs will come here at the bottom. That total printing cost added to the initial purchase cost will give you the actual cost for the two years of epsilon. And so your job is to figure out some of the formulas that would make these numbers make sense. Once you're done, you will highlight the final result. And you will create a chart that will show the differences

in each of these printers. Similar to the other problems. We're going to have a section for Susan, and then we'll copy the information for to the right side and we will compute Tim's numbers. And so you should have two charts when you're done and find out what the recommendation is for each of these people. Welcome to the last in this series of Excel problem solvers. On this video, Susan and Tim are asking us to help them purchase a car. Susan says, Would you help me pick one of these three cars, I want to spend as

little as possible, I want to drive the car until it has 250,000 miles, when I assume it will be nearly worthless. I drive about 30,000 miles every year. Thankfully, I have enough money to save so I don't have to borrow money and pay interest on a loan that would cost me an extra 40%. Let's see the three cars that Susan is considering. First of all, she has in mind the Chevy Spark. It's a small engine that gets good gas mileage, she has to pay 15 $100 a year in insurance. The initial cost for taxes is

1400 50. And every year she spends $210 for a license. Compare the other cars they have higher numbers for each of those. So the Ford Mustang is 31,000. The gas mileage is much lower, the insurance and taxes and license are all higher. Her third choice is the escalate. Certainly a luxury car with a price of 72,000. Low gas mileage, high insurance, high taxes and high license fees. Just how much more will that be than the other cars? We'll find out when we make the bar graph to compare the total cost of ownership for All of

these cars. Tim said he would also like to buy one of these three cars, he wants to spend as little as possible, he's going to drive the car until it has 250,000. He drives 30,000 miles a year. So everything so far is the same as Susan. However, the difference is he does not have enough money to purchase the car. And so to make things simple, we are just simply going to add 40% of the price of the car to his spreadsheet. So that will include all the interest in bank fees. Now let's look at the

spreadsheet to solve this problem. On the left side, we have Susan and all of the calculations that we need to come up with a price for which one is going to cost the most. We'll put the cars in column B, C and D spark Mustang in escalate. The yellow section at the top we'll talk about the initial costs. So this is the price that she pays the day she buys the car, she has to pay the original price plus taxes. The more complicated question is how much does it cost to own this car every year?

And there are three costs that we need to add together. We have insurance, license and gas. How much are we going to pay for gas? That's a more complicated question than just looking at the price at the pump. We have to know several things. And so we have a blue calculation area from row 12 to 16. First of all, we need to ask the question, how much does she drive? How many miles every year? What is the miles per gallon or the mpg for her car? How much is she paying for gas. And then we

will know how much she pays every year for gas. Add those three together, and we will have total annual costs insurance plus license plus the gas. Now the question is, how much is this car going to cost over the entire lifetime of the car? Well, first of all, we know how long she wants to drive she said 250,000 miles after which we will assume that the car has basically no value left and she can give it away to a friend. To find out how long the car will live. We have to ask how many miles

does she plan to drive each year? What is her goal, which is 250,000. And then we can use those two numbers to find out the total lifetime expectancy of the car. Finally, we will arrive at the bottom where we get the calculations For the annual cost of the car times the number of years that we expect the car to live. We add in the initial price of the costs. And we have a total lifetime cost of the car it might surprise you that you'll see the numbers up in the range of where you expect to

pay for a house down a little further. We will calculate the final answer in row 27 and 28. We will calculate the average cost of a year. So we know how long the car expects to go how many years it expects to go. We also know how many dollars we have total. And so now we can find out what each year will cost for the spark Mustang and escalate. highlight these last three numbers with their labels create a bar chart and you will give Susan some good information on how much more it will cost to

buy luxury cars. On the right side of the equation we will have Tim Tim is going to be calculating almost the same values except him doesn't have any money saved. And so the initial cost of his car is going to be 40% higher In what Susan paid, and so that 40% includes all the interest in bank fees. So everything else should be the Same. He's driving the same number of miles and he expects to go 250,000 before the end of life. And so when you're finished with Tim, you'll have a similar graph. But you might

find his expenses quite a bit higher than Susan because he doesn't have enough money to pay for the car in cash. Congratulations, you've made it to the end of the course if you've survived this long, you have gone from the beginning to an expert person in Excel. Now, I appreciate you spending the time. And if you're interested in these kinds of things, check out my channel on YouTube. My name is shad Sluiter. I'm a professor at Grand Canyon University in Phoenix, Arizona. I not only teach with Excel, but I teach programming so you can learn

how to build software, websites and mobile applications. So thanks for joining me, and best of luck with what you've learned here.