Ad banner
Ad banner

SearchThisVideo: Microsoft Excel Tutorial for Beginners – Full Course

Copy Help
  • Public/Private: Change the visibility of this video on your My Videos tab
  • Save/Unsave: Save/Unsave this video to/from your Saved Videos tab
  • Copy: Copy this video link to your system clipboard
  • Email: Copy this video link to your default email application
  • Remove: Remove this video from your My Videos or Saved Videos tab

Watch video at 00:00
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

Watch video from 03:00 - 6:00

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

Watch video from 06:00 - 9:00

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

Watch video from 09:00 - 12:00

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

Watch video from 12:00 - 15:00

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

Watch video from 15:00 - 18:00

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

Watch video from 18:00 - 21:00

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

Watch video from 21:00 - 24:00

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

Watch video from 24:00 - 27:00

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

Watch video from 27:00 - 30:00

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

Watch video from 30:00 - 33:00

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
it 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

Watch video from 33:00 - 36:00

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

Watch video from 36:00 - 39:00

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

Watch video from 39:00 - 42:00
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 grade book and
formatting data that we haven't done
before
and you'll have a very nice looking
chart when you're finished
what you see on the screen in front of
you is a grade book for 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

Watch video from 42:00 - 45:00

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 11
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

Watch video from 45:00 - 48:00

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

Watch video from 48:00 - 51:00

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

Watch video from 51:00 - 54:00
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

Watch video from 54:00 - 57:00

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

Watch video from 57:00 - 60:00

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

Watch video from 60:00 - 63:00

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

Watch video from 63:00 - 66:00

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

Watch video from 66:00 - 69:00

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

Watch video from 69:00 - 72:00

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

Watch video from 72:00 - 75:00

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

Watch video from 75:00 - 78:00

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

Watch video from 78:00 - 81:00

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

Watch video from 81:00 - 84:00

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

Watch video from 84:00 - 87:00

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

Watch video from 87:00 - 90:00

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