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.

Channel: freeCodeCamp.org

Views: 907,060

Rating: 4.9574895 out of 5

Keywords: google, sheets, excel, excel course, excel tutorial, excel for beginners, exsel

Id: Vl0H-qTclOg

Length: 146min 9sec (8769 seconds)

Published: Wed Oct 28 2020