hello and welcome my name is spaz cream
and this is accel intermediate using
Excel 2019 I'd like to give a short
little introduction video discussing
what we're gonna cover in this class
we're gonna go ahead and learn about the
difference between a list and a table as
well as some useful features with charts
in Excel and then later on we're gonna
dive into pivot tables and once we
master that we're gonna learn about
protecting worksheets and workbooks as
well as linking various cells from
different worksheets and different
workbooks together through this course I
want you guys to go ahead and practice
and try hands-on what I'm showing you
so how it's designed is I want you to
follow along as I do each step so I'll
go ahead and let you guys know to pause
the video or to go ahead and follow
along with me as I do these steps I'm a
firm believer that using hands-on
methods to practice Microsoft Excel
really helps it stick you establish a
muscle memory and let's go ahead and
dive into class see you soon
all right before we begin be sure to
download the sample file they came with
the class if you have yet to do that go
ahead and pause the video and grab that
and come right back
alrighty welcome back hopefully you grab
that sample file and you're ready to go
I'm currently on the creating table
sheet of this file and let's go ahead
and start talking about a list a list of
data in excel so a list of data is
simply data that you put on a
spreadsheet and what we did here is we
put in some headers and each header
describes the data below it notice how
we have email there's their email
address I notice have a phone extension
we have a phone extension and location
describes the location what building
they're in and so on and so forth
now this lists some data we have a bunch
of tools up here that we can use to
analyze it like maybe in our data tab we
can go ahead and turn on a filter which
we'll cover or we can sort the data
alphabetically or numerically or we
could split up the data now a list of
data is great but Microsoft Excel has
this data analytic tool known as a table
so what we can do is we can convert a
list into a table we could insert a
table into Excel and convert this list
into a table now what does that do for
us what is converting a list of data do
well when you convert it to a table it's
a data analytic tool you get a set of
features that you did not have with the
list to analyze that data for yourself
now there's several ways you can convert
this to a table more importantly there's
just about two that I'm going to show
you
and once faster than the other and I
just want to show you the difference
between the two so really quick in order
to convert your list into a table you
should have three properties for your
list of data property number one to
convert this list into a table is you
should go ahead and have headers headers
describing the data set below notice how
we have pay rate and then we have higher
date location and phone extension email
and so on and so forth so these headers
they have to be unique amongst each
other meaning I can't have two higher
dates
I can't have to pay rates that doesn't
mean I can't have a pay rate one or a
pay rate zero I add a little numerical
value at the end but they must be unique
amongst each other
now that's guideline one uniqueness
amongst the headers now guideline number
two to convert this list into a table is
simply having a unique header compared
to the data set now what I mean by that
is you can't have a header called
building one because that's part of the
data I can't have a header called Howard
that's part of the data
so that's guideline number two you have
to have a unique header compares to the
data and just in case you forgot I'm
number one was you must have unique
headers compared to each and every other
header now what's the third guideline
the third guideline is simply you have
to have a complete data set
no blank cells so that way our functions
and our calculations can come out
correctly when we use the table now that
we have the guidelines let's go ahead
and see how we can convert this list
into a table so just in case you forgot
let's do a little wrap-up of those three
guidelines guideline number one we must
have headers and the headers have to be
unique compared to each other
guideline number two is your headers
must be unique compared to the data set
and guideline number three you must have
a complete data set for the calculations
used in the table to come out correctly
Oh righty this format this list into a
table so we have to insert the data
analytic tool known as a table into
excel notice how we have an insert tab
here so everyone let's do this together
but first be sure your cursor is inside
of the data set like so and then go
ahead and run up to your insert tab and
give it a click inside the insert tab I
just want to expose you to the tables
command group where you can find in the
third option the data analytic tool
known as the table I'm gonna go ahead
and hover over it to expose the
definition that Microsoft gave it create
a table to organize and analyze related
data tables make it easy to sort filter
and format data within a sheet sounds
nice I'm gonna go ahead and click that
option again I want to point out that my
cursor is within the data set and give
it a click just like that I got the
create table box here and because I had
my cursor inside of that complete data
set it found the entirety of the data
and notice how there's a checkmark box
here asking if your table does have
headers and in fact it does so I'm gonna
leave that on and hit OK but before we
hit okay guys and gals let's make sure
that we know the quick way of doing
things and the long way in case we
forget the quick way so let's go ahead
and X out the create table box notice
how I hovered over here and it said
table ctrl + T in parenthesis that's
because this has a keyboard shortcut to
activate that icon on your screen
without you going to the insert tab and
clicking on it so let's say you're on
your home tab and you remember the fact
that if I hold ctrl + the letter T you
have the ability to activate the insert
tabs create table icon and it just did
that for us now that we know the short
way of creating a table let's all go
ahead and hit OK to make this list into
a table alright
all righty now that we converted this
listen to a lovely table let's go ahead
and discuss some features this data
analytic tool has to offer the first
thing I want everyone to do is now that
we have this list into a table I want to
point out that we have this lovely table
formatting has banded rows let's go
ahead and click inside of the table and
notice how we have a design tab that
popped up and give it a click this
design tab is all the tools the table
has to offer now if you go ahead and
click away from the table the design tab
will go away
it's one of those contextual features a
contextual tab based off the context
you're using the software and may or may
not be there now I'm gonna go ahead and
click on the table and based off this
context I have a design tab inside of
the design tab I want to point out one
of the features that this table has to
offer is a filter the filter button can
be turned off I'm gonna go ahead and
turn that off go ahead and try that to
just give it a click and notice how
these little arrows went away from each
of my header names because that was the
filter option so let's turn back on the
filter and go to these arrows and notice
how the filter is not only a filter down
here notice how you can unselect an
option and then filter for the criterias
you want so all the employees getting
paid in 925 and 950 and just go ahead
and hit OK and it filtered the table and
the only indicator that you had at this
table is filtered is that little funnel
right there and if you send a file
filtered they will receive it filtered
so they can't accidentally miss out a
lot of work if you leave a file filtered
without letting them know that hey this
is filtered so another giveaway that
this file is filtered is notice how the
table goes from 321 35 and 36 because
all this filtering is doing is hiding
rows from us based off the criteria we
give it so let's go back to this filter
here and let's clear the filter
notice how the option to clear the
filter is right there and I want to just
expose you to another form of filtering
this table has to offer which is known
as a slicer so I'm gonna go ahead and go
to the design tab of the table and give
it a click if it's not there just make
sure your cursor is inside that table
and go to insert slicer inside of the
tools
I'm gonna go ahead and give that a click
and this time I'm gonna build a slicer
for the location column here and matter
of fact I'm gonna do one for the
department too
because you can do two at once and once
you're satisfied go ahead and click OK
I'm gonna go ahead and give that a click
now these slicers are another way of
filtering this data set here so check it
out I'm gonna move these to the side
just a zoom out of my spreadsheet a bit
as well and if I want to built err if I
would like to filter for my location I
can just select one of these options and
it filters my table now if I want to do
more than one option I turn on the multi
select go ahead and try that out and
select another option and then you can
go ahead and use this one to filter for
departments notice how it's also
applying the funnel here I don't
recommend to turn off the design tabs
filter button and the design tab I don't
recommend to turn those off because it
lets it know what's column is being
filtered here using these slicers so a
table is a great way to go ahead and
sort your data alphabetically with these
filter options as well as a great way to
filter the table both filtering using
the filter button as well as a visual
way of filtering using the slicers go
ahead and play around with the table and
the slicer a bit and see what you can
build the table to be based off the
criteria you gave it
all righty hopefully you're having fun
playing around those slicers they're
pretty cool and nifty if you haven't
been exposed to them yet now let's go
ahead and clear the filters from these
slicers just by hitting that little X on
the side of the slicers and make sure to
turn off the multi-select just to keep
them clean and consistent and now that
we have the ability to filter data I
want to let you know that this table
does have the ability to find the
duplicate values within it and remove
them
notice how in the design tab go ahead
and put your cursor inside the table and
go to the design tab and give it a click
in the design tab you have some tools
and one of the options removing a
duplicate so let's go ahead and build
the duplicate in this table so I can
show you how that can be done I'm gonna
scroll all the way to the bottom of my
table here to the fortieth row and I'm
gonna go ahead and select row 40 and I'm
gonna go to my home here and I'm just
gonna copy the row and then I'm gonna go
to row 41 and I'm just gonna go ahead
and paste the row alrighty now once I
pasted the row I'm gonna go ahead and
extend my table because if you remember
my table went to I 40 and notice how
that little blue indicator right there
stopped itself so I'm gonna go ahead and
extend my table to go into row 41 there
sometimes it does not extend itself like
it just did it there and now that I have
my table extended I'm gonna go ahead and
go to my design tab I just want to show
you the option to remove duplicates so
delete duplicate rows from a sheet you
can pick which column should be checked
for duplicate information so remove
duplicates notice that we have every
column over here you don't have to have
every column selected you can use one of
the columns that uniquely describe each
of the rows so notice how my employee
IDs describe each row based off this
unique value if I find an employee ID
that's not unique that's gonna imply
that I have two of the same employee in
there so you're allowed to go ahead and
unselect everything and just search
based off one criteria that one unique
value and once you go ahead and do that
and you hit OK go ahead and do that
yourself again this was in the design
tab under remove dupe
wickets and then we got here okay it
says one duplicate value found and
removed 37 values remain I'm gonna click
OK and show you that it removed
everything pertaining to that duplicate
value so it removed the second instance
of 1999 and it removed everything
related to it go ahead and give that a
try adding in a duplicate and removing
it and it's not that you could have done
this without converting your list of
data into a table so that you have the
design tab because notice how the option
to remove duplicates is in the design
tab I want to just let you know that you
also have the option
remove duplicates in your data tab
that's if you had a list of data and you
had not converted it into a table you
would have used this route inside of the
data tab just want to go ahead and
expose you to that go ahead and add a
duplicate into the table and remove it
just to get a muscle memory and for that
and if you have time go ahead and pause
the video and maybe try it out with a
list of data instead of a table
looking at the custom sorting sheet on
the sample file provided for the class I
want to go ahead and cover a useful
feature in Excel now sometimes you'll be
building a spreadsheet and you want to
build additional columns of data like
for example notice how I have a last
name column and I have a first name
column and over here I want to build a
full name column looking at it and
looking at how many employees I have
okay
52 that can be a pretty tedious task to
write out all these full names and if
you know of various other functions you
can use you can use concatenate or
connect at put that together but since
Excel 2013 there's this feature known as
flash fill that I want to expose you to
so we can automatically just fill in all
these data sets with the values we want
within a flash if we give it one example
I want to go ahead and expose you to the
fact that in your data tab let's all go
to the data tab by giving it a click we
have some data tools yeah I know I said
data and then I said data I do that all
the time well in the data tools you have
an option for flash fill I'm gonna go
ahead and show you what the definition
Microsoft gave it as flash fill the
shortcuts ctrl e4 enter automatically
fill in values enter a couple of
examples you want as an output and keep
the active cell in the column you want
filled let's go ahead and run through an
example of that so everyone let's go
ahead and go to cell d4 here and let's
give it an example of what we want you
want to put hazel and we want to put
Gorton I'm gonna hit enter it on this
blank cell I gave it my example I want
it to go ahead and do the rest for me so
let's all go to our data tab and just
give it flash fill a click pretty cool
how it did that for us fill than every
single name for us how this worked is it
looked over to the column to the left
hand side of it and tried to see if
you're doing a pattern here it's like oh
you did this and this together I think
you're gonna do the same thing here and
it just replicated it for us now that we
expose you to that I want to show you
what else flash Ville can do because it
could save you time if you know this so
let's all undo this action
by going up here and clicking undo or
hitting the shortcut ctrl + Z to undo
the flash fill now that we undid the
flash fill let's give it another example
this time I want to capitalize the first
name and I want to leave the last name
as is so this time I'm gonna hit enter
and I know the shortcut it's ctrl + the
letter e for enter I'm not gonna run up
to my data tab and click on flash fill
because I'm just gonna use the shortcut
instead so you should try that too so
I'm gonna hit ctrl E and now I just
showed you that not only does it find
patterns within the spreadsheet and
mimics them for you
it also can do formatting for that
pattern as well like capitalizing the
word go ahead and get flash feel a try
expose yourself to it and think of all
the great ways you can save a bunch of
time using flash fill on your
spreadsheets
currently on the custom sorting sheet of
the sample style provided for you in
this class so sometimes you'll be
analyzing data and you'll realize that
if you sort the data searching way it'll
be quicker for you to retrieve the
information you're asking for for
example if you're a manager trying to
contact employees it'd be nice to have a
list of all the employees days off put
together so that you're not contacting
employees on their days off or maybe if
you're a manager dealing with a bunch of
employees across different locations to
have it sorted based off the location
here and see all the employees in one
location together or if you need to see
a bigger picture and you see all the
employees across all locations it would
be nice to have an alphabetical list of
everyone inside of this data set in
alphabetical order so that way you have
a quick way of finding people what
you're asking is to sort the data set
and I want to let you know that if you
have a filter icon on the list meaning
if you go to your data tab and turn on
the filter icon go ahead and do that for
yourself you have these arrows that pop
up that are filters and we discussed
earlier that they're also sort options
for the column below now that's one way
of sorting but I want to let you know
that in your data tab if you did not
have this filter on you also have the
ability to sort columns notice you have
a Z to a and an A to Z sort let's go
ahead and run through an example of how
this works with the full name column
here I want to sort it alphabetically
all you have to do is you have to put
your cursor inside of the column you
don't have to drag and highlight a range
you don't have to sit there and select
the entire column just the cursor just
that cell address box should be inside
of the column that you want sorted once
you have that in there you're gonna run
up to the data tab and hit a to Z I want
to point out that it sorted every single
piece of data pertaining to that column
with it so we have everything in order
still it's just alphabetical based off
the full name now that we have the
ability to sort a certain column what if
we want it to sort based off priority
levels meaning I first wanted to sort on
my full name and then after I have all
my full names in alphabetical order I
want it to sort based off the locations
put together or maybe I want it to first
have all my
zones put together and then after I see
all my zones in order try to put the
locations in the best order possible
based off the zones and then once those
are all in order I want to go ahead and
maybe see that list in alphabetical
order with the employees full names so
what you're asking for is the first sort
on the zone then go ahead and sort based
off the location after the zone and then
after all that make that list in
alphabetical order based off the full
name you're asking for a multi-level
sort pretty easy to do it's actually
right next to the A to Z and Z to a
icons notice how we have a big old sort
box here this is where you go ahead and
do multi-level sorts let's go ahead and
do that exact example so first by zone
then by location then after we do that
let's go ahead and do it by full name so
I'm gonna click that icon sort in my
data tab and I have the full name column
sort right over here it's already in
there
full name based off the cells values not
the color not the font in alphabetical
order I'm gonna go ahead and add in a
second level of sorting except I want to
go ahead and do this one based off the
zone that we were talking about the
cells values a to Z but I said that I
wanted my full name to be the last level
I want to be first priority zone so
let's go ahead and click this up arrow
to move that priority up first give it a
click great so the zone full name well I
still need that extra location level
here so I'm gonna go ahead and add in
that third level you should too
at level I'm gonna use zone and this
time I'm ready location and then full
name and then I'm gonna hit OK so now
what this data set is all the zones are
gonna be put together this is everyone
in Alaska now after that it's gonna put
all the buildings together this is all
building ones there may be some
discrepancies here's because of course
it's based off the zone first but the
reason why I did this is typically if
they're in the same zone it's good
chance to get me in the same building
right now considering the fact there's
only three buildings
after all that it's an alphabetical
order so the first person in that zones
name is going to be starting with an e
that's an alphabetical order
so go ahead and play around with
multi-level sorting in the data tab and
see what ways you can sort the data to
make it useful for you
alrighty we are currently on the
subtotal sheet provided in the sample
file if you have yet to grab that go
ahead and grab that and go to the
subtotal sheet and get started
alrighty hopefully you grab the file on
your back so let me give a little
scenario for you you're having a lovely
day at work not much work to do matter
of fact it's Friday and it's around that
time where you were kind of checking out
but your manager comes in and he's like
hey I have a spreadsheet it's actually
our pear wine distribution sales our
margins or quantities currently at the
current state I just pulled it from the
database and I need you to find and
build some data for me I want you to
find all our red sales of wine and then
after that I want you to find maybe all
our white sales of wine and I want you
to build a summary sheet so that we can
see all our subtotals for the different
categories that we have and you're
sitting there is like great now I have
to go sit there and turn on a filter and
then filter for the Reds and find a
calculation for sales and margin or
quantity and then I have to go ahead and
do the same thing for whites and that's
gonna take so long because I have to do
six different calculations and six
different filters unless you know how to
use a feature to calculate the subtotals
for you now I want to point out the fact
that sub totally how it works before we
even dive into it I just want to discuss
how it works so sup totally there's this
feature in Excel known as sub totally
and what it allows us to do is we get to
get a column and the software is gonna
run through the column and it's gonna
make okay okay okay
and once it notices a change in that
columns value so right here went from
red to white it's gonna say what
function do you want me to run on
another column do you want me to sum the
sales from this point do you want me to
sum or maybe find the average of the
margins or do you want me to find the
max of the quantity or the sum of the
quantity what function do you want me to
run on another column so at this being
said in order for sub tooling to find
all the Reds and all the whites together
we first need to sort our columns that
we want to find the subtotal for so we
want to sort the type column so let's go
ahead and do that so step one for every
sub totalling is you must have that
column sorted for it to calculate the
subtotal correctly so that's before we
even touch that icon we show you where
it is your column has to be sorted and
if you do need to refer to the sorting
videos it's right before this just see
how you can sort a column this is just
the single level sort I'm just going to
go ahead and click my cursor in here and
just go up to my data tab and I'm gonna
sort it from A to Z and now that I did
the sort I'm ready to expose you to the
sub totaling option in Excel well we're
gonna deal with data so it's still gonna
be in the data tab I want to point out
that you have a command group all the
way to the right with an outline option
outline the outline command group and
one of the options over here is subtotal
so what the column sorted don't forget
that first step that important step
that's in the data tab a to z let's all
go ahead and click on subtotal now keep
in mind it didn't really matter where my
cursor was it didn't happen to have the
whole dataset highlighted as you can see
it highlighted it for me the day the
cursor or the cell address box of course
did have to be inside of the data set
just anywhere within the data set now we
get to fill in the subtotal box instead
of calculating each one of those
subtotals ourselves using the various
methods that we know so in each change
in let's all go ahead and change that to
type because that was the column we just
sorted and that's the column that says
all red and then it breaks the white
when we needed to and we want to go
ahead and sum up so I'm gonna go ahead
and do the sales the margin and the
quantity if I did have subtotals that I
had done before this I have the option
to replace those subtotals with these
current subtotals since I don't have one
to begin with it doesn't matter that
this is checked marked on so I'm just
gonna leave it you can too and I'm gonna
just hit OK
and you should too now that we hit OK I
want to expose you to the fact that on
the left hand side you have three
different views of the workbook views we
call them I'm gonna go ahead and click
on the first view here now it shows you
the grand total and I'm know of some of
us are thinking why's that show pound
signs that did not work the pound signs
just mean the column is too small to
show you the values within it just
quickly go up in between the column and
do - clicks - clicks and I'll show you
the value inside of it so first sheet is
gonna show you the grand total this is
all the total inside the type column we
have 45,000 and sales 21,000 a margin
and that's our quantity bears for red
and white let's all go ahead and expose
ourselves to view number two by giving
it a click view number two is actually
the breakdown the thing we wanted the
red sales the white and then the grand
total and each value we wanted now view
number three that's pretty cool it's
actually the complete data set but if
you go ahead and scroll through it
you're gonna notice a an extra row well
few extra rows added in so just
scrolling through it going down the reds
oh there we go
once the red ended it plopped in on row
181 red total this sales margin and
quantity let's go ahead and scroll
through it you're gonna notice down here
we have the white and then we have the
grand total so these are all the three
views that we have and all we needed to
do was know that in our data tab we have
this lovely ability to calculate
subtotals on a sorted column go ahead
and play around with sub totaling try
out other columns to subtotal like that
let's see here try another column to
subtotal maybe go ahead and do the
groups and see the breakdowns of each
group and then after that maybe try it
apart so go ahead and pause the video
play around with it and see what other
subtotals you could come up with and
expose yourself to the three views
we are currently on the charting
one-sheet on the sample file provided
for the class you have yet to grab that
go ahead and pause the video and get
that come right back welcome back so
let's go ahead and start talking about
some charts in Excel 2019 first things
first I'm gonna highlight this data set
I'm just gonna use a shortcut I'm gonna
click my cursor within the data set hold
ctrl and hit the letter A for all to
highlight all of the data set and let's
talk about inserting some charts and
notice how we have an insert tab let's
all go to it let's give it a click on
insert I want to expose you to the
charts command group we have various
types of charts that you can insert to
analyze the data set visually you can go
ahead and do a column align a pie chart
all various types of charts or you can
go ahead and use the recommended charts
feature that we're about to cover let's
all go to the insert tab with the
datasets selected and choose recommended
charts so Microsoft Excel has gotten so
advanced that based off the data set you
give it it can recommend a set of charts
that you might want to use for example
notice how I highlighted the data set
and I clicked on a recommended charts
and it took me to the insert chart
window on the recommended charts tab
right there now the recommended charts
tab shows you a set of charts that you
want to use based off the data set you
gave it for example a clustered column
chart is used to compare values across a
few categories usually when the orders
of categories is not important so it
kind of gives you a description of when
you might want to use this chart then
you're gonna go ahead and click on the
next one a stacked column chart is used
to compare parts of a whole use the show
house segments of a whole change over
time oh so this chart would be great to
compare our Smith week one to week two
amongst themself but this chart would be
great to compare our Smith week one to H
James week one so different charts are
used for different data sets and Excel
can help you figure out what chart to
use now I want to point out that if you
don't want to use the recommended charts
and you already have a chart in mind you
have the all charts window the all
charts tab I should say right next to
the recommended charts go ahead and give
that a click
- and in case you forgot how we got here
all we did is highlight the data set and
chose recommended charts and I just move
this to the side and this time we're
gonna click on all charts so we have a
variety of charts Excel can use notice
how templates is there - we'll discuss
that later and you can go ahead and
browse through the set of charts to see
how it's gonna look based off your data
set and you can pick an appropriate
chart I'm gonna go ahead and go to the
column here pick this very first one and
hit OK alrighty so we just inserted a
chart I want to point out that when you
have a chart selected when you have
these dots around it you'll have this
conceptual tabs design and format for
the chart these are the chart tools you
click away from the chart it will go
away
now that we have the chart we know how
to insert it from the insert tab using
recommended charts is useful and all you
got to do is pick the chart you want and
it pops up let's go ahead and select the
chart and just hit the Delete key on our
keyboard and now that we know the proper
way of inserting a chart I want to point
out the fact that if you have a data set
selected and you were to hit the Alt key
+ f1 it would generate a chart for you
like so it just picks the very first
chart on the top of the recommended
charts list
it's the Alt key + f1 this doesn't work
try holding the FN key on your laptop if
you have one now this generates a chart
on the same sheet of the data set useful
when the data set is small but what if
this was really large and I wanted a
sheet separate for the chart all you
have to do is highlight the data set and
this time you're just going to hit f11
and then it generates a chart on the
left hand side of the sheet so several
ways to insert charts just go ahead and
pick your favorite and before we go
ahead and practice let's go ahead and
reiterate those ways you can always
highlight the data set and go to your
insert tab and insert one of the charts
or use that recommended charts tool or
you can be clever and use that shortcut
alt f1 to generate the chart ski
the first one that it recommends like so
and if you want to have a chart on its
own unique sheet on the left-hand side
of the sheet that you're currently on
you're simply going to highlight the
data set and you're just going to use
f11 on the top row of your keyboard to
generate it on the left hand side of the
sheet like so go ahead and practice
inserting charts expose yourself to the
recommended charts tool
all righty we are currently on the
charting one sheet and we generated a
chart using alt f1 on the sheet and I
want to go ahead and discuss some
features of a chart we have using Excel
2019 the first thing I want to point out
is when you have a chart selected you're
gonna have these little dots around it
so make sure we have the chart selected
you also have these two tabs up here
called design and format once you have
the chart selected these are the chart
tools so let's all go ahead and hover
over to the design tab of the chart and
again if it's not there just click on
the chart and it will be I want to point
out the fact that Excel has updated the
way you modify and edit the chart
visually let's go ahead and expose
ourselves in the design tab to the chart
layouts command group and click on add
chart elements let's go ahead and remove
the gridlines the grid lines on the
chart these lines going across primary
major horizontal gridlines major primary
horizontal I don't really want any I'm
gonna go to more grid line options
alrighty
scroll down here and size color solid
line I'm just gonna do no line all
righty
and I'm gonna close out that so I put no
line in there I removed the line the
grid lines for ourselves let me show you
the Excel 2019 way much simpler so add
chart elements was here back in the day
notice how there is this green plus
right next to the chart once you
selected this is the same options just
easier to use check it out
I'm gonna turn off the grid lines
they're gone they're off now over here
I'm going to turn off the legend it's
gone I'm going to turn back on the
legend oh it showed up to the right
that's because if you go over here to
the legend you have options you have
options for options go ahead and expose
yourself to that okay you want it to the
right you want it to the top to the left
to the bottom I'm gonna go ahead and do
it to the top like so
now
I don't ever leave gridlines on charts
because it makes it look messy but I do
leave legends we'll go ahead and put it
on the right there just expose yourself
to the fact that we now get to add in
the features of the charts known as
chart elements directly from the chart
previously we'd go up here to add them
but we can still do them that way it's
just quicker to do it from here pause
the video play around with the various
chart elements that you can add and you
can remove like for example you can
remove the axis there and it goes away
and you can maybe add in some data
labels and then you can format the data
labels and maybe see how they're gonna
show up inside the spreadsheet like so
and just go ahead and pause the video
and play around with all the chart
elements and come right back
alrighty hopefully your charts looking
nice and pretty now that you've played
around with the icon over here and
expose yourself to all the various
options and the options on top of the
options let's go ahead and see the
second icon this paintbrush here notice
how it says Styles here
kind of ironic how it says Styles right
there as well almost the same options
are populating here if you can notice
same options because they are either
just the chart Styles the various chart
Styles you can use it just makes sense
to put it right next to the chart if you
have the chart selected as opposed to
going up here in the design tab and
using it just various styles for the
chart that you can apply you don't have
time to build your own
now I'm just hovering over them this is
a live preview if I click it it will
accept it and I'm just hovering over
them so that's what the Stiles does
there now a cool feature is you can also
filter the chart so if you want to go
ahead and analyze let's say I want to
compare my first week of the month to my
fourth week but I want to ignore my
other two weeks just go ahead and click
on the funnel here and just deselect
those two weeks and if you want to dive
down even further and just compare one
salesperson at a time
you can also do that too by just
selecting a salesperson and applying
that and then you can compare their week
one to week four and as you can tell
they get a little slow towards the end
of the month compared to the beginning
of the month go ahead and expose
yourself to the filter apply some
various filters go ahead and reselect
everything and apply them back and just
play around with all the chart elements
and come right back
all righty hopefully you had some fun
playing around and modifying that chart
and filtering it and seeing the various
options you can use on the chart
directly from the chart using these
contextual features of the chart once
you have it selected now it does take
time to modify a chart and make it look
pretty
it's a great thing that Excel allows you
to save the modifications you've made to
a chart so that maybe one day you can
apply it on another data set you don't
have to sit there and remove the
gridlines and move the legend to the
right and all these other various
features what that's called is saving it
as a template so if you would like to
get this chart and save the look and
feel of it all you have to do is get the
chart and right-click it and notice how
the option to save as templates
available go ahead and do that would you
save as template now we're just saving a
chart template file here what this is
saving is all those little properties
that we modified not the data itself not
week 1 week 2 week 3 week 4 but just the
properties like no legend
no gridlines all these options one thing
I want to point out with chart templates
is you can change the location of where
you save the template but if you change
it this location here Microsoft
templates charts you won't be able to
use the template inside of the software
so it's a good idea to never change the
destination of where it's gonna save it
it's always gonna save it in Microsoft
templates charts you can save it to your
desktop but it won't let you use it
inside the software so I'm going to go
ahead and call this the chart template
and
changing the location I'm just gonna hit
save now that we have a chart template
in play I'm gonna go ahead and click on
charting - and you should - to expose
yourself to a completely different set
of data could have had more rows could
have had less so happens to have the
same four quarters had four weeks now
this time we want to use that template
to generate the chart so we don't have
to spend time modifying a chart once
it's built well if you hover over to
your insert tab you're gonna notice
there's no option for template really
but if you go to the recommended charts
option let's go ahead and click that
earlier we discussed that the all charts
a templates a chart the all charts tab
is right next to the recommended charts
tab let's go ahead and give that a click
to access the template that we just
saved you're just going to go to the
templates folder here and if you had not
saved it in that location it won't be
here I'm gonna go ahead and click on
templates and notice how it says the
chart template here you can go ahead and
select it and choose okay now if you
ever want to modify and delete your
search in templates if it gets too
hectic you could always go to manage
templates and it'll open up that folder
of where all your templates are stored
and you can delete them or move them or
rename them appropriately because you
can't rename them from here you can only
apply them so go ahead and apply that
template make sure it works hit OK and
expose yourself to see what pieces of it
stuck so notice how the week's tits now
quarters legends gone gridlines gone
same size is there the power of a
template go ahead and try to save
yourself one or two templates see if you
can apply them on to the next sheet on
charting too
all righty in the previous videos we
learned how to save a template and apply
it and this is the result this is the
template we built now sometimes you'll
have a chart built and you want to
change the data source for it or you
want to go ahead and maybe add in some
more data to it maybe you added in a new
drawn read to it and you want to go
ahead and add in some more data and you
don't want to sit there and rebuild the
chart afterwards you just want to adjust
the chart to include the extra data so
I'm gonna go over here and I'm gonna add
in another data point to my data set
here and you should to go ahead and put
your favorite type of music it can go
ahead and be any genre let's see we have
country jazz pop rock rap let's do
techno and I'm just gonna put some fake
data in there it's really quickly let's
just put a thousand I just grabbed that
little autofill handle and dragged
across in case you don't know it's in
previous videos in the other section so
we just have a new data set add it in
down below on row 10 so let's all hover
over to our design tab with the chart
selected and choose select data do you
include the new data that we just put in
notice how in our formula it goes to e 9
and the dancing ants goes to 9 you can
go over here and adjust it to 10 and all
you need to do is hit OK and just like
that you added in that extra value here
so now that we can add an extra data to
a pre-existing chart I want to point out
the fact that sometimes you're going to
want to view the chart a little
differently so really quickly let me go
back onto my chart elements and let's
turn on let's see here you have the axis
let's turn that on got country jazz pop
rock rap and techno there we go
sometimes what if I wanted to see these
country jazz pop rock as the bars and
not the quarters all
you have to do is once the charts built
and you realize it's not the way you
want to view it you want to view it
differently you're gonna go into the
design tab and you're just gonna switch
the rows and columns and that will give
you the result that you want and if you
ever want to switch it back go back to
switch row and columns so go ahead and
play around with select data once more
maybe add it in another data set in row
11 and add in that data to it and then
try seeing what switching rows and
columns will do and see the reflective
change and come right back
currently on the sparkline sheet in the
sample file provided for the class go
ahead and pause the video to grab that
if you've yet to cover it and come right
back
alrighty so you have a visual manager
and they love seeing charts but your
data sets too large to show a chart for
each salesperson now notice how many
salespeople I have in this data set at
thirty one thirty one rows so thirty
salespeople here adding in a chart for
each salesperson can be a little tedious
so in comes a sparkline a sparkline is a
chart that fits in a single cell and
acts as a background image to the cell
so if you wanted to you can still type
data in it you use this when you have
too many data points to show and you
still want to show it visually now to
use a sparkline you're gonna have to
insert it to insert it you're gonna have
to have the data selected so let's
altogether
select May through October by going over
to cell b2 holding the ctrl + Shift key
and the right arrow to highlight the
data set to the right until the data set
stopped and now that we have that let's
go ahead and insert our sparkline to go
to the insert tab I wanna expose you to
the sparklines command group where you
have three types of sparklines three
types of charts how we want you to see
it you have a line a column and a
win-loss we're gonna go ahead and do all
three so let's start out with the line
I'm gonna choose that option with the
data set selected and because I have the
data set selected I don't have to sit
there and grab it now and here it
already found it I just have to put my
cursor in here and pick where I want the
sparkline to go and typically you put it
right next to the data set because it
fits in one tiny little cell I wanted to
go here and you just hit ok now the
sparkline has its own tab you have a
cell address that's a sparkline you're
going to get the sparkline tools design
tab now these are the tools that you use
for the sparkline you can go ahead and
maybe change the sparkline to a various
different sparkline which we'll do in a
second to the two other ones but
sparklines not really showing the data
set it's just showing a line going
across what if we wanted to see the
highest value in the data set well
notice how in the design tab of the
sparkline you have a show command group
where you can show certain points I want
to show the high point notice how a red
dot appeared right there
now if you want to go ahead and maybe
your high point to me should be green
you want to change that high point to be
a green marker all you need to do is
hover over to the style of the sparkline
and go to the marker color and you
choose your high point to maybe be green
and it changes it to a green dot and
over here you can change it to a black
line to show the difference more so a
line lets you see different various
points of data here using a line chart
inside of a tiny little cell if you
wanted to see the low point you can turn
that on and then you can go over here to
marker color and you can make your
lowest point a red you can go ahead and
see that Jun was your lowest point for
this data set let's go ahead and expose
ourselves through the other various
types of spark lines and it's good
practice so do it with me go down to b3
hold ctrl and shift and hit the right
arrow to highlight the data set until it
stops to the right and go up to your
insert tab and let's insert a column
spark line and once more we're just
gonna select where the data set would
like to show up right over there and hit
OK and right now it's fairly useless
because all of them are all the same
color let's go ahead and show certain
characteristics of the data set using
the show command group like maybe the
highest point except that should be
green I'm gonna go ahead and change that
to green here high point green and then
turn on the lowest point that's red
already so that's fine it's my lowest
point seems like everyone's having a
really low point in June here it's
really easy to tell with a visual chart
kind of hard to tell with data alrighty
let's go ahead and expose yourselves to
the third type of spark line well let's
go ahead and highlight the data set
using from B for control shift to the
right go to the insert tab so we can
insert our very last spark line the
win/loss I'm gonna go ahead and put that
there and hit OK and how the win/loss
works it's gonna show a bottom bar if it
has a negative value
so if it has a negative value in a cell
so negative 100 just like that it shows
a bottom bar and again you can change
the color of this bar if you choose to
in the design for the marker color for
the negative points you can also show
the high point with a different color
maybe high point with green but the
point of the win/loss to show the
negative values really quickly go ahead
and expose yourself to the three types
of spark lines play around with them
once you find your favorite one realize
that your auto fill handle will also
work with the spark line for yourself so
you can just do one make it the way you
want it and drag your auto fill handle
down to fill on the rest
as you can see Excel is a great tool to
analyze data using the software but
sometimes you need to go with paper and
you need to start printing certain
pieces of the data to show certain
people during meetings or client
appointments and usually you want to
show the visual aspects of the data not
just the numbers so you want to print
out the chart I want to let you know if
you have the chart selected and you
hover over to file you can go ahead and
hover over to print and you can go ahead
and print the selected chart just like
that another way of printing the chart
instead of you going over to file and
choosing print you can just select the
chart hit ctrl and the letter P for
print and it'll go ahead and print the
chart on that page you can go ahead and
adjust the chart to be in portrait or
landscape adjust the margins just be
sure that your printer can print out
that far and when you're ready to go you
can go ahead and hit print and that's
how you go ahead and print a chart
we are
PivotTable sheet and the sample file
provided for the class if you've yet to
go over there go ahead and grab that
file and come right back
alrighty so a pivot table another data
analytic tool similar to a table it's
used to analyze data it's very powerful
it's one of the most powerful data
analytic tools known to man so let's say
you have a very large data set mine's
not too large but it's fairly large but
what if you had 60 columns I've seen
that before and he had thousands of rows
so I've seen larger and you want to find
out maybe what's your top selling
product or maybe was your top selling
salesperson what's your top selling
region notice he has sales here you want
to find out this information you can
build pivot tables to summarize these
data sets and build these reports for
you now you can build the pivot table
off of a list or a table I'm so happen
to use a list of data here now all you
have to do is you have to insert a pivot
table so with this data set notice how
it's a complete data set I have my
cursor within it I'm gonna go to the
insert tab and i'm gonna choose pivot
table and because I had my cursor inside
the data set I don't have to sit there
and find my data set here it already
found it for me and all I have to worry
about is whether or not I want to go
ahead and add this pivot table on a new
worksheet or an existing worksheet now
I'm gonna go ahead and choose a new
worksheet and hit OK and you should too
and now that we built our first pivot
table it still happens to be blank I
wanted to show you the contextual
features about a pivot table because you
need to have certain pieces on your
screen in order to build a pivot table
like you need to have the field list
over here you need to have the pivot
table tools analyzed and design tab but
these sometimes go away I want to point
out the fact of what triggers that go
away it's when your cell address goes
outside of the pivot table so just
expose yourself to that go ahead and
click outside the pivot table realize
you lost these two tabs
you lost it to the table field list and
then click back and everything's back
and we're in good shape
now that we did that really quickly you
cope with your cursor inside the pivot
table so obviously blank right now go
ahead and exit out the field list hit
that X there now this happens sometimes
by accident and you're gonna be clicking
back and forth and it's not gonna come
on that's because if you read the pivot
table it says you need to turn on the
pivot table field list to work with the
pivot table and you might be wondering
well where is it in Excel 2019 you don't
need to know you have a search bar up
here so you're just gonna go up here
make oh I'm looking for something called
a field Oh field list show it sure and
it's back but it is nice to know the
proper spot of where the field list is
so I'm gonna go ahead and show you if
you ever lose this portion of the screen
and you're trying to modify the pivot
table and build one like to see who's
your top-selling salesperson based off
the sales you'd have to go ahead and
bring back the field list but if you
don't have a search bar here what you do
is you put your cursor inside the pivot
table you click on the analyze tab and
inside the analyze tab you have a show
command group to where the field list
option lives and if you exit out it's
like you would have turned it off from
there you can turn it back on from here
you can exit out from there and you can
turn it back on from here go ahead and
expose yourself to that notice the pivot
table fields box notice how you have a
search over here and every single column
name is listed down below and you can
search for a column using that search
another thing I want to expose you to in
the pivot table fields list is you have
this little tool box here what this
allows you to do is check it out I have
all these columns month year type
salesperson I just want to show you that
this is actually
the same order month year type
salesperson that we have on our data
unless you know the data really well
this is going to be difficult to build
to the tables because what if you had 60
of these columns now let's go back to
the sheet that we built our pivot table
on and I want to expose you to what this
toolbox does you can go ahead and sort
the columns alphabetically here you can
go ahead and be easier to analyze the
data and build your pivot tables for
yourself go ahead and build the pivot
table expose yourself to the toolbox
trigger on a to Z use the search one or
two once or twice this notice what it
does it filters the box down below and
come right back and we'll build some
more pivot tables
Oh writing in the part 1 of this video
we went ahead and built a blank pivot
table and discuss the textual features
of what goes away when you click away
from the pivot table and what comes back
and what to do when we close out the
field list and how to organize and use
the field list if you need help on those
topics go ahead and refer back to the
part 1 of this video if not let's go
ahead and start building some pivot
tables now the first pivot table we're
just gonna go ahead and build one here
the pivot table that I want to build is
I want to see a breakdown of the sales
by salesperson on that spreadsheet so
what I'm going to do is I have four
boxes that I have the ability to throw
these columns into I can left click hold
and drag these boxes these columns into
one of these boxes here and depending on
what box I throw it in its gonna do
something different to the data now
let's go ahead and run through this
let's say I want to go ahead and build a
filter so I can filter further regions
and see a region breakdown I can get
this region column and put it in the
filters box and notice how an area up
here got built and I can filter my pivot
table once I build it based off a
certain region now that we have that
down you should go ahead and throw in
the region left click hold and drag and
throw it in the region box you could
have used the check mark but it what
it's gonna do is it's gonna predict
where you want it to go and as you can
see it predicted wrong you can
left-click from here and move it up but
I just like having a left click and
dragging it into the field I want it to
go in now we can go ahead and get the
salesperson and we can move them over
into rows here and what that does is it
builds me a pivot table with the rows of
each salesperson check it out I have
three salespeople
in that data set and I want to point out
the fact that try getting your
salesperson and filtering by them now
okay
outside the Rose it didn't let you do
that let's go ahead and put back
salesperson into rows and out the reason
why they don't let you throw in an
existing row into the filters box
because if it's a row you already have
the filter the filter is right here if
you want to filter for that row you can
have the sort and you have the filter
for it so it doesn't let you throw it in
the filter box if it's already in the
row box because of that icon right there
now that we have that down we want to
see a breakdown of the sales they bring
and we have a values box the values box
is going to run a function on the data
set we give it so the sales is a
numerical I'm just gonna drag it into
values and right over here I found the
sum of the sales if I want it to filter
by over here I can go ahead and filter
by the regions now everything's looking
good we can still make our pivot table
even better let's go ahead and sort this
pivot table so I'm gonna click on this
data set here and I know earlier in the
day we discussed a sort option over here
to sort but let's sort the pivot table
itself and not the column so I'm gonna
go ahead and right-click the pivot table
and sort from largest to smallest great
so I went ahead and sorted my pivot
table Watsons my bestseller here and
another thing we can do to the pivot
table is we can apply some number
formattings by right-clicking it
choosing number format and applying that
currency format there and choosing okay
alrighty now we have a pretty basic
pivot table it's showing us just the
sales people and the sales they bring
with the grand total down below let's go
ahead and go to our analyze tab of our
pivot table so click on the to the table
and go to analyze and one thing I want
to expose you to is this filter over
here it's it's useful but it's the old
way of filtering it be nice to go into
the analyze tab just click on it and
insert a slicer for the region instead
the thing we covered for tables in the
previous videos I'm gonna go ahead and
sir to slicer and I'm gonna build a
slicer for the region and I'm gonna hit
okay and just like that I have a now a
visual way to filter for my regions and
I can see what regions doing I can see
how each salesperson is doing for the
regions like so go ahead and build this
pivot table add in another slicer for
the salesperson play around with it sort
and add the currency format and if you
feeling courageous maybe see a pivot
table that shows you the revenue or the
sales by region instead
all righty part two the video we went
ahead and discussed adding in a slicer
as well as sorting the pivot table and
adding in the currency values from the
right click way as well as the fact that
your rows have a filter here and it
can't be thrown inside the filter box
there so let's go ahead and extend
further on a pivot table because it can
do a lot so what if we wanted to see
what these sales persons were selling
what types of products they were selling
you're allowed to get a pivot table and
get two columns and throw it in two rows
so I just got salesperson and now I
threw the type of what they're selling
so Bishop selling frozen yogurt ice
cream popsicle and tasty treats now I
can get this aspect of it and I can go
ahead and sort it from largest to
smallest
all righty I can see that ice creams
bishop stop selling and he's my top
seller and it so happens that Watson's
second is ice cream top selling product
is ice cream and he's my second top
seller
I can maybe tell my other sellers to
start selling more ice cream so we can
find out a lot of information on a pivot
table and you can put more than one
thing into the rows here like I just did
now that we have that down we've exposed
ourselves to the analyze tab of the
pivot table quite some time now let's go
ahead and hover over to the design tab
of the pivot table where I want to just
let you know you have several different
report layouts several different ways
you can view this report more
importantly just three we are currently
looking at in compact form how I
describe this is we have two rows but
one column in our pivot table
hence compact I'm gonna go ahead and
change that to outline form and this
shows you each row as a separate column
like so some people really don't like
these blank cells here they don't know
if this is Watson or is this Bishop for
those people they can go to report
layout and they can repeat the item
labels so they can see each item like so
makes it easier to analyze I'm going to
go ahead and turn off the report layout
item lay
by going to do not repeat and the last
layout is tabular which makes it look
like a spreadsheet but it's boxes around
everything you still have the pivot
table functions makes it look nice like
a spreadsheet and of course you have the
ability to remove subtotals these are
subtotals and as well as grand totals
you can even show them above or below
bottom of the group top of the group so
you can also go ahead and turn off the
grand total like so or you can go ahead
and turn on the grand total like so
various settings that you can modify in
the design tab of the pivot table go
ahead and expose yourself to the design
tab play around with it maybe change the
color scheme of it see the different
report layouts and then come right back
we discuss some more features of a pivot
table
starting to notice
a PivotTable is a huge function in
Microsoft Excel now sometimes when
you're building a pivot table you're
gonna have to see the same pivot table
every month on different data you're
gonna add more data to it or maybe
you're gonna change the data and you
want to see the pivot table but you
already have it built now you don't have
to sit there and build another pivot
table just because you updated the data
let's go ahead and just try this out
let's go to the pivot table sheet here
this is the sheet that we built the data
for this pivot table this is known as
the source data sheet source data in the
source data sheet on the very first sale
let's go ahead and make that into
something really large let's just do a
million
all righty so he had a big old sail for
his first sail right over there made a
million dollars now in the north region
let's all go back to our pivot table and
let's notice well let's first clear the
filter here and let's notice how we
don't see a million dollars anywhere for
Bishop it was Bishop that had the sails
that's because you have to refresh the
pivot table you have to let Excel know
that you want to update this pivot table
on the newly added or the newly modified
data several ways to refresh let's go
ahead and run through those everything's
always a right-click away so if you
right click from your pivot table you
will see the option to refresh if you're
in your pivot table when you go to your
analyze tab you're gonna also see the
option to refresh if you hover over it
you're gonna see that alt f5 is the
keyboard shortcut to refresh and if all
else fails and you're forgetting all
these ways just know that in your data
tab you have an option to refresh as
well now that we expose ourselves to the
various places that we can refresh I'm
just going to right click and choose to
refresh this pivot table and just like
that you saw the updated change so now
that we have a refresh and we saw what
it did let's go back to the source data
let's go back to the pivot table sheet
and this time let's go all the way to
the bottom now
I'm just gonna duplicate this row and
you should too so select the row by
clicking on the number going to your
Home tab and just copy that row and then
going down to the next row here and
hitting Enter
now for this one I'm just gonna go ahead
and add in a new salesperson we'll just
say my name will put faz it'll be the
North Region except my sales we're gonna
make it something large again we'll put
it a million all right I don't think
that's a million nope that's ten million
and doing even better so I just added in
a new salesperson and I add it in a
whole new row let's go back to our pivot
table and refresh it so we just go to
pivot table right click and refresh and
we don't see a fast sales person that's
interesting let's go ahead and dig down
on this and see what's going on let's go
analyze this by going to our analyze tab
so next to the refresh which is one way
of updating your pivot table there's
change datasource
so I'm gonna go ahead and select that to
show you that the source of the pivot
table the source that it refreshes from
stops at 168 because that's what we
built the pivot table off of so that
newly added row I'm going to scroll all
the way to the bottom here is not
included into the refresh every time we
hit refresh all we need to do is adjust
that to change that to a nine I could
have dragged it across and then I hit OK
and then it automatically put in the
refresh it put me on top the list is
sorted so it automatically sorted me up
to the top added in my salesperson and
put in my total right down below go
ahead and play around with that and
check to see if you can go ahead and add
in another value to the bottom of the
list and update the source data and
refresh it to reflect that change
then the tables are a great way to
summarize large data sets
now some people are just visual people
and they're gonna request a chart you
are allowed to make a pivot chart and if
you have a pivot table you're in luck
because you can build the chart based
off a pivot table now let's go ahead and
make a very simple pivot table again
let's just remove the type breakdown
from this sheet so just drag it out and
throw it out just have a very basic
pivot table to start out with and if you
want to build a pivot chart off this
pivot table we have shortcuts to
generate charts all you need to do is
hit the Alt key and you hit f1 and you
generated a pivot chart for yourself
just like so and go ahead and select the
pivot chart you have three tabs that
come with it analyze design and format
and it's basically the visual version of
your pivot table notice how we have an
axis now it's salesperson you have the
filters it's region and you have the sum
of sales right over here and it just
shows your pivot table visually the same
charting rules apply you have the plus
and the paintbrush can't filter it
because it's a pivot table you can
filter it through the pivot table but
pivot charts are very simple to build
based off a pivot table that's already
existent if you know the shortcuts all
you need to do is set all to f1 to
generate it on the same sheet if you
still happen to want it on a separate
sheet you're just going to go ahead and
hit the f11 key and this will generate
the pivot chart on a separate sheet to
the left hand side of the pivot table
sheet so go ahead and expose yourself to
a pivot table and the pivot chart
version of it realize that if you move
something outside of the pivot table
it's going to move it outside the chart
they're all connected if you filter
something from the pivot table it's
going to filter something for the chart
it's pretty cool right
currently on the data validation sheet
of the sample file provided for the
class yet to grab that go ahead and grab
it and go to data validation and come
right back
alrighty so sometimes you build a
spreadsheet you want to send it out to
your colleagues or maybe a client except
that this spreadsheet goes into a
database or for some reason you need to
have everything spelled correctly like
the sales rep names that they're gonna
fill out they need to have the date in a
certain format that they need to put in
and they need to have a certain range of
dates that they want to put in or maybe
the credit limit can only be a certain
value otherwise it won't be accepted now
you can set these certain rules to apply
on certain cells to validate the data
that goes within them it's known as data
validation let's go ahead and run
through the various ways that you can
validate data on a spreadsheet so the
first one is let's say you have some
sales reps and you have a list of sales
reps here and everyone keeps into
spelling it maybe one person is putting
Jack T the next one is gonna go ahead
and put Jack T and your database that's
supposed to be a J and your database
keeps having a hard time finding Jack
because there's so many different
versions of them and it's hard to
summarize and analyze data that way
hence comes in data validation
let's all go ahead and highlight the
data set that we want to validate
let's all go ahead and highlight the
cells that we'd like to validate so
these are the cells I only want these
values to go within it I need to tell
Excel to do that so I highlight the
cells then I go to my data tab here and
over here I have an option for data
validation in my data tab it's in the
data tools I'm gonna go ahead and give
that a click and for data validation
here you have some settings an input
message and an error alert these two are
optional meaning if you don't fill them
in they have default ones that we be
filled in for you let's go ahead and run
through this so I want to build a list
of values I want to have a list of
people they can fill in so I'm gonna
pick a list and I want to find out what
values I want to use for that list
that's called the source
so these are these are the values I want
to use for the list that I want to build
for data validation so I'm just gonna
put my cursor in here I can collapse
this window by hitting that little arrow
to see more of my spreadsheet drag down
on the range of cells that I want to use
as a list hover up and collapse or
expand the window this time and I filled
in the settings let's all go to input
message I'm gonna go ahead and put title
this is input message just very generic
so we can see how it's going to look
I'm gonna do an error alert style stop
you can do warning information I'm just
going to stop I'm gonna do error alert
and I'm gonna put error message just so
we can see how that's gonna look and
once we fill in these three settings
vote settings
input message and error alert we can hit
OK now every time you go ahead and hover
over one of these cells you're gonna get
the input message you go away from it it
goes away the input message it's gonna
be moved looks like it can be just left
click hold it and drag it and it moves
it away
notice how an arrow appeared on this
cell but it didn't appear on this cell
cuz these are the ones with data
validation if you try typing into the
cell like hello and you hit enter you
will get the error message like so now
I'm gonna go edit cancel here and each
one of these cells now have a drop-down
list where a user can go ahead and
select and if they don't have these
matching criterias of the drop-down list
it will no longer let them put it into
that cell so for yourself go ahead and
expose yourself to the various other
data validation rules that you have and
just realize that instead of doing a
list of data you can have a range of
numbers by just highlighting the data
set and going to the data tab and
choosing data validation from here and
this time maybe choosing decimal numbers
and doing a minute a max so either gonna
be 0 or 1 between 0 or 1 nothing else
and remember earlier I said these are
optional and you can just hit OK so if
you put in 10 here it doesn't match but
if you put in 1 it accepts go ahead and
practice doing a data validation for a
range of dates by highlighting the date
column running up to your data tab and
exposing yourself to data validation
using dates
all right we are currently on the
conditional format sheet and the sample
file provided go ahead and go to that
sheet and come right back if you're not
there yet
now you'll be analyzing data and
sometimes it would be helpful to point
out certain traits of the data to
conditionally format certain pieces like
for example highlight all of the town's
called Fayetteville or maybe highlight
all of the square feets that are above
2000 and green because I'm ok with those
and then over here maybe show me all the
bedrooms above 3 because if it's above 3
I'm ok but below 3 I'm not what you're
asking to do is apply conditional
formats let's go ahead and run through
various conditional formats so notice
how in your home tab you have a bunch of
Styles one of the styling methods it's
conditionally formatting something let's
say let's go I'll select column B and
you want to see all of the houses that
are above a hundred thousand and you
want to just be highlighted nice and
green because you're gonna be ok with
that
just all go to select column B and go to
the Home tab and go to conditional
formatting and if you want we have
several ways to do conditional
formatting we can highlight cell rules
we can do something greater than a
certain value less than a certain value
between a range of values will trigger
the conditional format equal to
something text that contains all these
various options we're gonna do greater
than alrighty now I'm just gonna do
greater than a hundred thousand and for
me if it's greater than 100 thousand I'm
gonna go and make it a green fill with
dark green text and hit OK let's try
another example of a conditional format
let's all click on column C here and
this time I want to highlight a search
in town you can go ahead and pick your
own I'm just gonna pick the first one I
want to highlight every town with that
name and I want to make it red so I'm
just gonna go to select column C hover
up to home go to conditional format and
this time I'm gonna do highlight cell
rules here and I'm gonna do greater than
less than between
equal to seems right I say what I want
it to be equal to to trigger that
conditional format just like that I'm
gonna make it red and hit ok now for
square feet here I'm going to select
column D and go to conditional format
and for square feet I want to expose you
to color scales you can go ahead and
maybe make like a rubric from green to
red to see how close it is or how far it
is compared to the average of the
numbers so it's between a set of ranges
so the greenest is the smallest and the
red is the largest and square feet
pretty nifty go ahead and play around
with the other conditional formats in
these two columns and when you come back
we can go ahead and show you some other
conditional formats that allow you to
build a formula within the conditional
format
all right I'm currently on the custom
conditional format sheet in the sample
file provided for you for this class go
ahead and go to that sometimes you would
want a conditional format and you're
going through the list and you realize
the conditional format that you want is
not there for example what if we wanted
a conditional format that highlights the
entire row if the units is less than 5
now there's no conditional format to do
that and so you have to build a custom
conditional format let's go ahead and do
it together let's highlight the data set
that we want to do a conditional format
on and let's go to conditional
formatting none of these rules are going
to work so we're gonna go over here to
new rule we have several types of
conditional formats that we can build
let's go ahead and select use a formula
determine which cells to format I'm
gonna go ahead and select that and now
we have an ability to build a formula to
do a conditional format for us so if I
want to select an entire row and I want
it to highlight at a certain color if
the units is less than 5 all I need to
do is go equal to and this conditional
format starts in Row 2 and I want it to
run through column B but I wanted to
highlight the entire row so what I'm
going to do is I'm going to absolutely
reference column B there with the dollar
sign and I'm not gonna absolutely
reference I'm gonna use relative
referencing for Row 2 for it to iterate
down each row and once it does that if
it's less than 5 I want to format it
let's click on the format here do red
and hit OK I don't want to do the fill
let's change that to no color here let's
do the font color let's change the font
color just want the font over to red
letting us know that we're low in unit
stock equal to absolute reference b2
less than 5 let's hit OK and only one
value in there is less than 5 and now I
know which one to order what stocked
order now if you ever need to change
this conditional format what if it had
to be less than or equal to 5 all you
have to do is go back to conditional
formatting and go to manage rules and
under manage rules you have the ability
to edit the rule and I'm
less than or equal to five and choose
okay
and I'm gonna hit okay this time and as
you can see the conditional format
applied and updated itself to the data
set like so go ahead and give that a try
try out another type of conditional
format maybe a different formula in
there to see the results that you want
I'm currently in the linking data sheet
that's in the sample file provide it to
you go ahead and go to that sheet all
right so in Microsoft Excel
you have spreadsheets inside of a
workbook and you can actually link a
cell to a cell from one sheet to another
sheet or from the same sheet or even
from workbook to workbook let's go ahead
and start out with the simplest of them
all linking one sales value to another
cell I want to put a one's value inside
of this cell here I have to use the
formula to do that so let's all go ahead
and click on g7 here and you have to put
equal for a formula and just select the
cell you want it to be equal to and you
hit enter and went ahead and link those
two cells if you change the value from
this cell
and you hit enter it updates it to that
cell so there's connected at all times
now another example is what if you
wanted to link a value from one
worksheet maybe I have another sheet
here to here how I'm gonna do that is
let me just show you the other worksheet
I'm gonna go ahead and go to the next
worksheet link from here on the
right-hand side of it and what if I
wanted to link this very last value
11:30 to that cell all you have to do is
build a formula for it let's go ahead
and do it together
I'm gonna put my equal sign in to let it
know I want a formula and this time it's
not on this sheet so I need to click on
the sheet name and then it filled in the
formula the sheet name with an
exclamation point and then I get to
select the cell and a lot of people they
want to click on the sheet here to take
them back to the sheet and then they
want to hit enter well if you do that
you will replace link from here with
linking data and it won't pull the
correct cell so all you have to do from
this point once you have the cell that
you want to link is just hit enter and
let the software take you back to the
original sheet with the cell linked like
so alrighty pretty nifty now the last
one the last method is linking from
another workbook if you just go ahead
and hold ctrl in the letter n it will
open up a brand new workbook notice how
book 1 my old ones right behind it and
I'm gonna move this one to the side now
I'm gonna go ahead and move this one to
the side that's just a Windows 10
feature known as windows snap but not
the lesson here now what if I want to
get this cells value value and I want to
link it to this book here that's just
another formula and the most simplest
way to do it is putting in an equal sign
and then opening
that other book and selecting the cell
the syntax we'll call it to link another
workbook to an existing workbook is
putting the book name in brackets and
then the sheet name and then an
exclamation point with the cell address
you're referring to and when you go
ahead and do that hit enter a link in
the value just be sure to keep it in the
same local spot once the values are
linked otherwise we'll have a hard time
updating itself later on now when you go
ahead and close this book so notice how
it says book 1 sheet 1 a 1 I'm gonna go
ahead and get this book over here and
save it and I'm just gonna go ahead and
browse I'm going to save it as book 1
onto my desktop
that's fine hit save now that I have
book 1 in there I'm gonna close it and
I'm gonna make this back to full screen
I just want to show you the change in
the formula now because I have the book
closed it needs the location of it and
the book name with the file type and the
sheet name with an exclamation point and
then the cell address so you need to
have the direct path for it to be linked
go ahead and try those out expose
yourself to the different methods of
linking workbooks and start thinking
what workbooks can you link to benefit
you
all right I'm currently on the
protection sheet in the sample file
provided go ahead and go to that once we
were on the protection sheet
let's all go ahead and hover over to our
review tab and give that a click now a
great tool that's very underutilized in
Excel are comments you can comment on
certain cells to leave a little remark
or a little pointer about it so that
when you send the file to someone they
can go ahead and view the comments now
you're gonna notice that one of these
cells has a little tiny red triangle in
the corner of it right there now if you
hover over that cell that is a comment
and the reason why I asked you to go
into your review tab is this is how you
add in comments let's go ahead and put
the cell next to it let's go ahead and
select that cell and let's add in a new
comment comments are super useful when
collaborating when sharing files so that
way you can talk about things without
actually emailing back and forth
now you can go ahead and put the comment
there and they can hover over it and
they can see the comment when they hover
over it
or you can go into your review tab and
turn on show all comments and that
allows the comment to remain on the
screen at all times
now these comments if you click on them
notice how you can adjust the size you
can make them bigger and smaller if you
go to the border of them you can move
them around and arrow points the cell
that they're referring to so use these
comments to communicate with one another
amongst the worksheet and if you ever
need to remove a comment like so the one
I just added you can go ahead and select
it and choose to delete it or if you
ever need to edit a comment you can go
ahead and select the comment and choose
to edit the comment now once a workbook
has a lot of comments in it a great tool
to navigate through the workbook and
make sure you don't miss any comments is
by using the next and previous to
navigate through all the comments on the
workbook and then you can go ahead and
answer each remark go ahead and play
around with comments try adding one by
yourself turning on show all comments
and exposing yourself to the various
settings
I'm currently on the protection sheet
provided in the sample file given in
this course let's go ahead and go to
that sheet and I'm gonna zoom in a
little bit fast will always protect you
unless you unlock your cells those fats
won't protect
all righty
and over here we have a comment let's
unlock these cells the yellow cells then
protect this sheet like a knight in
shining armor so in Excel you can have
certain cells be protected meaning a
user can't type into them while other
ones can be typed into so in this
example what I want to do is I want to
have these cells be able to be filled in
meaning a user can go ahead and put
value into these cells but if they were
gonna click anywhere else they can't
it's locked in order to do that it's a
two-step process you first must unlock
the cells and then protect the sheet
keep in mind you can do the whole
workbook but we're just gonna do the
sheet now let's talk about unlocking
cells let's highlight this area over
here and let's right-click it and expose
ourselves to the fact that you have an
option to format these cells I'm gonna
give that a click and you should too so
just highlight this chunk of cells right
click go to format cells we've used this
window a lot typically for number
alignment font and border but very
rarely for protection let's go ahead and
give that a click and notice how these
cells are locked every cell on every
spreadsheet by default is locked let's
unlock these ones and then hit OK so
that's step one unlocking the cells now
in your review tab you have the ability
to protect the sheet and the review tab
or you can also right-click the sheet
and you're going to see the option to
protect sheet is right there and they're
the same thing I'm gonna go ahead and
choose the right-click from the sheet
name and I'm gonna choose to protect the
sheet I can password protect the sheet
keep in mind there's no help desk you
can call if you forget the password I
can also allow the users of this
worksheet to only select the unlock
cells so the lock cells everything they
can't touch just the cells that I let
them touch they can touch and then I can
hit OK but before we hit OK just be
aware that there's a bunch of settings
that you can control on a workbook
like whether or not person gonna insert
hyperlinks delete columns delete rows if
they can sort they can edit objects all
these different various settings that
you can control and once you hit okay
you're gonna notice how up here in the
review tab the option became unprotect
and over here in protection sheet when
you do a right-click you're gonna notice
the option to unprotect sheet now you're
gonna go ahead and just try to click on
any cell and it's not working for me
scroll down a little bit try clicking on
any cell it's not working can I move the
comment nope it's stuck but let me go
into these yellow cells the ones that I
unlocked and put the number one end
great two three oh it went right to the
next cell that i unlock six so as you
can see protection is pretty useful so
you can have certain fields to be filled
in in certain fields they can't kind of
building a user form for someone go
ahead and play around a protection play
around the various other settings and
keep in mind if you forget your password
and you so happen to use one you cannot
go back into that file until you figure
out that password there's no help desk
you can call
thanks for watching don't forget we also
offer live classes and office
applications professional development
and private training visit learning comm
for more details please remember to like
and subscribe and let us know your
thoughts in the comments thank you for
choosing learn it
[Music]
you
[Music]