Microsoft Excel Tutorial for Beginners - Full Course
Microsoft Excel Tutorial for Beginners - Full Course
Lean how to use Microsoft Excel from the beginning by creating 6 real-world projects. Most of the content applies to Google Sheets as well.
You will learn how to
- enter data,
- navigate through a spreadsheet,
- create formulas to solve problems,
- create charts and graphs,
- understand relative vs absolute references,
- import and export data,
- implement VLOOKUP,
- use pivot tables,
- split and concatenate text,
- and more.
✏️ Course developed by Shad Sluiter. Check out his YouTube channel: / shadsluiter
⭐️ Course Contents ⭐️
⌨️ (0:00:00) Intro
⌨️ (0:04:14) Payroll
⌨️ (0:39:35) Gradebook
⌨️ (0:57:22) Decision Factors
⌨️ (1:04:40) Sales Database
⌨️ (1:23:13) Car Inventory
⌨️ (1:59:29) Problem Solving Templates
⭐️ Special thanks to our Champion supporters! ⭐️
🏆 Loc Do
🏆 Joseph C
🏆 DeezMaster
—
Learn to code for free and get a developer job: https://www.freecodecamp.org
Read hundreds of articles on programming: https://freecodecamp.org/news
Content
0.32 -> Hi, and welcome to this course called Excel from
the beginning. In this course, we're going to
5.04 -> learn how to use Excel using six projects that you
can use in real life. So my name is shad Sluiter,
11.12 -> and I've been a teacher for computer science
and computer applications for several years,
16 -> what you're about to see are applications
that I've used in my classroom, and have
20.56 -> been successful with other students in the past.
So I really appreciate the opportunity that Free
26.24 -> Code Camp has given me to present this to you.
I have used Free Code Camp in my own classroom,
32.72 -> with success with students from high school to
the university level. And so we're going to see
38.32 -> part of what I've done in class. And I'm glad to
contribute back to help you as well. Let's take a
44.56 -> look at some of the projects that we'll build in
this course. So Excel from the beginning is going
48.72 -> to use six projects, the first one, and it'll be
the most basic will be to set up a payroll. So
53.92 -> imagine if you were a company and trying to pay
your employees and keep track of their overtime.
58.24 -> So that's what we'll do in this first project. The
second is to set up a gradebook. And so we'll be
64 -> doing computing on percentages, we'll be finding
who's in the top of our class and who's not.
68.88 -> Also we'll have a factor decision tree factoring
program. So we will try to decide what career
74.32 -> would be best based on what we prefer, what the
pay is and other benefits of a job. And so a
80 -> spreadsheet will help us determine how to make
a decision. Another application we'll create is
84.88 -> a sales database. And so I will give you a bunch
of data and we will sort that we will determine
89.92 -> who are the best salespeople what their commission
is, and make some charts, we'll also do what's
94.64 -> called a car inventory project where we'll create
what's called database actions, we're going to
99.76 -> have a large number of data again, and we can show
you how to concatenate fields, how to split them,
106.72 -> and how to make reports with this. And then
lastly, I'm going to reserve this section for
112 -> six different problems that we're going to solve.
So the first five projects are more tutorials to
118 -> show you how something is done. And then I'm going
to give you some challenge assignments at the end,
123.04 -> where I give you a half of a solution. And then
you use your creativity in what you've learned in
128.8 -> the previous courses to see if you can solve the
problem. And so both we have a tutorial section,
135.44 -> as well as a practice where you can put things
into service. Now let's take a look at some of
140.32 -> the things you'll learn throughout here. So some
of the terms, you'll learn from the very beginning
144.8 -> how to enter data, how to navigate through a
spreadsheet, and how to save it. Also, I'm going
149.76 -> to show you how to set up formulas because using
math formulas, simple formulas to solve problems,
155.6 -> such as how to compute overtime, or how to
compute the percent that a salesperson should
161.04 -> get on commission, or to simply do averages and to
find the maximums and minimums. So those are basic
167.6 -> formulas will create charts. So you'll have pie
charts and line charts, and you'll have scatter
172.64 -> charts, Excel is really easy and creating charts,
you basically have to choose what kind you want.
178.48 -> And it does it for you. Also, we're going to
do Excel with some more advanced features.
183.76 -> So relative and absolute references is used
if you have some kind of a factor that you
189.76 -> want to use and other parts of the spreadsheet.
And so that's another feature that we'll see.
194.56 -> We're going to do imports and exports of
data. So CSV is a common file you find on
200.16 -> the internet. So maybe your bank statement comes
to you in the mail, or at least online, I guess.
205.44 -> And it says we produce these items in CSV
format as if you know what CSV format is.
212.32 -> So I'll show you how to use this. And so you can
interface with other programs on the internet,
216.88 -> we're using something called v lookup. So v
lookup is a powerful tool that is like searching
222.96 -> through a menu to find the right item for your
choice. Also, we're going to use pivot tables,
228.96 -> pivot tables are summary reports. So if you have
a large amount of data, and you want to condense
234.8 -> it to a very readable decision, this is kind of
a big data tool. So the last one is to split text
241.84 -> and concatenate text. And so you can modify
things according to how you want them to be.
248.08 -> So that's some of the features that were ahead
of us. If this looks interesting to you, then
251.92 -> please let's continue on with the first lesson.
So here's the first lesson, we're going to work
256.48 -> with something called payroll. And you can see
that we're going to set up a group of employees,
261.92 -> we're going to give them some fake numbers,
their hourly wage, and calculate once again,
268 -> with fake numbers, how many hours they worked,
then we're going to take those numbers and use
273.36 -> some simple formulas to fill in these columns
of data where we can see how much they get paid.
278.88 -> Also, I'll show you shortcuts so that large
numbers of columns and rows get rather tedious but
286 -> there are shortcuts in Excel to make it work fast.
So let's get started with our first lesson ever
291.84 -> an Excel so this particular lesson on Excel the
beginning parts of how to use Excel has been seen
298.24 -> over a million times. on my YouTube channel, so
welcome. If you haven't seen it before, this is a
303.68 -> great way to start using Excel, we're going to use
Excel to create a spreadsheet for our business.
310.72 -> We're going to launch the program and start
a blank workbook. Now since this is the first
315.28 -> assignment that we're going to do with Microsoft
Excel, we need to go through some of the things
319.2 -> that you see in any spreadsheet. First of all, the
spreadsheet is designed in organized according to
324.72 -> cells. Just like in the game battleship where you
have columns that are letters and rows that are
329.52 -> numbers, you can identify cells such as this one
by such as G six. To enter in information into a
337.6 -> cell, you simply click it and start typing.
And so I'm going to type employee payroll,
346.48 -> press Enter. And you notice that this cell,
even though it extends into column B, really,
353.44 -> it is only in a one. Now let's use Excel as
a payroll, we're going to keep track of the
360.48 -> hours that our employees worked this week. And
we'll keep track of their wages. So first of all,
366 -> we need to put some column headings
in. So I'm going to put in the title,
370.88 -> last name, and then first name. And then I'm
pressing tab between these keys so that it moves,
378.24 -> the selection moves to the right, hourly
wage. And then I'm going to put in the date,
384.16 -> let's say January 1, and press enter. Over here,
I'm going to say that this is the hours worked.
394.56 -> And this is their total pay. So I'll just put
the word pay. Now you notice you can expand a
400.64 -> column by clicking between the column headers like
between D and E. Now you can see the whole word.
409.04 -> The next thing you need to do is you need to
invent some names. So you get to come up with
414.08 -> about 20 names, maybe 15 for your employees.
I'll type them in now. Now you can see that
420.64 -> I've invented names, last names and first names.
And now I'm going to give them an hourly wage,
426.8 -> each person makes a route $15 or so. So I'll type
in some numbers here. You notice when you type in
434.08 -> numbers, they are right justified when you type
in the person's name in your spreadsheet, that
439.84 -> they are left justified. That's just a convention
that Microsoft Excel uses so that you can identify
445.76 -> letters versus numbers. Now one of the items that
you will most commonly use in Excel is formatting.
454.88 -> These are all hourly wages, I'm going to select
this range with my mouse by clicking and dragging.
461.6 -> The first square is actually highlighted even
though it's white, the other ones are gray. But
466.24 -> what I want to do is change them into a currency.
So I'm going to click on the dollar sign up here.
470.88 -> And you notice that they all become dollar signs.
They all are the hourly wage for each employee.
478.56 -> Now let's invent a number for each of
the employees for how long they worked.
482.96 -> The average workweek for a full time person is
about 40 hours. So I'll create wages or sorry,
490.4 -> I'll create the number of hours that
each person worked in this column.
496.24 -> After giving each employee a number of ours,
we're going to start working with formulas. Now,
502.08 -> over here on this line, this square in
e4, I'm going to create a calculated
507.76 -> number a calculated cell. If a person gets
$15.90 an hour, and they work for 40 hours.
516.64 -> How much do they get paid? Well, fortunately,
the calculations in Excel are quite easy to do.
522.32 -> on my keyboard, I'm going to press the
equal sign. And you notice up here in this
528.08 -> area called the formula bar, we have an equal
sign, as well as the equal sign in the cell.
534.64 -> Now I'm going to click on the cell that has
the wage in it 1590. When I click there,
541.68 -> the letter C four is entered into the
formula bar, c four is the wage $15.90.
550.48 -> Now I want to multiply 1590. So I'm going to press
the shift and the 80, which is the multiply sign.
558.64 -> And then I'm going to click the 40 the dot
the number of hours that this person worked.
564.96 -> So my formula is equals to c four times the
number that's in cell D four. When I press enter,
573.84 -> it'll tell me that this person gets $636. If
you go and change some of these numbers, such as
581.36 -> we added an hour here and I type 41 and press
enter. That calculation is automatically updated.
589.68 -> When you double click on a cell that has
a formula, you will see that the colors
594.16 -> correspond to the cells that you clicked.
So c four is in blue, which is this number
600.4 -> And D four is in red. In a press ENTER
again. And it shows the numbers again.
608.96 -> Microsoft Excel has a nice feature where you
can copy and paste formulas. If I right click
615.2 -> on this cell and choose Copy. And then I
click on the next cell and choose Paste,
622 -> it'll automatically calculate the next line. You
can also highlight a range and choose right click
630.88 -> and paste, it will fill down where all of those
numbers are, why is this one luckily look like
636.88 -> railroad tracks where a bunch of pound signs, it's
because the value is actually too large to be held
642.88 -> in this in this cell. So I need to make the column
a little bit wider. And now it becomes visible.
649.92 -> Another way to fill down if you want to, you can
go to the corner of a cell with this little green
656.08 -> dot. And if you click exactly on the corner and
drag your mouse down, it will also fill down the
663.28 -> values in that column. And so now I've quickly
calculated the pay for every single employee.
670.56 -> One more thing we're going to do with this formula
is we're going to add a few lines at the bottom.
677.44 -> I want to know who is the maximum, press enter,
who is the minimum, press Enter? What is the
684.16 -> average pay, and what is the total pay these
numbers max, min, average and total are just
691.52 -> labels. But over here in this column, I'm going
to put in a corresponding formula. Microsoft Excel
698.72 -> has hundreds of formulas that are predefined.
Here's one of them, you type in the equal sign.
705.04 -> And then you type the word MA X, you notice that
these automatically are drop down menus that show
712.32 -> that this is one of the formulas that Excel
knows about. After typing Max, I use the open
717.92 -> parentheses, which is the shift key and nine. And
I'm going to drag my mouse across a range of cells
727.2 -> till they come to the very
top, release the mouse button
731.76 -> and press Shift and zero to do a close
parentheses. So what this will do,
736.64 -> it will give me the maximum of all
of these cells, the maximum person
742.16 -> is getting $45 an hour. And so you can see in this
line here that Trent man is making 45 an hour. Now
750.48 -> I'd like to know who the minimum is. So I'm going
to do the same process type equals sign, am I N,
757.2 -> open parentheses and drag my mouse across a
range. And I will tell it what the minimum is.
765.76 -> After I press Shift and zero, enter, and the
lowly person in my office is getting $6.90. It
774.8 -> looks like it's Paul Smith. Now what's the average
employee? Well, I can type in equals and then Av.
782.32 -> And you see there's a bunch of formulas for
average, I just want this most simple one,
786.72 -> I'm going to choose average.
I'm going to select the range,
796.56 -> close parentheses, and press enter.
The average employee makes $16.48.
805.2 -> Okay, let's take this range here of
three different formulas, highlight it,
812.64 -> right click it with the mouse copy. I want
to click in the next column over right click
821.76 -> and pastes. And now you see that we have the
maximum, the minimum and the average for the
829.36 -> number of hours that are being worked. Now you
notice that these values are listed as dollars,
834.48 -> they're actually not dollars, they're number of
hours that the persons are working. So I want to
839.84 -> change these formattings back to general numbers.
Up here in the ribbon, where you will find the
847.68 -> number formatting area, there's a small arrow
that if you click it will bring up a dialog box
853.6 -> that shows all different types of formatting for
numbers. I'm going to select general and click OK.
863.28 -> Now, why does this number have so many decimal
places in it? It's because it's because we are
868.96 -> computing an average. It would be nice if
we would just round these off to maybe the
874.56 -> 10th the 10th place or the hundreds place. So
let's go to these zeros here. And we're going to
881.28 -> decrease the decimals that we're rounding to so
to the nearest 10th is good enough for us. So
888.24 -> now we have the number of hours maximum minimum
and average for our employees here in column D.
894.64 -> for column E, we could also find the min the
maximum the minimum and the average as well.
899.84 -> So let's highlight this group, I'm going to select
this bottom right corner, drag to the right.
907.68 -> And it fills to the right now and shows me that
these are the maximum dollars that someone made
913.44 -> the minimum dollars and the average. Once more,
these are dollars figures, these are not hours.
918.96 -> So let's change these to the dollars format. So
I'm going to click up here on the dollar sign.
926 -> And now we have the average the maximum and the
minimum in the salary for this for this week.
933.04 -> So now we've created the maximum the minimum and
the average for each of these columns, column C,
938.64 -> D, and E. We haven't done anything yet with a
total. For the total, let's find out what the
944.08 -> total number of hours worked was. And let's find
out what the total number of wages that we've paid
948.96 -> is. So for the word sum, we type equals su m,
and that will give us the total. Let's do shift
956.64 -> and nine. And then I'm going to select a range
here. I'm going to select all of the hours worked,
964.32 -> close parentheses and Enter. And you'll see
that there were 695 hours worked in my shop.
970.88 -> Now I'm going to fill this to the right, and shows
that I made a lot of more paid a lot of money
978.08 -> for my wages this week, I'm going to change
this to $1 sign format. And it shows now $11,532
988.08 -> The last thing we should do is put our name
at the top, so maybe c one is a good place,
993.2 -> type in your name, press enter, save the
spreadsheet, print it. And you're done
998.4 -> with assignment number one for Excel. Welcome
to assignment number two for Microsoft Excel,
1004.16 -> we're going to extend assignment number
one using the payroll spreadsheet that
1008.08 -> we had created earlier. What I'd like to
do in this assignment is add a new formula
1013.04 -> using the if formula, and also adding the
idea of paying overtime to our employees.
1019.76 -> So you notice here in column D that we have
the number of hours that each employee worked,
1024.24 -> the first employee worked 41 hours, we're going to
give him a bonus for his extra hour of overtime.
1030.08 -> So I'm going to start by inserting a column
here in column E. If I click the column header,
1036.16 -> the entire column is selected, I can right click
on the column header and choose the Insert button.
1043.12 -> And it will now give me a new column to work with.
The first column, column D was the hours worked.
1049.84 -> Now I'm going to say this is the overtime hours.
The overtime hours is a calculated field. So let's
1057.6 -> come up with a formula that will tell us how many
hours extra that this employee worked. At first,
1063.04 -> it would seem simple, we would just say equals the
number of hours the person worked, subtract 40.
1069.68 -> And that will tell us how many hours of overtime
this person has. It works great if you have 41.
1075.28 -> But there's some errors. If you have less than 40,
I'm going to fill the column down. And you will
1081.36 -> see that when we come to Paul Smith in row number
eight, he actually gets a negative one hours,
1088.16 -> it's a negative hours that he's worked, that
doesn't seem to work too well. What we would
1092.32 -> rather give is Paul should have zero hours of
overtime, not a negative number. So let's revise
1098.72 -> the formula a little bit. So let's go up to here.
And this time, I'm going to type in an equal sign
1104.72 -> with the word if if is a command that says let's
take a logical test, and we will a logical test is
1112.64 -> either an equal sign greater than or less than,
and then we'll give it a value depending on if
1117.68 -> that test is true or not. And if it's false, we'll
give it a different number. Watch how this works.
1124 -> The question is, if his hours worked,
is greater than 40. And a comma,
1132.32 -> the value if it is greater than 40
should be his hours worked minus 40.
1139.04 -> Comma, and if he worked less than 40 hours,
then let's just give him zero for this column,
1145.04 -> which would be zero hours of overtime, I
close parentheses and press rest press return.
1151.52 -> So he still gets one hour of overtime. But
now when I fill the formula down, you see that
1157.68 -> the people that worked less than 40 hours gets
zero in their form in their formula instead of
1163.12 -> negative numbers. And so this is the correct
formula for calculating overtime pay. Now,
1170.24 -> how about the pay? What does that do? Let's go
back and review this here. If I double click here,
1174.96 -> you will see that this formula takes the number
of hours hourly wage and fills it in column F.
1181.2 -> That's great, but now we give them a bonus for
working overtime. So let's create create a new
1186.16 -> column and call it overtime bonus. Now in this
formula, we're going to calculate a bonus for
1193.76 -> the number of overtime hours they worked. So I'm
going to say equals point five Use the time sign
1201.36 -> point five times their hourly wage, so we're going
to give them half of their hourly wage times the
1206.72 -> number of overtime hours they worked. What that
does is it gives us a formula to give them time
1212.56 -> and a half, or one and a half times for each hour
that they worked overtime, press enter. So since
1220.72 -> john Kern earned one hour of overtime, we give him
his full 41 hours of pay, plus an extra 50% of his
1230.32 -> pay for one of those hours. Let's fill this down
and see what we have. For the rest of the people.
1238 -> Some of the people have zero. This one got no
extra time bonus, because he worked 39 hours. Some
1246.16 -> of these people worked 40 hours and still got zero
time. Why? Because the formula is about how many
1252.96 -> hours beyond 40 that we're calculating. So they
got paid their full wage for the first 40 hours.
1260.24 -> Now, how about the total pay, let's put
in a new column, the total pay is simply
1267.44 -> equals to their regular 40 hours of pay
plus their overtime bonus and a return.
1275.52 -> And let's use the fill down option. Some of the
numbers are too big, so we will expand the column
1283.28 -> and this is their total pay. Down here at
the bottom, we calculated maximums minimums,
1289.28 -> averages and totals. For all of these
formulas. Let's move these to the right.
1295.44 -> Calculate the totals now and see we can see
what everybody earned including their overtime.
1302 -> Welcome to assignment number three with Microsoft
Excel. What you see on the screen before you is
1307.44 -> a final version of the payroll spreadsheet, you
notice that it's got a lot of cells, don't worry,
1314.56 -> most of these are copied and pasted. It's an
extension of assignment number one and two.
1321.36 -> In the gray area here where you see hours
worked. In the previous assignments, we
1326.48 -> only were concerned with one week of pay. And this
assignment, we're going to add four more weeks of
1334 -> pay. And we were you will use calculations to find
out how much they earned their overtime bonus,
1341.92 -> and then here in the blue area, their total
pay. So we'll start from an assignment here
1348.4 -> that we did before. And we'll simply extend
it to look like the one that you just saw.
1355.28 -> Well, first of all, what we need to do is add
some new columns. So starting in column E,
1361.6 -> I'm going to right click and choose
Insert and clicking on the column header.
1367.44 -> Let's do this a few times. And will give us some
space to work with for other weeks in the month.
1381.52 -> Now starting here in column E three, I'm going
to add seven days to this date. Notice the first
1388.08 -> date that we chose was January 1, you can either
type in January 8 for the next week, or we can
1394.4 -> use a calculation, let's use a calculation
I'm going to say this equals this square d3,
1402.64 -> plus seven. And now it starts at January 8. Now
if I use the fill command, I'm going to create
1413.12 -> several more columns. And
they're automatically calculated.
1421.04 -> Next, I'm going to invent some more
hours. These are numbers that should
1425.44 -> be approximately 40. They can be a little
less, they can be a little bit more.
1430.4 -> But it doesn't matter really what the numbers
are just so that we have some data to work with
1435.2 -> for each employee. Now we've reached
the end of our data for the number of
1441.52 -> hours for each employee. It looks like I've
created one extra column here in column II,
1446.72 -> so I'm simply going to remove it by choosing
right click on the column header and delete.
1456.16 -> Now let's go to the overtime hours. We're going
to put a date here for the week, January 1. And
1462.32 -> once again, I'm going to insert some columns. You
can actually insert columns more than one at a
1468.08 -> time. If you highlight four column row headers and
choose Insert, you get four new rows. Once again,
1476.16 -> I'd like to add seven to the date that's in
January 1, this plus seven and fill to the right.
1491.28 -> Now let's add some overtime hours. Now I want to
calculate the hours of overtime for each week.
1498.24 -> Well we've already done this in the previous
assignment with this famous if formula,
1502.96 -> if d four is greater than 40, then give us the
value of d four minus 40. Otherwise give us zero,
1510.16 -> I can take this entire block and copy it,
we're going to right click on it, choose Copy.
1519.6 -> And I'm going to right click on
the next square and choose Paste.
1523.92 -> So now it's calculating the overtime
hours for the week of January 8.
1528.56 -> Let's double click on this cell. And you notice
that we are now calculating the overtime hours
1535.44 -> from this blue square the 42 hours from January
8. So the formula automatically adjusted for the
1543.2 -> column letter. This is using cell e4. I'm pressing
escape now double clicking on this one. And you
1552.16 -> notice that this cell was using this cell D for
as its source for its numbers. So when you copy
1559.68 -> and paste, Excel automatically assumes that you
are relatively addressing columns somewhere else
1567.2 -> in the spreadsheet. So if this is one cell to the
right, then the next one is one cell to the right.
1575.04 -> I'm going to copy and paste again. So I'll
copy this row here. Copy and paste it.
1586.56 -> Copy and Paste again. And paste again. So now we
have overtime hours calculated for every week.
1600.4 -> Now when you start to get a lot of cells
on a spreadsheet, it can get confusing
1605.04 -> looking at so many numbers. So Excel allows
us to paint the cells in certain colors.
1612.16 -> These cells here that I'm highlighting now are all
related to the number of hours that they worked.
1618.32 -> Let's give them a color. Let's
paint them all something gray.
1624.4 -> So now you can see that they
all belong together as a block.
1628 -> For overtime hours, let's paint them
a different color. Let's choose,
1631.28 -> you can choose whatever color you like. But I'm
going to choose some kind of a salmon color.
1638.4 -> And now we're going on to calculate their pay.
1643.44 -> This pay here really is the pay for January 1. So
the week of January 1 is what they got paid here.
1651.28 -> Now I'm going to calculate the pay for every week.
So let's insert about how many three more columns.
1664.4 -> And let's do once again equals
the previous date plus seven.
1672.32 -> And then fill that formula to the right.
Looks like we need one more column.
1679.6 -> I'll fill this one to the right. Now, how do we
calculate pay, we simply took two numbers from
1689.44 -> their hours and the wage hours worked, which was
41 times their wage, we're going to find something
1700.88 -> new here. If we just simply copy and paste these
formulas, we're going to get the wrong result.
1707.2 -> I'll show you what happens in a minute. And a
copy this formula, paste it for the next week.
1715.68 -> Now why in the world is everyone
making over $1,000 some up to 3300.
1721.76 -> What happened? Well, let's double click on
this cell and find out where it's coming from.
1729.2 -> First of all, you notice is very difficult
to see the other side of the world here,
1733.84 -> we can actually zoom out on our spreadsheet.
1737.6 -> And we can make it easier to see the
whole page. So let's zoom out to 50%.
1745.84 -> We can see the whole spreadsheet now
but the numbers are a little small.
1750.08 -> Let's choose something else. I'm going
to select the part that I'm interested in
1755.12 -> just the cells. Click on zoom and choose fit
selection that will fit this highlighted area
1763.12 -> on the screen. So now I can see hourly
wage all the way to the last formula. Okay,
1769.6 -> now let's go look in this cell here. What's going
on in cell number Oh, four. It says take the
1778.24 -> cells D four and multiply by E four and give me
the results D four and e4. Remember great while
1785.04 -> we were in the previous week, we were taking the
hourly wage times the number of hours. Well now
1790.88 -> the relative referencing is saying well let's take
the first two cells and multiply them together.
1797.76 -> So we need to make a modification This actually
should be referencing c four, and pressing escape
1806.8 -> on the keyboard. And I'm going to delete all
of this here, I'm going to right click on these
1813.28 -> and choose clear contents. What I need is what's
called absolute cell referencing. So I'm going to
1820.16 -> modify my original formula here. It's telling me
in this formula, that we should use C four times
1828 -> d four. And I know that I'm going to copy it to
the right four times. Well, what I really want
1834.24 -> to do is keep referencing the hourly wage,
because that's how you calculate pay hourly
1839.2 -> wage. In C four times the number of hours worked,
which is going to be D, E, and F and G and H. So
1848.08 -> is there a way to tell Excel not to use relative
referencing instead absolute referencing, that's
1855.36 -> what we're going to call it anyway, I'm going to
go up to this formula bar and modify the letter C,
1862.24 -> I'm going to simply type in $1 sign in front of
it, that doesn't mean a value dollars, it just
1867.28 -> means that every time that you think about sales,
C, or C four, you're going to always use column C.
1875.44 -> For the first week, nothing changes, all of
the numbers should stay exactly the same.
1882.96 -> But now when I copy this range of cells,
and paste it into the next column,
1890.64 -> let's take a look at what this reference
is. I'm going to double click here,
1894.72 -> you notice that it is still referencing cell
C four. But now the other cell is relative
1902.24 -> referencing says let's move to the right one
every time. And so it's now multiplying the
1907.04 -> hourly wage times the hours from January 8, or
the 42. I'm pressing escape on the keyboard.
1915.92 -> Now I'm going to highlight this whole range. This
time, I'm going to instead of copy and paste,
1921.36 -> I'm just going to use the fill right option, using
the little square in the bottom right corner.
1927.36 -> It feels right. And all of the wages
are calculated, let's double click here.
1933.12 -> You see it's using their proper range, it's number
of hours. In this case, it's 30. But it's still
1938.4 -> referencing c four. And so now their overtime,
I'm sorry, their their regular pay work is all
1945.92 -> calculated correctly. Well, let's give this range
a separate color as well. This is our regular pay.
1953.28 -> So I'm going to highlight this section here.
Go back to home and choose a color from the
1960.8 -> bucket. Let's see it's time I like green, green
for pay. Let's move to the right a little bit.
1970.32 -> Now we need to calculate
their overtime for each week,
1974.16 -> their overtime pay. Well, we've
done that for the first week. But
1978.16 -> we need to have some more weeks. So let's insert
four new columns. Choose the insert command.
1987.76 -> Let's put in a date for each of these
columns. This one was January 1,
1992.8 -> this one's going to equal the first cell
plus seven and then fill it to the right.
2003.04 -> And so we have all of the weeks for January.
2006.8 -> Once more, this is going to be a problem. If we
don't have absolute cell referencing right now.
2014.8 -> The overtime pay is correct for the first week. If
we fill this to the right, we're going to have a
2022 -> problem, we're going to have numbers that are way
too high, like this one. If I double click here,
2030.8 -> you see it's referencing overtime hours. But
way back at the beginning, it is referencing
2036.72 -> not the wage that we're expecting. It's
referencing cell D four, so we're going to
2042.24 -> have to change this formula to use absolute
cell referencing again. So I'm going to
2048.72 -> clear these highlight and choose
clear contents from the menu.
2054.48 -> I'm going to double click on this formula. And
instead of C four, I'm going to reference it
2060 -> as dollar sign c four. And I'm going to copy
this formula all the way through the block.
2067.84 -> Here's another way to copy and paste through the
entire block. I'm going to copy just one cell and
2073.84 -> then highlight the entire place where I
want to use this cell and choose Paste
2081.44 -> and all of the formulas now show the
overtime bonus formula for each week.
2090.32 -> Okay, the last thing we should probably do is
give this its own color. So let's use the overtime
2095.68 -> pay as a color such as I don't know what Pick
something red, something blue, how about blue,
2103.84 -> blue is looking good. Now for the total,
what in the world is a total gonna look like?
2111.76 -> First of all, I need to zoom out
a bit. Let's go to zoom to 50%
2122.4 -> total wages. Well, the total wages right
now is calculated using, it looks like
2131.12 -> pay plus the overtime bonus looks
great. Let's put in a date over this,
2138.72 -> this is going to be January 1. And once more we
need to calculate equals this cell plus seven.
2148.48 -> And we're going to use a few more weeks of this.
Now this time, we should be able to get away
2157.36 -> with using relative cell referencing five,
highlight this and I'm feeling to the right.
2167.2 -> I'm going to double click on that cell titles
so that they all adjust to the proper width.
2176.32 -> Let's check this one out. If I double click
on this cell, what's it adding together
2180.8 -> looks like it's adding the pay from the first
note from the second week, times the overtime
2186.48 -> bonus for the second week. That's exactly what
we want it let's see a try another one here,
2190.96 -> I'm going to pick this one randomly chose one
cell, it looks like it's heading the proper
2196.72 -> cell. So in this case, we don't want absolute
cell referencing the usual default settings for
2204.48 -> relative referencing work just fine. Let's add one
more color. And we'll call it a day. Let's go to
2213.12 -> a darker gray. One more thing that you might
want to do is use the formulas across the bottom.
2222.32 -> I'm going to actually highlight this section
here. Fill it all the way to the right.
2231.84 -> We calculate the totals the maximums
the minimums for every week.
2243.36 -> One more thing you might want to add here is the
total pay for all weeks. Let's say January pay.
2252.24 -> And I'm going to put in this equals sum, formula
equals sum, and then add up all four of these.
2264 -> five of these with a closed parentheses. Let's
zoom in a little bit so you can see better.
2275.04 -> Double click again. So we're adding up
all five weeks that were paid in January.
2281.76 -> And then fill this down for everybody. You can
see now who is the top pay earner in the company,
2292.16 -> I'm going to copy this range, paste it
over here. The maximum person is this one,
2301.92 -> earn $8,000 this month that looks like this line
right here. Second from the last employee, let's
2308.56 -> scroll to the left and see who that is second
to the last employee. The name is Trent man.
2316.72 -> Now when you print, you're going to have a
difficult time fitting all of this on one page.
2321.92 -> Fortunately, Excel gives us a nice way to print
all things on one sheet. Let's go to the File menu
2332.64 -> and choose Print. Now down here, it says no
scaling, we are actually using five different
2341.6 -> sheets of paper to get everything printed.
It's going to be very wasteful, don't do that.
2348 -> First of all, we can turn the paper sideways.
So let's change the orientation to landscape.
2353.44 -> That helps a little bit now it's only four pages
wide. It says scaling here. We can say this,
2360.24 -> we can fit all of the columns on one page or
fit all the sheets in one page. Let's try that.
2367.04 -> Okay, it's very small. It seems to work though.
2372.8 -> Let's save it and print it as this size. The next
lesson we're going to do is called grade book.
2379.12 -> And you can see that the grade book is not just a
simple grade book, but we're going to show what's
2383.2 -> called conditional formatting. That's what those
little colored dots are. They're going to show
2387.36 -> who are the top students in your class and who
are the bottom. We're going to do percentiles,
2392.16 -> as you can see on the right side. And so several
things about a grade book and formatting data
2397.52 -> that we haven't done before and you'll have a
very Nice looking chart when you're finished.
2402.32 -> What you see on the screen in front
of you is a grade book for employees,
2406.48 -> we're going to give them a series of
tests for the company, a safety test,
2411.28 -> a company philosophy tests, a financial skills
test and a drug test. And then we will give them
2416.88 -> employment based on their testing level. So
let's assume these are all new employees.
2421.2 -> And they are testing after their job interview.
So this is the completed spreadsheet. Let's start
2426.96 -> from scratch and build it one place at a time.
So I'm going to file and choose New and a blank
2434.72 -> workbook. Let's start by giving this a title and
the first cell, let's call it grade book. And
2440.96 -> we're going to keep people's names in here.
So we'll put last name here and first name.
2451.28 -> Now you can see that I've typed in the names
of the employees that are used from my payroll
2455.92 -> spreadsheet, you can just copy and paste these
names in or if you'd like to re type different
2460.08 -> names, you can, but we're going to use up to line
number 20, for those that are going to be taking
2464.56 -> the company test. Now you notice that I put the
titles of each test in C one, D one, e one and f1.
2474 -> The first test is called safety test. The next
one is called the company philosophy test.
2479.04 -> The third one is the financial skills test.
And then finally, the drug test. Notice that
2484.56 -> these words all run into each other. Let's do a
format on these cells to make them fit better.
2491.92 -> I'm highlighting all four cells.
And up here on this button.
2495.76 -> It's called orientation. I click it. And
let's choose rotate text up. And now,
2502 -> each of these cells is written vertically.
So it allows us to make the columns narrower.
2512.88 -> Now, how much is each test worth? Well, let's
fit in here and B two, and let's put in here,
2519.6 -> points possible. That'll show us what
the maximum grade is for each of these.
2529.04 -> Let's say the first one is worth 10. The next
test is worth 20 points, the financial test is
2534.08 -> worth 100. And the drug tests we're just going to
have as a pass or fail. So it is worth one point.
2540.96 -> Notice that I need to make the column E a
little bit wider because 100 doesn't fit in
2546.08 -> the square very well. Now, all we have to do next
is starting with john Kern is invent a number, how
2553.52 -> many points did john earn and so on? Now, you can
see that I've entered numbers for every one of my
2558.8 -> employees. So the maximum is 10. Nobody has more
than 10 points do they owe Trent man got an 11.
2566 -> But then over on this side where it says drug
test, you notice that everybody has a one or
2570.56 -> a zero. So there were two employees that got
zeros, that means they failed their drug test.
2575.84 -> Next, let's calculate the percent that
they earned for each of these tests.
2580.16 -> I want to take the titles and copy them.
So I'm highlighting all four of these,
2585.2 -> right click on one of them and choose
Copy. And let's put them in the next
2589.68 -> area over how about column H. We'll start with
column H. Now what would be the formula for a test
2597.12 -> and the percentages? Well, you would take
the equals, and we'll take this number 10
2604.24 -> and divide it by the points possible divided by
10 and press enter. This turns out to be a one.
2611.84 -> Actually I wanted to say 1%. So I'm going
back to the ribbon and choosing the Home
2616.4 -> tab. And you'll find in here the formatting
for percentages. Here's a percent sign.
2623.36 -> So on the safety test, john Kern earned 100%. Now
I'd like to copy and paste this formula so that
2631.68 -> all the percents are shown for this assignment.
Let's just fill it down and see what happens.
2639.12 -> Now there's a problem. This one says divide
by zero error. This one says 80%. This
2644.24 -> one's is 90 90%. And this one says 100%. It should
say 90, so it's not working like we thought it
2652 -> would. Let's double click on this 100% do you see
what's being divided, it says take C seven and
2658.8 -> divided by C five. Really what we want to do is
take C seven the score that Wendy received and
2666.08 -> divided by the points possible. And so
this error is what's called a relative
2671.52 -> referencing error. It's counting back two
cells and dividing by two cells above it. We
2678 -> need to use absolute referencing here to get the
correct results. So I'm going to clear these and
2684 -> try again going to right click and choose clear
contents. What I really want is to take equals
2691.84 -> the cell here of the points that john earned
and divided by this number with an adjustment
2700.72 -> I want to absolutely choose row number two every
time. So I'm going to put $1 sign in front of two.
2709.04 -> And so now when I fill down, it's going to always
reference row two for all of these assignments.
2716.88 -> And so Indeed, we get the correct answers,
there is 110% listed here. That is correct,
2722.64 -> because Trent earned in 11 points. Now, I should
be able to just copy and paste these cells,
2730.4 -> or use the fill command and calculate
all of these numbers, immediately.
2737.28 -> Notice the drug test is either 100%
or 0%. And so now even though the
2743.44 -> tests are all worth different points,
we scored each of them with percentages.
2751.2 -> Now, it would be nice to tell at a glance which
of these students are which of these employees is
2756.16 -> doing extremely well and which ones are failing.
Let's highlight a set of test scores. Let's do the
2763.04 -> first one here. And I'm going to do conditional
formatting. Conditional Formatting will color the
2768.88 -> cells according to the numbers that are inside. So
on my ribbon, I look for conditional formatting.
2774.72 -> And in this case, I'm choosing icon sets.
This set here is like a set of traffic lights,
2780.72 -> it's got red, yellow, and green, and then a black
one. Automatically, it puts an icon based on
2788.88 -> who's in the top set percentages and who's
in the bottom. So you can see that the green
2794.64 -> lights show up immediately, with the top test
scores. There's a red light for somebody that's
2800.24 -> failing, and then there's a black one for an
absolute miserable score of five. Let's try
2806.4 -> that with the next row. Let's highlight the next
set. And we'll choose conditional formatting,
2812.24 -> and icons and choose the traffic lights again,
and make the box a little bit wider. So you can
2820.56 -> see once again, that Karen is having difficulty
she received a six on the company philosophy test.
2828.72 -> You have to do these all individually, because
each one of them has its own set of scores.
2836.96 -> If you try to format them all at the same
time, you'll get different results. Choose
2842.24 -> the traffic lights, again, for our financial
skills test, and see who sorts to the top.
2849.84 -> This time you notice right away there's
a black line, or a black.on blessing,
2854.56 -> she's not so good at financial skills. And for the
drug test. The last one, it will format it again.
2863.52 -> Under icons and feel free to experiment with
the others. There's color scales, and there's
2867.2 -> data bars, interesting things you can see on
each of them. We're just using icons for these.
2876.88 -> And so now we have little traffic lights,
showing us quickly who is doing well, you
2882.4 -> can see there's four green lights for john, are
some people that have mixed results in between.
2889.44 -> Now let's also make a rule that we want
to sort out people that receive less than
2893.44 -> 50% on any test. So a quick way to find out who
that is, would be to highlight all of the test
2900.4 -> scores and their percentages. Let's go back to
conditional formatting. Now here's a nice option,
2906.8 -> the first one called highlight cell rules.
Let's choose the one that says less than.
2913.2 -> So in this in, we want to find out who is
receiving less than and let's put in 50%.
2919.28 -> So I can put point five in here. And then the
options here are light red filled with a dark
2924.48 -> red text, you can choose different options, but
we'll just leave it as the first and click OK.
2929.6 -> And right away, you can see that at a glance,
there are some people that have problems,
2935.84 -> less than 50%. For Karen, less than 50% on a
drug test means you failed it you got zero,
2943.36 -> less than 50% on financial. And so we have
a few of these people that are problematic.
2950.64 -> Now since I have a concern about who should be
fired, I'd like to create a another line called
2956.4 -> fire employee with a question mark. And then
we're going to say should we fire them or not?
2963.76 -> We're going to use a formula that asks this
question. Are any of these scores less than 50%?
2970.48 -> If so, then we should fire them,
you should at least pass with 50%.
2975.36 -> So the formula we're looking for is called the
or formula. he typed the equal sign and type or,
2981.92 -> or in a parentheses. This means that we're
going to ask a series of logical questions is
2987.44 -> some number less than another one? And if so,
then we're going to return true. Watch this,
2993.2 -> I'm going to say or is this score
here the safety test. Less than 1.5
3002.48 -> and I'll put a comma. And then I'll ask another
question. Is I for less than 50%? Another comma?
3013.68 -> Is this less than point five? And then finally,
the question is, is, comma is this one,
3022.56 -> less than point five. So I
have four questions in a row,
3026.96 -> all of them asked the same thing is this number
less than point five. And if I press enter,
3032.56 -> it says false. None of these scores are less
than point five. However, when I fill down, you
3038.8 -> will see that some of these scores are less than
point five. Here's a true and a true and a true.
3045.44 -> Once more, we can see from this side from this
line, Who should we fire? Who should we dismiss?
3053.12 -> Let's do a conditional formatting on this
one. Let's go highlight the cells choose
3058.4 -> conditional formatting. And this time,
I'm going to ask the question is equal to
3064.32 -> is this question equal to true, I can spell
true correctly. and press Enter. And so now
3073.92 -> all the ones that are true are highlighted
in red. So we know who we should fire.
3081.2 -> Lastly, let's put some numbers
at the bottom of the chart.
3084.48 -> Let's go down to here and use
our famous for max min, average.
3093.76 -> Let's just use three max min and average. So
in this cell, we ask equals this is going to
3101.92 -> be the maximum of the range that's above it. So
maximum of all of these scores with a parentheses.
3111.36 -> And let's do the same for minimum equals min,
parentheses. And we'll ask about all of these
3119.68 -> parentheses, what is the average score
equals average, and then the cell range.
3133.6 -> And then we can use the fill right? option,
show what the averages is for each of these.
3143.84 -> We're going to copy all these and put them
on the other side, where the percentages are.
3156.64 -> Now since these are percentages, it would
make sense to format them with a percent sign.
3165.04 -> Let's create a chart that shows all the graphs
of each of these scores from the safety test. I'm
3173.68 -> going to highlight safety tests. And now let's go
to insert. And let's choose a chart. Here's called
3184.48 -> a column chart, the column chart, we'll just
click it, and we'll choose clustered columns.
3194.72 -> Slide it over to the side. Now we're missing
some things on it, we need a chart title,
3200.88 -> double click where it says title, we
can change it to call it safety tests.
3209.84 -> We also need to know who are the people taking
the test, we just have numbers at the bottom.
3215.04 -> So it'd be nice to change that. After we have
the graph on the screen, it would be nice to show
3219.6 -> the actual names of the people as well as the
numbers. Or instead of the numbers right here,
3225.28 -> we just have employee numbers at the bottom. let's
right click on the graph, and choose Select Data.
3232.96 -> Now when it says here, horizontal axis labels,
let's change that, edit that. Now it's asking us
3240.4 -> what is the label range, it's asking us for
a range of cells, we can either type it in
3245.84 -> or we can just simply go over here and click on
Kern and drag down to Underhill. You'll notice as
3253.52 -> I do that, it's telling me that from sheet one,
the range a four to a 20 is going to be used.
3260.96 -> And click OK. And click OK again. And so now
you see all the people's names at the bottom.
3268.96 -> So we've created a test, graph the safety test.
3273.6 -> Let's do another graph. Let's do the company
philosophy tests. So highlight the scores. Go
3280.72 -> up to the Insert button or the insert options. And
let's choose another chart. Another column chart
3290.56 -> and slide it down below this
one. Let's give it a new title.
3297.36 -> Let's call it the company philosophy tests.
3304.88 -> Once more, we need to add these labels
in so that they are in the names instead
3309.68 -> of the numbers. So let's choose Select Data
where it says axis labels, we'll edit that.
3318.4 -> And we need to slide over to select the
people's names. So from under Hill, up to Kern.
3327.84 -> And okay. And Okay. Let's close this menu here.
And you can see the company philosophy test.
3338.56 -> One more graph, the financial skills
test. Let's create that one. Same process,
3346.88 -> insert the charts and choose a
column chart. Give it a name.
3364 -> And let's give the names at the
bottom something instead of numbers.
3383.44 -> And let's slide this one in
place below the other two.
3400.72 -> Okay, that brings us to the finish of this of
this gradebook test. Let's save our document and
3405.68 -> print it once more when you print. Be careful that
you don't print on 1000s of pieces of paper that
3412.64 -> says we're using six. So maybe I'm exaggerating.
Let's change a few things. Let's orientate it,
3417.92 -> so it's sideways. Let's see if we can fit this to
one one page. Once you have one page listed here,
3434.64 -> you can see all the graphs neatly arranged and
your data on the left side, you're ready to print.
3441.68 -> In this lesson called decision maker, we're going
to create a scenario where you're supposed to pick
3447.2 -> a job. And we're going to weigh different factors
based on your opinion. So pay the amount of jobs
3453.84 -> that are out there in the job market, how much
you enjoy it, how reliable the job is to you and
3459.12 -> various factors. And then based on your opinions,
Excel will give you an answer on what career you
3465.28 -> should choose. Welcome to excel assignment number
five. This is a decision chart that we're going to
3471.36 -> create using Microsoft Excel. Let's assume that
you're going to choose a career based on several
3477.28 -> factors, we're going to consider several jobs here
on the column A, and several factors that would
3483.04 -> lead you to decide to go into that field such
as your pay in that job, the job market, which
3488.56 -> would mean the likelihood that you'll be hired the
enjoyment factor How well do you like this job,
3495.12 -> your talents, how well are you at doing the
job. And then finally, another factor might
3500.56 -> be schooling, how much time is invested to get to
this career. And so we'll create a chart that will
3506.48 -> lead you to a decision that probably is one of
the most important in your life is which career
3511.68 -> Are you going to work at. So let's start from
scratch, I'm going to choose a new workbook,
3516.48 -> a blank one. And let's start by giving it a title.
Let's call it career decisions. Now down here,
3527.12 -> we're going to call this the job column. And you
invent some jobs that you think you might do.
3533.84 -> So I've placed a few jobs here in column A,
from McDonald's manager to an NFL player.
3539.68 -> Now let's have a large variety of types
of things that you could possibly choose,
3543.68 -> you might have different ones. Now let's
put in the factors that would lead you to
3548.16 -> decide on one of these, obviously,
pay is an important factor.
3554.32 -> I've placed other factors into the chart as well.
Not only pay but job market, the enjoyment your
3560.48 -> talents and your schooling that's required. So
now, under the column pay, let's assume that
3567.2 -> a five or some number large is the best that
you can do in the career and a one is the worst.
3574.24 -> So a McDonald's manager that's put him down
at near the bottom and doctors probably doing
3579.44 -> pretty well. Let's give him a for NFL it's as
soon as the best it's a five engineers probably
3585.52 -> pretty good as a three and a truck driver might
be a three as well. And then for the others job
3591.28 -> market. Likely you probably can get a job there.
A doctor once you get your school you're probably
3597.12 -> guaranteed a job. NFL Probably the worst of
the factors because hardly anyone gets that
3603.76 -> gets to that level. Engineers, they've got very
good job prospects and truck drivers apparently
3609.28 -> are doing well as well as well. How well would
you enjoy doing the job, maybe McDonald's, not
3614.96 -> so much, doctor if that's your passion. So make
up some numbers based on your own preferences.
3624.32 -> And so I filled in each of these categories
based on what I think about each of the each
3629.84 -> of the each of the factors. Now, it
would be simple justice to sum up the
3636.16 -> lines here. So I'm just going to put su
m, and then add up all of these together.
3645.04 -> And then we'll fill down to see
what kind of results we get.
3650.8 -> So this is telling me that number 19 is the
highest, I should choose engineering to be my job.
3657.76 -> However, there's more to the decision than just
adding up all of these factors. This assumes that
3663.36 -> every factor is equal, such as the amount of pay
you get is just as likely to influence you as the
3670.24 -> job market. Well, in fact, job market might
be more important, it's nice to have a high
3676.08 -> paying job. But if you can't get it, then
maybe job factor is a more important decision
3680.56 -> than you thought it was. Let's add some new
columns. In between each of these categories.
3688.08 -> We're going to insert a new columns.
So I'm clicking on the title,
3692.4 -> the letters at the top the column headers,
and right clicking there and choosing insert.
3700.64 -> What I want to do next is add what I would
call an importance factor for each of
3704.64 -> these categories. For instance, job market, I
would put that as the highest of the factors,
3710.16 -> if I can't find a job, then there's no sense in
going into it, employment or enjoyment, I should
3715.6 -> probably enjoy it, I'm going to give it as a for
my talents. So I think I can learn whatever it
3720.64 -> is. So I'm going to say that's not as important
schooling, it's not important, I'm willing to
3725.44 -> do as much school is I have to, and then pay
I'm going to count that as a medium factor.
3732.64 -> Now for each of these, let's take a formula to
multiply the relative importance, so equals the
3739.76 -> relative importance of this times the factor
for each career. Now this is going to require
3748.88 -> a absolute reference. So where it says dollar
sign see for I'm going to put $1 sign in front
3755.36 -> of the four, because I'm going to reference
this number in the blue every single time
3760.88 -> after I fill down. So filling down here
shows now the importance of the NFL 15
3769.44 -> is somewhat moderated now, it's got less of
importance than maybe the job market would have.
3778.56 -> So let's copy this section here.
Let's paste it into the next area
3785.2 -> and paste it into the next area. And continue on.
3795.36 -> Now let's readjust this when I do the final
tally, I just want to say equals and I'm going to
3803.6 -> add up each of these factors. So I'm
putting a plus sign between each cell.
3817.36 -> And then I will fill down in the last
row to give it a title and call it total.
3826.08 -> So once more 65 shows up as the most important of
the jobs at least according to my criteria, a 65.
3835.84 -> One more final touch might be to color code each
3838.72 -> of these areas so that they stand
out distinctly one from another.
3846.56 -> Now looking at the last the totals, let's do
a conditional formatting on these numbers. So
3851.04 -> highlighting the numbers, I'm going to choose
the top 10% that'll show basically the first
3858.48 -> highest ranked item. And so 65% 65 is the highest
number in the list at shows which one comes out
3866.56 -> on top. Engineering, by the way, is what the
computer decision chart recommends for me.
3872.8 -> Let's see what you have prints your work, put
your name on it. And you have a decision now
3879.44 -> of what your career will be. In this lesson
called sales report, we're going to summarize
3884.72 -> a large amount of data we're going to have lots of
different sales items will calculate who the best
3890.8 -> sales people are in our department and
create this pie chart when we're done.
3895.6 -> We're going to use some data from some sales
figures to do data. work in Microsoft Excel.
3902.96 -> What you see on the screen in front of
you is a report from perhaps the company's
3907.68 -> database. on sales for a store, you can see that
there's a lot of columns and a lot of rules. So
3914.64 -> you don't have to type all these things in, I'll
provide you with the spreadsheets so that you can
3918.8 -> just manipulate what's already there. So you can
see each title is at the top and row number one,
3925.28 -> there's a few blanks that we're going to
use formulas for. And then let's scroll to
3929.84 -> the bottom and you will see that has, I think
it's 172. Lowe's opens a sales transaction. So
3938.32 -> all the way to December here, you can see the
sales of each item in our pool supply store. And a
3945.6 -> little bit of data about each one. The store cost
and column E is how much the item cost wholesale.
3952.08 -> Sale Price is what we sold it for. And then we
need to calculate a few items such as the profit,
3957.52 -> the commission, and then we'll do some reports
on each salesperson to find out who the best
3962.8 -> salesperson is in our store. Well, let's do
some of the formulas here that we will have
3969.2 -> to encounter when we work with sales data.
Over here in column L, I've put some notes
3975.04 -> on the formulas that we're going to use and some
of the techniques that you'll see in this lesson.
3979.52 -> text to columns, which we'll use to split these
names, you can see that there's a first and last
3984.32 -> name and column I we want to split them into
separate columns. The second function is the
3989.2 -> if formula. You've seen that before. The next
function is called the sum if, which means
3996.08 -> you can pick certain areas or certain items to
add together based on a criteria that you choose.
4003.28 -> This is a database really more than a spreadsheet.
And in databases, we do a lot of sorting,
4009.04 -> and filtering, a new concept that you'll see
as a pivot table, which will give you a summary
4015.12 -> of like the number of sales that each employee
makes. And then finally, we'll review charting
4020.32 -> by making a pie chart. Let's start by making
the title up here in row one more readable.
4027.68 -> Right now they're all compacted together. So let's
highlight these. And let's go to text wrapping.
4034.64 -> text wrapping allows us to see
each one of these in its full text.
4041.6 -> So now you can see in column B, C, and each that
there's more text that we didn't see before.
4049.12 -> Let's start with the item called text
to columns, what we would like to do
4053.04 -> is to split these columns so that they have first
name in one column and a last name in the other.
4059.52 -> So to do that, we're going to need to insert
a new column to give it some space. So right
4064.64 -> click on column header j, and choose Insert.
And so now we have a blank column to work with.
4071.44 -> Let's go to column I. And we're going to the
Data tab here, and then choosing text to columns.
4079.92 -> What this will do, it will allow the computer
to parse the data that's in column I,
4086 -> there's two options if you use fixed width. That
means the first column might have five characters,
4091.52 -> or seven or whatever you want to choose. But in
our case, we want to split it based on the space
4096.56 -> that's between these names. So I'm going to choose
delimited. When you select delimited, it says what
4102.48 -> are your delimiters a delimiter is a divider.
And so if you select any of these items here,
4108.96 -> a tab, a semi colon a comma or space, it will
automatically divide the words on what you choose
4116.64 -> above. So I'm choosing space. And so a space
character between the words gives us two columns.
4125.92 -> Now let's change these headers now since
they make more sense to say first name,
4130.96 -> and then last name for each of the
salespeople. And so we've added a column. Now,
4137.6 -> the next item is to calculate how much profit
was sold. For each of these items. For instance,
4144.08 -> transaction 1001 was a pool cover. The product
code is something in our inventory that we just
4151.76 -> use is based on the maybe the manufacturer's code.
But how much profit do you make if you sell the
4158.32 -> item for $98 and it costs you $58 to buy it from
your supplier. It's a simple formula to say equals
4166.08 -> this square here, which is F to subtract E to
and press Enter. And you can see that we made
4176.16 -> $40.10. Now you might have to format that using
the key right here under the home button to get
4184.8 -> the dollar sign. Now how about commission? How
much money are we going to give to Charlie Barnes
4192.32 -> for selling this pool cover? Well, here's the
rule above 10% commission for items less than $50
4199.68 -> by If he sells an item that sells for more
than $50, we'll give him 20% of the profits.
4206.48 -> So let's go and make a formula using
the if command equals, if my rule says,
4214.24 -> if this sale price, I'll click here is greater
than $50. Comma, then let's give him more than
4225.04 -> more profit than the less. So the
rule says give him 20% of the sales.
4232.16 -> Let's make it 20% of the profits actually.
So let's take the profit and multiply
4239.84 -> shift an EIGHT and a decimal two, so that's 20%.
But if it's less than or equal to 50, comma,
4249.2 -> then let's take the profits and
multiply by point one, or 10%.
4257.04 -> So that's the rule that tells us the commission
is based on the value of the item that was sold
4264.88 -> per center. So the commission for this item is
20%. It costs more than $50. So we give him 20%
4276.72 -> of the profits of the store. So Charlie burns earn
$8.02, which is 20% of the profit on this item.
4286.64 -> Now, let's just highlight these two squares, and
fill down so I'm going to grab the little corner
4292.72 -> and drag down. That's a little bit hard to fill
down when you have 172 rows. So here's another way
4299.6 -> to fill down. I'm going to hold the shift key on
my keyboard, after selecting the first two rows,
4309.36 -> and then slide to the bottom of the spreadsheet.
Now I'm going to hold the shift key keep holding
4314.96 -> the Shift key and click at the last area, you
notice that the whole zone or this whole region
4321.92 -> is selected, I'm sliding back to the top and
sliding back to the bottom. Now once I have
4328 -> the region selected, where I want to fill these
items, I'm going to look for an AI command called
4333.52 -> fill down. If I click on home, way over on the
right, you will see an item called fill. If I
4339.76 -> click here, I have down right up and left is
my options I want to fill down, I click here,
4347.36 -> it automatically fills the entire range. So
sometimes that's quicker than trying to fill
4352 -> it down using this little item in the corner.
So I'm going to click to unselect the range.
4360.88 -> Now you can see in some items such as this
4364.56 -> one gallon of muriatic acid, it costs the company
or cost the customer $7. The profit for the store
4372 -> was $3. And Doug Smith earned 10% on that sale,
so less than $50 item, you get less commission.
4382 -> scroll back to the top. Now the next item
that we're going to look at is called sum
4389.92 -> if some if allows you to add together a range
of items based on a condition. Let me show you
4397.52 -> something more specific. Let's go to the bottom.
And you can see that I have a few formulas here,
4404.4 -> three different sums, I want to do the sum of
all items, the sum of all the items that are
4409.76 -> valued more than $50. And the sum of items valued
at $50 or less. So let's go to the cost here.
4419.52 -> Column F, which is the profit or that
that is the cost to the customer.
4424.96 -> The easiest formula is just the sum formula.
So I'm going to type equals sum. Now what's the
4431.04 -> range, I'm going to type the range this time since
the cells are so so many, we're going to type the
4437.6 -> letter F and two, and a colon and then the word
the letter F 172. And you can see on the screen,
4446.8 -> that there's a blue rectangle surrounding the
range, close the item with the parentheses and
4452.64 -> press enter. So in this year, the store sales were
$17,110. Now what my question is, is how many of
4462.8 -> those items were valued at $50 or more? What is
the sum of all those items? The new formula that
4468.88 -> we use now is called sum if So type equals sum
if and that let's take a look at what I can put
4475.76 -> in here. It's called a range and a criteria. So
the range is once again f two colon f 172. That
4484.88 -> gives me this the range of all the sales and the
price to the customer. Now a comma, the criteria,
4493.44 -> I'm going to have to put a quotation mark and
then are greater than 50. Another quotation Mark,
4501.12 -> what that will do is it will sum only the
items that are greater than 50. So press enter,
4508.72 -> you can see that $16,088 is the sum of
all items that are costing more than $50.
4515.76 -> Well, let's use the same formula. But this time,
let's choose any items that are valued at 50 or
4521.6 -> less. So that is equals sum if the range will
be the same f two colon, f 172, and a comma.
4532.48 -> Now this time, I'm going to put in
my rule as less than or equal to 50.
4541.52 -> And quotation and a parentheses. So now I can
see that the vast majority of my sales are for
4549.36 -> items that are $50 or more, hopefully, these two
items, these two cells add up to the entire sum.
4559.36 -> Okay, let's scroll back to the top and see what
other items we have to do. two items that you do
4564.64 -> in most database work is with sorting, and
filtering. So let's go see how that works.
4571.52 -> Go to the Data tab. And right here you see sort
and filter, sorting and filter exactly how you
4580.48 -> would think it would work. Let's choose sorting
first. What do we want to sort by, we can sort by
4588.48 -> the first, we can sort by the last name of an
employee. And click OK. And so now all the items
4598.96 -> have been rearranged so that the last name is
alphabetized. So all the Barnes items come up
4604.88 -> first, and then Hernandez starts at row number
35. So we've sorted by column j, basically,
4614.48 -> you can resort again, and this time, choose
a different item. Let's go back to sorting by
4624.56 -> the sale location.
4628 -> So if you're looking for all the items that
are alphabetized, according to column K,
4632.88 -> you sort by sale location. And so all the Arizona
sales are first, then the letter C comes next
4639.6 -> New Mexico. And then finally, the last items
that show up on the list are Utah. So sorting,
4647.68 -> let's see it one more time, I went to sort back
to the transaction number, which was the original
4652.64 -> way that the spreadsheet was sorted. So the last
item, and the first item now are back in order.
4663.2 -> The next item is filtering. If you want to
filter some items to show only certain values,
4669.84 -> use the Next button filter, what are we going to
filter. soon as you click that button, all these
4675.28 -> titles automatically have a little arrow next to
them. So what happens if I choose one of them such
4682.16 -> as sales location, I can unclick certain items. If
I only want to show one state such as New Mexico,
4691.12 -> I leave a checkmark there and click OK. And so
now it looks like my spreadsheet is much shorter.
4698.24 -> It's only one screen full. All the items have
this in common they have n m in this column K.
4707.52 -> However, don't be fooled, the other items are
still there, they're just hidden. Look at the
4711.28 -> row numbers, we start with to jump to eight 913
18. So there's lots of rows that are hidden.
4719.04 -> Just because we sorted or we filtered by the sales
location. Let's go back and select all of them.
4726.8 -> Click ok. Try filtering by other columns. Let's
filter by, let's say by the first name. Let's see,
4735.04 -> I only want to see Helen. I'm looking at her
sales. And now I see just Helens results.
4744.16 -> Yeah, remember, the other items are still
there. We just want to hide them temporarily.
4748.88 -> So that's filtering, sorting and filtering are
useful when you're doing analysis on a bunch of
4753.84 -> data like you have here. The next item that I
would like to use is called the pivot tables.
4759.28 -> Pivot Table is a way to summarize a large group
of data. So I'm going to select just the data
4764.88 -> that I'm going to work with here. I'm going to
select from cell A, and highlight all the way
4770.16 -> down to the other corner of my spreadsheet.
So I'm holding my mouse button down, moving
4775.44 -> all the way to the bottom to row number 172. It's
important that I limit my my pivot table to only
4782.16 -> this data I don't want to include the summary
at the bottom, nor do I want to include anything
4786.72 -> that's over on the right in column M. So after I
have selected all items, I go to the Insert tab
4796.16 -> and I'm choosing a pivot table. Pivot Table So
up next says what columns are we going to use,
4804.16 -> you can see the selection here is from a one
to K 172. That's what I've just selected,
4811.28 -> it's going to create a new worksheet. Up
until now we've only used one worksheet,
4816.4 -> it's always been called sheet one, as you
can see at the bottom. But when I click OK,
4821.44 -> you will see now I have a new sheet. This one
says sheet three, yours probably says sheet two.
4828.48 -> Now what are we going to put in a pivot table?
First of all, in a pivot table, you think of what
4833.36 -> you're going to add together or make a summary of?
Well, all I care about now is the sales figures
4840.48 -> for each of my salespeople. So I'm going to
select last name, and the sale price of each item.
4847.92 -> You notice that we have a little
summary here, Barnes sold $6,000.
4855.92 -> When I click those, the computer guessed what I
wanted to do. It says I'm going to use the rows
4861.52 -> of the last name and choose the sum of the sales
item. There are other ways you can add these
4868.4 -> together, you can count them, you can average
them put the maximum. Most often, you use some,
4875.6 -> you can experiment with other items, you can
drag these around and get strange results.
4882.24 -> You can do filtering. There's a lot to do with
pivot tables. But for all our purposes today is
4888.16 -> I want to leave this as the sum of the values and
the row labels as the last name of each employee.
4895.92 -> Let's format these as dollars
figures because that's what they are.
4901.52 -> And now you can see that the best salesperson in
my store is Barnes. Smith comes in a close second,
4908.48 -> Hernandez needs some help. To show this visually,
we can highlight these numbers and create a chart.
4916.24 -> Let's go to insert insert. And a pie chart would
be an appropriate type of sales figure here. So
4923.92 -> I like three dimensional pie charts. Let's
put this next to the other. And you can see
4931.04 -> the last name of each salesperson and
the percentages of the total sales.
4939.2 -> You might want to show some data on this graph as
well. You can right click this and you can choose
4947.28 -> Add Data Labels. So you can click here. And it
will show the actual number of sales that each
4953.6 -> employee earned. Adjust the colors and the
formatting to your style. Now the last thing
4960.56 -> we should do is print to worksheets. So let's put
your name up here. And then print this page. When
4967.2 -> you go to print, make sure that it fits on one
sheet. So I choose Print. And you can see in the
4973.36 -> preview that I have two pieces of paper here, it's
not really necessary to have two. Let's make this
4981.2 -> a horizontal landscape. And
let's condense this to one page.
4991.84 -> And click Print. In this lesson called card
database, we're trying to get some more
4997.84 -> advanced features of Excel, you can see that
we have literally a database of lots of cars,
5003.68 -> we're going to find out how many miles they each
worked. We're going to do some formulas with text.
5008.96 -> So we can combine two fields together and split
them apart. And we're going to do some averages,
5014.8 -> and create some charts as well. So let's get
started with a car database. Welcome to excel
5019.6 -> assignment number seven. This is a database of all
the company cars that you own in your business.
5026 -> This is a rather extensive spreadsheet. So we're
going to split the lesson into three parts.
5030.4 -> You can see the lesson contents on the screen.
First of all we're going to use importing to
5035.2 -> create a text file into a spreadsheet. The second
is we'll introduce three new formulas left,
5040.8 -> middle and right. We'll use v lookup
formulas to create value out of a table.
5047.2 -> We'll review the if formula. We'll use the
concatenate formula to put cells together,
5052.64 -> we'll work on a pivot table. And lastly, we'll
import our documents into Microsoft Word.
5060.56 -> Your company fleet manager might have a computer
system that does tracking and he's asking you
5066.56 -> to do some analysis now on the cars that are
in your company. Usually when a person gives
5071.2 -> you a database, you have a few options. You can
modify it in a database programs such as access,
5078.4 -> or perhaps what you're more familiar with is
with Excel. And so we're going to use a database
5084.16 -> aspect of how Excel works today. You'll see that
they have the spreadsheet on the screen shows the
5089.76 -> cars that we own in the company with some IDs
some miles their cars and years and their makes
5095.52 -> their numbers the principal driver and so we're
going to do several steps. With database functions
5102.24 -> using Excel. First of all, you won't get a
spreadsheet. When you ask for inventories, usually
5108.8 -> what you get is a text file. And so what I have on
the desktop here is a spreadsheet in the form of a
5114.56 -> text file. If I double click this, it looks ugly,
you see that it is not in a spreadsheet format,
5123.04 -> at least not yet. This is what you get from
reports from sales data from your Amazon account,
5131.68 -> or your insurance company or your bank. These
formats are called text formats, they don't have
5138.72 -> any, they don't have any spreadsheet, graphs, they
don't have any colors. It's just straight text.
5145.84 -> But Excel knows how to work with these very
well, you notice all these commas here.
5150.08 -> These are all separators, they show the different
columns that will come in the spreadsheet. So
5156 -> I'll show you how to work with this. Now I'm
going to close this first and start Excel.
5163.36 -> Now instead of starting by working with the
file itself, we have to import it. So I'm
5170.64 -> going to the word file. And I'm going to
open a document. And I'm going to choose
5176.88 -> this text file, I have to go find
it first. So I click on computer.
5181.12 -> And let's browse. And let's navigate to the
desktop. That's where I have this file saved.
5190.48 -> And it doesn't show up. Why not? Well, remember,
the extension on a txt file is txt. And Excel is
5198 -> just looking for spreadsheets right now. So I'm
going to change the filter here to show all files
5203.2 -> on the desktop. And then scroll through it again.
And there it is. There's the car inventory. txt.
5211.12 -> Now when I open it, it's going to ask me some
questions. How do I handle this? What do I do?
5218.08 -> Are these like, text files that are equal
sized cells each? Or are they delimited?
5224.32 -> Well, these are all separated by commas, as you
can see in the preview. And so we're going to
5229.2 -> stick with the delimited idea. Choose next. And
how are they delimited? Is the next question.
5236.56 -> Are they separated by tabs, semi colons, commas,
spaces or something else? Well, these are all
5242.64 -> separated by commas. And as soon as I click comma,
you notice all these columns seem to line up
5249.68 -> as they are intended. And so that looks like
I'm on the right track. I'll click Finish.
5255.36 -> And so now I have the start of my spreadsheet.
Each comma creates a separate column in the
5261.76 -> spreadsheet, let's take you through some steps
here, we're going to have to follow this pretty
5265.84 -> closely. There's no room for creativity on this
assignment, just follow exactly how it's presented
5273.52 -> here. First of all, let's change the columns so
that the column headers so that they can show
5279.28 -> the entire title. So we'll use text wrapping, the
first thing I would like to do is introduce some
5283.92 -> new formulas that are able to handle text, you
can take pieces of a field and create new fields.
5290 -> For instance, whoever invented this car ID for the
company was trying to squeeze as much information
5297.2 -> into the ID as possible. And so we used
a code like fd for Ford 06 for the year,
5305.04 -> m T, G, four Mustang. And 001 is the car
number. That's not normally recommended to
5312.88 -> try to squeeze data into a field like this,
but that's what he's doing. So we need to
5318.24 -> come up with a few ideas on how to separate
these fields. Let's go to where it says make.
5325.92 -> I want to take the first two
letters off of each of these
5329.76 -> data items. So the first two letters is
going to be using a formula called left.
5336.88 -> And if I chose if I choose that, you notice the
options are. First of all choose text. Well,
5344 -> I'm going to choose this as my text. And the
next after this comma is the number of letters.
5351.04 -> Well, in my database, it looks like the first two
letters are the manufacturers names. So let's use
5360 -> a close parentheses. And sure enough, you can see
that it's just slicing off the first two letters
5366.88 -> of the database. Well, let's fill
this down and see what we have here.
5373.92 -> All the way to the bottom, how
many are there looks like 53.
5378.56 -> And so you can see we have two letters for each
of the make. Now what do those mean? Let's Let's
5385.44 -> Okay, let's create a new formula. And we're going
to put in column C. The manufacturers name I'd
5391.68 -> like to put in just the word Ford. Or down here
General Motors t y must stand for Toyota a Jo is
5399.36 -> Honda car is Chrysler and ah y is Hyundai. We're
going to introduce a new function here called
5408.32 -> a lookup table. Let's take these items
here, like CR and h, y and t, y, and h, o,
5415.68 -> and GM, and f, d. These are all our manufacturers.
And let's actually put the real name here.
5430.08 -> Okay, you can see that I've created a small
table that coordinates these abbreviations
5434.88 -> with the full name. To make this work, though,
I have to have these in alphabetical order.
5440.24 -> So I'm going to highlight these, just
these cells, not the whole table,
5444.72 -> and sort them. So I look for the data command,
and sort. It says, What do you want to sort by
5452 -> I want to sort by the first column, and click
OK. If you don't put them in alphabetical order,
5458 -> this next process is not going to work. Now
that I've got these charts here, I'm going to
5463.12 -> look them up. I'm going to look up f d
in the chart and put in the word Ford,
5469.84 -> the computer function to do that is called
equals v lookup. That means vertical lookup,
5476.64 -> it's going to look through a table vertically,
look up the words f d, or the letters f, d,
5483.2 -> and then return the second column, which is Ford.
So let's type in the whole command here lookup.
5491.44 -> And let's follow all these options through First
of all, the first item it's looking for is the
5497.12 -> lookup value. So fd is the lookup value, put a
comma. And then the next item says table array,
5505.44 -> the table is the little set of data at the
bottom, you're going to scroll down this table,
5514.56 -> and then a comma. Then it says column index
number. That means which column in this table
5525.76 -> contains the real word that you're looking for
not the abbreviation but the real word. So the
5531.04 -> second column contains Chrysler, Ford General
Motors. So I'm going to type a two up here
5538.08 -> and a comma, and then actually, this is the
only option that we need, we can close the
5543.04 -> parentheses Now press Enter, and scroll to the top
notice it says forward. Looks like it's working.
5550.08 -> Be careful, there's still one error with
this. But we'll discover that in a moment.
5555.28 -> If I fill this down, you're going to notice
there's a relative reference error problem.
5562.32 -> everything stops working after the third row.
5568.16 -> It has this na, which means not available or
not accessible. And so what's going on? Well,
5574.96 -> if I double click on the second item, you'll see
that this table is looking at rows 57 through 62.
5583.92 -> And you notice down here that
it's been shifted down one,
5587.36 -> that should send an alert to your mind to say,
Oh, the computer is using relative references
5593.76 -> instead of absolute references. So if I
go back to Ford, and double click this,
5599.76 -> 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.
5607.36 -> So that means that it's always going to use those
row numbers, every time I look up a value. And
5614.64 -> now it looks like it's creating the results I
want. So let's go all the way to the bottom.
5621.76 -> And you see that we've created a new field in
our database that shows the manufacturer's name.
5628.64 -> Now the model is the second item. While the model
is found here, this is the the letters empty G.
5636.88 -> So we can just use the left command like we did
for the manufacturer. So we're going to use a new
5642.88 -> item called mid command. So in this case, we have
an a Ford Mustang, empty g stands for Mustang.
5651.84 -> So let's, let's pull out some letters. Well, to
pull out the manufacturers make we use the first
5658.32 -> two letters and so we use the left command.
Well, we want to pull something out of the
5662.96 -> middle of the text. So this one is called the mid
command and my D. It says here that it's going to
5669.12 -> return the characters from the middle of a text
string, given a starting position and the length.
5675.76 -> So let's put a parentheses. The first item that
it's looking for is the text. So we'll click here.
5684 -> And then a comma, the start number where
does this m start? That's it position 12345.
5690.88 -> So it starts at position five and the string and
then a comma. How many characters do we need?
5697.2 -> Well, these all have three characters in each The
model numbers. Okay, close parentheses and Enter.
5705.84 -> And now we see MTG. So let's fill these down
a ways and you can see that it's pulling out
5711.6 -> other items. FCS must stand for Ford Focus,
and then a gender General Motors si MRSLV.
5725.92 -> Let's see what some of these others are. Scrolling
all the way to the bottom and filling down,
5733.68 -> you can see that we have some of these others. Now
the meaning of all these models can be interpreted
5738.48 -> as follows mt G is for Mustang. FSC is for
focus. CMR is a Camaro SLV is a Silverado,
5749.52 -> Toyota Camry and a Toyota Corolla are next.
And let's see a civic looks like a Honda Civic,
5757.52 -> Honda Odyssey. And what's next here,
a PT Cruiser, Christ's their caravan,
5764.64 -> and then the Honda Elantra. So these are
all different models. So at the bottom here,
5769.28 -> we were going to do the exact same process of
creating a lookup table. So I'm going to copy down
5775.44 -> all of these manufacturer model names,
and then put the real name next to them.
5782.24 -> Okay, I'm finished with typing in all of these car
models and their full name. Remember, a V lookup
5790 -> table only works if it's alphabetized. So I'm
going to highlight these squares, choose sort,
5797.6 -> and sort by the first column, which is D.
Click OK. And so now the this line is in
5804.24 -> alphabetical order. Once we have that done,
we can create a lookup value. So I'm going to
5811.44 -> use the exact same lookup as my model. I'm
going to copy this and paste it over here.
5821.52 -> It doesn't seem to be working right?
It says, A lantra is the first item.
5827.12 -> What's going on, I'll double click here to
find out. We're looking for D 56 through e 61.
5835.84 -> Now that's almost there. The only problem
is, this table is longer. So I'm going to
5841.28 -> extend this one all the way to the bottom,
and press enter and go back to the top.
5847.28 -> Now it looks like it's finding Mustang correctly.
Since the other one was created with the
5853.44 -> absolute references, we have the dollar signs
in the in the top, it seems to be working right,
5859.04 -> so we'll fill down and we'll have the
model name for each of these cars.
5867.6 -> Almost there. Let's open this a little light
wider. We have an error down here what's going on.
5875.44 -> This one says not accessible or not available.
What's wrong with this, we have a 00 D,
5883.68 -> somebody in their stupidity, typed in a zero
instead of an O back at the beginning here.
5891.36 -> So let's fix that. Up here at the top,
we have the NA h o and then the year
5899.36 -> we have an extra zero in there. So let's delete
that zero and press Enter. And now it appears that
5906.48 -> that typo was fixed. So when you get text from
another company, sometimes you have to realize
5913.04 -> that people didn't enter the data correctly.
Lastly, let's put in the manufacturer year.
5920.72 -> This is going to use the mid command again, we're
going to pull out the year from this square.
5930 -> But a comma, it's going to start at position
three. And we're going to take two text items,
5936.16 -> two letters, and we get 06. That one seems
a little simpler. So we'll scroll that down.
5943.84 -> And now we'll pull out just the year, a
two digit year for each of these cars.
5951.6 -> Now we need a formula to calculate the age. How
old is our car? Well, let's put in an equal sign.
5961.84 -> What would you do? Given that I'm creating this
spreadsheet in the year 2014. I'm just going to
5969.04 -> take 14 and subtract the years that come
before and press Enter. And it says eight.
5977.6 -> So a 2006 car is eight years old. seems to
work. Let's see what happens when I feel down.
5987.44 -> Most of the time it works except for here.
What's this says there's a problem with it.
5993.2 -> Now if you look closely at this, this is an
O six. It's literally an O six. We want 06.
6000.8 -> So once more, somebody thought that it was an O
six, and in the database, they put a o letter O
6007.2 -> in here. So let's delete that Oh, and put
in a zero. And so that fixes that value.
6014.72 -> The same happened here. Let's go back to this row
15. I'm deleting the Oh, and I'm re typing a zero.
6024.16 -> Let's continue to fill down and see
what other errors might show up.
6031.76 -> Oops, there's another one another value that
says it cannot be read. So let's go to here.
6039.84 -> Ah, oh, and then a 05. So we've had
to do some corrections to our data.
6049.52 -> Now, this formula is very simple. It's actually
too simple. This car is negative 84 years old.
6058.32 -> Where did that come from? Well, that was called
the y2k bug where computers just held two digits
6065.52 -> for every year, it worked until we switched
over to the year 2000. So a 98 is actually
6073.84 -> 16 years old. And it gives us a negative 84.
That's because it's taking the formula 14
6081.68 -> minus the value in cell B nine f 19. Well, it's
a correct number according to the computer,
6089.44 -> but it doesn't serve our purposes, we want to
make sure that the number comes out correctly.
6094.64 -> So let's readjust our formula. Let's erase
this one. And I'm going to ask a question,
6100.64 -> if if the difference between 14
minus this gets less than zero,
6112.8 -> then I want a formula for all those 90s and
80s. Cars. Well, I just simply say I'll take 100
6122.08 -> minus the year. And that'll get us the distance
between 98 to 2000, that would be about two.
6130.16 -> And then I'm going to add 14 to get to our
current value. Otherwise, we'll just use
6137.36 -> the previous formula, we'll say 14 minus this
here. And so now we have two different cases,
6145.12 -> for those that are less than 2000. And those
that are greater than 2000. If I press enter,
6151.28 -> I still get the same results for the first. And
let's scroll to the bottom and see what happens.
6158.64 -> And so sometimes, a simple calculation needs to
have a few cases. So yeah, this is correct. Now,
6165.6 -> a 1999 car in the year 2014 is 15 years
old. So the formula seems to work.
6174.16 -> Now let's go over to this formula, miles per year.
6178 -> Well, that should be pretty simple. least at first
glance, we want to say this equals the total miles
6185.76 -> divided by the years driven, which is the age
of the car. And so that shows 55,000 miles.
6195.84 -> Let's fill this down. And let's see if it works.
Almost all of them work. There's a few errors.
6206.4 -> There's an error, it says divide by zero.
Why in the world are we getting that error?
6211.44 -> That's because over here in the age, it says
this car is zero years old. It was made in
6217.52 -> 2014. Is it really zero years old? Well, it's
less than a year old. But it's not zero. So it's
6226.64 -> probably six months old, maybe three months old.
Let's make a slight adjustment to our formula.
6233.28 -> If I double click on this formula, you'll see
that I'm dividing eight, the age divided by
6240.16 -> the miles. Well, the age and some of these
was zero, we can't really divide by zero.
6247.12 -> And this car really is not exactly eight years
old, it might be eight years and six months,
6252.72 -> because a car that is zero years old really
has a few months already on it. So let's just
6257.92 -> round a little bit. Let's add a little bit
of a of a number. Well, let's see, we want
6263.6 -> to add something to G two. So I'm going to have
to put a parentheses before g two and add point
6272.24 -> five let's add let's add a decimal point five. So
that that year is really a year and six months.
6281.36 -> Why parentheses? Well, you should know from
your order of operations in your algebra class
6286.88 -> that you're supposed to add and subtract after
you divide and multiply. And so we want to make
6292.64 -> sure that we add first and then divide.
So that's why the parentheses are there.
6298.96 -> If I press Enter And then fill this formula down,
6306.96 -> we should get better results. So the mileage
calculation is based on the age plus six months.
6314.72 -> And we have no divide by zero errors. Let's format
these two columns, they could probably use some
6322.4 -> commas to make them look more like miles, it's
easier to see when you have 1000s. If you have
6328.08 -> a comma in there, let's move, let's keep moving.
The color of this is black, and white, green, etc.
6335.04 -> That's fine. We'll use that in a few minutes. When
we get to the new car ID. The principal driver
6341.6 -> shows the last name of each employee. How many
warranty miles are there on this car? This is a
6348.8 -> 50,000 mile warranty. And this car is just barely
under the warranty, it still has 10,000 to go.
6355.52 -> Well, the question is, is this covered under
warranty, that's the formula we're going to
6359.44 -> do next. And so we'll do an IF calculation.
If if the miles are less than or equal to
6371.6 -> the warranty, and yes, it is covered. And so
I'm just going to put the letter Y for covered
6377.04 -> a comma. So if it's not covered, I'm going to
write the word not covered. And then a quotation
6383.76 -> and a parentheses. So now this says
yes, it is covered, I get a why.
6390.16 -> If I fill down, I will see a different
value for each of these cars.
6395.76 -> And some are covered and some are not covered
under the warranty. The last item that we're
6400.48 -> going to fill in here is called the new car
ID, your boss gives you this requirement.
6407.04 -> He says take the original ID. And I want you
to squeeze in three more letters in between the
6413.04 -> manufacturer and this last three digit number. And
I want to I want to know what color is the car.
6420.88 -> So once more, this is a bad idea to try to squeeze
as much information into one field. But your boss
6426.64 -> said so. So it's the truth. Let's do it. How do
we how do we how do we combine fields, the formula
6434.88 -> you're looking for is called concatenate co n. And
then you'll see concatenate shows up here. I click
6442.72 -> it, double click the format that you're supposed
to fill in is simply says text one, text two, etc.
6451.12 -> So we're going to concatenate a bunch of fields,
we want to put first of all continue using f d,
6458.56 -> comma, and then we want to have the
manufacturers year comes, as you can see,
6463.44 -> that comes second in the list. And then
we want to continue on with the model.
6471.92 -> And then a comma, and now your boss said put
in the colors. So we want to put in black,
6479.6 -> we're going to adjust that because
he only wants the first three letters
6483.68 -> of the word. So we're going to delete this
two squares, I want to know just the left
6492.24 -> three letters of black. So I left left
three of j two, so j two comma three,
6501.84 -> and a close parentheses and a comma. So we've
just added the left three letters of black.
6508.24 -> And now what we need is the last three
from here. So we use the formula right
6518.96 -> click here, comma, three, double close parentheses
and press enter. So now we have the new car ID.
6529.68 -> One more change that we need to make
is that this shows lowercase letters.
6536.56 -> Your boss didn't tell you but he forgot to say
all IDs in cars have uppercase letters only.
6544.24 -> Now there's a nice formula to put
in there. If I want to isolate these
6551.28 -> letters and change them to uppercase, the
formula they're looking for is called upper.
6558.48 -> So I put a parentheses around the part that I
want to be uppercase and press Enter. And now
6566.96 -> the new car ID shows bl a as black. I'm
filling down and now we have a new car ID.
6581.12 -> Okay, so those are a lot of different database
functions that you can use to manipulate text.
6586.4 -> The next item in our checklist of things
that we're going to see on this assignment
6590.4 -> is called a pivot table. a pivot table
allows you to summarize data. For instance,
6597.12 -> your boss might ask of these drivers
Who has the greatest amount of miles.
6604.32 -> There's some formulas you could
work with. But here is one of them.
6608.16 -> It's called a pivot table, I'm going to insert,
and under the insert command, you see pivot table.
6615.36 -> It says, What's my range that I'm going to
work with, and it automatically selects the
6619.76 -> entire spreadsheet. Click ok. a pivot table
creates a new sheet down here, you notice this
6628.24 -> one said car inventory. And this sheet is called
sheet one, I'm going to select the driver first.
6638.08 -> put a checkmark next to him. And then I want to
know about the miles on the car. So I click on
6644.16 -> miles, automatically, the computer assumes that I
want the sum of the miles and the driver. And so
6651.2 -> now we have a chart that shows each driver
in the number of miles that he created.
6657.6 -> Well, you could also put in a chart
and create a list of all the drivers
6665.2 -> and their miles. And so Smith jumps
out right away from our graph.
6670.64 -> Whoever Smith is he's driving a lot. Let's go back
to our car inventory and see what Smith is up to
6678.48 -> Smith, what kind of car does he have, oh, here's
a Ford Mustang. No wonder he's driving so much,
6683.36 -> I would drive a lot to have somebody gave
me a Mustang. And so Smith shows his miles
6689.36 -> as the most of anyone in this series. Another
type of graph that we haven't worked with yet
6694.88 -> is called a scatter chart. A scatter chart
allows you to put a specific data points
6701.28 -> on a graph. So I'm going to select here, the age
of the car, and the miles, just these two columns,
6709.92 -> I'll click on their column headers, and select the
entire column. I'm going to insert a new chart.
6717.28 -> And the chart that I'm looking for here has a
bunch of points on it. It's called a scatter
6720.72 -> chart. And as soon as you select it, you can
see what it's doing. It's pointing each of these
6726.32 -> graphs, each one of these dots on the graph shows
that the years across the bottom are showing from
6734.08 -> zero years up to looks like about 18 is the oldest
car and then the number of miles each car has.
6743.52 -> So it almost looks like a straight line.
Well, there is something called a trend line
6750 -> that we're looking for. So I'm clicking on the
plus sign up here. And I'm selecting trend line.
6757.04 -> And now there's a dotted line that goes up
and down through the middle of these dots,
6763.04 -> we could probably make these access titles
a little bit easier to read. So I'm going
6768.8 -> to select those as well. deleting the word access
title and putting in the word miles, miles driven.
6782.32 -> And then down here on this axis, I'm
changing this to the age of the car,
6788.72 -> age of the car, and then in parentheses,
years. And so let's Park this off to the side.
6797.12 -> And slide over. So now we have a chart that shows
the miles in our inventory. Some of these are
6803.84 -> outliers. Some of these are right on the line.
one more way that we could do some analysis is
6813.04 -> we could find out which cars are being driven
more than others. So let's select column I.
6821.92 -> Let's go to conditional formatting.
Let's try out something called the
6826.32 -> color scales. It's pick one of these color
scales, there's blue and green and red
6830.8 -> doesn't matter which one you choose. But when
you pick one, you will see that some colors are
6836.32 -> highlighted in darker and lighter colors, they
show you the extremes. So like this 35,000
6843.92 -> shows up as one of the highest. Let's sort this
spreadsheet based on the miles driven per year.
6852.16 -> The first thing we have to do is select just the
range where our data is stored. We're going to
6857.92 -> ignore these lookup tables at the bottom. So
I'm highlighting all the way down to row 53.
6865.44 -> And then going to the Data tab and choosing sort.
Let's sort by column. What column is this column I
6878.32 -> and let's go from the largest to the smallest and
click OK. And so you can see that this car here
6886 -> this particular car has 35,000 miles per year. On
average. It only has six months and the guy has
6894.24 -> already driven at 17,000 miles. We scroll to the
bottom you'll see that the minimum Drive driven
6900.8 -> car is this Ford Mustang here, it's eight
years old and it has 40,000 miles on it.
6906.4 -> The last item I'd like to do is to create a
report. So let's go back to Microsoft Word.
6914.56 -> And we're going to copy and paste a few
of these items from our spreadsheet,
6918.88 -> the title of a report is
going to have our name on it.
6925.68 -> Next, I'm simply going to put the top
drivers by miles. And then I'm going to
6929.76 -> go back to excel. Go back to my sheet, copy
this graph, so I'm going to right click it,
6937.92 -> choose Copy. And you can see you can go back
to word right click it and choose Paste.
6948 -> And so these two programs integrate with each
other. Press Enter a couple of times. And I'm
6954.64 -> going to show the next is the scatter chart for
the car inventory. So it says the scatter chart
6963.84 -> for the car inventory miles on each car. Now
I'm going back to excel. And I'm going to copy
6971.44 -> this graph here that has the scatter chart. So
right click on it, choose Copy, go back to word,
6980.08 -> right click, and choose Paste. And so I have two
charts that came from my graph. let's print these
6990.56 -> and call that our final assignment. One more
thing when you go to save a document that in
6996 -> Excel that was created using a txt file, you
can see up here it says car inventory. txt.
7003.12 -> When you go to save that, you're
going to get an error message.
7006.56 -> It says you're still in the tab formatted
text. Do you want to keep using that format,
7013.6 -> you should say no, we want to save this
using a new format. Instead of tab delimited,
7020.72 -> or text delimited, we're just going to choose
Excel workbook. And now all those pretty blue
7028.4 -> and all the formula and all the other things
that were created in the charts, they will
7032.8 -> stay with your spreadsheet, a regular tab or text
delimited file cannot possibly save these items.
7042.48 -> Okay, in Microsoft Word. Let's go File and print.
And we'll call our document done. In this section,
7050.4 -> we're going to talk about problem solving. And
so you can see that Susan in front of us has
7054.96 -> a problem. She wants to take a vacation and
wants to spend as little amount as possible.
7060.08 -> And so our options are to go on a
Caribbean cruise, go to Orlando,
7064.32 -> or go to Chicago. Each one of these requires
flights, car rentals, and hotels admission fees.
7070.08 -> And so Excel is a great tool to figure out
which one of these would be the least expensive.
7076 -> And so I will give you a partial solution.
But that by this point in the course though,
7081.36 -> you're supposed to be able to solve some of
these problems on your own. And so it will
7086 -> stretch your thinking and reinforce the skills
that you've learned before. So problem solving
7091.44 -> is going to start with a simple problem. And then
gradually, I'll give you several until you get to
7096.64 -> the most complex problem. When you reach the end,
you will be considered a person that can use Excel
7103.04 -> to solve real problems in your personal life
and in your job as well. Hi, in this video,
7110.24 -> I'm going to show you how to calculate
interest on a simple interest payment plan
7114.4 -> for four loans with different interest rates and
then make a graph. So here's the final product
7119.92 -> that we're going to arrive at at the end of this
video. And so you'll see that we have a $10,000
7124.24 -> loan with four different interest rates. And then
we have monthly payments at the right column.
7130.08 -> Let's start up a new spreadsheet. And then
we're going to put in the data labels.
7134.56 -> So the first thing I'm going to do is fill out
the column titles for our loan. So the first
7139.68 -> column is principal, the interest rate, the months
that we have to pay over the total interest paid
7145.84 -> the number of dollars that the total loan
will be and then the monthly payments.
7151.6 -> In the first column, let's put in labels here for
loan A, B, C and D. I'd like to make sure that
7157.52 -> these column headers are correct. So let's double
click on the little line that's between each of
7161.52 -> these dividers. And that will separate the column
to be exactly the right width for the label.
7168.16 -> So let's zoom in and let's check out what our
payments will be. So let's make up a principal
7173.76 -> 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.
7179.84 -> So I enter in 10,000. And you can see that the
numbers don't quite fit now. So let's expand the
7185.28 -> column a little bit. And if you don't have dollar
signs, you can go up to the accounting area and
7190.4 -> you can change this to $1 sign if you want. Now
the interest rate if I put in a number here,
7196.24 -> let's say 9% and I use the percent sign
It will calculate the number correctly.
7202.64 -> However, if you don't put in there 9%, you can put
in 0.09, which is the decimal equivalent of 9%.
7209.6 -> And then if you choose the percent
sign, it will show it correctly.
7213.92 -> Now the number of months, let's say I have
a 12 month loan, and so I put in a 12.
7218.48 -> So these three columns here are
going to be numbers that we enter,
7222.16 -> and then the last three columns are going to
be the formulas that help us solve our problem.
7227.12 -> So let's start with the formula interest paid.
So that's a formula where we type in equals,
7232.48 -> we want to take the principal, which is B two,
and I will multiply by C two, which is the rate.
7240.32 -> So you can either type in equals b two,
and use the star key, which is the shift
7245.36 -> eight. And that's for multiply. And then finally,
type in z two, C two, and press Enter. And that
7251.92 -> will tell us that the total amount of interest is
$900, over the price of the loan. So then the next
7258.16 -> column is the number of dollars paid total. So we
have to pay back our money. Let's go with equals,
7264.8 -> and we'll click the 10,000, then we put in a plus
sign, because we have to add the number of dollars
7270.8 -> interests that we're going to pay as well. And
so that's 900, and press enter. So the total
7276.96 -> cost of your car will be $10,900. Now you're
going to pay this over the life of the loan,
7283.6 -> so we're going to take this total amount and
divide it by 12. So we'll take divide by the
7290 -> number of months that we just agreed to pay. So
that's f two, divided by E two, and press enter.
7297.04 -> So your monthly payment for this car is $908. Now
you can adjust this quite easily if you want to
7303.28 -> change something. So let's click in here and let's
say I have a $20,000 loan for my car. And you can
7308.48 -> see the payment automatically is recalculated. Now
you're shopping between different banks, and you
7314.16 -> want to see what the competitive rates are. So
let's take our 20,000. And I'm going to select
7320.88 -> and fill down. So let's see I'm selecting this
whole bracket of looks like 12 different cells,
7327.6 -> I'm using the Ctrl key and D on the
keyboard. And that is for fill down.
7333.92 -> Now I want to calculate on different rates. So
one bank here decided to give me an 8% loan.
7339.84 -> Another one is seven and even 6%. So I shopped
around, and I found four different rates.
7345.68 -> Now the number of dollars that I pay is equal
and the number of months that I pay is equal.
7350.72 -> Now let's take the last three columns, and we're
going to fill down so I'm going to select those
7356.72 -> three columns, and drag from the small dots in
the corner. And this will calculate my interest
7365.2 -> based on the 12 month payment. So let's see
here, the interest rate is better for the 6%.
7373.52 -> And then the total monthly payment is slightly
lower. Now I'd like to visually present this. So
7378.72 -> let's select the total monthly payment, and we'll
make a bar chart out of it. So let's see. Let's go
7384.56 -> to the Insert tab here. And I'm going to select a
chart for bar chart. Let's choose this first one.
7392.48 -> Now there's my chart. And let's
see, I'll zoom out slightly.
7397.12 -> Let's put in a chart title. And I'm going to
call this monthly payments for $10,000. All right
7408.24 -> now at the bottom, you can see that I have these
1234. I would like to compare the interest rates,
7414.4 -> that's the only thing that changes in here is
the 9876. So let's do a right click on the chart
7419.68 -> here and choose Select Data. In the column here
where it says axis labels. I'm going to edit this
7426.64 -> and it says please give me a range. So I can
just simply select from the chart, here's 876789.
7434.64 -> And then it puts in the correct columns are c two
to C five. And that is exactly what I'm looking
7440.24 -> for. Click OK. And then it says here, this is what
I'm going to display now, which is 987 6%. And
7446.88 -> click OK. And there you got your chart. My name
is shed Sluiter, and my youtube channel is called
7453.52 -> tech Made Simple. I'm a university professor that
teaches programming, computer software development
7459.2 -> and security, all kinds of technology, including
what you just saw here with Microsoft Excel.
7465.84 -> Welcome to excel problem solvers. This is
a set of examples of how you can use Excel
7471.52 -> in solving real world problems, problems that are
too complex to solve with a regular calculator, or
7478.4 -> to solve in your head. So we're going to look at
Susan and Tim. Every time we get a new page in
7484.4 -> this booklet, we're going to see that they have a
different task. And so here's what we're going to
7489.52 -> do for each page. First of all, we'll listen to
Tim and Susan and tell us about their problem.
7494.72 -> We'll build a spreadsheet with proper formulas.
Now I'm going to show you a partial solution. For
7500.16 -> each of these solutions, we're going to create
a spreadsheet with a little bit of a design.
7505.28 -> But I'm going to assume that you've already done
some Excel work and so that you can finish this
7510 -> with a little bit of collaboration with
your friends and a little bit of thought.
7513.84 -> And then finally, every one of these solutions
will have a graph, it'll be a bar chart that
7517.84 -> will show a comparison. So let's see what these
different problems are. The first problem we're
7523.36 -> going to look at is a school shopping list. And
so Susan has a lot of things to buy. And she has
7529.28 -> three different stores to shop that Tim also
has a shopping list that's slightly different.
7535.84 -> The second problem we'll face is, should we buy a
cat or dog. And so we'll look at all the different
7540.88 -> costs associated with buying each of those. The
next problem that Susan faces that she wants to
7546.56 -> take a vacation. And so we're going to give three
different options either a Caribbean cruise,
7552.16 -> Orlando or Chicago, Tim's going to do the same
vacation, but he has a different number of people.
7557.92 -> The next problem is that they're going to purchase
printers. And so there's three different kinds of
7561.84 -> printers to choose from. And the cost of ink
for each is different. Number five is more
7566.24 -> complicated yet, we're going to untangle the cell
phone bill. Number six is we're going to choose
7571.84 -> from three different cars, a very economical
car and a luxury. In this first Excel problem,
7578.08 -> we're going to go on a shopping trip. And
so we have a shopping list. Susan says,
7584.08 -> I'm Susan, would you help me pick one of
these three stores to buy my school supplies.
7589.36 -> Here's my shopping list. And so to the right of
the shopping list, you can see all the items that
7594.32 -> Susan would like to buy. Her choices are Walmart,
dollar trap, and office repo. Each price is listed
7604.08 -> below. And then her number of items that she
would like to buy are listed on her shopping list.
7611.04 -> So for instance, three ballpoint pens. at Walmart,
it's going to cost two or three times 50 cents,
7619.2 -> or $1.50. Now this is a large complicated shopping
list with many items. So this is a perfect way to
7626.4 -> solve a problem using an Excel spreadsheet. Inside
the Excel spreadsheet that we're going to create,
7632.32 -> we need to add a list of all of the items in
the shopping list, and then a column for each
7638.72 -> store. And so pause the video right now and
type these in just as you see on the screen.
7648.08 -> Next, let's go back to the shopping list.
Each price listed at each store has to be
7653.12 -> put into the spreadsheet, and then put
them into this column under Walmart.
7659.68 -> Pause this video until you
have the numbers typed in.
7665.68 -> Now you might say these look like dollars.
So let's make $1 sign each of them. Open the
7671.76 -> currency, add $1 sign. Let's add the prices
for dollar trap and also for office repo.
7680.32 -> So pause the video until you have all
the prices listed for each of the stores.
7687.36 -> Now I'm going to help you a little bit further
with the problem, but not completely. Let's start
7692.32 -> by looking at Susan's shopping list for each item
for the ballpoint pen. We know how many she needs
7699.44 -> such as three. And so I copy down
the number of items that she needs.
7705.2 -> Now, how would you figure out the amount of
money that she would spend on ballpoint pens.
7710.96 -> Let's put that formula right here. ballpoint
pens, you know that she's going to spend
7716.48 -> $1.50 if she shops at Walmart, three times
50. Here are some hints for the formula.
7724.64 -> In this square, which is G three, we're going
to multiply the quantity times the price.
7732.72 -> So enter your formula once you figure that
out. After you figure out the first formula,
7739.36 -> you'll be able to fill down and calculate all
the rest of the prices as well. At the very end,
7746.48 -> let's put in the total. And so you'll know
what the total cost is for purchasing supplies
7751.44 -> at Walmart. Repeat this same process for another
column, we're going to figure out the price for
7757.68 -> the entire shopping list the dollar trap, and then
we'll figure out the price again. And obviously,
7763.52 -> once you finish, you should have three total
prices listed down here. After you add up all
7768.24 -> of these numbers close. The last thing you'll need
to do is put a chart on the graph. And so copy the
7775.2 -> labels from row two down to the bottom. And when
you find the totals like I've mentioned here,
7780.64 -> you can highlight the entire two rows, create
your chart and it will be labeled correctly.
7787.76 -> Now for the other fellow, we're going
to have to make a another chart so
7791.84 -> copy everything you get from Susan and then
you could probably move it over to column K
7797.2 -> or so and paste all of the information
Just change the shopping list so that
7802.72 -> Tim's list will be hidden and shown instead
of Susan's, then you'll be finished Good luck.
7812.48 -> In this video, in Excel problem solvers, we're
going to look at a problem that Susan and Tim
7817.36 -> are facing. They want to purchase a dog or
a cat. And so let's see what Susan says.
7826.16 -> She says, Hi, I'm Susan, would you help
me choose a pet. I love both cats and
7830.96 -> dogs equally. But I would like to spend
as little money as possible each year.
7836.8 -> compare these expenses in a spreadsheet and show
me your answer and create a graph. First of all,
7843.12 -> notice that she's talking about a year. And so
at the bottom down here, when it says hints,
7850.24 -> it says use a spreadsheet to calculate the total
cost of ownership for owning a pet for one year.
7856.8 -> So we'll just assume one year is the length
of our expenses, pets, adoptions, dogs or 50,
7865.2 -> cats or 90. All expenses include all of
the vaccinations, spaying and neutering.
7872.56 -> However, there are more expenses
than just buying the cat or the dog.
7877.28 -> First of all, this is a list of what
we consider necessary accessories.
7881.68 -> The cat has three items, the dog is
four, so you have to buy this one time.
7887.92 -> The second box shows the ongoing
expenses, a box of cat food, kitty litter,
7895.28 -> and the small print says buy two of each of these
for my pet each month. And so we'll double that
7902.16 -> expense. Each month. The dog has some expenses to
his dog food is more money, and his dog treats her
7909.28 -> also an expense. Here's a good pattern for setting
up the spreadsheet, you can see that I have
7915.68 -> two boxes, and I colored these boxes. So that way
you can distinguish that there's a section called
7921.68 -> initial costs. So these are just one time costs.
And these are monthly costs. Then we have a column
7929.36 -> for the dog, and a column for the cat. When we're
done, we're going to have a total of how much it
7936.88 -> costs to keep the dog for the first year. So
we'll be adding up all the numbers above it.
7943.52 -> What are the initial costs, while
the purchase price for the dog,
7948 -> we have to buy a collar a taggable leash,
and then the initial total will be the sum
7953.36 -> of everything above it, the cat will have some of
these expenses. I think the cat will have a zero
7959.52 -> here because it doesn't need a leash. For the
monthly expenses, remember, there's going to be
7965.04 -> food litter and treats. So these will be
subtotal. And then we're going to multiply
7971.84 -> by two because we need to have a monthly total,
each one of those items was bought twice a month.
7980 -> For the one year costs, we're going to
have to take 12 times the monthly total,
7984.08 -> and then add it to the initial. So think through
how each of these costs need to be added in
7989.84 -> to get an accurate price for a dog
and an accurate price for a cat.
7995.12 -> When we're done, we will highlight these
two lines, create a chart and see you'll
8000.72 -> have a bar chart that shows the cost
of a dog compared to the cost of a cat.
8006.88 -> So there's a good outline for you remember, this
is just a partial solution that I'm offering you.
8012 -> You have to come up with the formulas and
use the numbers to get an accurate result.
8016.48 -> Welcome to the next lesson of Excel problem
solvers. This is number three, where we're going
8020.8 -> to take Susan and Tim and ask them what they want
to buy. We'll evaluate this with a spreadsheet,
8027.68 -> and then hopefully come up with a graph. So
Susan and Tim are both going on vacation,
8033.28 -> and they're going to explore either Caribbean
cruise, Orlando, or Chicago. First of all,
8040.8 -> she says I'm Susan, would you help me pick
one of these three vacations, my husband and I
8046 -> like all three vacation ideas, but we
want to spend as little as possible.
8050.56 -> We will fly stay five nights and four days.
Tell me which one of these will cost the least.
8058.24 -> And so we're going to build a spreadsheet to
answer that question, which one of these will
8062.24 -> cost the least? Let's look at the details of what
kind of vacations they are. The Caribbean cruise
8068.32 -> is the simplest, it's $555 with almost everything
included. The only thing that does not include
8074.4 -> it says here is airfare. So we have to buy plane
tickets to get to Miami. Orlando has theme parks.
8083.44 -> And so they're going to spend four days
and look at each theme park. So for her,
8088.48 -> it's going to cost twice the price of each ticket.
They're not going to rent a car. It says in the
8094.08 -> small print. They will spend money on a hotel and
they estimate $50 per person on food. Each day.
8102.08 -> Lastly, we have Chicago. Chicago is a city
of museums. And so they're going to go visit
8107.52 -> the natural history, the Museum of Art, the
Science Museum and the Broadcast history museum.
8112.8 -> And so each of these prices is per person.
In Chicago, they have to rent a car,
8117.92 -> their hotel is a little bit more money, and
they estimate about $50 per day for food.
8124.56 -> And this box over here, we show the costs of the
airlines. So Miami, Orlando, and Chicago are all
8131.68 -> different costs, and those are per person. Now
let's look at how we would build a spreadsheet.
8137.6 -> First of all, we have a column for each
city. So we have Chicago, Orlando, and Miami,
8145.12 -> at the bottom of the page, we're
eventually going to have a total
8148.4 -> for all of these cities. So we'll find out the
answer down here. And then we'll make a graph.
8154.16 -> I've divided the costs up into per person
expenses. So if you have more people in
8159.04 -> your family per person expenses will go up.
And then hotel expenses, assuming that all
8164.56 -> people stay in the same room, it will cost
the same for two people as it will for four.
8170.16 -> And so the hotel is more of a fixed
cost per night rather than per person.
8176.24 -> Now for some of these, we won't
have a number. For instance,
8180.32 -> if we look at Chicago, we will have no money for
Disneyland, so we can just put zero in there.
8187.12 -> Also for like the museum, the Science Museum, we
will not have a science museum cost in Orlando,
8193.92 -> some of these costs will be just zero, it
appears that there's something missing.
8198.56 -> I have hotels and tickets, but there is also
car rentals. So you might want to include
8203.2 -> another car rental expense, somewhere down the
bottom, and then add that into the subtotal.
8210.24 -> So calculate all of the subtotals per person
expenses, hotel expenses, car expenses,
8215.68 -> add them together, compare the three cities
and give Susan her answer with a bar graph.
8221.04 -> Once you have finished with Susan, then you
can use her work to copy and paste a new chart,
8226.399 -> a new bar graph, and you'll have Tim's answers
as well. Remember, he has two children, so his
8231.6 -> per person expenses will be higher. Good luck
and helping Tim and Susan pick their vacation.
8238 -> Welcome to excel problem solvers. So Susan, and
Tim, both would like to buy a new printer, Susan
8244.24 -> says would you help me pick one of these three
printers, I plan to print about 15 pages each day,
8250.56 -> so that if days per week, I want the total cost
to be as low as possible, I expect the printer
8256.24 -> to last two years, you'll see that the epsilon
printer is only $29. And so you might think that
8261.52 -> is the best price at the beginning. Until you
look at the supplies, you'll see that ink is $40
8268.16 -> a cartridge. And it will add up quite a bit. Tim,
on the other hand has a different request. He said
8275.2 -> I'm Tim would you help me pick one of these three
printers, I plan to print about 500 pages each
8281.52 -> day. And so his printer will probably be different
than Susan's. Let's look at the fine print.
8289.68 -> First of all, you'll notice that each cartridge
on the epsilon will only print 200 sheets.
8296.08 -> While at the other end, the zero printer
will print 11,000 pages for a cartridge.
8302.88 -> And so the initial price on the
printer, even though it's high,
8306.319 -> may end up being less expensive per page. Here's
a suggestion for how to set up your spreadsheet.
8312.479 -> We'll do columns B, C, and D for each of the
printers, we'll put in a line for their initial
8318.08 -> purchase price. And then we'll put the yellow
section for the cost of their print per page.
8324.64 -> And that's important because even though inkjet
cartridges are less than LaserJet cartridges,
8330.319 -> they don't print nearly as many pages. And
then for each of these people will have to
8335.439 -> compute their expected pages per day. So Susan
is only expecting 15 per day. This is the number
8342.88 -> she will actually print per year. And so you'll
need a formula to calculate that number. Once we
8349.04 -> know the pages per year, we can estimate how
much it will cost per year because we have a
8353.6 -> and then the total printing costs will come here
at the bottom. That total printing cost added to
8359.92 -> the initial purchase cost will give you the
actual cost for the two years of epsilon.
8366.72 -> And so your job is to figure out some of the
formulas that would make these numbers make sense.
8373.12 -> Once you're done, you will highlight the final
result. And you will create a chart that will
8377.52 -> show the differences in each of these printers.
Similar to the other problems. We're going to have
8382.08 -> a section for Susan, and then we'll copy the
information for to the right side and we will
8387.68 -> compute Tim's numbers. And so you should
have two charts when you're done and find
8392.399 -> out what the recommendation is for each of these
people. Welcome to the last in this series of
8398.399 -> Excel problem solvers. On this video, Susan and
Tim are asking us to help them purchase a car.
8406 -> Susan says, Would you help me pick one of these
three cars, I want to spend as little as possible,
8412.24 -> I want to drive the car until it has 250,000
miles, when I assume it will be nearly worthless.
8420.16 -> I drive about 30,000 miles every year.
Thankfully, I have enough money to save so I
8426.319 -> don't have to borrow money and pay interest
on a loan that would cost me an extra 40%.
8432.24 -> Let's see the three cars that Susan is
considering. First of all, she has in mind the
8437.12 -> Chevy Spark. It's a small engine that gets good
gas mileage, she has to pay 15 $100 a year in
8444 -> insurance. The initial cost for taxes is 1400
50. And every year she spends $210 for a license.
8454.8 -> Compare the other cars they have higher numbers
for each of those. So the Ford Mustang is 31,000.
8461.28 -> The gas mileage is much lower, the insurance and
taxes and license are all higher. Her third choice
8468.24 -> is the escalate. Certainly a luxury car with a
price of 72,000. Low gas mileage, high insurance,
8476.399 -> high taxes and high license fees. Just how
much more will that be than the other cars?
8483.2 -> We'll find out when we make the bar graph
to compare the total cost of ownership for
8488.08 -> all of these cars. Tim said he would also like to
buy one of these three cars, he wants to spend as
8493.52 -> little as possible, he's going to drive the
car until it has 250,000. He drives 30,000
8499.28 -> miles a year. So everything so far is the same as
Susan. However, the difference is he does not have
8504.88 -> enough money to purchase the car. And so to make
things simple, we are just simply going to add 40%
8511.359 -> of the price of the car to his spreadsheet. So
that will include all the interest in bank fees.
8518.8 -> Now let's look at the spreadsheet
to solve this problem.
8522.399 -> On the left side, we have Susan and all of the
calculations that we need to come up with a
8527.04 -> price for which one is going to cost the most.
We'll put the cars in column B, C and D spark
8533.76 -> Mustang in escalate. The yellow section at the top
we'll talk about the initial costs. So this is the
8539.92 -> price that she pays the day she buys the car,
she has to pay the original price plus taxes.
8546.8 -> The more complicated question is how much
does it cost to own this car every year?
8551.52 -> And there are three costs that we need to add
together. We have insurance, license and gas.
8557.359 -> How much are we going to pay for gas? That's a
more complicated question than just looking at the
8562.399 -> price at the pump. We have to know several things.
And so we have a blue calculation area from row 12
8569.6 -> to 16. First of all, we need to ask the question,
how much does she drive? How many miles every
8576.64 -> year? What is the miles per gallon or the mpg
for her car? How much is she paying for gas.
8585.12 -> And then we will know how much
she pays every year for gas.
8589.12 -> Add those three together, and we will have total
annual costs insurance plus license plus the gas.
8596.319 -> Now the question is, how much is this car going
to cost over the entire lifetime of the car? Well,
8602.96 -> first of all, we know how long she wants to drive
she said 250,000 miles after which we will assume
8608.8 -> that the car has basically no value left and
she can give it away to a friend. To find out
8613.2 -> how long the car will live. We have to ask how
many miles does she plan to drive each year?
8618.72 -> What is her goal, which is 250,000. And then we
can use those two numbers to find out the total
8625.359 -> lifetime expectancy of the car. Finally, we will
arrive at the bottom where we get the calculations
8631.359 -> for the annual cost of the car times the
number of years that we expect the car to live.
8637.28 -> We add in the initial price of the costs. And we
have a total lifetime cost of the car it might
8643.439 -> surprise you that you'll see the numbers up in
the range of where you expect to pay for a house
8649.52 -> down a little further. We will calculate the final
answer in row 27 and 28. We will calculate the
8657.92 -> average cost of a year. So we know how long the
car expects to go how many years it expects to go.
8665.359 -> We also know how many dollars we have total.
And so now we can find out what each year will
8671.92 -> cost for the spark Mustang and escalate.
highlight these last three numbers with
8676.56 -> their labels create a bar chart and you will
give Susan some good information on how much
8682.16 -> more it will cost to buy luxury cars. On the
right side of the equation we will have Tim
8688.479 -> Tim is going to be calculating almost the
same values except him doesn't have any
8694.16 -> money saved. And so the initial cost of his car
is going to be 40% higher In what Susan paid,
8701.6 -> and so that 40% includes all the interest in
bank fees. So everything else should be the
8706.8 -> same. He's driving the same number of miles and
he expects to go 250,000 before the end of life.
8713.68 -> And so when you're finished with Tim, you'll have
a similar graph. But you might find his expenses
8719.84 -> quite a bit higher than Susan because he doesn't
have enough money to pay for the car in cash.
8733.12 -> Congratulations, you've made it to the end of
the course if you've survived this long, you
8738.08 -> have gone from the beginning to an expert person
in Excel. Now, I appreciate you spending the time.
8745.04 -> And if you're interested in these kinds of
things, check out my channel on YouTube. My
8748.8 -> name is shad Sluiter. I'm a professor at Grand
Canyon University in Phoenix, Arizona. I not
8754.08 -> only teach with Excel, but I teach programming
so you can learn how to build software, websites
8759.439 -> and mobile applications. So thanks for joining me,
and best of luck with what you've learned here.
Source: https://www.youtube.com/watch?v=Vl0H-qTclOg