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 chad sluter 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 freed
code camp in my own classroom with
success with students from high school
to the university level and so we're
going to see a 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 sales
people 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 and 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 the percent that a
sales person should get on commission or
to simply do averages and to find the
maximums and minimums so those are basic
formulas we'll create charts so you'll
have pie charts and line charts and
you'll have scatter charts excel is very
easy in 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 in 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 cse
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 vlookup so vlookup 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 in 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 a
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 g6
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 a1
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 1st 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 about 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're all 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 how long they
worked the average
work week for a full-time person is
about 40 hours so i'll create wages or
i'm sorry i'll create the number of
hours that each person worked in this
column
after giving each employee a number of
hours 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 c4
is entered into the formula bar c4 is
the wage fifteen dollars and ninety
cents
now i want to multiply fifteen ninety so
i'm going to press the shift and the
eight key which is the multiply sign
and then i'm going to click the forty
the doll the number of hours that this
person worked
so my formula is equals to c4 times the
number that's in cell
d4
when i press enter
it'll tell me that this person gets
636 dollars
if you go and change some of these
numbers such as if we added an hour here
and i type 41 and press enter the
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 c4 is in blue
which is this number and d4 is in red
i'm going to 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 looking look like railroad
tracks or 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 where
there's a 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 m-a-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 parenthesis which is the
shift key in nine
and i'm going to drag my mouse across a
range of cells
until i come to the very top
release the mouse button
and press
shift and 0 to do a close parenthesis so
what this will do it will give me the
maximum of all of these cells the
maximum person
is getting
dollars 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
m-i-n
open parenthesis
and drag my mouse across
our range
and i will tell it what the minimum is
after i press shift and 0
enter and the the lowly person in my
office is getting six dollars and ninety
cents
that 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 it
now i want to click in the next column
over
right click
and paste
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 their 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 tenth the tenths
place or the hundredths 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 tenth 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 ours 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 set for this week
so now we've created the maximum the
minimum and the average for each of
these columns columns 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 s-u-m
and that will give us the total
let's do shift in nine
and then i'm going to select a range
here
i'm going to select all of the hours
worked
close parenthesis 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 a dollar
sign format
and it shows now eleven thousand five
hundred and thirty two dollars
the last thing we should do is put our
name at the top so maybe c1 is a good
place
type in your name press enter save the
spreadsheet print it and you're done
with the 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
a 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 a
close parenthesis 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 fear if i
double click here you will see that this
formula takes the number of hours to the
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
0.5
and use the time sign 0.5 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 we 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 i'm 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 e3 i'm going
to add seven days to this date
notice the first date that we chose was
january 1st
you can either type in january 8th 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 eighth
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 i 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
i'm going to put a date here for the
week january 1st
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 first
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 d4 is greater than 40 then
give us the value d4 minus 40 otherwise
give us 0.
i can take this entire block and copy it
i'm 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 8th
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
8th
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
the cell d4 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
cop 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 1st so the week of january 1st
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 did 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
results i'll show you what happens in a
minute i'm gonna copy this formula
paste it for the next week
now why in the world is everyone making
over a thousand dollars some up to three
thousand three hundred
what happened
well let's double click on this cell
and find out where it's coming from
first of all you notice it's very
difficult to see the other side of the
world here
we can actually zoom out on a
spreadsheet
and we can
make it easier to see the whole page so
let's zoom out to 50 percent
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 these 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 o
four
it says take the cells d4 and multiply
by e4 and give me the results d4 and e4
that worked 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 c4
i'm 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 c4 times
d4
and i know that i'm going to copy it to
the right four times
what i really want to do is keep
referencing the hourly wage because
that's how you calculate pay hourly wage
in c4
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
a dollar sign in front of it that
doesn't mean a value dollars it just
means that every time that you think
about cell c
or c4
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 c4
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 8th 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
its
number of hours in this case is 30 but
it's still referencing
c4
and so now they're 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 this 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 1st
this one's going to equal
the first cell
plus 7
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 over time hours
but way back at the beginning it is
referencing
not the wage that we're expecting
it's referencing cell d4 so we're going
to have to change this formula to use
absolute cell referencing again
so i'm going to
clear these
highlight choose clear contents from the
menu
i'm going to double click on this
formula
and instead of c4 i'm going to reference
it as dollar sign c4
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
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
let's pick something red
something blue how about blue blue is
looking good
now for the total
what in the world is a total going to
look like
first of all i need to zoom out a bit
let's go to zoom to
50 percent
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 1st 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
if i highlight this
and i'm filling to the right
i'm going to double click on the cell
titles so that they
all adjust to the proper width
now 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 no from the second week
times the overtime bonus for the second
week
that's exactly what we wanted let's see
try another one here i'm going to pick
this one randomly choose one cell
it looks like it's adding the proper
cells 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
and 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 s equals sum
formula equals sum
and then add up all four of these
five of these
with a close parenthesis
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
let's 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
earned eight thousand dollars 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 could fit all of the
columns on one page or fit all the sheet
in one page let's try that
okay it's very small
seems to work though
let's save it and print it as this size
the next lesson we're going to do is
called gradebook and you can see that
the gradebook is not just a simple
gradebook 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 gradebook 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 gradebook for our employees
we're going to give them a series of
tests for the company
a safety test
a company philosophy test 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 in
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 i used from
my payroll spreadsheet you can just copy
and paste these names in or if you like
to retype 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 c1
d1 e1 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
let's fit in here in b2 and let's put in
here
points possible
this will show us what the maximum grade
is for each of these
let's say the first one is worth ten
the next test is worth twenty points the
financial test is worth a hundred and
the drug test 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 oh trent man got
an eleven
but then over on this side where it says
drug test you notice that everybody has
a one or a zero so there are two
employees that got zeros that means they
failed their drug trust
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 want it to say one percent so
i'm going back to the ribbon and
choosing the home tab
and you'll find in here they're
formatting for percentages there's a
percent sign
so on the safety test john kern
earned 100 percent
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 eighty percent this one says
ninety
ninety percent no 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 percent
do you see what's being divided
it says take c7 and divided by c5
really what we want to do is take c7 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 a dollar 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 percent 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 one
hundred percent
or zero percent
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 or 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
and 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
tests
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 dot on blessing
she's not so good
at financial skills and for the drug
tests
the last one we 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
there's 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 percent on any test
so a quick way to find out who that is
would be to highlight all of the test
scores and the 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 and we want to find out who
is receiving less than and let's put in
50 so i can put 0.5 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 fifty percent
for karen
less than fifty percent on a drug test
means you failed it you got zero
less than fifty percent 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 fifty
percent if so
then we should fire them you should at
least pass with fifty percent
so the formula we're looking for is
called the or formula
you type the equal sign and type or
or in a parenthesis
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 0.5
and i'll put a comma and then i'll ask
another question
is
i-4
less than
50 percent another comma
is this less than
0.5 and then finally the question is is
comma is this one
less than 0.5
so i have four questions in a row
all of them ask the same thing is this
number less than 0.5
and if i press enter
it says false
none of these scores are less than 0.5
however when i fill down you will see
that some of these scores are less than
0.5
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 it 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
parenthesis
and let's do the same for minimum equals
min
a parenthesis 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 average
is for each of these
i'm 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
let's choose a chart
here's called a column chart
in the column chart we'll just click it
and we'll choose
clustered columns
and 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 test
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 let's 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 notice as i do that
it's telling me that from sheet 1 the
range a4 to a20 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 test 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
test
once more we need to add these
labels in so that they are the names
instead of the numbers so let's choose
select data
where it says access labels we'll edit
that
and we need to slide over to select the
people's names
so from underhill
up to kern
and okay
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
go to 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
thousands 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's
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 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 that's 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 let's put him down
at near the bottom a doctor's probably
doing pretty well let's give him a four
nfl let's assume is 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 uh
likely they 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 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 just
to sum up the
lines here so i'm just going to put sum
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 column 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 four
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 as i have to
and then pay i'm going to account that
is 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 c4 i'm going
to put a dollar sign in front of the 4
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
let's 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 percent
that'll show basically the first highest
ranked item
and so 65 65 is the highest number in
the list
that 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 print 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 we'll 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 database 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 rows so you don't
have to type all these things in
i'll provide you with a spreadsheet 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 boos 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 in 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
sales person to find out who the best
sales person is
in our store
well let's do some of the formulas here
that we will have to uh 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 in 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 sumif
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 is 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 titles 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 h
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
a 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 used fixed
with 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 semicolon 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 sales people 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 uh use is based
on the maybe the manufacturer's code
but how much profit do you make if you
sell the item for 98 dollars and it
costs you 58 dollars to buy it from your
supplier it's a simple formula to say
equals
this square here which is f2
subtract
e2
and press enter and you can see that we
made
forty dollars and ten cents
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
ten percent commission for items less
than fifty dollars
but if he sells an item that sells for
more than fifty dollars we'll give him
twenty percent 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 fifty dollars
comma
then
let's give him more than
more profit than than less
so the rule says give him 20 percent of
the sales
let's make it twenty percent of the
profits actually so let's take
the profit
and multiply
that shift in eight
and a decimal two so that's twenty
percent
but if it's less than or equal to fifty
comma
then let's take the profits
and multiply by point
one or ten percent
so that's the rule that tells us the
commission
is based on the value of the item that
was sold
press enter
so the commission
for this item
is 20 percent it cost more than fifty
dollars
so we give him twenty percent of the
profits of the store so charlie barnes
earned eight dollars and two cents which
is
twenty percent 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
now it'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 i a 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 as 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 seven dollars
the profit for the store was three
dollars and doug smith earned ten
percent on that sale so
less than fifty dollar item you get less
commission
scroll back to the top
now the next item that we're going to
look at is called sumif
sumif 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 fifty dollars
and the sum of items valued at fifty
dollars 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
i'm going to type the letter f and 2
and a colon
and then the word letter f 172
and you can see on the screen
that there's a blue rectangle
surrounding the range
close the item with a parenthesis and
press enter
so in this year the store sales were
seventeen thousand one hundred and ten
dollars
now what my question is is how many of
those items were valued at fifty dollars
or more what is the sum of all those
items the new formula that we use now is
called sumif so type equals sum if
and now let's take a look at what i can
put in here it's called a range and a
criteria
so this the range is once again f2 colon
f172 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 a greater than 50 and 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 sixteen thousand eighty
eight dollars is the sum of all items
that were costing more than fifty
dollars
well let's use the same formula but this
time let's choose any items that are
valued at fifty or less
so that is equals sum if
the range will be the same f2 colon f172
and a comma
now this time
i'm going to put in my rule as
less than
or equal to
50
and a
quotation and a parenthesis
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 in filter
is 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 or
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
and 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 sort one more time i want 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
you use the next button filter what are
we going to filter
as 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 check mark 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 nm 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
two jump to eight nine thirteen eighteen
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 helen's 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 tables show up next
says what columns are we going to use
you can see the selection here is from
a1 to k172
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 sheet3
yours probably says sheet2
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 sales people
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 six thousand dollars
when i clicked 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 sales
person 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 insur 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 sales person 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 a worksheet 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
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 car 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 to so we can
combine
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
vlookup formulas to create a value out
of a table
we'll review the if formula
we 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 program 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 a 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 would 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 text
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 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 semicolons
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
you'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
zero six for the year
mtg
for mustang
and zero zero one 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 cho if i choose that
you notice the options are
first of all choose text well i'm going
to choose this is 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
manufacturer's names so let's
use a closed parenthesis
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 ca
let's create a new formula
and we're going to put in column c
the manufacturer's name i'd like to put
in just the word ford
or down here general motors t y must
stand for toyota
ho is honda
cr is chrysler and hy is
hyundai we're going to introduce a new
function here called a lookup table
let's take these items here like
cr and hy and ty and ho
and gm and fd 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 fd 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 fd or the letters fd
and then return
the second column which is forward
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 i'm 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 4 general motors so
i'm going to type a 2 up here
and a comma and then actually this is
the the only option that we need
we can close the parenthesis 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 n a which means not
available or not accessible
and so what's going on
well if i double click on the second
item
and 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 a dollar sign in front of 56
and a dollar 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
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
well the model is found here this is the
the letters mtg
so we can't 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 mtg stands for mustang
so let's uh 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 mid
it says here that it's going to return
the characters from the middle of a text
string
given the starting position and the
length
so let's put a parenthesis
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 at position one two three four
five so it starts at position five in
the string
and then a comma
how many characters do we need well
these all have three characters in each
of the model
numbers
okay close parenthesis 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 that must stand for ford focus
and then a
general motors
cmr
an slv
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 mtg
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
a honda odyssey and what's next here a
pt cruiser
a chrysler caravan and then the honda
elantra so these are all different
models
so at the bottom here we're 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 vlookup 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
elantra is the first item
what's going on i'll double click here
to find out
we're looking for d56 through e61
well 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 lighter 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 zero od
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 ho 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
is 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
put a comma it's going to start at
position 3
and we're going to take two text items
two letters
and we get zero six
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 8 so a 2006 car is eight
years old
seems to work
let's see what happens when i fill down
most of the time it works except for
here what's this it says there's a
problem with it
now if you look closely at this this is
an o6
it's literally an o6
we want zero six so once more somebody
thought that it was an o6 and in the
database they put a oh the letter o in
here
so let's delete that o
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 o 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
h o
and then a
zero five
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 b1 f19
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 200 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 two thousand and those that
are greater than two thousand
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
at 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 50 5000 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 in 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
let's add a little bit of a of a number
well let's see we want to add something
to g2
so i'm going to have to put a
parenthesis before g2
and add
0.5 so 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 thousands if
you have a comma in there let's move
let's keep moving the color of this is
black
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 fifty thousand mile warranty
and this car is just barely under the
warranty it still has ten thousand 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
parenthesis so now this says yes it is
covered i get a y
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 con how do we how do we
combine fields
the formula they're looking for is
called concatenate c-o-n
and then you'll see concatenate shows up
here if i click it double click it
the format that you're supposed to to
fill in is simply says text 1 text 2 etc
so we're going to concatenate
a bunch of fields we want to put first
of all continue using fd
comma and then we want to have the
manufacturer's year that 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 color 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 have the left three of j2
so j2 comma three
and a closed parenthesis 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 parenthesis and press enter
so now we have the new car id
one more change that we need to make is
that
this shows
lower case letters
your boss didn't tell you but he forgot
to say
all ids in cars have upper case letters
only
well 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 parenthesis
around the part that i want to be
uppercase
and press enter and now
the new car id shows bla
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
sheet1
i'm going to select
the driver first
put a check mark 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 and 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 well he
has a ford mustang no wonder he's
driving so much i would drive a lot too
if somebody gave me a mustang
and so smith shows his
miles as the most of anyone in the
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
and 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
axis titles a little bit easier to read
so i'm going to select those as well
deleting the word axis 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 let's pick one of these color
scales there's blue and green and red
it doesn't matter which one you choose
but when you pick one you'll see that
some colors are
highlighted in darker and lighter colors
these 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 is
already driven at 17 000 miles
if we scroll to the bottom you'll see
that the
minimum driven car is this ford mustang
here it's eight years old and it has
forty thousand 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 our 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 pastes
and so these two programs integrate with
each other
press enter a couple 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 in excel that was created using
a text file you can see up here it says
car inventory text
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
ten thousand dollar 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 ten thousand
dollars 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 a dollar sign if you
want now the interest rate if i put in a
number here let's say nine percent and i
use the percent sign it will calculate
the number correctly
however if you don't put in the nine
percent you can put in
0.09 which is the decimal equivalent of
nine percent
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
pay so that's a formula where we type in
equals
we want to take the principal which is
b2
and i will multiply
by c2 which is the rate so you can
either type in equals b2
and use the star key which is the shift
8
and that's for multiply
and then finally type in z2 c2 and press
enter
and that will tell us that the total
amount of interest is nine hundred
dollars 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 ten
thousand
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 f2
divided by e2 and press enter so your
monthly payment for this car is 908
dollars
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 from 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 control 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 eight percent loan another one is
seven and even six percent 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 dot 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 six
percent 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 ten
thousand dollars
all right now at the bottom you can see
that i have this one two three four
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 8
7 6 7 8 9
and then it puts in the correct columns
there c2 to c5 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 9876
and click ok
and there you got your chart
my name is chad sluter and my youtube
channel is called tech made simple i'm a
university professor that teaches
programming uh 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 at
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 waltmart
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 her three
times 50 cents
or a dollar fifty
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 a dollar sign each
of them
let's open the currency add a dollars
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
3
times 50.
here are some hints for the formula
in this square which is g3
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 cost is for purchasing
supplies at walmart
repeat the same process for another
column we're going to figure out the
price for the entire shop list the
dollar trap and then we'll figure out
the price again at austral
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 2 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 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 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 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 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 are 50
cats are 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 prince says buy two of
each of these for my pet each month
and so will double that expense
each month
the dog has some expenses too his dog
food is more money and his dog treats
are 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 well the
purchase price for the dog
we have to buy a collar a tag a bowl a
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 subtotaled
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 so 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 a 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 it 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 airline 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 a 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 at the bottom and 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've 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 in helping tim
and susan pick their vacation
welcome to excel problem solvers so
susan and tim
both would like to buy a printer susan
says would you help me pick one of these
three printers i plan to print about 15
pages each day
so the five 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 twenty nine dollars and so you
might think that is the best price at
the beginning
until you look at the supplies and
you'll see that ink is forty dollars a
cartridge
and it uh 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 xero 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 laser
jet 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 that you 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 cost will
come here at the bottom
the 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 the 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
miles when i assume it will be nearly
worthless
i drive about thirty thousand 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 forty percent
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 fifteen hundred dollars a
year in insurance
the initial cost for taxes is fourteen
hundred fifty
and every year she spends two hundred
and ten dollars 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 escalade
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
and 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 and escalade
the yellow section at the top will 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 escalade
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 percent
higher than what susan paid and so that
40 percent includes all the interest and
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 kind
of things check out my channel on
youtube my name is shad slooter 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