Hi, and welcome to this course called Excel from the beginning. In this course, we're going to
learn how to use Excel using six projects that you can use in real life. So my name is shad Sluiter,
and I've been a teacher for computer science and computer applications for several years,
what you're about to see are applications that I've used in my classroom, and have
been successful with other students in the past. So I really appreciate the opportunity that Free
Code Camp has given me to present this to you. I have used Free Code Camp in my own classroom,
with success with students from high school to the university level. And so we're going to see
part of what I've done in class. And I'm glad to contribute back to help you as well. Let's take a
look at some of the projects that we'll build in this course. So Excel from the beginning is going
to use six projects, the first one, and it'll be the most basic will be to set up a payroll. So
imagine if you were a company and trying to pay your employees and keep track of their overtime.
So that's what we'll do in this first project. The second is to set up a gradebook. And so we'll be
doing computing on percentages, we'll be finding who's in the top of our class and who's not.
Also we'll have a factor decision tree factoring program. So we will try to decide what career
would be best based on what we prefer, what the pay is and other benefits of a job. And so a
spreadsheet will help us determine how to make a decision. Another application we'll create is
a sales database. And so I will give you a bunch of data and we will sort that we will determine
who are the best salespeople what their commission is, and make some charts, we'll also do what's
called a car inventory project where we'll create what's called database actions, we're going to
have a large number of data again, and we can show you how to concatenate fields, how to split them,
and how to make reports with this. And then lastly, I'm going to reserve this section for
six different problems that we're going to solve. So the first five projects are more tutorials to
show you how something is done. And then I'm going to give you some challenge assignments at the end,
where I give you a half of a solution. And then you use your creativity in what you've learned in
the previous courses to see if you can solve the problem. And so both we have a tutorial section,
as well as a practice where you can put things into service. Now let's take a look at some of
the things you'll learn throughout here. So some of the terms, you'll learn from the very beginning
how to enter data, how to navigate through a spreadsheet, and how to save it. Also, I'm going
to show you how to set up formulas because using math formulas, simple formulas to solve problems,
such as how to compute overtime, or how to compute the percent that a salesperson should
get on commission, or to simply do averages and to find the maximums and minimums. So those are basic
formulas will create charts. So you'll have pie charts and line charts, and you'll have scatter
charts, Excel is really easy and creating charts, you basically have to choose what kind you want.
And it does it for you. Also, we're going to do Excel with some more advanced features.
So relative and absolute references is used if you have some kind of a factor that you
want to use and other parts of the spreadsheet. And so that's another feature that we'll see.
We're going to do imports and exports of data. So CSV is a common file you find on
the internet. So maybe your bank statement comes to you in the mail, or at least online, I guess.
And it says we produce these items in CSV format as if you know what CSV format is.
So I'll show you how to use this. And so you can interface with other programs on the internet,
we're using something called v lookup. So v lookup is a powerful tool that is like searching
through a menu to find the right item for your choice. Also, we're going to use pivot tables,
pivot tables are summary reports. So if you have a large amount of data, and you want to condense
it to a very readable decision, this is kind of a big data tool. So the last one is to split text
and concatenate text. And so you can modify things according to how you want them to be.
So that's some of the features that were ahead of us. If this looks interesting to you, then
please let's continue on with the first lesson. So here's the first lesson, we're going to work
with something called payroll. And you can see that we're going to set up a group of employees,
we're going to give them some fake numbers, their hourly wage, and calculate once again,
with fake numbers, how many hours they worked, then we're going to take those numbers and use
some simple formulas to fill in these columns of data where we can see how much they get paid.
Also, I'll show you shortcuts so that large numbers of columns and rows get rather tedious but
there are shortcuts in Excel to make it work fast. So let's get started with our first lesson ever
an Excel so this particular lesson on Excel the beginning parts of how to use Excel has been seen
over a million times. on my YouTube channel, so welcome. If you haven't seen it before, this is a
great way to start using Excel, we're going to use Excel to create a spreadsheet for our business.
We're going to launch the program and start a blank workbook. Now since this is the first
assignment that we're going to do with Microsoft Excel, we need to go through some of the things
that you see in any spreadsheet. First of all, the spreadsheet is designed in organized according to
cells. Just like in the game battleship where you have columns that are letters and rows that are
numbers, you can identify cells such as this one by such as G six. To enter in information into a
cell, you simply click it and start typing. And so I'm going to type employee payroll,
press Enter. And you notice that this cell, even though it extends into column B, really,
it is only in a one. Now let's use Excel as a payroll, we're going to keep track of the
hours that our employees worked this week. And we'll keep track of their wages. So first of all,
we need to put some column headings in. So I'm going to put in the title,
last name, and then first name. And then I'm pressing tab between these keys so that it moves,
the selection moves to the right, hourly wage. And then I'm going to put in the date,
let's say January 1, and press enter. Over here, I'm going to say that this is the hours worked.
And this is their total pay. So I'll just put the word pay. Now you notice you can expand a
column by clicking between the column headers like between D and E. Now you can see the whole word.
The next thing you need to do is you need to invent some names. So you get to come up with
about 20 names, maybe 15 for your employees. I'll type them in now. Now you can see that
I've invented names, last names and first names. And now I'm going to give them an hourly wage,
each person makes a route $15 or so. So I'll type in some numbers here. You notice when you type in
numbers, they are right justified when you type in the person's name in your spreadsheet, that
they are left justified. That's just a convention that Microsoft Excel uses so that you can identify
letters versus numbers. Now one of the items that you will most commonly use in Excel is formatting.
These are all hourly wages, I'm going to select this range with my mouse by clicking and dragging.
The first square is actually highlighted even though it's white, the other ones are gray. But
what I want to do is change them into a currency. So I'm going to click on the dollar sign up here.
And you notice that they all become dollar signs. They all are the hourly wage for each employee.
Now let's invent a number for each of the employees for how long they worked.
The average workweek for a full time person is about 40 hours. So I'll create wages or sorry,
I'll create the number of hours that each person worked in this column.
After giving each employee a number of ours, we're going to start working with formulas. Now,
over here on this line, this square in e4, I'm going to create a calculated
number a calculated cell. If a person gets $15.90 an hour, and they work for 40 hours.
How much do they get paid? Well, fortunately, the calculations in Excel are quite easy to do.
on my keyboard, I'm going to press the equal sign. And you notice up here in this
area called the formula bar, we have an equal sign, as well as the equal sign in the cell.
Now I'm going to click on the cell that has the wage in it 1590. When I click there,
the letter C four is entered into the formula bar, c four is the wage $15.90.
Now I want to multiply 1590. So I'm going to press the shift and the 80, which is the multiply sign.
And then I'm going to click the 40 the dot the number of hours that this person worked.
So my formula is equals to c four times the number that's in cell D four. When I press enter,
it'll tell me that this person gets $636. If you go and change some of these numbers, such as
we added an hour here and I type 41 and press enter. That calculation is automatically updated.
When you double click on a cell that has a formula, you will see that the colors
correspond to the cells that you clicked. So c four is in blue, which is this number
And D four is in red. In a press ENTER again. And it shows the numbers again.
Microsoft Excel has a nice feature where you can copy and paste formulas. If I right click
on this cell and choose Copy. And then I click on the next cell and choose Paste,
it'll automatically calculate the next line. You can also highlight a range and choose right click
and paste, it will fill down where all of those numbers are, why is this one luckily look like
railroad tracks where a bunch of pound signs, it's because the value is actually too large to be held
in this in this cell. So I need to make the column a little bit wider. And now it becomes visible.
Another way to fill down if you want to, you can go to the corner of a cell with this little green
dot. And if you click exactly on the corner and drag your mouse down, it will also fill down the
values in that column. And so now I've quickly calculated the pay for every single employee.
One more thing we're going to do with this formula is we're going to add a few lines at the bottom.
I want to know who is the maximum, press enter, who is the minimum, press Enter? What is the
average pay, and what is the total pay these numbers max, min, average and total are just
labels. But over here in this column, I'm going to put in a corresponding formula. Microsoft Excel
has hundreds of formulas that are predefined. Here's one of them, you type in the equal sign.
And then you type the word MA X, you notice that these automatically are drop down menus that show
that this is one of the formulas that Excel knows about. After typing Max, I use the open
parentheses, which is the shift key and nine. And I'm going to drag my mouse across a range of cells
till they come to the very top, release the mouse button
and press Shift and zero to do a close parentheses. So what this will do,
it will give me the maximum of all of these cells, the maximum person
is getting $45 an hour. And so you can see in this line here that Trent man is making 45 an hour. Now
I'd like to know who the minimum is. So I'm going to do the same process type equals sign, am I N,
open parentheses and drag my mouse across a range. And I will tell it what the minimum is.
After I press Shift and zero, enter, and the lowly person in my office is getting $6.90. It
looks like it's Paul Smith. Now what's the average employee? Well, I can type in equals and then Av.
And you see there's a bunch of formulas for average, I just want this most simple one,
I'm going to choose average. I'm going to select the range,
close parentheses, and press enter. The average employee makes $16.48.
Okay, let's take this range here of three different formulas, highlight it,
right click it with the mouse copy. I want to click in the next column over right click
and pastes. And now you see that we have the maximum, the minimum and the average for the
number of hours that are being worked. Now you notice that these values are listed as dollars,
they're actually not dollars, they're number of hours that the persons are working. So I want to
change these formattings back to general numbers. Up here in the ribbon, where you will find the
number formatting area, there's a small arrow that if you click it will bring up a dialog box
that shows all different types of formatting for numbers. I'm going to select general and click OK.
Now, why does this number have so many decimal places in it? It's because it's because we are
computing an average. It would be nice if we would just round these off to maybe the
10th the 10th place or the hundreds place. So let's go to these zeros here. And we're going to
decrease the decimals that we're rounding to so to the nearest 10th is good enough for us. So
now we have the number of hours maximum minimum and average for our employees here in column D.
for column E, we could also find the min the maximum the minimum and the average as well.
So let's highlight this group, I'm going to select this bottom right corner, drag to the right.
And it fills to the right now and shows me that these are the maximum dollars that someone made
the minimum dollars and the average. Once more, these are dollars figures, these are not hours.
So let's change these to the dollars format. So I'm going to click up here on the dollar sign.
And now we have the average the maximum and the minimum in the salary for this for this week.
So now we've created the maximum the minimum and the average for each of these columns, column C,
D, and E. We haven't done anything yet with a total. For the total, let's find out what the
total number of hours worked was. And let's find out what the total number of wages that we've paid
is. So for the word sum, we type equals su m, and that will give us the total. Let's do shift
and nine. And then I'm going to select a range here. I'm going to select all of the hours worked,
close parentheses and Enter. And you'll see that there were 695 hours worked in my shop.
Now I'm going to fill this to the right, and shows that I made a lot of more paid a lot of money
for my wages this week, I'm going to change this to $1 sign format. And it shows now $11,532
The last thing we should do is put our name at the top, so maybe c one is a good place,
type in your name, press enter, save the spreadsheet, print it. And you're done
with assignment number one for Excel. Welcome to assignment number two for Microsoft Excel,
we're going to extend assignment number one using the payroll spreadsheet that
we had created earlier. What I'd like to do in this assignment is add a new formula
using the if formula, and also adding the idea of paying overtime to our employees.
So you notice here in column D that we have the number of hours that each employee worked,
the first employee worked 41 hours, we're going to give him a bonus for his extra hour of overtime.
So I'm going to start by inserting a column here in column E. If I click the column header,
the entire column is selected, I can right click on the column header and choose the Insert button.
And it will now give me a new column to work with. The first column, column D was the hours worked.
Now I'm going to say this is the overtime hours. The overtime hours is a calculated field. So let's
come up with a formula that will tell us how many hours extra that this employee worked. At first,
it would seem simple, we would just say equals the number of hours the person worked, subtract 40.
And that will tell us how many hours of overtime this person has. It works great if you have 41.
But there's some errors. If you have less than 40, I'm going to fill the column down. And you will
see that when we come to Paul Smith in row number eight, he actually gets a negative one hours,
it's a negative hours that he's worked, that doesn't seem to work too well. What we would
rather give is Paul should have zero hours of overtime, not a negative number. So let's revise
the formula a little bit. So let's go up to here. And this time, I'm going to type in an equal sign
with the word if if is a command that says let's take a logical test, and we will a logical test is
either an equal sign greater than or less than, and then we'll give it a value depending on if
that test is true or not. And if it's false, we'll give it a different number. Watch how this works.
The question is, if his hours worked, is greater than 40. And a comma,
the value if it is greater than 40 should be his hours worked minus 40.
Comma, and if he worked less than 40 hours, then let's just give him zero for this column,
which would be zero hours of overtime, I close parentheses and press rest press return.
So he still gets one hour of overtime. But now when I fill the formula down, you see that
the people that worked less than 40 hours gets zero in their form in their formula instead of
negative numbers. And so this is the correct formula for calculating overtime pay. Now,
how about the pay? What does that do? Let's go back and review this here. If I double click here,
you will see that this formula takes the number of hours hourly wage and fills it in column F.
That's great, but now we give them a bonus for working overtime. So let's create create a new
column and call it overtime bonus. Now in this formula, we're going to calculate a bonus for
the number of overtime hours they worked. So I'm going to say equals point five Use the time sign
point five times their hourly wage, so we're going to give them half of their hourly wage times the
number of overtime hours they worked. What that does is it gives us a formula to give them time
and a half, or one and a half times for each hour that they worked overtime, press enter. So since
john Kern earned one hour of overtime, we give him his full 41 hours of pay, plus an extra 50% of his
pay for one of those hours. Let's fill this down and see what we have. For the rest of the people.
Some of the people have zero. This one got no extra time bonus, because he worked 39 hours. Some
of these people worked 40 hours and still got zero time. Why? Because the formula is about how many
hours beyond 40 that we're calculating. So they got paid their full wage for the first 40 hours.
Now, how about the total pay, let's put in a new column, the total pay is simply
equals to their regular 40 hours of pay plus their overtime bonus and a return.
And let's use the fill down option. Some of the numbers are too big, so we will expand the column
and this is their total pay. Down here at the bottom, we calculated maximums minimums,
averages and totals. For all of these formulas. Let's move these to the right.
Calculate the totals now and see we can see what everybody earned including their overtime.
Welcome to assignment number three with Microsoft Excel. What you see on the screen before you is
a final version of the payroll spreadsheet, you notice that it's got a lot of cells, don't worry,
most of these are copied and pasted. It's an extension of assignment number one and two.
In the gray area here where you see hours worked. In the previous assignments, we
only were concerned with one week of pay. And this assignment, we're going to add four more weeks of
pay. And we were you will use calculations to find out how much they earned their overtime bonus,
and then here in the blue area, their total pay. So we'll start from an assignment here
that we did before. And we'll simply extend it to look like the one that you just saw.
Well, first of all, what we need to do is add some new columns. So starting in column E,
I'm going to right click and choose Insert and clicking on the column header.
Let's do this a few times. And will give us some space to work with for other weeks in the month.
Now starting here in column E three, I'm going to add seven days to this date. Notice the first
date that we chose was January 1, you can either type in January 8 for the next week, or we can
use a calculation, let's use a calculation I'm going to say this equals this square d3,
plus seven. And now it starts at January 8. Now if I use the fill command, I'm going to create
several more columns. And they're automatically calculated.
Next, I'm going to invent some more hours. These are numbers that should
be approximately 40. They can be a little less, they can be a little bit more.
But it doesn't matter really what the numbers are just so that we have some data to work with
for each employee. Now we've reached the end of our data for the number of
hours for each employee. It looks like I've created one extra column here in column II,
so I'm simply going to remove it by choosing right click on the column header and delete.
Now let's go to the overtime hours. We're going to put a date here for the week, January 1. And
once again, I'm going to insert some columns. You can actually insert columns more than one at a
time. If you highlight four column row headers and choose Insert, you get four new rows. Once again,
I'd like to add seven to the date that's in January 1, this plus seven and fill to the right.
Now let's add some overtime hours. Now I want to calculate the hours of overtime for each week.
Well we've already done this in the previous assignment with this famous if formula,
if d four is greater than 40, then give us the value of d four minus 40. Otherwise give us zero,
I can take this entire block and copy it, we're going to right click on it, choose Copy.
And I'm going to right click on the next square and choose Paste.
So now it's calculating the overtime hours for the week of January 8.
Let's double click on this cell. And you notice that we are now calculating the overtime hours
from this blue square the 42 hours from January 8. So the formula automatically adjusted for the
column letter. This is using cell e4. I'm pressing escape now double clicking on this one. And you
notice that this cell was using this cell D for as its source for its numbers. So when you copy
and paste, Excel automatically assumes that you are relatively addressing columns somewhere else
in the spreadsheet. So if this is one cell to the right, then the next one is one cell to the right.
I'm going to copy and paste again. So I'll copy this row here. Copy and paste it.
Copy and Paste again. And paste again. So now we have overtime hours calculated for every week.
Now when you start to get a lot of cells on a spreadsheet, it can get confusing
looking at so many numbers. So Excel allows us to paint the cells in certain colors.
These cells here that I'm highlighting now are all related to the number of hours that they worked.
Let's give them a color. Let's paint them all something gray.
So now you can see that they all belong together as a block.
For overtime hours, let's paint them a different color. Let's choose,
you can choose whatever color you like. But I'm going to choose some kind of a salmon color.
And now we're going on to calculate their pay.
This pay here really is the pay for January 1. So the week of January 1 is what they got paid here.
Now I'm going to calculate the pay for every week. So let's insert about how many three more columns.
And let's do once again equals the previous date plus seven.
And then fill that formula to the right. Looks like we need one more column.
I'll fill this one to the right. Now, how do we calculate pay, we simply took two numbers from
their hours and the wage hours worked, which was 41 times their wage, we're going to find something
new here. If we just simply copy and paste these formulas, we're going to get the wrong result.
I'll show you what happens in a minute. And a copy this formula, paste it for the next week.
Now why in the world is everyone making over $1,000 some up to 3300.
What happened? Well, let's double click on this cell and find out where it's coming from.
First of all, you notice is very difficult to see the other side of the world here,
we can actually zoom out on our spreadsheet.
And we can make it easier to see the whole page. So let's zoom out to 50%.
We can see the whole spreadsheet now but the numbers are a little small.
Let's choose something else. I'm going to select the part that I'm interested in
just the cells. Click on zoom and choose fit selection that will fit this highlighted area
on the screen. So now I can see hourly wage all the way to the last formula. Okay,
now let's go look in this cell here. What's going on in cell number Oh, four. It says take the
cells D four and multiply by E four and give me the results D four and e4. Remember great while
we were in the previous week, we were taking the hourly wage times the number of hours. Well now
the relative referencing is saying well let's take the first two cells and multiply them together.
So we need to make a modification This actually should be referencing c four, and pressing escape
on the keyboard. And I'm going to delete all of this here, I'm going to right click on these
and choose clear contents. What I need is what's called absolute cell referencing. So I'm going to
modify my original formula here. It's telling me in this formula, that we should use C four times
d four. And I know that I'm going to copy it to the right four times. Well, what I really want
to do is keep referencing the hourly wage, because that's how you calculate pay hourly
wage. In C four times the number of hours worked, which is going to be D, E, and F and G and H. So
is there a way to tell Excel not to use relative referencing instead absolute referencing, that's
what we're going to call it anyway, I'm going to go up to this formula bar and modify the letter C,
I'm going to simply type in $1 sign in front of it, that doesn't mean a value dollars, it just
means that every time that you think about sales, C, or C four, you're going to always use column C.
For the first week, nothing changes, all of the numbers should stay exactly the same.
But now when I copy this range of cells, and paste it into the next column,
let's take a look at what this reference is. I'm going to double click here,
you notice that it is still referencing cell C four. But now the other cell is relative
referencing says let's move to the right one every time. And so it's now multiplying the
hourly wage times the hours from January 8, or the 42. I'm pressing escape on the keyboard.
Now I'm going to highlight this whole range. This time, I'm going to instead of copy and paste,
I'm just going to use the fill right option, using the little square in the bottom right corner.
It feels right. And all of the wages are calculated, let's double click here.
You see it's using their proper range, it's number of hours. In this case, it's 30. But it's still
referencing c four. And so now their overtime, I'm sorry, their their regular pay work is all
calculated correctly. Well, let's give this range a separate color as well. This is our regular pay.
So I'm going to highlight this section here. Go back to home and choose a color from the
bucket. Let's see it's time I like green, green for pay. Let's move to the right a little bit.
Now we need to calculate their overtime for each week,
their overtime pay. Well, we've done that for the first week. But
we need to have some more weeks. So let's insert four new columns. Choose the insert command.
Let's put in a date for each of these columns. This one was January 1,
this one's going to equal the first cell plus seven and then fill it to the right.
And so we have all of the weeks for January.
Once more, this is going to be a problem. If we don't have absolute cell referencing right now.
The overtime pay is correct for the first week. If we fill this to the right, we're going to have a
problem, we're going to have numbers that are way too high, like this one. If I double click here,
you see it's referencing overtime hours. But way back at the beginning, it is referencing
not the wage that we're expecting. It's referencing cell D four, so we're going to
have to change this formula to use absolute cell referencing again. So I'm going to
clear these highlight and choose clear contents from the menu.
I'm going to double click on this formula. And instead of C four, I'm going to reference it
as dollar sign c four. And I'm going to copy this formula all the way through the block.
Here's another way to copy and paste through the entire block. I'm going to copy just one cell and
then highlight the entire place where I want to use this cell and choose Paste
and all of the formulas now show the overtime bonus formula for each week.
Okay, the last thing we should probably do is give this its own color. So let's use the overtime
pay as a color such as I don't know what Pick something red, something blue, how about blue,
blue is looking good. Now for the total, what in the world is a total gonna look like?
First of all, I need to zoom out a bit. Let's go to zoom to 50%
total wages. Well, the total wages right now is calculated using, it looks like
pay plus the overtime bonus looks great. Let's put in a date over this,
this is going to be January 1. And once more we need to calculate equals this cell plus seven.
And we're going to use a few more weeks of this. Now this time, we should be able to get away
with using relative cell referencing five, highlight this and I'm feeling to the right.
I'm going to double click on that cell titles so that they all adjust to the proper width.
Let's check this one out. If I double click on this cell, what's it adding together
looks like it's adding the pay from the first note from the second week, times the overtime
bonus for the second week. That's exactly what we want it let's see a try another one here,
I'm going to pick this one randomly chose one cell, it looks like it's heading the proper
cell. So in this case, we don't want absolute cell referencing the usual default settings for
relative referencing work just fine. Let's add one more color. And we'll call it a day. Let's go to
a darker gray. One more thing that you might want to do is use the formulas across the bottom.
I'm going to actually highlight this section here. Fill it all the way to the right.
We calculate the totals the maximums the minimums for every week.
One more thing you might want to add here is the total pay for all weeks. Let's say January pay.
And I'm going to put in this equals sum, formula equals sum, and then add up all four of these.
five of these with a closed parentheses. Let's zoom in a little bit so you can see better.
Double click again. So we're adding up all five weeks that were paid in January.
And then fill this down for everybody. You can see now who is the top pay earner in the company,
I'm going to copy this range, paste it over here. The maximum person is this one,
earn $8,000 this month that looks like this line right here. Second from the last employee, let's
scroll to the left and see who that is second to the last employee. The name is Trent man.
Now when you print, you're going to have a difficult time fitting all of this on one page.
Fortunately, Excel gives us a nice way to print all things on one sheet. Let's go to the File menu
and choose Print. Now down here, it says no scaling, we are actually using five different
sheets of paper to get everything printed. It's going to be very wasteful, don't do that.
First of all, we can turn the paper sideways. So let's change the orientation to landscape.
That helps a little bit now it's only four pages wide. It says scaling here. We can say this,
we can fit all of the columns on one page or fit all the sheets in one page. Let's try that.
Okay, it's very small. It seems to work though.
Let's save it and print it as this size. The next lesson we're going to do is called grade book.
And you can see that the grade book is not just a simple grade book, but we're going to show what's
called conditional formatting. That's what those little colored dots are. They're going to show
who are the top students in your class and who are the bottom. We're going to do percentiles,
as you can see on the right side. And so several things about a grade book and formatting data
that we haven't done before and you'll have a very Nice looking chart when you're finished.
What you see on the screen in front of you is a grade book for employees,
we're going to give them a series of tests for the company, a safety test,
a company philosophy tests, a financial skills test and a drug test. And then we will give them
employment based on their testing level. So let's assume these are all new employees.
And they are testing after their job interview. So this is the completed spreadsheet. Let's start
from scratch and build it one place at a time. So I'm going to file and choose New and a blank
workbook. Let's start by giving this a title and the first cell, let's call it grade book. And
we're going to keep people's names in here. So we'll put last name here and first name.
Now you can see that I've typed in the names of the employees that are used from my payroll
spreadsheet, you can just copy and paste these names in or if you'd like to re type different
names, you can, but we're going to use up to line number 20, for those that are going to be taking
the company test. Now you notice that I put the titles of each test in C one, D one, e one and f1.
The first test is called safety test. The next one is called the company philosophy test.
The third one is the financial skills test. And then finally, the drug test. Notice that
these words all run into each other. Let's do a format on these cells to make them fit better.
I'm highlighting all four cells. And up here on this button.
It's called orientation. I click it. And let's choose rotate text up. And now,
each of these cells is written vertically. So it allows us to make the columns narrower.
Now, how much is each test worth? Well, let's fit in here and B two, and let's put in here,
points possible. That'll show us what the maximum grade is for each of these.
Let's say the first one is worth 10. The next test is worth 20 points, the financial test is
worth 100. And the drug tests we're just going to have as a pass or fail. So it is worth one point.
Notice that I need to make the column E a little bit wider because 100 doesn't fit in
the square very well. Now, all we have to do next is starting with john Kern is invent a number, how
many points did john earn and so on? Now, you can see that I've entered numbers for every one of my
employees. So the maximum is 10. Nobody has more than 10 points do they owe Trent man got an 11.
But then over on this side where it says drug test, you notice that everybody has a one or
a zero. So there were two employees that got zeros, that means they failed their drug test.
Next, let's calculate the percent that they earned for each of these tests.
I want to take the titles and copy them. So I'm highlighting all four of these,
right click on one of them and choose Copy. And let's put them in the next
area over how about column H. We'll start with column H. Now what would be the formula for a test
and the percentages? Well, you would take the equals, and we'll take this number 10
and divide it by the points possible divided by 10 and press enter. This turns out to be a one.
Actually I wanted to say 1%. So I'm going back to the ribbon and choosing the Home
tab. And you'll find in here the formatting for percentages. Here's a percent sign.
So on the safety test, john Kern earned 100%. Now I'd like to copy and paste this formula so that
all the percents are shown for this assignment. Let's just fill it down and see what happens.
Now there's a problem. This one says divide by zero error. This one says 80%. This
one's is 90 90%. And this one says 100%. It should say 90, so it's not working like we thought it
would. Let's double click on this 100% do you see what's being divided, it says take C seven and
divided by C five. Really what we want to do is take C seven the score that Wendy received and
divided by the points possible. And so this error is what's called a relative
referencing error. It's counting back two cells and dividing by two cells above it. We
need to use absolute referencing here to get the correct results. So I'm going to clear these and
try again going to right click and choose clear contents. What I really want is to take equals
the cell here of the points that john earned and divided by this number with an adjustment
I want to absolutely choose row number two every time. So I'm going to put $1 sign in front of two.
And so now when I fill down, it's going to always reference row two for all of these assignments.
And so Indeed, we get the correct answers, there is 110% listed here. That is correct,
because Trent earned in 11 points. Now, I should be able to just copy and paste these cells,
or use the fill command and calculate all of these numbers, immediately.
Notice the drug test is either 100% or 0%. And so now even though the
tests are all worth different points, we scored each of them with percentages.
Now, it would be nice to tell at a glance which of these students are which of these employees is
doing extremely well and which ones are failing. Let's highlight a set of test scores. Let's do the
first one here. And I'm going to do conditional formatting. Conditional Formatting will color the
cells according to the numbers that are inside. So on my ribbon, I look for conditional formatting.
And in this case, I'm choosing icon sets. This set here is like a set of traffic lights,
it's got red, yellow, and green, and then a black one. Automatically, it puts an icon based on
who's in the top set percentages and who's in the bottom. So you can see that the green
lights show up immediately, with the top test scores. There's a red light for somebody that's
failing, and then there's a black one for an absolute miserable score of five. Let's try
that with the next row. Let's highlight the next set. And we'll choose conditional formatting,
and icons and choose the traffic lights again, and make the box a little bit wider. So you can
see once again, that Karen is having difficulty she received a six on the company philosophy test.
You have to do these all individually, because each one of them has its own set of scores.
If you try to format them all at the same time, you'll get different results. Choose
the traffic lights, again, for our financial skills test, and see who sorts to the top.
This time you notice right away there's a black line, or a black.on blessing,
she's not so good at financial skills. And for the drug test. The last one, it will format it again.
Under icons and feel free to experiment with the others. There's color scales, and there's
data bars, interesting things you can see on each of them. We're just using icons for these.
And so now we have little traffic lights, showing us quickly who is doing well, you
can see there's four green lights for john, are some people that have mixed results in between.
Now let's also make a rule that we want to sort out people that receive less than
50% on any test. So a quick way to find out who that is, would be to highlight all of the test
scores and their percentages. Let's go back to conditional formatting. Now here's a nice option,
the first one called highlight cell rules. Let's choose the one that says less than.
So in this in, we want to find out who is receiving less than and let's put in 50%.
So I can put point five in here. And then the options here are light red filled with a dark
red text, you can choose different options, but we'll just leave it as the first and click OK.
And right away, you can see that at a glance, there are some people that have problems,
less than 50%. For Karen, less than 50% on a drug test means you failed it you got zero,
less than 50% on financial. And so we have a few of these people that are problematic.
Now since I have a concern about who should be fired, I'd like to create a another line called
fire employee with a question mark. And then we're going to say should we fire them or not?
We're going to use a formula that asks this question. Are any of these scores less than 50%?
If so, then we should fire them, you should at least pass with 50%.
So the formula we're looking for is called the or formula. he typed the equal sign and type or,
or in a parentheses. This means that we're going to ask a series of logical questions is
some number less than another one? And if so, then we're going to return true. Watch this,
I'm going to say or is this score here the safety test. Less than 1.5
and I'll put a comma. And then I'll ask another question. Is I for less than 50%? Another comma?
Is this less than point five? And then finally, the question is, is, comma is this one,
less than point five. So I have four questions in a row,
all of them asked the same thing is this number less than point five. And if I press enter,
it says false. None of these scores are less than point five. However, when I fill down, you
will see that some of these scores are less than point five. Here's a true and a true and a true.
Once more, we can see from this side from this line, Who should we fire? Who should we dismiss?
Let's do a conditional formatting on this one. Let's go highlight the cells choose
conditional formatting. And this time, I'm going to ask the question is equal to
is this question equal to true, I can spell true correctly. and press Enter. And so now
all the ones that are true are highlighted in red. So we know who we should fire.
Lastly, let's put some numbers at the bottom of the chart.
Let's go down to here and use our famous for max min, average.
Let's just use three max min and average. So in this cell, we ask equals this is going to
be the maximum of the range that's above it. So maximum of all of these scores with a parentheses.
And let's do the same for minimum equals min, parentheses. And we'll ask about all of these
parentheses, what is the average score equals average, and then the cell range.
And then we can use the fill right? option, show what the averages is for each of these.
We're going to copy all these and put them on the other side, where the percentages are.
Now since these are percentages, it would make sense to format them with a percent sign.
Let's create a chart that shows all the graphs of each of these scores from the safety test. I'm
going to highlight safety tests. And now let's go to insert. And let's choose a chart. Here's called
a column chart, the column chart, we'll just click it, and we'll choose clustered columns.
Slide it over to the side. Now we're missing some things on it, we need a chart title,
double click where it says title, we can change it to call it safety tests.
We also need to know who are the people taking the test, we just have numbers at the bottom.
So it'd be nice to change that. After we have the graph on the screen, it would be nice to show
the actual names of the people as well as the numbers. Or instead of the numbers right here,
we just have employee numbers at the bottom. let's right click on the graph, and choose Select Data.
Now when it says here, horizontal axis labels, let's change that, edit that. Now it's asking us
what is the label range, it's asking us for a range of cells, we can either type it in
or we can just simply go over here and click on Kern and drag down to Underhill. You'll notice as
I do that, it's telling me that from sheet one, the range a four to a 20 is going to be used.
And click OK. And click OK again. And so now you see all the people's names at the bottom.
So we've created a test, graph the safety test.
Let's do another graph. Let's do the company philosophy tests. So highlight the scores. Go
up to the Insert button or the insert options. And let's choose another chart. Another column chart
and slide it down below this one. Let's give it a new title.
Let's call it the company philosophy tests.
Once more, we need to add these labels in so that they are in the names instead
of the numbers. So let's choose Select Data where it says axis labels, we'll edit that.
And we need to slide over to select the people's names. So from under Hill, up to Kern.
And okay. And Okay. Let's close this menu here. And you can see the company philosophy test.
One more graph, the financial skills test. Let's create that one. Same process,
insert the charts and choose a column chart. Give it a name.
And let's give the names at the bottom something instead of numbers.
And let's slide this one in place below the other two.
Okay, that brings us to the finish of this of this gradebook test. Let's save our document and
print it once more when you print. Be careful that you don't print on 1000s of pieces of paper that
says we're using six. So maybe I'm exaggerating. Let's change a few things. Let's orientate it,
so it's sideways. Let's see if we can fit this to one one page. Once you have one page listed here,
you can see all the graphs neatly arranged and your data on the left side, you're ready to print.
In this lesson called decision maker, we're going to create a scenario where you're supposed to pick
a job. And we're going to weigh different factors based on your opinion. So pay the amount of jobs
that are out there in the job market, how much you enjoy it, how reliable the job is to you and
various factors. And then based on your opinions, Excel will give you an answer on what career you
should choose. Welcome to excel assignment number five. This is a decision chart that we're going to
create using Microsoft Excel. Let's assume that you're going to choose a career based on several
factors, we're going to consider several jobs here on the column A, and several factors that would
lead you to decide to go into that field such as your pay in that job, the job market, which
would mean the likelihood that you'll be hired the enjoyment factor How well do you like this job,
your talents, how well are you at doing the job. And then finally, another factor might
be schooling, how much time is invested to get to this career. And so we'll create a chart that will
lead you to a decision that probably is one of the most important in your life is which career
Are you going to work at. So let's start from scratch, I'm going to choose a new workbook,
a blank one. And let's start by giving it a title. Let's call it career decisions. Now down here,
we're going to call this the job column. And you invent some jobs that you think you might do.
So I've placed a few jobs here in column A, from McDonald's manager to an NFL player.
Now let's have a large variety of types of things that you could possibly choose,
you might have different ones. Now let's put in the factors that would lead you to
decide on one of these, obviously, pay is an important factor.
I've placed other factors into the chart as well. Not only pay but job market, the enjoyment your
talents and your schooling that's required. So now, under the column pay, let's assume that
a five or some number large is the best that you can do in the career and a one is the worst.
So a McDonald's manager that's put him down at near the bottom and doctors probably doing
pretty well. Let's give him a for NFL it's as soon as the best it's a five engineers probably
pretty good as a three and a truck driver might be a three as well. And then for the others job
market. Likely you probably can get a job there. A doctor once you get your school you're probably
guaranteed a job. NFL Probably the worst of the factors because hardly anyone gets that
gets to that level. Engineers, they've got very good job prospects and truck drivers apparently
are doing well as well as well. How well would you enjoy doing the job, maybe McDonald's, not
so much, doctor if that's your passion. So make up some numbers based on your own preferences.
And so I filled in each of these categories based on what I think about each of the each
of the each of the factors. Now, it would be simple justice to sum up the
lines here. So I'm just going to put su m, and then add up all of these together.
And then we'll fill down to see what kind of results we get.
So this is telling me that number 19 is the highest, I should choose engineering to be my job.
However, there's more to the decision than just adding up all of these factors. This assumes that
every factor is equal, such as the amount of pay you get is just as likely to influence you as the
job market. Well, in fact, job market might be more important, it's nice to have a high
paying job. But if you can't get it, then maybe job factor is a more important decision
than you thought it was. Let's add some new columns. In between each of these categories.
We're going to insert a new columns. So I'm clicking on the title,
the letters at the top the column headers, and right clicking there and choosing insert.
What I want to do next is add what I would call an importance factor for each of
these categories. For instance, job market, I would put that as the highest of the factors,
if I can't find a job, then there's no sense in going into it, employment or enjoyment, I should
probably enjoy it, I'm going to give it as a for my talents. So I think I can learn whatever it
is. So I'm going to say that's not as important schooling, it's not important, I'm willing to
do as much school is I have to, and then pay I'm going to count that as a medium factor.
Now for each of these, let's take a formula to multiply the relative importance, so equals the
relative importance of this times the factor for each career. Now this is going to require
a absolute reference. So where it says dollar sign see for I'm going to put $1 sign in front
of the four, because I'm going to reference this number in the blue every single time
after I fill down. So filling down here shows now the importance of the NFL 15
is somewhat moderated now, it's got less of importance than maybe the job market would have.
So let's copy this section here. Let's paste it into the next area
and paste it into the next area. And continue on.
Now let's readjust this when I do the final tally, I just want to say equals and I'm going to
add up each of these factors. So I'm putting a plus sign between each cell.
And then I will fill down in the last row to give it a title and call it total.
So once more 65 shows up as the most important of the jobs at least according to my criteria, a 65.
One more final touch might be to color code each
of these areas so that they stand out distinctly one from another.
Now looking at the last the totals, let's do a conditional formatting on these numbers. So
highlighting the numbers, I'm going to choose the top 10% that'll show basically the first
highest ranked item. And so 65% 65 is the highest number in the list at shows which one comes out
on top. Engineering, by the way, is what the computer decision chart recommends for me.
Let's see what you have prints your work, put your name on it. And you have a decision now
of what your career will be. In this lesson called sales report, we're going to summarize
a large amount of data we're going to have lots of different sales items will calculate who the best
sales people are in our department and create this pie chart when we're done.
We're going to use some data from some sales figures to do data. work in Microsoft Excel.
What you see on the screen in front of you is a report from perhaps the company's
database. on sales for a store, you can see that there's a lot of columns and a lot of rules. So
you don't have to type all these things in, I'll provide you with the spreadsheets so that you can
just manipulate what's already there. So you can see each title is at the top and row number one,
there's a few blanks that we're going to use formulas for. And then let's scroll to
the bottom and you will see that has, I think it's 172. Lowe's opens a sales transaction. So
all the way to December here, you can see the sales of each item in our pool supply store. And a
little bit of data about each one. The store cost and column E is how much the item cost wholesale.
Sale Price is what we sold it for. And then we need to calculate a few items such as the profit,
the commission, and then we'll do some reports on each salesperson to find out who the best
salesperson is in our store. Well, let's do some of the formulas here that we will have
to encounter when we work with sales data. Over here in column L, I've put some notes
on the formulas that we're going to use and some of the techniques that you'll see in this lesson.
text to columns, which we'll use to split these names, you can see that there's a first and last
name and column I we want to split them into separate columns. The second function is the
if formula. You've seen that before. The next function is called the sum if, which means
you can pick certain areas or certain items to add together based on a criteria that you choose.
This is a database really more than a spreadsheet. And in databases, we do a lot of sorting,
and filtering, a new concept that you'll see as a pivot table, which will give you a summary
of like the number of sales that each employee makes. And then finally, we'll review charting
by making a pie chart. Let's start by making the title up here in row one more readable.
Right now they're all compacted together. So let's highlight these. And let's go to text wrapping.
text wrapping allows us to see each one of these in its full text.
So now you can see in column B, C, and each that there's more text that we didn't see before.
Let's start with the item called text to columns, what we would like to do
is to split these columns so that they have first name in one column and a last name in the other.
So to do that, we're going to need to insert a new column to give it some space. So right
click on column header j, and choose Insert. And so now we have a blank column to work with.
Let's go to column I. And we're going to the Data tab here, and then choosing text to columns.
What this will do, it will allow the computer to parse the data that's in column I,
there's two options if you use fixed width. That means the first column might have five characters,
or seven or whatever you want to choose. But in our case, we want to split it based on the space
that's between these names. So I'm going to choose delimited. When you select delimited, it says what
are your delimiters a delimiter is a divider. And so if you select any of these items here,
a tab, a semi colon a comma or space, it will automatically divide the words on what you choose
above. So I'm choosing space. And so a space character between the words gives us two columns.
Now let's change these headers now since they make more sense to say first name,
and then last name for each of the salespeople. And so we've added a column. Now,
the next item is to calculate how much profit was sold. For each of these items. For instance,
transaction 1001 was a pool cover. The product code is something in our inventory that we just
use is based on the maybe the manufacturer's code. But how much profit do you make if you sell the
item for $98 and it costs you $58 to buy it from your supplier. It's a simple formula to say equals
this square here, which is F to subtract E to and press Enter. And you can see that we made
$40.10. Now you might have to format that using the key right here under the home button to get
the dollar sign. Now how about commission? How much money are we going to give to Charlie Barnes
for selling this pool cover? Well, here's the rule above 10% commission for items less than $50
by If he sells an item that sells for more than $50, we'll give him 20% of the profits.
So let's go and make a formula using the if command equals, if my rule says,
if this sale price, I'll click here is greater than $50. Comma, then let's give him more than
more profit than the less. So the rule says give him 20% of the sales.
Let's make it 20% of the profits actually. So let's take the profit and multiply
shift an EIGHT and a decimal two, so that's 20%. But if it's less than or equal to 50, comma,
then let's take the profits and multiply by point one, or 10%.
So that's the rule that tells us the commission is based on the value of the item that was sold
per center. So the commission for this item is 20%. It costs more than $50. So we give him 20%
of the profits of the store. So Charlie burns earn $8.02, which is 20% of the profit on this item.
Now, let's just highlight these two squares, and fill down so I'm going to grab the little corner
and drag down. That's a little bit hard to fill down when you have 172 rows. So here's another way
to fill down. I'm going to hold the shift key on my keyboard, after selecting the first two rows,
and then slide to the bottom of the spreadsheet. Now I'm going to hold the shift key keep holding
the Shift key and click at the last area, you notice that the whole zone or this whole region
is selected, I'm sliding back to the top and sliding back to the bottom. Now once I have
the region selected, where I want to fill these items, I'm going to look for an AI command called
fill down. If I click on home, way over on the right, you will see an item called fill. If I
click here, I have down right up and left is my options I want to fill down, I click here,
it automatically fills the entire range. So sometimes that's quicker than trying to fill
it down using this little item in the corner. So I'm going to click to unselect the range.
Now you can see in some items such as this
one gallon of muriatic acid, it costs the company or cost the customer $7. The profit for the store
was $3. And Doug Smith earned 10% on that sale, so less than $50 item, you get less commission.
scroll back to the top. Now the next item that we're going to look at is called sum
if some if allows you to add together a range of items based on a condition. Let me show you
something more specific. Let's go to the bottom. And you can see that I have a few formulas here,
three different sums, I want to do the sum of all items, the sum of all the items that are
valued more than $50. And the sum of items valued at $50 or less. So let's go to the cost here.
Column F, which is the profit or that that is the cost to the customer.
The easiest formula is just the sum formula. So I'm going to type equals sum. Now what's the
range, I'm going to type the range this time since the cells are so so many, we're going to type the
letter F and two, and a colon and then the word the letter F 172. And you can see on the screen,
that there's a blue rectangle surrounding the range, close the item with the parentheses and
press enter. So in this year, the store sales were $17,110. Now what my question is, is how many of
those items were valued at $50 or more? What is the sum of all those items? The new formula that
we use now is called sum if So type equals sum if and that let's take a look at what I can put
in here. It's called a range and a criteria. So the range is once again f two colon f 172. That
gives me this the range of all the sales and the price to the customer. Now a comma, the criteria,
I'm going to have to put a quotation mark and then are greater than 50. Another quotation Mark,
what that will do is it will sum only the items that are greater than 50. So press enter,
you can see that $16,088 is the sum of all items that are costing more than $50.
Well, let's use the same formula. But this time, let's choose any items that are valued at 50 or
less. So that is equals sum if the range will be the same f two colon, f 172, and a comma.
Now this time, I'm going to put in my rule as less than or equal to 50.
And quotation and a parentheses. So now I can see that the vast majority of my sales are for
items that are $50 or more, hopefully, these two items, these two cells add up to the entire sum.
Okay, let's scroll back to the top and see what other items we have to do. two items that you do
in most database work is with sorting, and filtering. So let's go see how that works.
Go to the Data tab. And right here you see sort and filter, sorting and filter exactly how you
would think it would work. Let's choose sorting first. What do we want to sort by, we can sort by
the first, we can sort by the last name of an employee. And click OK. And so now all the items
have been rearranged so that the last name is alphabetized. So all the Barnes items come up
first, and then Hernandez starts at row number 35. So we've sorted by column j, basically,
you can resort again, and this time, choose a different item. Let's go back to sorting by
the sale location.
So if you're looking for all the items that are alphabetized, according to column K,
you sort by sale location. And so all the Arizona sales are first, then the letter C comes next
New Mexico. And then finally, the last items that show up on the list are Utah. So sorting,
let's see it one more time, I went to sort back to the transaction number, which was the original
way that the spreadsheet was sorted. So the last item, and the first item now are back in order.
The next item is filtering. If you want to filter some items to show only certain values,
use the Next button filter, what are we going to filter. soon as you click that button, all these
titles automatically have a little arrow next to them. So what happens if I choose one of them such
as sales location, I can unclick certain items. If I only want to show one state such as New Mexico,
I leave a checkmark there and click OK. And so now it looks like my spreadsheet is much shorter.
It's only one screen full. All the items have this in common they have n m in this column K.
However, don't be fooled, the other items are still there, they're just hidden. Look at the
row numbers, we start with to jump to eight 913 18. So there's lots of rows that are hidden.
Just because we sorted or we filtered by the sales location. Let's go back and select all of them.
Click ok. Try filtering by other columns. Let's filter by, let's say by the first name. Let's see,
I only want to see Helen. I'm looking at her sales. And now I see just Helens results.
Yeah, remember, the other items are still there. We just want to hide them temporarily.
So that's filtering, sorting and filtering are useful when you're doing analysis on a bunch of
data like you have here. The next item that I would like to use is called the pivot tables.
Pivot Table is a way to summarize a large group of data. So I'm going to select just the data
that I'm going to work with here. I'm going to select from cell A, and highlight all the way
down to the other corner of my spreadsheet. So I'm holding my mouse button down, moving
all the way to the bottom to row number 172. It's important that I limit my my pivot table to only
this data I don't want to include the summary at the bottom, nor do I want to include anything
that's over on the right in column M. So after I have selected all items, I go to the Insert tab
and I'm choosing a pivot table. Pivot Table So up next says what columns are we going to use,
you can see the selection here is from a one to K 172. That's what I've just selected,
it's going to create a new worksheet. Up until now we've only used one worksheet,
it's always been called sheet one, as you can see at the bottom. But when I click OK,
you will see now I have a new sheet. This one says sheet three, yours probably says sheet two.
Now what are we going to put in a pivot table? First of all, in a pivot table, you think of what
you're going to add together or make a summary of? Well, all I care about now is the sales figures
for each of my salespeople. So I'm going to select last name, and the sale price of each item.
You notice that we have a little summary here, Barnes sold $6,000.
When I click those, the computer guessed what I wanted to do. It says I'm going to use the rows
of the last name and choose the sum of the sales item. There are other ways you can add these
together, you can count them, you can average them put the maximum. Most often, you use some,
you can experiment with other items, you can drag these around and get strange results.
You can do filtering. There's a lot to do with pivot tables. But for all our purposes today is
I want to leave this as the sum of the values and the row labels as the last name of each employee.
Let's format these as dollars figures because that's what they are.
And now you can see that the best salesperson in my store is Barnes. Smith comes in a close second,
Hernandez needs some help. To show this visually, we can highlight these numbers and create a chart.
Let's go to insert insert. And a pie chart would be an appropriate type of sales figure here. So
I like three dimensional pie charts. Let's put this next to the other. And you can see
the last name of each salesperson and the percentages of the total sales.
You might want to show some data on this graph as well. You can right click this and you can choose
Add Data Labels. So you can click here. And it will show the actual number of sales that each
employee earned. Adjust the colors and the formatting to your style. Now the last thing
we should do is print to worksheets. So let's put your name up here. And then print this page. When
you go to print, make sure that it fits on one sheet. So I choose Print. And you can see in the
preview that I have two pieces of paper here, it's not really necessary to have two. Let's make this
a horizontal landscape. And let's condense this to one page.
And click Print. In this lesson called card database, we're trying to get some more
advanced features of Excel, you can see that we have literally a database of lots of cars,
we're going to find out how many miles they each worked. We're going to do some formulas with text.
So we can combine two fields together and split them apart. And we're going to do some averages,
and create some charts as well. So let's get started with a car database. Welcome to excel
assignment number seven. This is a database of all the company cars that you own in your business.
This is a rather extensive spreadsheet. So we're going to split the lesson into three parts.
You can see the lesson contents on the screen. First of all we're going to use importing to
create a text file into a spreadsheet. The second is we'll introduce three new formulas left,
middle and right. We'll use v lookup formulas to create value out of a table.
We'll review the if formula. We'll use the concatenate formula to put cells together,
we'll work on a pivot table. And lastly, we'll import our documents into Microsoft Word.
Your company fleet manager might have a computer system that does tracking and he's asking you
to do some analysis now on the cars that are in your company. Usually when a person gives
you a database, you have a few options. You can modify it in a database programs such as access,
or perhaps what you're more familiar with is with Excel. And so we're going to use a database
aspect of how Excel works today. You'll see that they have the spreadsheet on the screen shows the
cars that we own in the company with some IDs some miles their cars and years and their makes
their numbers the principal driver and so we're going to do several steps. With database functions
using Excel. First of all, you won't get a spreadsheet. When you ask for inventories, usually
what you get is a text file. And so what I have on the desktop here is a spreadsheet in the form of a
text file. If I double click this, it looks ugly, you see that it is not in a spreadsheet format,
at least not yet. This is what you get from reports from sales data from your Amazon account,
or your insurance company or your bank. These formats are called text formats, they don't have
any, they don't have any spreadsheet, graphs, they don't have any colors. It's just straight text.
But Excel knows how to work with these very well, you notice all these commas here.
These are all separators, they show the different columns that will come in the spreadsheet. So
I'll show you how to work with this. Now I'm going to close this first and start Excel.
Now instead of starting by working with the file itself, we have to import it. So I'm
going to the word file. And I'm going to open a document. And I'm going to choose
this text file, I have to go find it first. So I click on computer.
And let's browse. And let's navigate to the desktop. That's where I have this file saved.
And it doesn't show up. Why not? Well, remember, the extension on a txt file is txt. And Excel is
just looking for spreadsheets right now. So I'm going to change the filter here to show all files
on the desktop. And then scroll through it again. And there it is. There's the car inventory. txt.
Now when I open it, it's going to ask me some questions. How do I handle this? What do I do?
Are these like, text files that are equal sized cells each? Or are they delimited?
Well, these are all separated by commas, as you can see in the preview. And so we're going to
stick with the delimited idea. Choose next. And how are they delimited? Is the next question.
Are they separated by tabs, semi colons, commas, spaces or something else? Well, these are all
separated by commas. And as soon as I click comma, you notice all these columns seem to line up
as they are intended. And so that looks like I'm on the right track. I'll click Finish.
And so now I have the start of my spreadsheet. Each comma creates a separate column in the
spreadsheet, let's take you through some steps here, we're going to have to follow this pretty
closely. There's no room for creativity on this assignment, just follow exactly how it's presented
here. First of all, let's change the columns so that the column headers so that they can show
the entire title. So we'll use text wrapping, the first thing I would like to do is introduce some
new formulas that are able to handle text, you can take pieces of a field and create new fields.
For instance, whoever invented this car ID for the company was trying to squeeze as much information
into the ID as possible. And so we used a code like fd for Ford 06 for the year,
m T, G, four Mustang. And 001 is the car number. That's not normally recommended to
try to squeeze data into a field like this, but that's what he's doing. So we need to
come up with a few ideas on how to separate these fields. Let's go to where it says make.
I want to take the first two letters off of each of these
data items. So the first two letters is going to be using a formula called left.
And if I chose if I choose that, you notice the options are. First of all choose text. Well,
I'm going to choose this as my text. And the next after this comma is the number of letters.
Well, in my database, it looks like the first two letters are the manufacturers names. So let's use
a close parentheses. And sure enough, you can see that it's just slicing off the first two letters
of the database. Well, let's fill this down and see what we have here.
All the way to the bottom, how many are there looks like 53.
And so you can see we have two letters for each of the make. Now what do those mean? Let's Let's
Okay, let's create a new formula. And we're going to put in column C. The manufacturers name I'd
like to put in just the word Ford. Or down here General Motors t y must stand for Toyota a Jo is
Honda car is Chrysler and ah y is Hyundai. We're going to introduce a new function here called
a lookup table. Let's take these items here, like CR and h, y and t, y, and h, o,
and GM, and f, d. These are all our manufacturers. And let's actually put the real name here.
Okay, you can see that I've created a small table that coordinates these abbreviations
with the full name. To make this work, though, I have to have these in alphabetical order.
So I'm going to highlight these, just these cells, not the whole table,
and sort them. So I look for the data command, and sort. It says, What do you want to sort by
I want to sort by the first column, and click OK. If you don't put them in alphabetical order,
this next process is not going to work. Now that I've got these charts here, I'm going to
look them up. I'm going to look up f d in the chart and put in the word Ford,
the computer function to do that is called equals v lookup. That means vertical lookup,
it's going to look through a table vertically, look up the words f d, or the letters f, d,
and then return the second column, which is Ford. So let's type in the whole command here lookup.
And let's follow all these options through First of all, the first item it's looking for is the
lookup value. So fd is the lookup value, put a comma. And then the next item says table array,
the table is the little set of data at the bottom, you're going to scroll down this table,
and then a comma. Then it says column index number. That means which column in this table
contains the real word that you're looking for not the abbreviation but the real word. So the
second column contains Chrysler, Ford General Motors. So I'm going to type a two up here
and a comma, and then actually, this is the only option that we need, we can close the
parentheses Now press Enter, and scroll to the top notice it says forward. Looks like it's working.
Be careful, there's still one error with this. But we'll discover that in a moment.
If I fill this down, you're going to notice there's a relative reference error problem.
everything stops working after the third row.
It has this na, which means not available or not accessible. And so what's going on? Well,
if I double click on the second item, you'll see that this table is looking at rows 57 through 62.
And you notice down here that it's been shifted down one,
that should send an alert to your mind to say, Oh, the computer is using relative references
instead of absolute references. So if I go back to Ford, and double click this,
I'm going to modify this. So I'm going to put in $1 sign in front of 56 and $1 sign in front of 61.
So that means that it's always going to use those row numbers, every time I look up a value. And
now it looks like it's creating the results I want. So let's go all the way to the bottom.
And you see that we've created a new field in our database that shows the manufacturer's name.
Now the model is the second item. While the model is found here, this is the the letters empty G.
So we can just use the left command like we did for the manufacturer. So we're going to use a new
item called mid command. So in this case, we have an a Ford Mustang, empty g stands for Mustang.
So let's, let's pull out some letters. Well, to pull out the manufacturers make we use the first
two letters and so we use the left command. Well, we want to pull something out of the
middle of the text. So this one is called the mid command and my D. It says here that it's going to
return the characters from the middle of a text string, given a starting position and the length.
So let's put a parentheses. The first item that it's looking for is the text. So we'll click here.
And then a comma, the start number where does this m start? That's it position 12345.
So it starts at position five and the string and then a comma. How many characters do we need?
Well, these all have three characters in each The model numbers. Okay, close parentheses and Enter.
And now we see MTG. So let's fill these down a ways and you can see that it's pulling out
other items. FCS must stand for Ford Focus, and then a gender General Motors si MRSLV.
Let's see what some of these others are. Scrolling all the way to the bottom and filling down,
you can see that we have some of these others. Now the meaning of all these models can be interpreted
as follows mt G is for Mustang. FSC is for focus. CMR is a Camaro SLV is a Silverado,
Toyota Camry and a Toyota Corolla are next. And let's see a civic looks like a Honda Civic,
Honda Odyssey. And what's next here, a PT Cruiser, Christ's their caravan,
and then the Honda Elantra. So these are all different models. So at the bottom here,
we were going to do the exact same process of creating a lookup table. So I'm going to copy down
all of these manufacturer model names, and then put the real name next to them.
Okay, I'm finished with typing in all of these car models and their full name. Remember, a V lookup
table only works if it's alphabetized. So I'm going to highlight these squares, choose sort,
and sort by the first column, which is D. Click OK. And so now the this line is in
alphabetical order. Once we have that done, we can create a lookup value. So I'm going to
use the exact same lookup as my model. I'm going to copy this and paste it over here.
It doesn't seem to be working right? It says, A lantra is the first item.
What's going on, I'll double click here to find out. We're looking for D 56 through e 61.
Now that's almost there. The only problem is, this table is longer. So I'm going to
extend this one all the way to the bottom, and press enter and go back to the top.
Now it looks like it's finding Mustang correctly. Since the other one was created with the
absolute references, we have the dollar signs in the in the top, it seems to be working right,
so we'll fill down and we'll have the model name for each of these cars.
Almost there. Let's open this a little light wider. We have an error down here what's going on.
This one says not accessible or not available. What's wrong with this, we have a 00 D,
somebody in their stupidity, typed in a zero instead of an O back at the beginning here.
So let's fix that. Up here at the top, we have the NA h o and then the year
we have an extra zero in there. So let's delete that zero and press Enter. And now it appears that
that typo was fixed. So when you get text from another company, sometimes you have to realize
that people didn't enter the data correctly. Lastly, let's put in the manufacturer year.
This is going to use the mid command again, we're going to pull out the year from this square.
But a comma, it's going to start at position three. And we're going to take two text items,
two letters, and we get 06. That one seems a little simpler. So we'll scroll that down.
And now we'll pull out just the year, a two digit year for each of these cars.
Now we need a formula to calculate the age. How old is our car? Well, let's put in an equal sign.
What would you do? Given that I'm creating this spreadsheet in the year 2014. I'm just going to
take 14 and subtract the years that come before and press Enter. And it says eight.
So a 2006 car is eight years old. seems to work. Let's see what happens when I feel down.
Most of the time it works except for here. What's this says there's a problem with it.
Now if you look closely at this, this is an O six. It's literally an O six. We want 06.
So once more, somebody thought that it was an O six, and in the database, they put a o letter O
in here. So let's delete that Oh, and put in a zero. And so that fixes that value.
The same happened here. Let's go back to this row 15. I'm deleting the Oh, and I'm re typing a zero.
Let's continue to fill down and see what other errors might show up.
Oops, there's another one another value that says it cannot be read. So let's go to here.
Ah, oh, and then a 05. So we've had to do some corrections to our data.
Now, this formula is very simple. It's actually too simple. This car is negative 84 years old.
Where did that come from? Well, that was called the y2k bug where computers just held two digits
for every year, it worked until we switched over to the year 2000. So a 98 is actually
16 years old. And it gives us a negative 84. That's because it's taking the formula 14
minus the value in cell B nine f 19. Well, it's a correct number according to the computer,
but it doesn't serve our purposes, we want to make sure that the number comes out correctly.
So let's readjust our formula. Let's erase this one. And I'm going to ask a question,
if if the difference between 14 minus this gets less than zero,
then I want a formula for all those 90s and 80s. Cars. Well, I just simply say I'll take 100
minus the year. And that'll get us the distance between 98 to 2000, that would be about two.
And then I'm going to add 14 to get to our current value. Otherwise, we'll just use
the previous formula, we'll say 14 minus this here. And so now we have two different cases,
for those that are less than 2000. And those that are greater than 2000. If I press enter,
I still get the same results for the first. And let's scroll to the bottom and see what happens.
And so sometimes, a simple calculation needs to have a few cases. So yeah, this is correct. Now,
a 1999 car in the year 2014 is 15 years old. So the formula seems to work.
Now let's go over to this formula, miles per year.
Well, that should be pretty simple. least at first glance, we want to say this equals the total miles
divided by the years driven, which is the age of the car. And so that shows 55,000 miles.
Let's fill this down. And let's see if it works. Almost all of them work. There's a few errors.
There's an error, it says divide by zero. Why in the world are we getting that error?
That's because over here in the age, it says this car is zero years old. It was made in
2014. Is it really zero years old? Well, it's less than a year old. But it's not zero. So it's
probably six months old, maybe three months old. Let's make a slight adjustment to our formula.
If I double click on this formula, you'll see that I'm dividing eight, the age divided by
the miles. Well, the age and some of these was zero, we can't really divide by zero.
And this car really is not exactly eight years old, it might be eight years and six months,
because a car that is zero years old really has a few months already on it. So let's just
round a little bit. Let's add a little bit of a of a number. Well, let's see, we want
to add something to G two. So I'm going to have to put a parentheses before g two and add point
five let's add let's add a decimal point five. So that that year is really a year and six months.
Why parentheses? Well, you should know from your order of operations in your algebra class
that you're supposed to add and subtract after you divide and multiply. And so we want to make
sure that we add first and then divide. So that's why the parentheses are there.
If I press Enter And then fill this formula down,
we should get better results. So the mileage calculation is based on the age plus six months.
And we have no divide by zero errors. Let's format these two columns, they could probably use some
commas to make them look more like miles, it's easier to see when you have 1000s. If you have
a comma in there, let's move, let's keep moving. The color of this is black, and white, green, etc.
That's fine. We'll use that in a few minutes. When we get to the new car ID. The principal driver
shows the last name of each employee. How many warranty miles are there on this car? This is a
50,000 mile warranty. And this car is just barely under the warranty, it still has 10,000 to go.
Well, the question is, is this covered under warranty, that's the formula we're going to
do next. And so we'll do an IF calculation. If if the miles are less than or equal to
the warranty, and yes, it is covered. And so I'm just going to put the letter Y for covered
a comma. So if it's not covered, I'm going to write the word not covered. And then a quotation
and a parentheses. So now this says yes, it is covered, I get a why.
If I fill down, I will see a different value for each of these cars.
And some are covered and some are not covered under the warranty. The last item that we're
going to fill in here is called the new car ID, your boss gives you this requirement.
He says take the original ID. And I want you to squeeze in three more letters in between the
manufacturer and this last three digit number. And I want to I want to know what color is the car.
So once more, this is a bad idea to try to squeeze as much information into one field. But your boss
said so. So it's the truth. Let's do it. How do we how do we how do we combine fields, the formula
you're looking for is called concatenate co n. And then you'll see concatenate shows up here. I click
it, double click the format that you're supposed to fill in is simply says text one, text two, etc.
So we're going to concatenate a bunch of fields, we want to put first of all continue using f d,
comma, and then we want to have the manufacturers year comes, as you can see,
that comes second in the list. And then we want to continue on with the model.
And then a comma, and now your boss said put in the colors. So we want to put in black,
we're going to adjust that because he only wants the first three letters
of the word. So we're going to delete this two squares, I want to know just the left
three letters of black. So I left left three of j two, so j two comma three,
and a close parentheses and a comma. So we've just added the left three letters of black.
And now what we need is the last three from here. So we use the formula right
click here, comma, three, double close parentheses and press enter. So now we have the new car ID.
One more change that we need to make is that this shows lowercase letters.
Your boss didn't tell you but he forgot to say all IDs in cars have uppercase letters only.
Now there's a nice formula to put in there. If I want to isolate these
letters and change them to uppercase, the formula they're looking for is called upper.
So I put a parentheses around the part that I want to be uppercase and press Enter. And now
the new car ID shows bl a as black. I'm filling down and now we have a new car ID.
Okay, so those are a lot of different database functions that you can use to manipulate text.
The next item in our checklist of things that we're going to see on this assignment
is called a pivot table. a pivot table allows you to summarize data. For instance,
your boss might ask of these drivers Who has the greatest amount of miles.
There's some formulas you could work with. But here is one of them.
It's called a pivot table, I'm going to insert, and under the insert command, you see pivot table.
It says, What's my range that I'm going to work with, and it automatically selects the
entire spreadsheet. Click ok. a pivot table creates a new sheet down here, you notice this
one said car inventory. And this sheet is called sheet one, I'm going to select the driver first.
put a checkmark next to him. And then I want to know about the miles on the car. So I click on
miles, automatically, the computer assumes that I want the sum of the miles and the driver. And so
now we have a chart that shows each driver in the number of miles that he created.
Well, you could also put in a chart and create a list of all the drivers
and their miles. And so Smith jumps out right away from our graph.
Whoever Smith is he's driving a lot. Let's go back to our car inventory and see what Smith is up to
Smith, what kind of car does he have, oh, here's a Ford Mustang. No wonder he's driving so much,
I would drive a lot to have somebody gave me a Mustang. And so Smith shows his miles
as the most of anyone in this series. Another type of graph that we haven't worked with yet
is called a scatter chart. A scatter chart allows you to put a specific data points
on a graph. So I'm going to select here, the age of the car, and the miles, just these two columns,
I'll click on their column headers, and select the entire column. I'm going to insert a new chart.
And the chart that I'm looking for here has a bunch of points on it. It's called a scatter
chart. And as soon as you select it, you can see what it's doing. It's pointing each of these
graphs, each one of these dots on the graph shows that the years across the bottom are showing from
zero years up to looks like about 18 is the oldest car and then the number of miles each car has.
So it almost looks like a straight line. Well, there is something called a trend line
that we're looking for. So I'm clicking on the plus sign up here. And I'm selecting trend line.
And now there's a dotted line that goes up and down through the middle of these dots,
we could probably make these access titles a little bit easier to read. So I'm going
to select those as well. deleting the word access title and putting in the word miles, miles driven.
And then down here on this axis, I'm changing this to the age of the car,
age of the car, and then in parentheses, years. And so let's Park this off to the side.
And slide over. So now we have a chart that shows the miles in our inventory. Some of these are
outliers. Some of these are right on the line. one more way that we could do some analysis is
we could find out which cars are being driven more than others. So let's select column I.
Let's go to conditional formatting. Let's try out something called the
color scales. It's pick one of these color scales, there's blue and green and red
doesn't matter which one you choose. But when you pick one, you will see that some colors are
highlighted in darker and lighter colors, they show you the extremes. So like this 35,000
shows up as one of the highest. Let's sort this spreadsheet based on the miles driven per year.
The first thing we have to do is select just the range where our data is stored. We're going to
ignore these lookup tables at the bottom. So I'm highlighting all the way down to row 53.
And then going to the Data tab and choosing sort. Let's sort by column. What column is this column I
and let's go from the largest to the smallest and click OK. And so you can see that this car here
this particular car has 35,000 miles per year. On average. It only has six months and the guy has
already driven at 17,000 miles. We scroll to the bottom you'll see that the minimum Drive driven
car is this Ford Mustang here, it's eight years old and it has 40,000 miles on it.
The last item I'd like to do is to create a report. So let's go back to Microsoft Word.
And we're going to copy and paste a few of these items from our spreadsheet,
the title of a report is going to have our name on it.
Next, I'm simply going to put the top drivers by miles. And then I'm going to
go back to excel. Go back to my sheet, copy this graph, so I'm going to right click it,
choose Copy. And you can see you can go back to word right click it and choose Paste.
And so these two programs integrate with each other. Press Enter a couple of times. And I'm
going to show the next is the scatter chart for the car inventory. So it says the scatter chart
for the car inventory miles on each car. Now I'm going back to excel. And I'm going to copy
this graph here that has the scatter chart. So right click on it, choose Copy, go back to word,
right click, and choose Paste. And so I have two charts that came from my graph. let's print these
and call that our final assignment. One more thing when you go to save a document that in
Excel that was created using a txt file, you can see up here it says car inventory. txt.
When you go to save that, you're going to get an error message.
It says you're still in the tab formatted text. Do you want to keep using that format,
you should say no, we want to save this using a new format. Instead of tab delimited,
or text delimited, we're just going to choose Excel workbook. And now all those pretty blue
and all the formula and all the other things that were created in the charts, they will
stay with your spreadsheet, a regular tab or text delimited file cannot possibly save these items.
Okay, in Microsoft Word. Let's go File and print. And we'll call our document done. In this section,
we're going to talk about problem solving. And so you can see that Susan in front of us has
a problem. She wants to take a vacation and wants to spend as little amount as possible.
And so our options are to go on a Caribbean cruise, go to Orlando,
or go to Chicago. Each one of these requires flights, car rentals, and hotels admission fees.
And so Excel is a great tool to figure out which one of these would be the least expensive.
And so I will give you a partial solution. But that by this point in the course though,
you're supposed to be able to solve some of these problems on your own. And so it will
stretch your thinking and reinforce the skills that you've learned before. So problem solving
is going to start with a simple problem. And then gradually, I'll give you several until you get to
the most complex problem. When you reach the end, you will be considered a person that can use Excel
to solve real problems in your personal life and in your job as well. Hi, in this video,
I'm going to show you how to calculate interest on a simple interest payment plan
for four loans with different interest rates and then make a graph. So here's the final product
that we're going to arrive at at the end of this video. And so you'll see that we have a $10,000
loan with four different interest rates. And then we have monthly payments at the right column.
Let's start up a new spreadsheet. And then we're going to put in the data labels.
So the first thing I'm going to do is fill out the column titles for our loan. So the first
column is principal, the interest rate, the months that we have to pay over the total interest paid
the number of dollars that the total loan will be and then the monthly payments.
In the first column, let's put in labels here for loan A, B, C and D. I'd like to make sure that
these column headers are correct. So let's double click on the little line that's between each of
these dividers. And that will separate the column to be exactly the right width for the label.
So let's zoom in and let's check out what our payments will be. So let's make up a principal
number for our loan. Let's say we're buying a car and I'm going to spend $10,000 that I don't have.
So I enter in 10,000. And you can see that the numbers don't quite fit now. So let's expand the
column a little bit. And if you don't have dollar signs, you can go up to the accounting area and
you can change this to $1 sign if you want. Now the interest rate if I put in a number here,
let's say 9% and I use the percent sign It will calculate the number correctly.
However, if you don't put in there 9%, you can put in 0.09, which is the decimal equivalent of 9%.
And then if you choose the percent sign, it will show it correctly.
Now the number of months, let's say I have a 12 month loan, and so I put in a 12.
So these three columns here are going to be numbers that we enter,
and then the last three columns are going to be the formulas that help us solve our problem.
So let's start with the formula interest paid. So that's a formula where we type in equals,
we want to take the principal, which is B two, and I will multiply by C two, which is the rate.
So you can either type in equals b two, and use the star key, which is the shift
eight. And that's for multiply. And then finally, type in z two, C two, and press Enter. And that
will tell us that the total amount of interest is $900, over the price of the loan. So then the next
column is the number of dollars paid total. So we have to pay back our money. Let's go with equals,
and we'll click the 10,000, then we put in a plus sign, because we have to add the number of dollars
interests that we're going to pay as well. And so that's 900, and press enter. So the total
cost of your car will be $10,900. Now you're going to pay this over the life of the loan,
so we're going to take this total amount and divide it by 12. So we'll take divide by the
number of months that we just agreed to pay. So that's f two, divided by E two, and press enter.
So your monthly payment for this car is $908. Now you can adjust this quite easily if you want to
change something. So let's click in here and let's say I have a $20,000 loan for my car. And you can
see the payment automatically is recalculated. Now you're shopping between different banks, and you
want to see what the competitive rates are. So let's take our 20,000. And I'm going to select
and fill down. So let's see I'm selecting this whole bracket of looks like 12 different cells,
I'm using the Ctrl key and D on the keyboard. And that is for fill down.
Now I want to calculate on different rates. So one bank here decided to give me an 8% loan.
Another one is seven and even 6%. So I shopped around, and I found four different rates.
Now the number of dollars that I pay is equal and the number of months that I pay is equal.
Now let's take the last three columns, and we're going to fill down so I'm going to select those
three columns, and drag from the small dots in the corner. And this will calculate my interest
based on the 12 month payment. So let's see here, the interest rate is better for the 6%.
And then the total monthly payment is slightly lower. Now I'd like to visually present this. So
let's select the total monthly payment, and we'll make a bar chart out of it. So let's see. Let's go
to the Insert tab here. And I'm going to select a chart for bar chart. Let's choose this first one.
Now there's my chart. And let's see, I'll zoom out slightly.
Let's put in a chart title. And I'm going to call this monthly payments for $10,000. All right
now at the bottom, you can see that I have these 1234. I would like to compare the interest rates,
that's the only thing that changes in here is the 9876. So let's do a right click on the chart
here and choose Select Data. In the column here where it says axis labels. I'm going to edit this
and it says please give me a range. So I can just simply select from the chart, here's 876789.
And then it puts in the correct columns are c two to C five. And that is exactly what I'm looking
for. Click OK. And then it says here, this is what I'm going to display now, which is 987 6%. And
click OK. And there you got your chart. My name is shed Sluiter, and my youtube channel is called
tech Made Simple. I'm a university professor that teaches programming, computer software development
and security, all kinds of technology, including what you just saw here with Microsoft Excel.
Welcome to excel problem solvers. This is a set of examples of how you can use Excel
in solving real world problems, problems that are too complex to solve with a regular calculator, or
to solve in your head. So we're going to look at Susan and Tim. Every time we get a new page in
this booklet, we're going to see that they have a different task. And so here's what we're going to
do for each page. First of all, we'll listen to Tim and Susan and tell us about their problem.
We'll build a spreadsheet with proper formulas. Now I'm going to show you a partial solution. For
each of these solutions, we're going to create a spreadsheet with a little bit of a design.
But I'm going to assume that you've already done some Excel work and so that you can finish this
with a little bit of collaboration with your friends and a little bit of thought.
And then finally, every one of these solutions will have a graph, it'll be a bar chart that
will show a comparison. So let's see what these different problems are. The first problem we're
going to look at is a school shopping list. And so Susan has a lot of things to buy. And she has
three different stores to shop that Tim also has a shopping list that's slightly different.
The second problem we'll face is, should we buy a cat or dog. And so we'll look at all the different
costs associated with buying each of those. The next problem that Susan faces that she wants to
take a vacation. And so we're going to give three different options either a Caribbean cruise,
Orlando or Chicago, Tim's going to do the same vacation, but he has a different number of people.
The next problem is that they're going to purchase printers. And so there's three different kinds of
printers to choose from. And the cost of ink for each is different. Number five is more
complicated yet, we're going to untangle the cell phone bill. Number six is we're going to choose
from three different cars, a very economical car and a luxury. In this first Excel problem,
we're going to go on a shopping trip. And so we have a shopping list. Susan says,
I'm Susan, would you help me pick one of these three stores to buy my school supplies.
Here's my shopping list. And so to the right of the shopping list, you can see all the items that
Susan would like to buy. Her choices are Walmart, dollar trap, and office repo. Each price is listed
below. And then her number of items that she would like to buy are listed on her shopping list.
So for instance, three ballpoint pens. at Walmart, it's going to cost two or three times 50 cents,
or $1.50. Now this is a large complicated shopping list with many items. So this is a perfect way to
solve a problem using an Excel spreadsheet. Inside the Excel spreadsheet that we're going to create,
we need to add a list of all of the items in the shopping list, and then a column for each
store. And so pause the video right now and type these in just as you see on the screen.
Next, let's go back to the shopping list. Each price listed at each store has to be
put into the spreadsheet, and then put them into this column under Walmart.
Pause this video until you have the numbers typed in.
Now you might say these look like dollars. So let's make $1 sign each of them. Open the
currency, add $1 sign. Let's add the prices for dollar trap and also for office repo.
So pause the video until you have all the prices listed for each of the stores.
Now I'm going to help you a little bit further with the problem, but not completely. Let's start
by looking at Susan's shopping list for each item for the ballpoint pen. We know how many she needs
such as three. And so I copy down the number of items that she needs.
Now, how would you figure out the amount of money that she would spend on ballpoint pens.
Let's put that formula right here. ballpoint pens, you know that she's going to spend
$1.50 if she shops at Walmart, three times 50. Here are some hints for the formula.
In this square, which is G three, we're going to multiply the quantity times the price.
So enter your formula once you figure that out. After you figure out the first formula,
you'll be able to fill down and calculate all the rest of the prices as well. At the very end,
let's put in the total. And so you'll know what the total cost is for purchasing supplies
at Walmart. Repeat this same process for another column, we're going to figure out the price for
the entire shopping list the dollar trap, and then we'll figure out the price again. And obviously,
once you finish, you should have three total prices listed down here. After you add up all
of these numbers close. The last thing you'll need to do is put a chart on the graph. And so copy the
labels from row two down to the bottom. And when you find the totals like I've mentioned here,
you can highlight the entire two rows, create your chart and it will be labeled correctly.
Now for the other fellow, we're going to have to make a another chart so
copy everything you get from Susan and then you could probably move it over to column K
or so and paste all of the information Just change the shopping list so that
Tim's list will be hidden and shown instead of Susan's, then you'll be finished Good luck.
In this video, in Excel problem solvers, we're going to look at a problem that Susan and Tim
are facing. They want to purchase a dog or a cat. And so let's see what Susan says.
She says, Hi, I'm Susan, would you help me choose a pet. I love both cats and
dogs equally. But I would like to spend as little money as possible each year.
compare these expenses in a spreadsheet and show me your answer and create a graph. First of all,
notice that she's talking about a year. And so at the bottom down here, when it says hints,
it says use a spreadsheet to calculate the total cost of ownership for owning a pet for one year.
So we'll just assume one year is the length of our expenses, pets, adoptions, dogs or 50,
cats or 90. All expenses include all of the vaccinations, spaying and neutering.
However, there are more expenses than just buying the cat or the dog.
First of all, this is a list of what we consider necessary accessories.
The cat has three items, the dog is four, so you have to buy this one time.
The second box shows the ongoing expenses, a box of cat food, kitty litter,
and the small print says buy two of each of these for my pet each month. And so we'll double that
expense. Each month. The dog has some expenses to his dog food is more money, and his dog treats her
also an expense. Here's a good pattern for setting up the spreadsheet, you can see that I have
two boxes, and I colored these boxes. So that way you can distinguish that there's a section called
initial costs. So these are just one time costs. And these are monthly costs. Then we have a column
for the dog, and a column for the cat. When we're done, we're going to have a total of how much it
costs to keep the dog for the first year. So we'll be adding up all the numbers above it.
What are the initial costs, while the purchase price for the dog,
we have to buy a collar a taggable leash, and then the initial total will be the sum
of everything above it, the cat will have some of these expenses. I think the cat will have a zero
here because it doesn't need a leash. For the monthly expenses, remember, there's going to be
food litter and treats. So these will be subtotal. And then we're going to multiply
by two because we need to have a monthly total, each one of those items was bought twice a month.
For the one year costs, we're going to have to take 12 times the monthly total,
and then add it to the initial. So think through how each of these costs need to be added in
to get an accurate price for a dog and an accurate price for a cat.
When we're done, we will highlight these two lines, create a chart and see you'll
have a bar chart that shows the cost of a dog compared to the cost of a cat.
So there's a good outline for you remember, this is just a partial solution that I'm offering you.
You have to come up with the formulas and use the numbers to get an accurate result.
Welcome to the next lesson of Excel problem solvers. This is number three, where we're going
to take Susan and Tim and ask them what they want to buy. We'll evaluate this with a spreadsheet,
and then hopefully come up with a graph. So Susan and Tim are both going on vacation,
and they're going to explore either Caribbean cruise, Orlando, or Chicago. First of all,
she says I'm Susan, would you help me pick one of these three vacations, my husband and I
like all three vacation ideas, but we want to spend as little as possible.
We will fly stay five nights and four days. Tell me which one of these will cost the least.
And so we're going to build a spreadsheet to answer that question, which one of these will
cost the least? Let's look at the details of what kind of vacations they are. The Caribbean cruise
is the simplest, it's $555 with almost everything included. The only thing that does not include
it says here is airfare. So we have to buy plane tickets to get to Miami. Orlando has theme parks.
And so they're going to spend four days and look at each theme park. So for her,
it's going to cost twice the price of each ticket. They're not going to rent a car. It says in the
small print. They will spend money on a hotel and they estimate $50 per person on food. Each day.
Lastly, we have Chicago. Chicago is a city of museums. And so they're going to go visit
the natural history, the Museum of Art, the Science Museum and the Broadcast history museum.
And so each of these prices is per person. In Chicago, they have to rent a car,
their hotel is a little bit more money, and they estimate about $50 per day for food.
And this box over here, we show the costs of the airlines. So Miami, Orlando, and Chicago are all
different costs, and those are per person. Now let's look at how we would build a spreadsheet.
First of all, we have a column for each city. So we have Chicago, Orlando, and Miami,
at the bottom of the page, we're eventually going to have a total
for all of these cities. So we'll find out the answer down here. And then we'll make a graph.
I've divided the costs up into per person expenses. So if you have more people in
your family per person expenses will go up. And then hotel expenses, assuming that all
people stay in the same room, it will cost the same for two people as it will for four.
And so the hotel is more of a fixed cost per night rather than per person.
Now for some of these, we won't have a number. For instance,
if we look at Chicago, we will have no money for Disneyland, so we can just put zero in there.
Also for like the museum, the Science Museum, we will not have a science museum cost in Orlando,
some of these costs will be just zero, it appears that there's something missing.
I have hotels and tickets, but there is also car rentals. So you might want to include
another car rental expense, somewhere down the bottom, and then add that into the subtotal.
So calculate all of the subtotals per person expenses, hotel expenses, car expenses,
add them together, compare the three cities and give Susan her answer with a bar graph.
Once you have finished with Susan, then you can use her work to copy and paste a new chart,
a new bar graph, and you'll have Tim's answers as well. Remember, he has two children, so his
per person expenses will be higher. Good luck and helping Tim and Susan pick their vacation.
Welcome to excel problem solvers. So Susan, and Tim, both would like to buy a new printer, Susan
says would you help me pick one of these three printers, I plan to print about 15 pages each day,
so that if days per week, I want the total cost to be as low as possible, I expect the printer
to last two years, you'll see that the epsilon printer is only $29. And so you might think that
is the best price at the beginning. Until you look at the supplies, you'll see that ink is $40
a cartridge. And it will add up quite a bit. Tim, on the other hand has a different request. He said
I'm Tim would you help me pick one of these three printers, I plan to print about 500 pages each
day. And so his printer will probably be different than Susan's. Let's look at the fine print.
First of all, you'll notice that each cartridge on the epsilon will only print 200 sheets.
While at the other end, the zero printer will print 11,000 pages for a cartridge.
And so the initial price on the printer, even though it's high,
may end up being less expensive per page. Here's a suggestion for how to set up your spreadsheet.
We'll do columns B, C, and D for each of the printers, we'll put in a line for their initial
purchase price. And then we'll put the yellow section for the cost of their print per page.
And that's important because even though inkjet cartridges are less than LaserJet cartridges,
they don't print nearly as many pages. And then for each of these people will have to
compute their expected pages per day. So Susan is only expecting 15 per day. This is the number
she will actually print per year. And so you'll need a formula to calculate that number. Once we
know the pages per year, we can estimate how much it will cost per year because we have a
and then the total printing costs will come here at the bottom. That total printing cost added to
the initial purchase cost will give you the actual cost for the two years of epsilon.
And so your job is to figure out some of the formulas that would make these numbers make sense.
Once you're done, you will highlight the final result. And you will create a chart that will
show the differences in each of these printers. Similar to the other problems. We're going to have
a section for Susan, and then we'll copy the information for to the right side and we will
compute Tim's numbers. And so you should have two charts when you're done and find
out what the recommendation is for each of these people. Welcome to the last in this series of
Excel problem solvers. On this video, Susan and Tim are asking us to help them purchase a car.
Susan says, Would you help me pick one of these three cars, I want to spend as little as possible,
I want to drive the car until it has 250,000 miles, when I assume it will be nearly worthless.
I drive about 30,000 miles every year. Thankfully, I have enough money to save so I
don't have to borrow money and pay interest on a loan that would cost me an extra 40%.
Let's see the three cars that Susan is considering. First of all, she has in mind the
Chevy Spark. It's a small engine that gets good gas mileage, she has to pay 15 $100 a year in
insurance. The initial cost for taxes is 1400 50. And every year she spends $210 for a license.
Compare the other cars they have higher numbers for each of those. So the Ford Mustang is 31,000.
The gas mileage is much lower, the insurance and taxes and license are all higher. Her third choice
is the escalate. Certainly a luxury car with a price of 72,000. Low gas mileage, high insurance,
high taxes and high license fees. Just how much more will that be than the other cars?
We'll find out when we make the bar graph to compare the total cost of ownership for
all of these cars. Tim said he would also like to buy one of these three cars, he wants to spend as
little as possible, he's going to drive the car until it has 250,000. He drives 30,000
miles a year. So everything so far is the same as Susan. However, the difference is he does not have
enough money to purchase the car. And so to make things simple, we are just simply going to add 40%
of the price of the car to his spreadsheet. So that will include all the interest in bank fees.
Now let's look at the spreadsheet to solve this problem.
On the left side, we have Susan and all of the calculations that we need to come up with a
price for which one is going to cost the most. We'll put the cars in column B, C and D spark
Mustang in escalate. The yellow section at the top we'll talk about the initial costs. So this is the
price that she pays the day she buys the car, she has to pay the original price plus taxes.
The more complicated question is how much does it cost to own this car every year?
And there are three costs that we need to add together. We have insurance, license and gas.
How much are we going to pay for gas? That's a more complicated question than just looking at the
price at the pump. We have to know several things. And so we have a blue calculation area from row 12
to 16. First of all, we need to ask the question, how much does she drive? How many miles every
year? What is the miles per gallon or the mpg for her car? How much is she paying for gas.
And then we will know how much she pays every year for gas.
Add those three together, and we will have total annual costs insurance plus license plus the gas.
Now the question is, how much is this car going to cost over the entire lifetime of the car? Well,
first of all, we know how long she wants to drive she said 250,000 miles after which we will assume
that the car has basically no value left and she can give it away to a friend. To find out
how long the car will live. We have to ask how many miles does she plan to drive each year?
What is her goal, which is 250,000. And then we can use those two numbers to find out the total
lifetime expectancy of the car. Finally, we will arrive at the bottom where we get the calculations
for the annual cost of the car times the number of years that we expect the car to live.
We add in the initial price of the costs. And we have a total lifetime cost of the car it might
surprise you that you'll see the numbers up in the range of where you expect to pay for a house
down a little further. We will calculate the final answer in row 27 and 28. We will calculate the
average cost of a year. So we know how long the car expects to go how many years it expects to go.
We also know how many dollars we have total. And so now we can find out what each year will
cost for the spark Mustang and escalate. highlight these last three numbers with
their labels create a bar chart and you will give Susan some good information on how much
more it will cost to buy luxury cars. On the right side of the equation we will have Tim
Tim is going to be calculating almost the same values except him doesn't have any
money saved. And so the initial cost of his car is going to be 40% higher In what Susan paid,
and so that 40% includes all the interest in bank fees. So everything else should be the
same. He's driving the same number of miles and he expects to go 250,000 before the end of life.
And so when you're finished with Tim, you'll have a similar graph. But you might find his expenses
quite a bit higher than Susan because he doesn't have enough money to pay for the car in cash.
Congratulations, you've made it to the end of the course if you've survived this long, you
have gone from the beginning to an expert person in Excel. Now, I appreciate you spending the time.
And if you're interested in these kinds of things, check out my channel on YouTube. My
name is shad Sluiter. I'm a professor at Grand Canyon University in Phoenix, Arizona. I not
only teach with Excel, but I teach programming so you can learn how to build software, websites
and mobile applications. So thanks for joining me, and best of luck with what you've learned here.