hello hello my name is fast cream with
learn it and this is excel at reduction
using Excel 2019 I bet you all are
wondering what does this course have to
offer this is an excel introduction
course so we're gonna go ahead and cover
the basics of Excel by covering the user
interface and once we feel comfortable
with that we're gonna go ahead and dive
into use of excels formulas and pre
built-in functions and once we nail
those concepts down and we fully
understand them we're gonna go ahead and
dive into how we can format our
spreadsheet design as well as set it up
so that we're able to print our
spreadsheets now this course is designed
to go ahead and be an interactive course
so we're gonna go ahead and hear me say
pause the video or do exactly what I
just did and come right back throughout
the course so that way you can just get
a hands-on feel of what Excel 2019 has
to offer I'm looking forward to teaching
these concepts with you guys so stay
tuned and get ready to learn
so I just went ahead and started Excel
2019 when you start the office product
since 2013 and up you get taken to what
we call the Start screen the Start
screen it's pretty beneficial for the
user have some uses let's go ahead and
cover those uses the first thing I want
to point out in the Start screen is the
fact that it has a tab structure on the
left hand side over here we have a Home
tab a new tab and an open tab let's go
ahead and start talking about this Home
tab which you're currently on I want to
point out the fact that the Start screen
is a great area to find some built-in
templates so what's a template Microsoft
loves using templates these little tiles
are templates and they're basically pre
formatted workbooks that you can use so
that you don't have to start from
scratch if you have a very generic task
I want to point out the fact that
Microsoft has quite a few Britian
templates you can access them all by
going over to more templates I'm gonna
go ahead and do that now and you should
too great so when I clicked on more
templates it just went ahead and took me
to the new tab the new tab is where all
the built-in templates live these tiles
are the built-in templates notice how
you have a template for it back to
school planner just a formatted look for
a back-to-school planner that you can
fill in you also have one for a calendar
now I want to point out there's just a
scroll bar over to the right-hand side
that you can just scroll through to
browse through the built-in templates
like a to-do list for projects or maybe
an expense calculator here and if you're
not finding something you're looking for
I want to point out you're not finding
what you're looking for you just go up
to the search box up top in the Start
menu and notice how there's suggested
searches down below I'm gonna maybe find
a business template by clicking on the
word business and filling it into the
search bar off the top and now the Start
screen populated a bunch of business
templates that I found online for me
using Microsoft search engine known as
Bing now you can go ahead and browse
through this list of templates to find
that look and feel for that workbook
that you're trying to create now if you
can't find what you're looking for here
just realize that there is a bunch of
other categories that you can browse
through to get more specific
exactly what you need so templates are a
great tool that you can use inside the
software to save time on tasks that are
very generic
besides the Start screen allowing you to
access the online in pre built-in
templates it also allows you to access
some pre-existing files on your computer
or your cloud service now I'm going to
go ahead and go back to the Home tab
here once more by clicking on it and I
want to point out the bottom right below
the templates is an area where you get
to set up some workbooks for yourself
that you want to quickly access this
section has three tabs we have a recent
pinned and shared with me so how the
recent tab works is if you have a file
that you've recently opened I will go
ahead and populate down below here with
its name and you can just hover over it
and this little pin icon will appear
like a little thumbtack and you can
click it and what you're asking it to do
is move it over to this tab over here
called pinned
now the pinned section will remain in
the software until you decide to unpin
that file and it will go ahead and stay
internally on the Start screen so you
don't have to sit there and look for
your file when it's time to modify it or
update the information on it I do want
to let you know that that final tab over
here shared with me are simply a
consolidated list of all the files that
are currently being shared with you and
you can access them from here if it so
happens to be a workbook so so far we've
covered the Start screen has a great use
for these templates that save us time
for generic tasks and if you want you
can customize the home of the Start
screen to put some commonly used files
in the pin section as well as to see
what's currently being shared with you
so in the Start screen you have a Home
tab and a new tab that do all that what
does open do because we've yet to even
go there I'm gonna go ahead and click on
open
and you should too so we can go ahead
and browse to see what that has in store
for us go click on that open the open
section of the Start menu is just an
area that allows you to access some
recently used workbooks under the recent
section you can also hover over these
workbooks if there are some in this list
you can also pin them as well to the top
part of the list to always stay there
for us another thing that this open
section does it connects us to our cloud
service like onedrive Microsoft's cloud
service that we use for office products
and if you're not using a cloud service
and everything stored locally you can go
ahead and select browse to open up a
dialog box called open to browse for the
files on your computer Start screen has
great uses I'm gonna go ahead and go
back to home and up next we're gonna go
ahead and discover the user interface by
clicking on a blank workbook template
oh we know what the Start screen does
let's all go ahead and select a blank
workbook so we can go ahead and cover
the user interface gonna go ahead and
click on blank workbook great so the
first thing I want to point out to
everybody is since 2007 Microsoft has
adapted what they call the ribbon
interface I'm gonna go ahead and draw a
box around what they call the ribbon go
ahead and draw that box there here we go
that's our ribbon now your ribbon
actually holds something what we call as
tabs you have a Home tab insert draw and
page layout now these tabs that live
inside of the ribbon they actually have
a third structure inside of them they're
called command groups so over here this
is a clipboard command group here's the
font command group and over here we have
the alignment command group for us I
want to point out the fact that some of
these command groups over in the corner
of them the right-hand corner there's
this little box out arrow in the corner
that's simply letting you know that
there are more options in the alignment
command group if you click here and if
you click here there are more options in
the font command group if you were to
click here you're gonna notice over here
styles it does not have one because
there's not that many options for them
so they're all live over here inside of
the command group itself so if you're
not finding what you're looking for
check under more options now this ribbon
over here the top part of your screen
that holds your tabs and has your
command groups we use it to use the
tools of the software you can click on
each one to see the command groups below
the home holds like the most commonly
used tools the insert holds all the
tools that allow you to insert a certian
thing inside the spreadsheet like maybe
a pivot table or maybe a shape or a
picture so go to the insert tab to
insert something I'm the draw tab we had
a touchscreen of course it would be
perfect you sit there and draw through
the spreadsheet and annotate it and
whatnot page Layout tab to customize how
it's gonna look each of these tabs names
help describe the command groups below
now these tabs over here allow us to use
the software by just going ahead and
clicking on them or you can use the
center scroll of your mouse to navigate
through these tabs to quickly browse
through them now that we understand that
the office products have a ribbon that
holds tabs and have command groups I
want to let you know that this whole top
part of your screen the whole ribbon can
sometimes disappear from you
the user accidently will hover over to
this box out arrow which happens to be
the ribbon display options that piece of
screener is just talking about and
without realizing it most of the time
you go to auto hide the ribbon and what
that does is it hides that ribbon the
top part of the screen I want to let you
know if that ever does happen to you all
you need to do is just go back up to the
ribbon display options and choose to
show the tabs the names of the tabs as
well as the command groups below them so
we see the tools that it has to offer in
each of those tabs like so now that we
have that down I want to let you know
that the ribbon is pretty customizable
so down below each of these tabs you
have tools that you use for the
spreadsheet there are certain tools that
you use a lot more than other tools like
for example in your world you can be
using the increased font size and
decrease font size icons a lot but you
still happen to also be using in the
pivot table or in the insert tab you
also be having to use the pivot table
icon so what you're doing with the
software is you're spending time going
to home and increasing and decreasing
font and then you're going back into
insert to insert pivot table you can
customize your ribbon so that you can
have a certain icon on top of the ribbon
at all times
this area that you can customize
actually has a special name I'm gonna go
ahead and draw a box around it right
over here it's called the quick access
toolbar so the word quick is in there
the word toolbar is in there the word
access is in there
no I'm not talking about the database
software so the quick access toolbar is
simply just a toolbar that you can
access for your convenience and it's
fully customizable and currently my
quick access toolbar has a Save icon
there it has an undo icon and a redo and
also a touch Mouse mode if I had a
surface laptop or a touchscreen laptop I
can go ahead and toggle into touch mode
it kind of just spaces things out for me
so I can touch it with my fingers you
can customize this quick access toolbar
by simply going to a tool like let's say
increase font size and instead of left
clicking it to use it you can just go
ahead and do a right-click and the very
first option will be to add it
to the quick access toolbar I'm gonna go
ahead and add the increase font size to
the quick access toolbar I'm gonna do
the same thing and you should to do both
the increase and decrease font size
button and add those to the quick access
toolbar so I'm just going to do it right
click it add to the quick access toolbar
and now they'll go ahead and remain on
our toolbar for our use until we decide
to remove them now this is not gonna be
associated to this workbook this is to
the software on our computer meaning if
I send this file to someone they won't
have my quick access toolbar
that's just my application level setting
I want to let you know that it feels
this easy to customize your toolbar just
by right-clicking a tool and just
right-clicking and adding it it must be
that easy to remove it you can
right-click and you can remove it from
the quick access toolbar like so so the
quick access toolbar is a great
customizable area I do want to let you
know this tip if you hold the Alt key
for a few seconds on your keyboard the
Alt key alt you'll see a bunch of
letters and numbers appear around
everything I call this the alternate way
of navigating the software because you
use the Alt key to use it I want to
point out that your quick access toolbar
puts a number on each of the icons so if
you need to use like one of these icons
and you can remember these shortcuts you
can hold the Alt key and hit 6 and
increase the font size for a word or
hold the Alt key and hit 7 you know
decrease the font size you can navigate
through anything anyway using this
method so if you want to go to the
insert tab you hit the letter n if you
want it to go to pivot table you hit the
letter V and it takes you there that's
the structure of the ribbon interface
now that we have the structure of the
office products understood with the
ribbon in the quick access toolbar I
want to start talking about one of these
special tabs it's actually the file tab
let's all go ahead and click on the file
tab to go here file oh right the file
tab actually has a special name it's one
of the only tabs that you click and it
doesn't show you a command group down
below it takes you to what they call the
backstage view in the backstage view of
Excel 2019 you can actually access the
Start screen tabs home new and open but
in all other versions of the backstage
view they've always had these set of
tabs down below so up top this is new to
2019 here and down below here we have
these tabs in our backstage view let's
start talking about them now the info
tab is where you go to find information
about your workbook like the size the
author the last time you'll modified as
well as protect your workbook or maybe
check if your workbook is using new
features that another older version of
the software does it have by going check
for issues and checking compatibility
besides using the info tab to check for
issues and protect your workbook and
gather some information about the
workbook it's also used to save the
workbook notice how there's a save and a
save as now the very first save of a
workbook is always going to be a save as
a save as simply gives it a file name as
well as a location on your computer that
you can save it to once you establish
these two points from that point forward
every save that you do will go ahead and
just be a save it's gonna update the
information within the file in the same
location so the backstage view does a
lot for us if you think about it it
gives us the entire start screen as well
as information on the file and the
ability to save it besides that you can
also print your file and we'll go
through the various printing options
later and even share your file lives on
a cloud service and if you still happen
to want to export it to a PDF so that
way anyone can open it on any software
or any
you can go ahead and do that too or
publish it to power bi if you're a power
bi user and from the backstage view if
you need to you can also close the
application or maybe modify and check
out your account settings here as well
as modify the application level settings
like increasing the font size on the
spreadsheet by default or maybe changing
the font entirely by default or maybe
changing the theme of the office product
itself so that way it's a little easier
on the eyes this will all be done in the
options in the backstage view
right now that we took a little tour of
the backstage let's go heading over to
the front of the stage I mean the
spreadsheet by clicking the little back
arrow here give that a click great now
Microsoft has been adding a lot of
functionality to their tools and I just
want to show you a few that might be
beneficial for us now one thing I want
to point out is since 2016 if you were
looking for a tool in this ribbon
interface and you keep forgetting where
it is like for example let's say I keep
forgetting that the table icon lives in
the insert tab and I use this pretty
commonly but I don't want to go ahead
and spend time looking for it every time
I want to use it and I don't want to
waste the space on my quick access
toolbar for this icon that I maybe use a
few times a day I want to let you know
the ribbon has the search bar right over
here it's called tell me what you want
to do this search bar is not connected
to the internet kind of will explain but
it's connected to the software and what
it does is it helps you find the tool
that you're looking for so if I don't
know where table is instead of searching
for it I just type in table because I
know the name of it it pops up the
option to use table from here and
earlier I said it doesn't necessarily
have online integration but it does
check it out if you need to get help in
this search bar on the tool table you
can go down to the help option of it and
find some videos or some articles on
that specific feature and even also down
here it gave a definition of a table and
they're actually talking about a piece
of furniture
it's powered by being Microsoft search
engine and then you even have an option
to do a smart look-up on it from the
search now you might be wondering what
is smart lookup what does that mean okay
you're gonna look up table what does it
mean to be smart about it so smart
lookup you learn more about the text you
select by seeing definitions images and
other results from various online
sources oh so you have this great tool
that you get to use on your spreadsheet
so if you have a certain word or a
certain company name you just want to go
ahead and get a web search on that you
can go ahead and highlight the word and
give it a right click and choose two
picks
look up another way of doing the exact
same thing would have just been going to
the review tab and choosing to add smart
lookup what this does I'm gonna go ahead
and click it here is it opens up an
online integration and it does a web
search what the word learn it opens up
some Wikipedia links if you browse
through here if you browse through here
for a bit it opens up some web searches
as well I want to let you know this is
powered by Microsoft search engine known
as being and it just saves us time
instead of opening a browser really
quickly we just do it from here
I'm gonna go ahead and close out the
smart lookup window because I'm done
with it so now that we have that tell me
what you want to do bar we have this
great way to find the tools and if we
ever need help on it we can go ahead and
just be smart and use the smart lookup
feature to either find out how to use
the tool inside the tell me what you
want to do or just to maybe you get a
definition of a certain word or a
sentence on the spreadsheet
now that we know about some useful
features I can save us time searching
for tools as well as getting some help
on some tools or maybe looking up some
words on our spreadsheet let's start
talking about the interface an Excel
2019 first thing I want to point out is
this green box over here I'm gonna go
ahead and click on a one here now how I
knew that a one where I was was I just
went ahead and looked at top where I can
list out my column names there each
signified by a letter in the alphabet
and then on the left hand side over here
we have our rows listed with numerical
values starting with one I want to point
out the fact that in the Excel interface
that you can know what cell you're on by
looking up at the name box over here it
gives you the cell address of the green
border now next to the name box is what
we call the formula bar now instead of
making quick edits or rather large
sentences that you need a type inside of
a tiny cell just know that this green
box is connected to whatever you type
inside of the formula bar so if you were
to go ahead and type in the formula bar
let's say fast cream and you were to hit
enter it would put it inside the cell
that you're currently on which is the
active cell which you could refer to by
looking up at the name box now that we
have that down I want to start talking
about more of the user interface besides
the name box and the formula bar down
below on the bottom of the software is
where we have all of our sheets notice
that we currently have one sheet in our
workbook if you so happen to want to add
extra sheets you would just click this
little plus sign down here you know it
added an extra sheet for us
so one thing I want to point out to you
is these scroll bars on the bottom it
can go ahead and become larger and
smaller
notice how when it becomes larger the
two arrows over here one of them become
green meaning you can click it that
allows you to just see the next sheet
name in the book here so see the next
sheet name if you click this one
here let's see the very first sheet so
if you had let's say I'm gonna mimic
having a ton of sheets in this workbook
and there we go
I just put like ten sheets I'm gonna
cover some of the names so I can't see
all of them if you would like to go to
the very last sheet you would hold ctrl
the ctrl key on your keyboard and then
click this arrow it shows you the last
sheet name if we were to go over here
and hold ctrl and click this arrow and
show you the first sheet name use this
method to navigate between a workbook
with many sheets now besides using that
navigation trick if you go over here in
this general direction or this area
we'll call it and just do a right-click
it opens up activate window
now this activate window it can be moved
go ahead and just do a right-click over
there yourself
right there in those arrows and then try
to move around the activate window for
yourself the activate window is simply a
vertical list of all your sheet names in
the workbook you can use it as a
navigation pane as well meaning if I
want to go and navigate to sheet one I
can just go ahead and select it by
highlighting it and then going down here
and hitting OK
go ahead and try doing that go ahead and
try navigating through your workbook
using the activate window I'm gonna go
back to sheet 1 and hit OK I'm gonna
maybe go back here to activate window
maybe go back to sheet 14 and then hit
OK and now I'm on sheet 14 so just
simple navigation
now that we have the interface of the
software understood let's start talking
about the various ways you can navigate
among cells so the obvious one is you
can go ahead and click on a cell over
here and it moves your cursor over there
the name box labels where it's gonna be
totally fine what if you had a large
data set another route that I see people
take is typically they'll go ahead and
use their directional arrows to move
around on their keyboard so they're
hitting the left arrow would go to the
left they'd hit the down arrow to go
down and the right arrow to go to the
right and the up arrow to go back up
that's perfectly fine as well now one
thing I do want to let you know is what
if you were importing from a really
large database and you really wanted to
quickly see the end of the data or you
really wanted to just see the end of the
columns of how many columns they had so
a few shortcuts that I want to mention
here I'm gonna just clear out these
cells contents and if you have them
there you should - so just back on a
blank spreadsheet here now I'm gonna
select cell a1 now the down arrow just
takes me down to the next cell if I want
it to see the very last cell in this
spreadsheet all I have to do is hold the
ctrl key if I want to see the last row
I'd hit the down arrow and once we go
down here we're gonna see that Excel has
1,048,576 rows now I bet you're
wondering how do we get back up top do I
have to grab that scrollbar do I have to
use my center scroll of my mouse to go
all the way back up top well if it was
that easy to go all the way to the
bottom of the spreadsheet notice how
we're at the end of it if you hold the
ctrl key and then hit home it'll take
you back to the top the ctrl key and
then hit home now I do want to let you
know that I just showed you the rows if
you would like to see the end of the
spreadsheet column wise you just simply
hold the ctrl key and then you're gonna
go ahead and hit the right arrow this
time on your keyboard now as you can see
it goes all the way down to this last
column here now I'm gonna go ahead and
hold ctrl home to go ahead and go back
to cell a1 and
sides that a quick way to go to the ends
of certain places I want to let you know
that besides you using your directional
arrow to go down and your directional
arrow to the right to go to the right
and your directional arrow to the left
to go to the left and up to go up
there's other ways to navigate so really
quickly we'll all being on a 1 go ahead
and try hitting enter on your keyboard
enter actually takes you down one cell
it gets that cell address box and it
just moves it down a cell I bet you're
wondering how do I go to the right well
instead of using my directional arrows I
just use the tab key closer to my home
keys so I hit tab tab tap and that will
take me to the right I bet you're
wondering that's only two directions how
do we go back up or how do I go to the
left
well if ENTER goes down enter down down
down if you hold shift and then hit
enter we'll go ahead and take you back
up so with that being said if you hit
tab to go to the right if you hold shift
and then hit tab it will go ahead and
take you back to the left that's just
some simple navigation to move around
the software and start inputting data
I'm pretty sure you guys are starting to
notice the hitting a combination of keys
at the same time does some pretty cool
actions these are known as keyboard
shortcuts so I want to just cover a few
keyboard shortcuts because they save you
time let me go ahead and give you an
example so in excel if you want to open
up another workbook when you already
have it open maybe you want to make
another copy and you want to change it
up a little bit so you want to open up
another workbook you'd have to go to
your file you'd have to hit new and then
hit blank workbook and then it open up
another workbook for you but if you like
keyboard shortcuts and you simply hold
the combination of control and the
letter n for new it will go ahead and
give you a brand new book notice how
this is a new book it says book 1 on top
and your old one is right behind it so
you saved a bunch of steps by opening up
a new book by using a keyboard shortcut
ctrl n now it feels that easy to open up
a new workbook using the N key how would
you open up a pre-existing one so n for
new I'm pretty sure if you heard hold
ctrl and hit o for open it will simply
take you to the open section of the file
tab to where you can browse your cloud
services or your computer to open up
another file so keyboard shortcuts are
pretty powerful in saving time now the
next keyboard shortcut I want to talk
about is a matter of saving something so
if ctrl n for new opens up a new
workbook and ctrl o opens up a
pre-existing workbook I wonder what the
shortcut for saving s for saving would
be now I'm just gonna hold the ctrl key
and the letter S and if you guess that
you guessed right now notice how when
you go to save and I hit ctrl s it took
me to the save as area of the backstage
view not the save but the save as that's
because the very first save of a file
it's different than every other save
it's a save as giving it a file name
when you save as and you also give it a
file location of where you want it to be
saved on
I'm gonna go ahead and choose that here
I'm gonna hit browse I'm just gonna save
this and My Documents folder here so I'm
just going to go over here and name the
file just going to call it save the file
well and when you save a file for the
first time you can also establish a save
as type now the save as type I'm just
gonna go ahead and open this little
window by clicking on that arrow over
there there are all the ways that this
software allows you to save this file
you can save it as an Excel 97 to 2003
workbook so if you're using an older
version of Excel and you just send this
to another client you should go ahead
and check if they have an older version
so you can change it to their file type
the dot XLS file type where you want to
save it as a template so you can have a
tile to access on the Start screen or
maybe you want to convert it to a PDF
document over here so that way any
screen can view it with the same fonts
and colors and prints will come out nice
so these are just various save as types
the save as area allows you to pick a
save as type as well as a location like
documents to save the file and then you
can go ahead and hit save now I'm gonna
go ahead and hit ctrl s once more
control on the letter S my little fat
white crosshair does a little spinny
circle if you can quickly see that and
it says saved up top but it doesn't
really take me back to the original save
as window that's because now that the
file has a name and a location every
time you hit ctrl s it just updates that
same file if you want to go ahead and
maybe make another copy of this file
with a different name on a different
location or maybe change the data type
or the save type of the file you can go
ahead and just hit the f12 key on your
keyboard and this will open up the save
as box to where you can save the file
version 2 and you can change the data
type you can save this somewhere else as
well and hit save so now you have two
versions of the file
so as you can tell there's quite a lot
to cover on the Excel user interface you
think about it we've yet to begin even
typing it that's all gonna change now
let's go ahead and start talking about
entering text so I've been thinking
about it and I'm deciding to open up my
own restaurant and sends my name being
faz I think it's a little catchy to call
my restaurant fast-food so I'm gonna go
into cell a1 and I'm just gonna start
typing fast food fast food and I now
want to build a spreadsheet that just
lists my three servers that I have
starting out as well as the sales that I
generated from Monday through Friday so
now that I have the title and cell a1
I'm just gonna simply hit enter to go
down a cell I'm gonna leave a little
space and go down one more cell by
hitting Enter and here I'm gonna start
typing in my headers my first headers
are gonna be my servers so I'm gonna
label that as servers so I'm just gonna
start typing in here servers
and earlier when I wanted to go down I
hit the enter key this time I want to go
to the right so I can fill in the word
Monday and then Tuesday and Wednesday
I'm gonna go ahead and instead of
hitting the enter T to go down I'm gonna
hit the tab key to go to the right now
to fill in the word Monday so tab goes
to the right however if you've so
happened to make a mistake in the last
cell let's say I didn't want to put that
s there I just wanted to have the word
server I can simply hold the shift key
and then I could hit tab to go back to
the left and if shift tab takes you to
the left I'm pretty sure shift enter
will take you right back up so now we
have all four directions when entering
text and keep in mind you can still use
your directional arrows it's just more
efficient to use your tabs and enters
it's more appropriate I'm gonna go ahead
and hit enter to go back onto a3 and
because I'm making an edit I'm gonna use
my formula bar that nice massive area
where I can just fix that s and delete
it
I'm gonna go ahead and accept that
change there by clicking the check mark
and then hitting tab to move on over
next I'm gonna go ahead and type in the
word Monday and you guys should go ahead
and try doing this as well go ahead and
try filling in a title cell called
fast-food hit enter twice and then on
the server cell type in server and then
hit tab to go to the right and start
filling in your headers with Monday and
hit tab once more and fill in Tuesday
and do the same thing up until Friday
not sure about the weekend yet Wednesday
Thursday and Friday all right I'm gonna
hit enter and it took me exactly where I
wanted to be that's the power of hitting
enter and tab knows where you want to go
and I now want to start talking about
the fact how my Wednesday the Y is cut
off but in the formula bar when you
click on the cell it shows you the Y's
there when you have situations like this
you can you can sit there and adjust
each column like here I'll do Thursday
by going up to the column name going in
between that
bar over here and you can just grab that
bar with the left click and you can just
drag it make it a little bigger make it
smaller however that's more of a manual
fit and that's gonna take some time to
do on each column of data that has a
word that's cut off or a character if
you want to fix this all in one go while
you're entering data I want to let you
know next to the column a there's this
little triangle this gray triangle you
click it you just highlight it every
column and every single row in your
spreadsheet now because you have them
all highlighted you just need to go in
between the closest column which will
always be the column a and B and instead
of left clicking and manually adjusting
it yourself because you will just every
single one when you do that because
they're all selected if you do two
clicks back to back just one to one to
the left click it will give you a best
fit for every single word in the cell
with the largest word in that column
taking the precedents for the size so
now that we fix that let's go ahead and
start typing in some server names so I'm
back over here on a4 and for my first
server I'm just going to go ahead and
type in
for my next server name I'm just going
to go ahead and type in server
- and I'm just hitting enter to go down
oh I made a typo there so I'm gonna hold
shift hit enter I'm just gonna
capitalize that header or that server
name by going back to the formula bar
and fixing that edit and then accepting
it alright next I'm gonna go ahead and
enter once more I'm gonna do one more
server we're gonna call this server 3
I'm gonna hit enter ok don't really want
to add another server so from this point
I'm just gonna go ahead and hit shift
enter and go back up and I'm gonna hit
tab to go to the right I'm gonna go
ahead and put the number 50 in I'm also
gonna put the number 100 in and I'm just
gonna put 150 I'm just going to
increments of 50 going up to 50 now in
case you haven't been following along go
ahead and start typing in some data into
the spreadsheet build this exact
spreadsheet and use your shifts and tabs
your Shift tabs and your shift enters
and your tabs and enters to navigate
around and once you're done typing in
the data here make sure to get the best
fit for every single column at the end
by going up to this triangle and then
just doing two clicks come right back to
class and unpause the video
so welcome back from that little
practice of typing in and entering in
data in Excel
hopefully that was some good muscle
memory for remembering to use tab and
enter when entering in data now that we
know how to enter in data and navigate
around the spreadsheet and start editing
using our formula bar let's start
talking about numbers and text and
something we call data types so in excel
these numbers over here these are
actually expressing currency values for
my sales data if you think about it and
they don't have any dollar signs like a
dollar sign 50 dollars and usually
dollars have cents at 0-0 like so and
same with this hundred it usually be
hundred dollars now some people they
spend a lot of time adding in these
values just like that making them
currency values I want to let you know
that you can have the software do it for
you instead of you manually adjusting
and making them currency values you can
actually choose that this cell holds a
currency value so no matter what number
you put in there it's gonna
automatically put in the decimal places
for you as well it's the dollar sign how
you might want to do that well first
you're gonna go ahead and select the
data set so I'm just gonna left click
and hold and just drag across the
numbers that I want to go ahead and add
those dollar signs to now once you have
this selection I want to let you know if
you hover over to your Home tab you have
this lovely number command group right
over here number
and if you just go ahead and notice how
there's this little arrow over here but
they'll drop down you can change the
number format of that cell so I want
everything to show up as currency
notice how the dollar signs got added as
well as the decimal places I want to let
you know that when you do a number
format from your home tab in the number
command group you have the ability to
change whether it's a dollar sign or a
pound sign whether or not you want two
decimal places or just one none all this
can be done if you go to the drop down
menu and then you choose more number
formats now when you choose more number
formats it's going to show you all the
number formats with some more settings
that you can apply to them for example
on the selection here
I chose currency but if you go to that
drop-down menu and you go to more number
formats you can adjust if you wanted
maybe zero decimal places you can also
adjust if you want to change the symbol
for the dollar sign go ahead and change
it to whatever you'd like over here once
you're ready you can also change how the
negative numbers will show up inside the
cells so you can have them show up with
a negative sign you can just shout have
them show up to be read we're just in
parentheses signifying that they're
negative or red and in parentheses
signifying they're negative
I'm gonna go ahead and just leave it
with the negative sign I'm gonna hit OK
the next thing I want to point out to
you is everything has a keyboard
shortcut and the keyboard shortcut to do
just what we did and change it to
currency format is simply holding the
control key and the shift key with the
number 4 that will turn on the currency
format for you here I'll do it to these
cells right over here I'm going to hold
ctrl shift and the number 4 and it
switched it from general to currency
I'm gonna quickly undo that action I'm
just gonna go up here and undo my last
action just to show you that again so
and you guys should try this out too so
on these bottom empty cells highlight
them and realize that up here these are
generally formatted cells meaning if I
input a number here like 50 it's just
gonna show up like a regular number now
if you highlight this area because
you know that currency values will go
here and you just simply hold ctrl shift
and the number for it will change it to
currency up here for us it's just a
shortcut now when you go ahead and type
into these cells like 50 and you could
enter the dollar signs and the zeros are
already in there for us
all right now we know about data types
and we changed the number format of
these cells do you hold currency values
let's go ahead and fill in the rest of
them so let's just put the same numbers
in the bottom of these cells again and
just start typing them in for practice
just do it along with me so I'm gonna
start typing in 100 it's up to 250 to
200 tab 250 just like that the dollar
signs and the decimals keep getting
added in for us I'm gonna do the same
thing for this next row here great and I
bet you're wondering is why do these
have decimal places and these don't it
is because after we made the currency
format for these ones we went into the
Home tab and we also knocked down the
decimal places click this icon twice
let's all go ahead and do the same thing
for this data set here but I want to
just throw out a little concept what if
this was 60 columns of data and what if
this was 100 rows and you're trying to
do exactly that
it would take you a pretty long time to
sit there and drag all the way down and
all the way to the left or the right so
let's all try this together let's all go
back to cell b5 and instead of manually
left clicking and dragging across let's
just hold ctrl and the shift key and hit
the right arrow to go to the right what
this essentially did for us is it
highlighted the data set all the way to
the right until the data set stopped now
I'm gonna do the same thing but I want
to highlight going down so I'm just
gonna hold ctrl shift hit the down arrow
great so now I've highlighted the area I
want and I'm just gonna hover up to my
home tab I'm gonna knock off those
decimal places all right now I'm just
gonna zoom out of the spreadsheet a
little bit just to give us a full view I
just held my ctrl key and I use the
center Scroll in case you forgot and now
that we have the currency format in and
we showed you this cool little trick to
highlight the range that you'd like to
highlight using ctrl shift and your
arrows here I would like to start
talking about how you can delete
contents from Excel cuz there's several
ways you can do it and it might not be
clear which one you want to do at first
glance so
let's say server 2 was actually not
hired and this was a typo this was
actually supposed to be put in next
month's data and we actually put it in
this month's or this week's we'll call
it and this wasn't supposed to be there
and we need to delete it so first let's
highlight the area that we want to
delete and let's all just hit the Delete
key on our keyboard okay so the Delete
key on my keyboard actually remove the
contents inside of the cell but it left
the cells on my spreadsheet intact I'm
gonna go ahead and undo that action in
case you're wondering how do you undo
something you just did I'm just gonna go
up to my quick access toolbar where I
expose you to the undo and the keyboard
shortcut is ctrl + the letter Z I'm just
gonna hit that combination ctrl Z to
undo that and now instead of hitting the
Delete key I'm gonna right click and I'm
gonna choose to clear the contents
okay so if you right-click a cell and
you choose to clear its contents it's
the equivalent of you hitting the Delete
key on your keyboard I'm gonna hold ctrl
Z to undo that once more now the next
way you can delete something is not
about deleting the contents inside of
the cell
it's about deleting the actual cells
because both ways whether you hit the
Delete key or you cleared your contents
left this blank row for us in the
spreadsheet which we didn't want I just
want to go ahead and move server 3 back
up to where server Chi was just one nice
fashion um let's go ahead and check out
the delete option instead of the clear
contents I'm gonna go ahead and delete
and now this window pops up and it's
asking me well what do you want me to do
do the rest of the data and I actually
want to shift the cells
up here once it gets deleted so I'm
gonna go ahead and hit okay to that
option and just like that I was able to
delete the cells and the contents so to
wrap things up guys I know this is a lot
of information but clear contents key
the right click clear contents is
connected to the Delete key on your
keyboard the delete option removes the
actual cells from the spreadsheet then
you have to decide whether or not you
want to shift to other cells in a
certain direction
of that extra row and adjusted all the
extra data to shift itself up I want to
show you how you can do some
calculations so over here in column G
well specifically cell address G 3 let's
go ahead and type in Auto total
and then over here in a six let's go
ahead and type in auto total again
now there's a way to have the software
go ahead and calculate some totals for
you automatically let me show you an
example of how to do that so if I want
to automatically sum up these numbers
here what I do is I just go ahead and
select the cell that I want it to show
up in and then I go into my home tap
just go ahead and give it a click your
home tab actually has an editing command
group and in the editing command group
you have an icon known as Auto sum Auto
sum is actually two separate icons
there's Auto sum in this arrow here this
symbol right here it's the back of the
backwards ee i'll call it it's the
mathematical symbol for summation now if
you get click on this arrow it's gonna
show you that you can also find the
average of certain numbers so you can
add up the numbers and divide them by
the amount of numbers you have or you
can count how many numbers there are or
what's the biggest number or what's the
smallest number or you can dig through
our function library here in excel now
if you want to go ahead and use the sum
you can just click on this icon here if
you want to use anything else you can go
ahead and go through this list I'm gonna
go ahead and just click on autosum here
and what it does is it predicts that you
want to go ahead and add up these two
other numbers why because it's right
above it it thinks that's make sense and
I'm gonna go ahead and hit enter and it
sums it up for me now Auto sum is a very
useful it lives in the home tab let's go
ahead and try it for the next sell here
so like have a cell selected currently
on c6 and this time let's click on our
formulas tab I just want to show you
that Auto sum is also found here it's
same exact thing I'm gonna go ahead and
use it from here this time to show you
that it does the same thing for us and
when you hit enter you have a total so
go ahead and try that out if you haven't
tried it out yet just go ahead and
select the cell and then right up and
hit auto sum from either the formulas
tab or the Home tab and your editing
command group and if you have to pause
the video to do that go ahead and do
that come right back so now that you're
back I want to let you know that
everything has a shortcut including Auto
sum so if you want to use autosum
shortcut I label it as the alternative
way to equal upsells because it holds
the Alt key as well as
the equal key so if you were to go ahead
and select this cell and hit alt an
equal it will go ahead and run Auto sum
for you and this is my preferred way
because my hands don't have to click
anything with my mouse I'm gonna go
ahead and hit enter go ahead and try
using the autosum keyboard shortcut for
these remaining two columns here so alt
equals honest empty cell and hit enter
and for the last one over here have an
empty cell selected and hit alt equals
we're good to go now auto sum can work
more than just down building a column of
calculations with the bottom row it can
also work going across so I'm just gonna
hold ctrl shift to the right to
highlight the data set going across and
hit alt equals check it out I
highlighted this data and I hit this
Auto sum key and in the only other spot
that it could make sense to go in was
here so put it in there for me I'll do
it on this one again and you should do
so select b5 bolt ctrl + shift and hit
the right key and instead of running up
here and hitting Auto sum we're just
gonna hold the Alt key and hit equal
fill it in for us let's go ahead and do
it for this last cell and I want to show
you that you don't have to have the data
set selected here if you're on an empty
cell you can have the software predict
for you and when you hit alt equals here
notice how it predicted wrong it Ritz
adding up these two columns when we
reach us want to add up these columns
here so you're allowed to fix the error
that I made for you before you hit enter
like so so go ahead and play around with
Auto sum expose yourself that it lives
also in your home tab and the formulas
tab and then I can do more than just
summing up some numbers because you can
have an average account a max or a min
and try to memorize that shortcut cuz
it's very useful and it's a very
commonly used key auto sum in Excel
as you can tell Otto seems pretty cool
there's a quick way to automatically sum
find the average or find the max or the
min or the count of a certain data set
so now that we have a way to
automatically find some calculations for
us let's go ahead and show you how Excel
can maybe do some calculations but in
the form of building it ourselves and
that's actually called a formula notice
how we have a formulas tab up there
let's go ahead and go inside of cell h3
and write totals with formulas I'm gonna
go ahead and hit enter so in excel
there's various ways to do calculations
and one of the most basic ways is to do
a formula now for a formula in Excel I
actually have one rule and a guideline
meaning you don't have to follow the
guideline but it makes your life easier
if you follow it so the rule let's go
ahead and give it get it out of the way
the rule is if you want to do a formula
inside of a cell meaning you want to do
a calculation whether it be addition or
subtraction or multiplication or
division or finding an average you're
gonna need to have an equal sign inside
of the cell what that does is it lets
the software know that hey I'm in the
formula mode I'm in the calculation mode
I'm about to have you do a calculation
for me now that we have the equal sign
in there that's the rule let's go ahead
and discuss the guideline nothing
stopping you for putting in the actual
numbers like 50 and then actually
holding in shift and putting the plus
and then throwing in that hundred and
then holding the shift' and hitting the
plus and throwing in 150 I mean we're
essentially building a math equation and
we're adding up 50 plus a hundred and
150 this will work but what happens if I
made a mistake here in this cell and
this was supposed to be zero I now have
to go back into this formula and change
this to zero so that answer can change
for us leads to multiple update spots so
the guideline I have for you is this
instead of actually putting the numbers
inside of a formula just use the cell
address that the number is held in so
we're not going to tell Excel what to do
we don't want it to add 50 and 100 and
150 we're just gonna tell Excel where to
look I want you to look inside of b4
then once you're done with that look
inside of c4 and notice how
just clicking on them I could be typing
them I can go ahead and type in d4 and
notice how the color coat went around d4
and I didn't even capitalize the D I'm
showing you that Excel is not
case-sensitive here it doesn't matter
I'm gonna go ahead and hold the shift
one more time ad on the + go that in
there well looks like I have to do once
more
and this time I'm gonna write it out for
myself I'm gonna write out f4 and again
it does not have to be capitalized so
this right here is a formula it's the
most basic way you can go ahead and do a
calculation in Excel now the pros of
this is it's really simple to do just go
ahead and add the operon that you want
after each cell address and then hit
enter when you're done and you have an
answer for yourself or a calculation we
should say the downside of this is how
many keystrokes you have to do how many
times you have to use your keyboard to
type in one formula so if you have a lot
of cell addresses to write out this
might be a little lengthy process but if
you have just one or two just go ahead
and use the formula route now go ahead
and try that out and maybe pause the
video and do a formula over here and the
server 3 and then as well as do a
formula over here for the totals over
there and then come right back
so we just learned that if you go ahead
and fill in a formula inside of Excel
you can go ahead and do a calculation
for yourself but if you have too many
cell addresses to add up becomes pretty
tedious and less efficient the more cell
addresses you have due to the amount of
keystrokes you have to use to fill in
that formula so let's talk about another
way that you can go ahead and perform a
calculation or find a total in Excel now
with a formula you have to write out the
operation that you do by hand each and
every time you do it what I mean by that
is if you want to add in some cells
you're gonna have to put the plus sign
every single time you put it in a cell
address and then you hit enter on the
last one the whole time that we were
doing this we already knew that we
wanted to sum up the numbers when you
already know the operation that you want
to do and you so happen to have a lot of
numbers to do it with or a lot of data
to sum up or find the average with go
ahead and use what we call a function to
perform that task because it's simpler
to do it that way so let's all go ahead
and let's do this together let's all
hover over to column I here and let's
build a header and I three the header
that we're gonna build is just gonna go
ahead and let's say that let's do totals
with
I'm gonna go ahead and enter here and I
want everyone to just go ahead and dive
into their formulas tab by giving it a
click and expose themselves to the fact
that they have a function library and
all the functions that Excel has can be
found here so if you have some time take
a look at them maybe see all the other
functions that has to offer it gives a
definition if you hover over them so
let's go ahead and take a look at some
functions do you sum up this data set
here now the rule for a function is the
same as the rule for a formula you're
gonna need an equal sign in the cell
that you want to do a function in so I'm
gonna go ahead and put that equal sign
but this time instead of adding the plus
sign after each cell we're just gonna
say the name of the operation want to do
so if you want to go ahead and sum up a
number you just say sum and you type out
some here if you want to go ahead and
say do an average you type out average
any type of average here so I'm gonna go
ahead and type in sum so I type in su
now you see a drop-down list of every
function that's gonna start with those
letters and you can use your keyboard
arrows to go ahead and navigate to the
function you want some adds all the
numbers in a range of cells however a
lot of people they want to hit Enter
when they see this they want to hit
enter and I'm gonna do it for you just
to show you what's gonna happen it says
name with the question mark that's a
Microsoft error code letting you know
there's no function with that name now
I'm gonna hold shift and enter to go
back up and just to show you that all we
did was write an equal su into the cell
and walk away now I want you all to have
a way to just quickly fill in a function
inside of this cell so if you hover over
to the drop-down list instead of hitting
Enter you hit the tab key to
auto-complete the name of the function
for yourself
and we just Auto completed the sum
function now that we have the sum
function auto completed I want to point
out something about a function that they
all come with I'm gonna go ahead and
move it around the thing about to start
talking about just moving in a circle
pops up when you type a name of a
function and it actually exists this is
actually the syntax of the function the
syntax are another way of putting it is
the manual for the function
is basically the rules you need to know
to fill in the function I like to say
English has its own syntax we have
commas periods parentheses we have
subject noun verb that's the English
syntax Excel functions have its own
syntax everything in syntax means
something I want to just go ahead and
describe the pieces of this syntax bar
so that we know how to fill in functions
so notice how we have some open
parenthesis number one which is bolded
comma number two in brackets comma and
then at dot dot dot which is an ellipses
and then a closed parenthesis everything
means something in syntax so let's say
number one and number two those are
actually called something it's either
called an argument or another way of
putting it as a parameter you could say
that's a parameter for the sum function
or that's an argument for the sum
function notice how number 1 is
currently bolded in the syntax bold
that's letting you know that it's
expecting that current parameter now I'm
going to put in the number one and
you're gonna notice how the bold did not
move over to number two even though I
have a number in there that has to do
with the comma over there in the syntax
bar the comma that's the delimiter in
the syntax the delimiter notice how the
word limit is in there it's the limit
for that parameter so I'm going to put
my comma head and now it moved over to
number two now number two is in brackets
simply because it's an optional
parameter you can just have one number
to sum if you wanted to and I'll show
you why in just a second now the
ellipses there are they're letting you
know that if you fill in those two
parameters like number one and number
two just how I did I'll generate a third
or maybe a fourth and a fifth letting
you add in more numbers to fill in that
function so it's kind of like a more
parameter option for yourself now the
close prints to see in a function your
version of the software Excel 2019 will
close the parentheses if you forget to
however it's a good practice just not
lose a good habit so always make sure to
close a friend see if you choose to go
ahead and use a function now the reason
why they allow you to do this and let's
all do this together type
equal s you go down to the sum function
hit tab the reason why they let you do
just one number is because you can left
click and hold and drag across a range
of cells to go ahead and fill in the sum
function and then it shows up as b4 : f
for this : what it means is I'm gonna
add up b4 through f4 including the outer
ends as well now I'm going to just go
ahead and close my parentheses and hit
enter and that's how you do a function
in Excel which is a more efficient way
of doing a calculation compared to a
formula
in the last video we just covered the
basics of a function now I'm gonna go
ahead and fill in inside of i-5
the sum function as well so equal s you
go down to some hit tab left click and
hold and drag across the range of cells
you like the sum up close the print C
and hit enter go ahead and try that once
more in the cell of i6 and we'll go
ahead and expose ourselves to a few
other functions so maybe pause the video
or follow along with me equal type in s
you go all the way down to some hit tab
then left click and hold and drag across
the range of cells you want to sum up
close the parentheses and hit enter
now that we have the sum function down
and we understand syntax let's go ahead
and expose ourselves to a few other
functions I'm going to hold ctrl and use
the center scroll my mouse to just zoom
out a bit to see more of my spreadsheet
and down here in cell D 11 let's all
together type in totals type in average
and then hit enter type in max it enter
type and min and then hit enter and type
in count so I just want to expose you to
another set of simple functions that you
have in the function library that's in
your formulas tab now that we know
syntax let's go ahead and do another sum
function just for practice in cell 11
so let's do it together um we put in an
equal sign type in su and we don't want
to fill on the rest so we hit tab once
we highlight the sum function and we
want to find the total so we're gonna go
ahead and click on b4 and earlier we
showed you some shortcuts to highlight
data I'm just gonna go ahead and because
it's small use my hand and my mouse and
left click and drag and go across down
I like the data set up until Friday I'm
going to close the parentheses hit enter
now that's again the some function that
we expose ourselves to in the previous
video let's go ahead and see another
function like maybe average so equal
average start typing it in find where it
says average in the drop down list
an average returns that arithmetic mean
of arguments which can be the number of
names arrays or references that contain
numbers I'm gonna go ahead and hit tab
to autocomplete that average word for me
and now for average it's the same data
set except that under the hood it's
going to add up the numbers and then
divide it by the total amount of numbers
you gave it hence finding the average so
another function I want to expose you to
is the max function you're gonna go
ahead and put that rule in that equal
sign type in max hit tab to fill in the
rest for you and once again you're going
to go up to the data left click and hold
the data set that you would like to sum
up here
close it and hit enter now for the men
you're gonna do the same exact thing so
you're gonna go equal type in min hit
tab and you're gonna left click and hold
the data set that you'd like to add up
and close the parentheses
go ahead and try that for the count
function and come right back to us just
go ahead and do an equal sign type in
count hit tab and do the same thing and
grab that data set once more and close
the parentheses and hit enter and we
just expose ourselves to the average
function in Excel the max min and the
count the count just went ahead and
showed us how many numbers we had
highlighted in that range
notice how the count didn't give an
currency value as the other ones did
since they were currency
herself to some basic simple functions
here I want to just go ahead and show
you some things that we should pay
attention to when copying functions over
to other cells now check out this
formula here or this function I should
say because we use the sum function in
it I'm gonna go ahead and show it to you
by hitting f2 on the cell to show you
the formula f2 on my keyboard and over
here it's saying that the sum function
is using b4 through f6 now I'm gonna go
ahead and copy this cell by
right-clicking it and choose to copy it
I could have gone over here in my home
tab and choose to copy it I could have
hit the letters ctrl or C to copy it I'm
just choosing the right click copy and
I'm just gonna quickly paste this value
this formula or this function into the
cell one column over and to call them F
here go ahead and do that yourself as
well so you go ahead and click on the
cell now you have many routes to paste
the shortcut is ctrl V or you can right
click and hit paste or you could have
gone into your Home tab and chose to
paste the option from there I'm gonna do
the right-click way cuz that's always
closer to me and I want to show you that
the values are different we copied 3000
but it gave us 5800 I now want to show
you why first I'm gonna hit the escape
key to move out of the copy mode and
remove the marching ants around that
cell so escape I'm gonna hit f2 on that
cell to show you the formula in the cell
so this formula we built together
and it's from Monday through Friday
summing up the totals there let's go
ahead and check out the next formula
that we copied over in this cell so
click on it and hit f2 okay so we're
gonna notice how the Monday column got
cut off
notice how we moved one column over when
we copied the data as well so instead of
saying b4 now earlier it said b4 in this
cell it's now saying c4 this is because
when you copy something it's relative to
where you're moving it to meaning the
cell addresses will shift up down left
right depending on where you moved it to
so again if I would have copied this
cell right over here control copy ctrl C
and I would have maybe pasted it down
here one row down and one column down
I'm gonna hit ctrl V to paste
it's a different number why because this
time we went down one row and a column
so it should shift the column to C
notice how it went to be over here and
then over here in this column over here
notice how I went to see I'm gonna go
back to the cell over here and show you
notice how it says b4 but I went down
one row over here notice how it says c5
so down one column one row it's relative
to where we moved it what if we wanted
to stay absolutely where we wanted it to
be meaning absolutely use those cell
addresses regardless of where we moved
it what we're asking Excel to do is
weíre asking Excel to go into absolute
referencing and all you have to do to
tell Excel to go into absolute
referencing is inside of each of the
cell addresses in your formula so let's
all go into the cell that has a sum
function e 11 and
put your cursor in front of b4 and just
hit the f4 key to add in a dollar sign
in front of the B and in front of the
four you could have done it manually by
just holding shift and four but it's
much quicker to just use the f4 key to
add in the absolute reference for you
now I'm gonna go ahead and hit some and
I put in the absolute reference I hit
enter and now just to show you when you
have the cell selected and you choose to
copy it maybe here by right clicking and
hitting paste it's gonna remain the same
because you chose to data in the dollar
signs which means you absolutely
referenced those cells in the formula
now that we have absolute reference down
we understand what it does and what its
function is so when you copy a formula
over the cell addresses remain the same
same with functions let's go ahead and
do the same thing for the average Max
min and count and we'll be right back
alrighty welcome back I'm gonna go ahead
and just quickly go ahead and add in my
absolute references myself so I just go
into that cell and hit f4 go into this
cell and hit f4 hit enter and I'm gonna
do the same thing for the next cell for
the max notice how I'm using the formula
bar to make my edits so I don't make a
mistake down there in that tiny cell now
I'm do the same thing for men f4
for its enter and one last time just for
practice do that for the count just
hitting f4 on each one of those cell
addresses making sure to hit both cell
addresses each time now let's all cleat
this copy that we just did just hit the
Delete key on our keyboard and let's
highlight this data set here the
shortcut for that is control on the
letter A and then let's all just copy
the data set just to show you the
difference copy and now this time I'm
going to paste the data let's say right
next to
the data set so in column K and cell k3
I want to put the data set analysis next
to it I'm going to ctrl + V earlier in
the day we discussed that if you have
pound sign in a cell let's say it's just
letting you know that the cell is too
small to show you the answer within that
cell and we showed you a quick trick to
fix that by going over to this triangle
here and then just going in between any
of the columns are doing two clicks in
between the columns and doing two clicks
one two and just like that the values
are the same here compared to here and
that's how you copy using absolute
reference which allows us leave the
formulas intact with the same values and
the same addresses for both formulas
we filled in some formulas did some
functions learn about the relative and
absolute referencing of them now let's
go ahead and clean up our spreadsheet a
little bit make it look nice a little
bit first thing I'm going to do is let's
just delete the totals here and this
data that we just built by hitting ctrl
a and hitting the Delete key and
removing it from there now that we've
done that I've decided I actually want
to move this area this chunk of data I
want to move it over to the bottom of
the spreadsheet right down below total
with functions so maybe over here in
Iten i just want to run through the
various ways that we can do that now the
way that's been there since 2007 as you
highlight the data you can use a
shortcut by ctrl a just quickly grab it
and then you can go ahead and hover up
to your Home tab and you can choose to
copy the data now notice how I just
clicked on it and hit copy and you see
these marching ants I call it around the
data set another way of copying I'm
gonna hit the Escape key to cancel that
let's just simply right clicking and the
option to copy what we've presented
right there and the second option for
yourself the option to cut would simply
to be to move the data somewhere else we
want to copy it so we're gonna go ahead
and choose copy now the last way that
you can copy data is you highlight the
data set and you hit the letter control
and the letter C for copy and I'll turn
in the copy mode now when it comes time
to copy this data and we said we wanted
to move it to what I 11 or I 10 let's
just say I 11 I'm gonna go ahead and
click on I 11 when you copy the data if
you go ahead and paste it by control V
and pasting it right clicking and
choosing to paste it or you can go up
here to your Home tab and choosing to
paste it what that's gonna do let me go
ahead and show you it's gonna paste it
but it's gonna leave the copy mode on
notice how the marching ants is still
ready you need to paste it again you can
hit paste here and paste it as well what
if you only wanted to paste it once
notice how every time I paste it I have
to hit the Escape key to get out of a
copy mode so I'm gonna go ahead and
delete this data here by hitting ctrl a
just hitting the Delete key on my
keyboard and this time let's go ahead
and highlight the data ctrl a and we're
gonna copy it using the shortcut ctrl C
and instead of hitting paste let's hit
enter enter the difference between a
pace than an enter is it takes you out
of the copy mode after you're done
pasting it so when you hit Enter it
copied it and it jumps you out of the
copy mode without you needing to hit
escape versus you hitting paste it's
gonna leave you in the copy mode and
you're gonna have to hit escape at the
end so if you're gonna have to copy this
data into multiple areas go ahead and
paste the data you're gonna go ahead and
have to just do it once go ahead and
enter in the data to save that last step
of escaping out of the copy mode
now that we're able to move data around
and copy it in various ways
I decided that my spreadsheet my
restaurant is doing pretty good in sales
analyzed it a bit we're doing pretty
good here we're doing pretty good here
the data seems good I think it's time to
open up on Saturdays and Sundays and I
think it's time to actually hire back
server to now I'm not gonna build a
whole new spreadsheet to do that that
means I have to modify this spreadsheet
by inserting rows and columns and
various positions so that I could add in
those extra values to do that let's go
ahead and cover how we can add in that
Saturday column and that Sunday column
now there are certain rules that we have
to discuss with inserting columns so
let's all click on column H here and
notice that if we right-click an option
to insert another column is available to
us with the selected column right click
and you can insert another column now
the question is where is that column
gonna show up an inserted column will
always show up to the left-hand side of
the selected column so you select a
column and you right-click and when you
hit insert what it's gonna do is is
gonna have the columns show up to the
left-hand side of it and the new column
will be called H again so it's gonna be
called H once more because it's gonna
shift every column over and again always
remember it shows up to the left of the
selected column now I'm gonna
right-click and hit insert and just like
that it added in to the left and now I
have a trick here the last action we did
in Microsoft Excel was insert a column
your last action gets mapped to the
keyboard shortcut f4 so because we just
inserted another column all we have to
do is hit f4 to insert another one
because that was our last action in a
case this wasn't clear let me show you
this I'm gonna highlight this header
yellow my last action was highlighting
it yellow so now my last action is
mapped to f4 so I can hit f4 and I like
this cell yellow so I just want to
expose you to that trick because
typically when you insert a row or a
column you're gonna do it multiple times
and that's how you insert columns here
now let's go ahead and delete those
columns just by right-clicking and
choosing delete right-clicking and
choosing delete now that we have that
down let's go ahead and actually insert
our Saturday and our Sunday columns in
the appropriate spots with that rule in
play it's gonna show up to the left with
that being said I'm gonna have column G
selected because I want it to show up
here so I'm gonna right click hit insert
and let's just sit F for because that's
my last action to add in the next column
like so now that I have that in I'm
going to type in Saturday
and I'm gonna type in Sunday and this is
obviously for next week so these are
gonna be blank here but this is for next
week just want to add that's into the
spreadsheet
now now that we have the columns in
let's go ahead and run through the rules
for rows I want to go ahead and rehire
my server too but I want him to view as
called server two that was his name and
I want him to be in that order so how
the rule is for rows if you want a row
to show up the row is always gonna show
up to the above the row will always show
up above the selected row
so if I have row five selected the new
row will show up above it and that's
exactly what I want so I'm gonna right
click and choose to insert a row and
don't forget you have that f4 trick if
you want to insert another row just like
that except I just want one server so
I'm gonna go back over here and undo
that last option and I'm just gonna go
in this cell and type in server two now
in our previous videos we discussed that
a lot of the shortcuts they break the
shortcuts to navigate around the
spreadsheet and highlight cells when
they see blanks so having this amount of
blanks in our spreadsheet it's not
beneficial for us and we did discuss
some quick ways to go ahead and remove
blanks in a case you weren't in those
other videos all I did is I highlight
the data set a shortcut for that is
control in the letter A and we're just
gonna go ahead and open up the replace
window by hitting ctrl and the letter H
here and then the Find and Replace
window will pop up you can go ahead and
find a blank cell and you can replace it
with a zero a dash even of the word
blank I'm gonna go ahead and put a zero
and I'm gonna choose to replace all just
like that
16 replacements were made and we're in
good shape
now that we're able to insert rows and
insert columns add in some data again
and fill in some blanks I want to show
you a feature that helps you fill in
data with empty cells or update formulas
and existing cells so that way you don't
have to do a lot of the work yourself
for this exercise I'm gonna go ahead and
add a new sheet to this workbook and you
should too so let's all go down to the
bottom of our software of Microsoft
Excel 2019 and hit that plus sign to go
ahead and add in a second sheet I'll go
hit the plus g2 and this sheet I'm gonna
go ahead and call this cell a1 I'm gonna
call it the autofill and you should too
in Microsoft Excel you can have it
automatically fill in some values for
you and I just want to run through the
various ways you can do that so let's go
ahead and build some numbers just do one
two and three inside of a three a four
and a five and let's do four five and
six inside of column b3 through b5 now
autofill if you want to go ahead and do
a calculation to go ahead and sum up
this number or a formula we'll call it
and this number here autofill can help
you do the rest let me show you now
notice how around the green border of
the cell there's this tiny little square
there that's the autofill handle you'll
see your fat white crosshair turn into a
skinny black crosshair like so a lot of
people get this confused with the
compass that they see on the green
border of the cell address but we're
referring to that skinny black crosshair
now how autofill works is you can left
click hold and drag down and what it
does is relative to where you move it it
will add in those formulas for you
notice how it fills in seven and up here
in the formula bar it says a4 + b4 and
then when you went down once more it
changed it to a 5 + b5 and it copied the
formula relative to where you're moving
it to for these cells down here
now if you wanted it to be stuck on
these
cells we covered absolute reference in
the previous videos and you'd add in the
dollar signs
but autofill that little tiny square
allows you to fill in a bunch of
formulas for yourself if you just do the
first one now that we have that down I
want to show you what else autofill can
do now besides it doing numbers I want
you to just go ahead and let's go and
call them II one and type in the word
Monday
now autofill works with text too I just
want to let you know if you type in a
day of the week it goes ahead and you
drag it down it gives you the rest of
the days of the week if you do it
abbreviated due Monday
notice how the M is not capital and you
go to the autofill see a skinny black
crosshair and this time instead of left
clicking and holding I'm gonna do two
clicks from the autofill handle one two
great I went ahead and work for us now
I'm gonna go ahead and undo that last
action undo it and I want to try that
same thing here I'm gonna double click
my autofill handle here one two and it
did nothing the only way an auto fill
handle will work let me go ahead and do
that again I'm gonna bring it back to
Sunday is if you give the autofill
handle text directly to the left hand
side of it you're allowed to double
click it here because it knows when to
stop but if it doesn't have text like
are we here I had a blank column and
then I went to double click on Monday
it wouldn't complete it because they
didn't know when to stop so the double
click on the autofill handle will only
work you have text directly to the left
hand side and then it'll stop with the
left hand side I want to point out
besides that having the ability to do
cells addresses and formulas for
yourself and as well as new days of the
week it can also iterate through numbers
by giving it two numbers and then
highlighting both numbers when you
highlight both numbers notice how they
share one autofill handle and then from
there you can left click hold and drag
and you'll notice it's incrementing by
one because that's the pattern amongst
the two cells it's a plus one pattern
plus one if it was plus 50 if those 50
100 it would have done that repeating it
all the way down besides days of the
week and iterations and
petitions so if you would have just gave
it one number it would have just
repeated one number for us it also has
the months built in so if you do January
and you go ahead and go to the autofill
handle and do a double click you have
the months as well so if you're thinking
about it a lot of the text that we wrote
in the previous example and sheet one
could have been redone with the autofill
handle let's all go back to sheet 1
let's all just use the shortcut to do it
let's hold the control key and hit page
up on our keyboard to go to sheet 1 now
the things that we could have filled in
with autofill here is think about it we
could have filled in the headers we just
needed to type in Monday and drag across
if we would have typed in 15 100 and
dragged across we would have had up to
250 if we would have just done one
formula and double clicked here we would
have had the rest and same for the other
ones as well so I don't feel could have
helped us a lot over here I just want to
introduce you to it on sheet 2 by
holding ctrl + page down these are all
the things it can do for you it can do
formulas or functions and it changes the
cell address every time you move it it
can go ahead and fill in text for you
like headers or it can mimic patterns
like so or repeat through and things for
you so autofill is very useful in Excel
and all you have to do is just grab that
handle by seeing a skinny black
crosshair on the cell address box
food sheet so we put some data in built
the spreadsheet we can go ahead and use
this week after week except that looks
pretty basic right now it's just black
and white I mean we do have these
headers here that are highlighted let's
go ahead and fix all that let's
highlight these data sets over here it's
over up to our home tab let's go to that
font command group and go to that fill
color and choose to have no fill now
that we have that cleaned up let's go
ahead and clean up some more data here
by maybe removing this extra column
choosing delete removing the totals from
down here by just deleting them let's
remove the totals from over here by
clearing the contents I'm also gonna go
ahead and get this chunk of data over
here and move it a little bit by cutting
it out of this area of my spreadsheet by
going to home and choosing to cut and
this time I'm just gonna go ahead and
paste it down below and to c16 so I'm
just gonna hit paste I'm gonna go ahead
and remove that extra row by selecting a
row and right clicking and choosing to
delete okay there we go cleaned up our
spreadsheet still looks very basic looks
very plain if only we can style it up in
a way so that way we can let people know
that hey this is the title cell up in a1
and hey maybe these are our inputs or
maybe these are our server names and
maybe these are our headers here on our
spreadsheet Microsoft Excel has built-in
cell styles for you to go ahead and do
that so you can let the user know that
hey these are data values these are
functions these are headers this is my
title
what you're asking you do is you have to
put some Styles on each one of these
cells and you apply them to it to apply
a style it's always done in your Home
tab I just want to expose you to the
Styles command group to where you find
the cell styles so notice how we're over
here in cell a1 go ahead and give that a
click that's a title but it looks just
like text looks regular text I'm gonna
go ahead and get this title cell click
on a-1 and I'm gonna go to the cell
styles and I'm gonna choose to make it a
title cell notice how it looks like a
title now change the font a little bit
change the styling of it I'm going to
the same thing for my headers so I'm
gonna hit enter
and hit enter once more except that at
work you're probably gonna have a data
set that's much larger than this so
you're probably gonna hold ctrl shift
and hit the right arrow to grab your
headers because you might have 60 of
them next I'm gonna go up back to home
go to cell styles and here you have a
bunch of headers that you can specify
I'm just gonna choose heading 1 you're
my first headers in my spreadsheet next
I'm gonna start focusing on the server
names here so I'm gonna highlight them
and you should too
and I'm gonna go pick a cell style for
the servers as well go pick cell styles
keep in mind that it doesn't have to be
these exact cell styles I'm just
choosing these ones I could have chose
heading 4 actually I will choose that
and then choose heading 4 for my server
names
but you can choose any one of them it
just changes the style of that cell and
again if you do type in another word
it's gonna keep in that same style of
the cell we chose to title it we chose
to make a one a tidal cell every word is
going to show up like a title we chose
to make this row over here headers so
it's going to show up like headers now
let's go ahead and do that for each
piece of the data I'm gonna get the
inputs and go up to sell styles and I'm
just gonna mark them as a let's do a 20%
accent of green for Microsoft Excel next
thing I want to do is I want to sell
style these here
that their outputs that there's formulas
here because technically there are
there's formulas and functions to do
that there's a sales style for that I'm
gonna go to home I'm gonna go to sell
styles and I'm just gonna make this an
output cell just like that and this lets
the user know that these are formulas or
outputs somewhere out there so there's
inputs to these outputs now that we have
stale Styles in play go ahead and play
around with the various other cell
styles maybe change it up a little
different than mine just to expose
yourself with the various cell styles
that you have in Excel 2019 and keep in
mind you're feeling a little courageous
you do have the ability to build your
own cell style by picking your own color
scheme in your own font that goes in it
and your own styling but you do have
available cell styles that you can pick
built-in in Excel 2019
hopefully he played around some cell
styles and your spreadsheet looks
slightly different than mine it still
has the same content but it just has a
different look to it
now cell styles are a great way to just
snazz up a spreadsheet really quickly as
you can see oh let's go ahead and cell
style this last one over here I'm gonna
get this section here and I'm just gonna
go up to my home tab and I'm gonna
choose cell styles I'm just gonna make
this explanatory
this thing's explaining the data set
above now that I have this information
in let's all go ahead and hover over to
our page Layout tab so we just styled up
some cells and I want to expose you to
the fact that Microsoft Office has a set
of themes that you can use the page
Layout tab has the set of themes notice
how there's a themes command group now
what is a theme to Microsoft so we're
gonna boil down a theme in Microsoft
Excel to be a unique set of colors that
you can go ahead and pick as well as
unique set of font that you can go ahead
and pick now every office document or
spreadsheet or PowerPoint deck has a
theme it's in the page Layout tab in
Excel when you go to the themes option
here these are the pre-built in office
themes that you have and by default
you're always gonna be on the office
theme notice that we have various other
themes and if you hover over these
because we have cell styles in play
we're gonna see the affected cell style
based off the theme so if I hover over
the facet theme we're gonna see a bunch
of changes there and these are live
previews it's not actually changing the
spreadsheet until I actually click on it
it's just showing me the reflective
change in case I don't want it I'm gonna
go ahead and change that to organic gave
it an entirely new color scheme change
the font up a bit alright so we just
changed the theme I'm gonna go ahead and
do it
two kliks between the closest column to
me to do a best fit and now that we have
the organic theme in I want to let you
know that this is a pre built in office
theme it's gonna be in the themes icon
every single time you go there organic
will always do this effect depending on
what cell styles you have chosen it may
affect it a little differently but
overall we'll give you this organic
theme look nothing is stopping you from
actually modifying this theme a little
further your
notice how colors and fonts are still
there I can change the organic color
scheme to go with the office color set
or maybe the blue-green color sket I'm
gonna do the yellow color set and then I
can also change the font you should too
- maybe kalbarri or Arial font and what
we just did is we just customized the
organic theme now it's really important
if you want this theme to be your theme
and you want to just have all your
spreadsheets have the same look so that
way if you send the data someone knows
oh this is so-and-so spreadsheet or they
have a familiar look with the
spreadsheet so it's easier to process
building a theme will allow you to do
that but it's really important to save
the theme once you customize it after we
do all the customization friends go
ahead and play around with it if you
have to pause the video and see what
other colors and fonts you can make
typically I see people try to mimic
their company colors once you modify it
and you get that look that you want and
you want to keep doing that every time
you build a spreadsheet just go back to
the themes that's in the page Layout tab
and I wanted to show you that the last
option for the themes is called saving
the current theme the theme that you
just built with the unique colors fonts
that you just applied to the organic
theme I'm gonna go ahead and save this
theme because there's a few things I
want to point out as we save it do you
save current theme from the page Layout
tab this little window pops up I'm gonna
go ahead and center it for ourselves
this is an office theme this will work
in other office products because it's
just an office theme file type they all
support that however if you were to save
this theme on your desktop it's not
going to show up here inside of the
software it needs to be inside of the
templates folder under document themes
because Excel only looks in this direct
path and if it has this file type you
can use it here if it's not there it
won't be able to populate it so I'm just
gonna call this the custom organic theme
not gonna change the location because I
won't be able to use it in the software
so I'm just gonna hit save what we
essentially did is from now on our
software has this theme built in because
we go into our page Layout tab and we go
to themes here we now have a new section
called custom that was not there
previously and the custom organic theme
will go ahead and remain there and
that's how you go ahead and add in a
custom theme that you built yourself go
ahead and play around the themes see
make at least one or two themes just get
exposed to it save them and decide the
themes command group try to see if you
can apply them on other data sets but
just make sure that they have some cell
styling in them so you can see some
changes as you apply the themes they
come right back
all right so we just formatted our
fast-food spreadsheet everything's
looking nice and in fact it looks so
nice that I want to use this over and
over again except I don't want to have a
spend time finding this file and
deleting the words out of it every
single time and the data that's
irrelevant so that I can fill it in for
next week so I'm gonna go ahead and save
this as a template so that way when I
click on my file tab and I go to maybe
open a new file I have an option to
maybe open up my template for this
spreadsheet from the new tab here now in
order to do this to build a template
there's some best practice rules now we
have to clear out the data that will be
irrelevant to us next week when we go in
to fill in the data and just to show you
that Monday through Friday sale maybe
Saturday and Sundays as well that will
be irrelevant next week this is gonna be
filled in with other data so I'm just
gonna highlight it and right-click and
clear the contents notice how my
formulas all went to zero
this says division by zero because an
average uses division notice how all my
functions over here went to zero we want
it to stay like that so that next week
when we go in to put in our numbers the
formulas automatically populate with the
correct values and because we set them
up using cell addresses and not the
numbers themselves they're gonna show
the reflective change so the best
practice rule is to clear out the
irrelevant data just how I did I've
cleared out the data so that way our
functions will go ahead and be empty and
ready for next use once you've cleared
out this data we have to go ahead and
save this as a template because we're no
longer gonna save this as an Excel
workbook we need to save it as a
template so we're just gonna go to our
file tab where we save our files and
this time we want to save it as a
template so we're gonna go save as
and instead of going here to an Excel
workbook we're just gonna switch that
expose yourself to the Excel template
and notice how up here it says that's in
the documents custom office templates
path and must be saved in this location
for you to use this template inside of
the new section I'm gonna go ahead and
save this template here it's save now
I'm gonna close out Microsoft Excel
close out the file I'm gonna start it
back up going into Excel
from now on under new here when you go
to new you're gonna have this option to
go to your personal templates this was
not there beforehand and when you go
ahead and click on it
access your template to where you can
just go ahead and put in the data and
fill it in and then save this file for
next week and just give it a timestamp
for this week so templates are a great
way to reuse spreadsheets that you've
spent a lot of time formatting and that
you need to use for certain tasks
all righty in previous videos we went
ahead and built the template I made some
modifications to the template I went
ahead and moved around totals there by
just highlighting it and cutting it out
and moving it away using the cut feature
now I want to just show you a scenario
here because let's say my restaurants
are doing fairly well and I hire a bunch
of servers and let's fast forward five
years from now
fast food is great to chain restaurant
you have tons of servers we're doing
great and we have this massive
spreadsheet like so and we have so many
servers now that when it comes time for
me to come analyze my data become rather
difficult I keep having an issue where I
go down and I'm looking at server 14 I'm
like oh he sold $10 here and then I go
I'm like was this Sunday did he sell
this on Monday then I have to go back up
oh that was Tuesday then I have to go
back down and I keep forgetting what
header am I looking at for the data but
I'm trying to follow well when that
happens and it typically happens that
spreadsheets that you deal with that are
fairly large because you go past your
headers when you scroll there's a way
that you can freeze like maybe freeze
this part of the screen so that you can
go ahead and have it stuck on your
spreadsheet at all times what we want to
do is we want to modify the view of
Microsoft Excel spreadsheet so that we
can go ahead and have the headers stuck
we want to freeze them in the top let's
all go to the View tab in Excel view so
I want to expose you to the option to
freeze your panes freeze a portion of
the sheet to keep it visible while you
scroll through the rest of the sheet
that sounds exactly like what I want it
to do
freeze panes has three options I'm just
gonna go run through the two simple
options first
you can obviously just freeze the top
row notice how a solid white line got
presented when I selected that option
from the View tab and when you scroll
down you're gonna see that the top row
is frozen not really useful for me in my
case because I have my title there but I
just want to show you that option let's
go back to freeze panes in our View tab
and choose to unfreeze that pane now now
the next option that you have that
simples you this freeze your first
column you click that option now all
your server names are frozen
you just analyzed across you do this
with you have a lot of columns to deal
with I'm gonna go back into my View tab
and I'm gonna unfreeze my panes
now what if we wanted to go ahead and
just lock up the first three rows if you
were watching the videos for inserting
rows and inserting columns you learned
that if you select a row the insert
shows up above same with frozen panes
it's the same rules
if you select row four and then you go
up here into your debut tab and select
freeze panes what you're asking to do is
freeze everything above row four so it's
frozen above the selection I'm gonna go
ahead and width row four selected go up
to my view tab and select freeze panes
and choose the first option freeze panes
now you're gonna see a solid line on the
third row and when you scroll down your
headers are stuck now for columns I'm
gonna go ahead and unfreeze my rows here
unfreeze let's say you want it to have
server name and their monday sales
available it's the same rule for
inserting a column so if you insert a
column you select column C it shows up
to the left same with freezing panes
everything to the left of the selected
column will be frozen I'm gonna go ahead
and show you that option here by going
back to my View tab and choosing the
freeze panes notice how there's a solid
line over here and when you scroll past
it just server and Monday are frozen now
I bet your minds are thinking hmm what
if we want the server and your headers
frozen what if you want this top part
frozen and I think it's gonna be useful
to see your server names frozen to when
we need to do that we're not gonna
highlight a search in row we're not
gonna select a search in column we're
just gonna select a search and cell
address that cell address should be the
intersect of where you want it to go
ahead and be frozen that so if I want
those two rows frozen I would select
here and it frees this area as well as
this area
if I wanted to maybe go ahead and have
Monday frozen and maybe have an extra
row included I'll probably include it
over here and what that's going to do is
going to include that extra row and it's
going to just block us off from here and
as well as here so we just picked the
intersect and then once we have the
inter set selected so I'm going to go
ahead and pick this intersect to have
just the first column in the top three
rows I'm gonna go back to freeze panes
and when I select freeze panes you're
gonna notice when you scroll down the
headers are frozen but when you also
scroll to the right your column a is
frozen
so the frozen column will always be to
the left the frozen rows will always be
above the selection and you can select a
frozen row and a column at once by just
picking the intersect of where you want
it to be frozen at go ahead and run
through the various ways that you can
freeze panes and play with that and come
right back
hopefully you had some fun freezing some
pains and unfreezing them and seeing the
various ways you can affect the view of
your spreadsheet but now that we have
that down I want to just quickly copy
this sheet because I realize I'm going
to open up another store so I'm just
gonna make another copy of this sheet by
going down to the sheet name and right
clicking and choosing to copy the sheet
I'm gonna make a copy before
sheet2 and i'm gonna choose you create a
copy and hit okay and just like that I
have a copy of sheet one and a numerical
assess too but let's say we have two
different sheets here and we have them
fully built out and they're all nice and
let's say we might have even ten of them
but for some reason we forgot to add in
the date in our sheets and we needed the
date and now we're sitting there and
adding them into each sheet we could
have maybe ten sheets and in my scenario
we have two if only there was a way that
we can go ahead and put these sheets on
top of each other so that everything we
write will go ahead and bleed onto the
other sheets
luckily Excel has a built-in function
just like that we can go ahead and put
these two sheets on top of each other
kind of have a carbon copy built
essentially and then write down on one
sheet and then everything will bleed on
to the next sheet kind of how that date
field that we want on both sheets in
order to group sheets together let's say
I want to group this sheet to this sheet
here all you have to do is hold ctrl and
shift and hit page down it will have
group these two sheets together you'll
know the sheets are group when up top
and your title it will say grouped up
top you also see down here when you do a
right-click it will say to ungroup the
sheets implying that they are grouped if
you were to click away on another sheet
it would also ungroup the sheets you can
have an infinite amount of sheets
grouped what it does is it stacks them
on top of each other and everything you
write within the sheet will go ahead and
bleed onto the second one so that date I
want to go ahead and put in the date
here I'm gonna go ahead and do that 12
21 18 and hit enter what the sheets
grouped and earlier I said if you click
on another sheet it will ungroup the
sheets I'm gonna go ahead and click on
sheet 2 now according to what I said
about grouping the date should be on
both sheets now and I only wrote it once
I'm gonna hold ctrl and hit page up to
check the first sheet so there's the
date inside of K 2 I'm gonna hold ctrl
and hit page up and show you that inside
of K 1 or K 2 inside of sheet 1 we also
have the date and we only wrote it once
so quick edits on multiple sheets
go ahead and group the sheets together
and make that its then and you'll know
that they're grouped once up top you see
the word go ahead and group some sheets
add some data into the sheets together
and see if they'll go ahead and populate
on both sheets come right back
excel we made a spreadsheet and we
expose ourselves to the fact that we can
lock certain pieces of the spreadsheet
with our View tab using freeze panes and
now I want to let you know the entire
time we've been playing around excel in
our View tab we've actually been in the
normal workbook view of excel there's
another view that's very useful it's
known as page layout
let's all go ahead and go to the page
layout for you over here of excel page
layout and give it a click it will show
you how the document will be printed I'm
gonna give that a click page layout so
this will give me a breakdown how the
documents gonna look when it gets
printed on sheets and you can actually
move things around in order to organize
them to print out on our appropriate
sheet so for a good example of that
let's try to move that date into the
first sheet when we print kind of like
next to the title maybe so we're gonna
go ahead and notice how the dates all
the way on the third sheet I'm just
gonna get this date go to my home tab
and you should too and just cut out the
date from here and then scroll on over
to the third sheet
and then go ahead and paste the date
there and I'll go ahead and be on the
first sheet when we print another very
useful reason to know about page layout
is it's super easy to add in a header
you just go up here and type in your
header and then when you're ready for
the footer you can go and scroll on down
and add in a footer
and just like that adds it to each and
every page and you can go ahead and
format it the way you'd want it so the
page layout view is a great way to go
ahead and organize your data before you
print it go ahead and play around with
the page layout view move the date just
how I did and just expose yourself to
see how it's going to print once we go
ahead and print it and then go back into
the normal view of Excel
in Excel sometimes you'll be analyzing a
spreadsheet and a lot of people will be
using this spreadsheet but only certain
pieces of it pertain to that person for
example the CEO might only care about
maybe the totals of everything your
operations team wants to go ahead and
see a list of the servers and not
necessarily see all the data now you can
go ahead and build separate spreadsheets
for each one and keep track of them or
you can customize this spreadsheet to
have certain views to maybe show you
just the server list and maybe have a
view to show you just the totals all
this can be done in the View tab by
adding in some custom views let's all go
expose ourselves to the View tab and go
to custom views and give it a click and
when you click on custom views it's
gonna be empty because we have yet to
add a view let's add in our first view
by choosing add in our first view let's
go ahead and label it as their original
meaning this is their original view with
all the data and I'm gonna include to
leave all my print settings that I've
chosen in this view and I'm gonna
include to leave all the hidden rows and
columns and filters for this view and
hit OK now that I have that view in play
my original view with all the data I'm
gonna go ahead and build that server
list by maybe highlighting all this data
here right-clicking it and maybe hiding
this column and then I'm gonna go back
to custom views let's all go back to
custom views and this time let's add in
a view known as server list
and just hit okay now at any point in
time if your operations team needs that
server list you can just go ahead and
let's say we're in their original view
and we're seeing this data here
operation team's emailing you for the
spreadsheet with the list of servers all
you need to do is just go back to your
View tab go into custom views and just
choose the server list view and hit show
and send this over to them and they'll
just have the nice piece of data that
they want to look at and not the whole
lot but if they do need to see the whole
lot it is hidden and they can go ahead
expose themselves to it if need be go
ahead and play around some custom views
and I build at least one view for
yourself like I did and the power of
this is now we don't have to go find
different spreadsheets and different
workbooks for the same information it's
all in one spot
before printing a workbook it's a good
idea to run a spellcheck on the workbook
let's all hover over to our review tab
in Microsoft Excel to expose ourselves
to the proofing command group to where
the spell check can be found the
keyboard shortcut is f7 so if you
memorize that you can just click f7 and
the spell check will pop up
but how spellcheck works in Microsoft
Excel is it first goes on the cell
address that you have selected and it
spell checks that and then it goes
through the entire spreadsheet so with
that being said I'm gonna first go ahead
and select column a because I know this
is the column that doesn't have names or
abbreviations because those will show up
as Anna spelled words and then I'm gonna
select spell check what's spell checking
I'm gonna go ahead and show you this
window here I have the ability so go
ahead and ignore a word a misspelled
word I could ignore it every time it
pops up or I can add that word to a
dictionary so I have an employee list
with weird names that keep popping up a
spell check I can go ahead and add it to
the dictionary so they're no longer
misspelled words or I can go ahead and
change this current misspelled word to
the suggestions that they give us or
maybe change all of them by clicking one
click here I'm gonna go ahead and click
server here and hit change and then
obviously you found another one I'm
gonna go ahead and hit change and just
like that spell checking is complete and
you're good to go so always go ahead and
be sure to run a spell check on your
spreadsheets and pick columns that you
know have misspelled words or they do
not have names and abbreviations because
those will have a spelling error but if
you do have to deal with those
abbreviations and names commonly go
ahead and add them to your dictionary so
you no longer have to deal with that
all righty now that we spell-checked our
workbook and we made it look pretty with
themes and cell styles it's coming time
to print out our workbook I just want to
run through the various ways we can
print out a workbook in Excel the first
one being let's all go ahead and select
up to server for include all their data
up to server for so just a server for
here and with this selection let's all
hold the ctrl key and hit the letter P
and we are now on the print page of the
backstage view really quickly it's all
go hit the back key here and that was
the shortcut way of printing let's all
click the file tab and just go to print
and ctrl P just did all that for us the
reason why I bring up printing is
there's various ways to print something
in Excel the first thing I want to point
out is notice how you have settings to
print and I first asked they're going to
highlight that area before we go and hit
ctrl P the reason why is because you
have the ability to print just the sheet
that you're looking at the entire
workbook and all of its sheets or maybe
just that piece that you had highlighted
because it's so large that you only need
that piece and when you go ahead and
hover over to print selection I'll go
ahead and change it to just that list of
people that you would like now a lot of
people complain that I have a hard time
printing sheets all in one page you have
that route of picking the things that
you need and putting them on a page by
printing the selection another route
that you could have had is maybe you can
switch it from portrait over to
landscape and what that does is it gives
you a few extra rows didn't do it all
but it made it work another route that
you could have took is if you so happen
to have a printer that takes multiple
page sizes at work you can check to see
if another page will print out the whole
page in one sheet now if all that
doesn't work you're gonna have to result
to scaling now scaling works but it
might not look exactly how you want it
when you scale something you can fit it
all onto one page meaning shrink it as
much as you can just to fit on one page
there we go it shrunk it may not be
readable when you print that all the
columns onto one page so if you have an
extra column or two try using this route
it may work out for you but again might
not
show up in the print looking pretty good
though fit it all one column now if you
have a hundreds of rows you can fit all
the rows onto one page over here I fit
all the rows onto one page here not that
many rows for us just 32 servers except
the columns got cut off now one thing I
want to bring up is don't change the
margins past your printer settings
because if it shows the margins go out
here your printer may not print out
there and you'll waste a bunch of ink
but those are the various settings that
you have to print your spreadsheets or
your workbooks or the selection on the
spreadsheet hopefully this was helpful
thanks for watching don't forget we also
offer live classes and office
applications professional development
and private training visit learning com
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