- [Voiceover] Hello, and
welcome to Excel 2016. My name is Sean Bugler and
I'll be your host today as we go through all of
Microsoft Excel 2016. Whether you're a beginner, advanced, or you've been using this
since the day you were born. Microsoft Excel has
something for everybody, and as we go through
these lessons I'm sure you're gonna find that there are features even you didn't know were available. Let's go ahead and get started. When you first open up
Excel for the first time this is the view you're presented with. This is called the start
screen and this is where we have access to any recent documents that we may have worked on in the past. Additionally we also have
access to the ability to open workbooks from other sources. Beyond that we also have
access to a template view. This template view is
where we have access to blank workbooks, additionally
in newer versions of Excel, we're also provided with a tour field. So if this is the first
time that you've ever opened this program and you'd like
to see what new features are available this is generally
the best place to find it. Additionally, we have access
to a number of templates provided by Microsoft that allow us to work with files that have
been created by others. This is a really quick and
easy way to start very quickly. Additionally, at the top
we can search through online templates, for
instance, maybe I'd like to see a grocery list. And you'll see that not
only is there more than one there are a couple and they look very different from each other. A monthly meal planner, that
might be something I need. To get back to the templates
view if you do decide to use the search feature,
we're gonna use the Home button. But before I do, I'd
like to point out that there are a number of categories, so if you're not entirely sure what it is that you're looking for but you'd like to narrow it down just a little bit, you can always use the category
selection field as well. So let's go ahead and dive in. I'm gonna go ahead and
start with a blank workbook. To select it I'm simply going to click and it's going to dive me right in. I know the inclination
is to do a double-click but in 2016 that's not necessary. And with that we are plopped straight into an Excel workbook. Go ahead and pause the
video for just a moment here and join me in the blank workbook view. So let's talk about
the structure of Excel. If you've used Excel in the
past, not a lot's changed, we still have access to the ribbon. For those of you who aren't
familiar with the ribbon it's this giant part of
the screen right here. Often considered to be
our navigation pane, this is where we have access to all of our tools and feature sets built-in to Excel. So within that we have Home, Insert, Page Layout, Formulas, and so on. Inside of each tab we
have access to something called command groups,
these are small groupings of tools based on one
of two sets of criteria. Either they manipulate similar content, or they do similar things. So either they work with
some of the same subjects, or their actions are all very similar. For example, we have the Clipboard, which is exclusively focused
on cut, copy and paste. Beyond that we have access
to the Font command group, whose explicit purpose
is to manipulate text. As we go on you'll see me
reference the command groups quite a bit, and as far
as learning Excel goes it does us well to remember
these command groups are often named very intuitively. So for instance, focusing on
the Alignment command group I could reasonably ascertain that we're probably going to be focusing on left-align, right-align, and so on. This is what's going to allow us to work with the alignment of the text. Hence, the Alignment command group name. Each command tab has their
own series of command groups. For my 2013 users, not a lot's changed, frankly there's not much
at all that's changed. As we go through you
might notice a few things here and there, but don't
worry if you've invested a bunch of time in learning older versions of Excel you're gonna be just fine. The next thing I'd like
to talk about is something that we've seen pretty
much the entire time we've worked inside of Excel but haven't given a lot of thought to. This can be found up
in the top left corner, we usually know it as just the Save icon, or the Undo icon, because that's generally all we've used it for but
it actually has a name. This is called the Quick Access Toolbar. The Quick Access Toolbar
provides us access to exactly that, a series of
tools based on our needs. If you click on the drop down arrow on the right-hand side of this, you'll see a list of provided tools; New, Open, Save, Email and so on. If you're really observant you'll notice that these checkboxes that already exist directly correspond
with the tools that are already available in the
Quick Access Toolbar. Save to save, Undo and
Redo, to undo and redo. So logic dictates that if I add a checkbox to a tool that doesn't have
a checkbox what will happen? If you said it will add it to the Quick Access Toolbar, you'd be right. If a click New, voila. A tool to create new
workbooks has just been added to my Quick Access Toolbar. Now I hear what you're saying, Sean I already know the
keyboard shortcut for new, I don't need that. In fact, I don't need any of these tools. None of them really strike
me as something that is imminently necessary to the point where I would need to put it up here. Well I hear what you're
saying, for my users out there who are curious to know
what the keyboard shortcut for a new workbook is by
the way, it's Control + N. But again, I hear you, these
might not necessarily be the tools that you need quick access to. But there might be a tool that you do. For example, you might notice in the Font command group here,
there's a blatant omission here, at least according to me. Of course I'm talking about
the strikethrough tool. I do a lot of collaboration in Microsoft Excel and you might too. The strikethrough feature
is a really useful tool. Or maybe there's another tool that you use all the time that you'd like access to. But if I click on this drop down here, I don't see access to strikethrough, but I do see More Commands. If you notice on the right-hand
side of More Commands you'll see that there's an
ellipses, that dot, dot, dot. That dot, dot, dot, simply
means that there is more behind the scenes,
there's a dialogue window. So I'm gonna go ahead and
click on More Commands here and I gain access to a series of tools and on the right-hand side I have my tools that are inside the Quick Access Toolbar, my Save, my Undo, my Redo, and so on. Now I think we can all agree
that we don't need New File. So to get rid of that
I'm gonna highlight it and either use the Remove
tool, or double-click. Either way will remove it from
your Quick Access Toolbar. Go ahead and pause the video and let's get to this point here. Once again to remind you how we got here, we were inside the Excel blank workbook and we navigated up to the drop down arrow on the right-hand side of
the Quick Access Toolbar. Having clicked on that we
navigated down to More Commands. Go ahead and do that now
and join me after the break. So now what I'd like to
do is I'd like to find a tool to add to my Quick Access Toolbar. So we'll go ahead and
stick with the example I gave previously, I'd
like to find Strikethrough and add it to my Quick Access Toolbar. So I'm gonna go ahead
and scroll down here, it seems like it's alphabetized so this should be pretty easy. And I get down to the bottom here, I've got my Save, Save
As, Set Print Shapes, but no Strikethrough, well what gives? I thought this instructor knew
what he was talking about. Did you figure it out? If you said it's because we are inside the Popular Commands drop down here you'd be absolutely right. Despite my objections,
Microsoft doesn't agree that Strikethrough is
considered a popular command. And in fact there are a lot
of tools that don't meet the criteria for being
considered a popular command. One of them might also be the tool that you're thinking of
that you'd like to use. So to fix that we're going to click on this drop down arrow here, and we're gonna go to All Commands. This is a list of every single tool inside of Microsoft Excel
and there are a lot. How many you ask? Not a clue, a lot. In fact most of them you'll
never use and that's okay, because this is about
finding the tools we do use. In fact we're finding the
tools that we use a lot. So let's go ahead and find
that Strikethrough tool. Now this scrolling is
taking a long time here, so I'm actually just going
to click inside this list and press the S key, now
at this stage right here you can't type out the
full name of the tool. I know that would be nice,
unfortunately that functionality has just not been added yet. But it does take me at
least to the S section. And I'm looking, and I'm
looking, and Strikethrough. Go ahead and find Strikethrough
from the All Commands list, and I'd like to add it to
my Quick Access Toolbar now. So either by highlighting it
and clicking the Add button on the right-hand side here,
or by double-clicking on it let's go ahead and add it
to the Quick Access Toolbar. A common question that
I often get asked is, let's say I do set all this
up, is it going to be available in all of my workbooks or just this one? And the answer is it's
gonna work in all of them. For as long as you have
this version of Excel and you're on your computer these tools will be available to you. If you'd like to reset it at any point, let's say you add 40
tools, you can always click on the Reset button on
the bottom right here and select either Reset
Only Quick Access Toolbar or for those of you who go
crazy with it and decide to customize the ribbon as well, we can Reset all Customizations. Now I'm not gonna do that
because I want Strikethrough. But know that that's available for you. Once you've found all the
tools you'd like to add to your Quick Access Toolbar
go ahead and click OK. And just like that I have
access to Strikethrough. How easy was that? Go ahead and pause the
video and get to where I am. Once again to reiterate,
in order to customize the Quick Access Toolbar we clicked on the drop down arrow right here, we selected More Commands, we found the tool and if it's not inside the Popular Commands drop down we now know we can change that to All Commands. From there we find the tool
in this giant list of ours, and either highlight it
and select the Add button or double-click to see it added
to our Quick Access Toolbar. And lastly we click OK to lock it in. Go ahead and pause the
video and do that now. All right, so we've customized
the Quick Access Toolbar it feels good to make the program a little bit more ours, doesn't it? In fact Microsoft provides a lot of tools that allow us to customize different parts of the Microsoft Excel interface. And we'll talk a little bit more so about those in just a little bit. But before I do, I'd like to dive into how we can make changes to the file. Up until now we've been
referencing tabs that are to the right of the Home tab. These are tabs where we can
make changes within the file. However, there's one
tab out of all of these that is slightly unlike the other. In this case I'm talking about, File. Go ahead and find the File tab
and let's click inside of it. And with a swoopy animation we dive into what we like to call the backstage view. This is where we can make
changes about the file. All other tabs inside of Excel
make changes inside the file, but this is where we can see the grand scheme of things if you will. The very top we have access to Info, where we can protect our
workbook, inspect our workbook, manage our workbook,
and even view options. As we go throughout the
lessons inside of Excel we'll talk a little bit
about more of these. Inside the New view here,
we see exactly the same view we saw inside of our start
screen, our templates. Of course we can always
select our Blank Workbook and yet again we have
access to our Search Bar so we can search for online templates. The Open tab does exactly
that, it opens any workbooks that we may have available to us. And in 2016 it's easier than ever to open from online storage solutions. In this case I have several
OneDrive accounts connected as well as a share point
site for those of you who operate inside share
point environments. Additionally I also have access to files on my PC and if I'm not
sure and I would just like to get to a more familiar view,
I can always click Browse. And that will bring up my open view. Additionally I have access
to Save and Save As, however because I have not
saved this file yet at all, I can't use the Save button. For those of you who aren't
sure what the difference is Save As creates a new copy of a file that you may have already saved before. Save on the other hand only works if a file has already been
saved and already has a place that you can save it to. If you've never defined a save point, Save won't work and it will force you into the Save As view as you
can see by my clicks here. No matter how many times I click on Save it forces me into the Save As view. Print brings us down into
our print preview field, where we can make a number of changes before we select the final Print. Inside of Share we have
the ability to work with a number of different features here, and this is kind of
cool we've actually seen some expanded options. We now have access to something
called the Share with People now there's another place
that we're gonna have access to this but I'd like to point out here that before we can do anything
we have to save our document to a OneDrive location,
once we've done that, we'll be able to do just
that, share with people. And we'll talk about that
in just a little bit. Within Email we can send as an attachment, for those of you who have Outlook set up or another default file email solution, clicking this button will open up Outlook, open up a compose email
and attach that file all in one click, and
that feels pretty good. We additionally have the ability to send as a PDF or XPS file. Additionally, we're seeing a resurgence in the Present Online feature. This allows us to present this program in a Skype meeting or conversation, for those of you who are
working in environments that are going to be engaging
in Skype for Business this is going to be a
really powerful tool. And lastly, we have
Send by Instant Message. So for all of you who work
in corporate environments if you've got Skype for
Business already interacted you can actually save this
via Skype for Business or for those of you who are more familiar with the older version of this, Lync, to anyone on our network. Everyone gets a copy and
everyone gets an instant message. Underneath Share we have Export. This allows us to either
create it as a PDF or XPS file or change the file type to a number of different options found here on the right. Where else do we have access to this? Well funny you should ask,
there it is right there. These are all things that we can do inside the Save As dialogue box as well. Underneath export we have Publish, which is exclusively focused on Power Bi, something that has newly been implemented out of the box inside of Excel 2016. Underneath Publish we have Close, which will do exactly that,
don't click on this just yet. Underneath that we have
Account where you can see information about the account
here and interestingly enough we can also change the theme here. Now what does that mean
to change the theme? Well, I'll show you, this
isn't the best view to do it but I will show you shortly. You can also see any Connected Services that might have been placed here. Lastly let's talk Options,
go ahead and find Options on the very bottom here and click on it. This is where we can
change a lot of deep down features inside of Excel. Things from the default file font, to where these files get saved, but this is also where we can change the Office theme, and
this is where we come back to the ability to customize
this program to our liking. In Excel 2016, and in
fact, all of Office 2016 we see that these programs have gotten a new colorful option. Now this theme is being provided across all of those programs but we do have the ability to change that. Currently we find here in the Office Theme that we can change it form Colorful to maybe a more familiar view. If I select the White
office theme and click OK all of my Excel 2013 users are going to breathe a sigh of
relief, because this is a much more familiar view. The File tab now has its
distinguished coloration the remaining tabs do not. Additionally we've been
provided one more theme because two wasn't enough,
we have the Dark Gray theme and this one is brand
new to Microsoft Excel and all of the Office
productivity programs. This provides a dark user
interface for the very first time, and it's getting rave reviews from users who prefer a dark user
interface whether because the white is too bright or
because they need something that's a little bit more
calming to the eyes. The dark user interface
is a really cool feature that's been built-in new to Office 2016. Personally, I prefer the Colorful theme, so I'm going to stick
with that for the duration of this presentation but do know that you have these options available. Go ahead and play around
with those options here for just a moment and let's
join us back after the break. Once again to get into that view, we jumped into the File, Options, and within Options we had
access to the Office Theme we also had access to the Office Theme inside the File, Account, where we can change the
Office Theme here, as well. So however you'd like to do
it, go ahead and try that and come back after that. So now that we've seen a little bit about what's new inside of Excel, let's talk about some things
that have stayed the same. On the top left-hand
side we have access to our Cell Map field here. This tells us where our active cell is, so for instance currently it's in cell A1, and amalgamation of the column value A and the row value 1, A1. However, if I place my
cursor anywhere else I can see exactly where that is without having to make that juxtaposition of D and 9 in this case. I can simply look up here
in the top left corner and see that I am
currently inside cell D9. Beyond that in the right-hand
side I have the Formula Bar. The Formula Bar is where I can manipulate things going on inside of that cell additionally I also have access
to my Insert Functions tool which we'll talk about in a little bit. On the right-hand side we
have access to our trusty Scroll Bar this is where
we can scroll up and down and if you'd like to,
additionally right and left using these scroll bars found to
the right and on the bottom. Simply by clicking and
holding the Scroll Bar we can do that, sliding
to the left and right, using the bottom Scroll Bar or up and down using the right Scroll Bar. Additionally I can use these
arrows to expand or contract my view in the upward
or downward direction, or the right and left
direction as necessary. As with all Microsoft programs in the top right-hand corner I have
access to my trusty three here; Minimize, Maximize or Restore, depending on the view you're in, and Quit. Additionally to the left
we have a new button here the ability to collapse our ribbon view. So for instance if you're
working on a screen that is less than the
size you'd like it to be and you need more working space, you can select the Display Ribbon Options and auto hide the Ribbon. This completely removes
the Ribbon from view and when we need it we
click on that Ellipses that pops up at the top and
we gain access to it again. Once again, if you're in this
view, simply find the Ellipses and give it a click and it
will reveal itself to you however to lock it in
you're gonna need to select that same tool again and deselect, by selecting the Show Tabs and Commands. Go ahead and take an opportunity
to play with those features for just a moment and come back to me. Now no matter what version
of Excel you're coming from, there are two things for sure
that you had to have noticed that are very different from any other version of Excel, ever. And we'll talk about both
of them but let's go ahead and start with this one right here. Because we've already
seen an example of where this tool also exists in
other parts of this program. This is called the Share button. The Share button allows
us to do just that, share the workbook we're in to encourage collaboration or to
submit a final product. Go ahead and click on the Share tool, and you'll see that the first
thing it asks us to do is save a copy of our file
to an online location, and it provides us with
a button to do just that, Save to the Cloud. In order to use this tool you
do need to save to the cloud. So I'll go ahead and do just that. I'm gonna click on Save to the Cloud here and it's gonna ask me
where I'd like to save it. And of course it provides my Save As menu, which we've seen already. I'm gonna go ahead and save
it to my OneDrive account. And maybe I'll go ahead and
call this Excel 2016 Training, and I'll go ahead and click Save. And there it is, and just like that I've saved it to the cloud. Once I've saved it in the
cloud, I'm provided with new share options, I can invite people, and depending on what it
is that I'm asking for I can control how they
have access to this file. So depending on who it is I can ask them to view the file and edit it or maybe I'd only like them to view it and give me their thoughts in a separate medium. I don't necessarily
want them to have access to my functions and formulas,
or any of my raw data to mess up or accidentally delete. Lastly, I can create a message asking them for either general assistance
or maybe more specific things. At the very bottom here we have access to anybody who has already
been shared this file. Currently, I'm the only
one and you can see as denoted by the Owner tag
that I'm the only person that has access to this because
I'm the one that created it. Once you've invited
somebody you simply click the Share button and
once they've accepted it you'll be able to see their name populate on the list down here below. Go ahead and close that. Now we've almost come
to a close on our tour of the interface of Microsoft Excel 2016, but there's one part of the Ribbon left that we haven't talked about
yet, and it's the biggest one. In fact, it's the biggest
change to ever come to the Microsoft Office Ribbon since the introduction of the Ribbon. It's called the Tell Me
bar and it's indicated by a light bulb followed by the phrasing, tell me what you want to do. Now I know that sounds a little vague and a little too open, but that's because this tool is so powerful
that that's all it needs is for you to talk to it. For instance, let's say I don't remember how to insert a column. So in this field right here,
I'm just gonna go ahead and click inside and say,
"How do I insert a column?" And check it out, the
tool that pops to the top of the list is the Insert
tool and it's not just what that tool is, oh lost it there, but if I hover over it, check it out. I can either insert individual
cells, I can insert rows, or there it is insert
entire columns or sheets. If I give it a click it doesn't
just show me where it is it actually inserted an
entirely new column for me. This is a game changing
feature because there are so many tools inside of
Microsoft Excel there's no way you're going to remember
where all of them are. And for the times where you don't remember you don't have to Google it anymore. You don't have to Bing it
if that's your thing too. All you have to do is go
to the Tell Me bar and say, "Hey, how do I add comments?" And there it is the
Insert Comment feature. The Tell Me feature is a huge introduction into navigating this
increasingly complex program. And while Microsoft just made huge strides in terms of making this
a more usable program, not everybody is going to
remember where everything is. And that's okay and in
fact it's easier than ever to not remember with a tool like this. Go ahead and take a moment
to try it out for yourself. The Tell Me bar it's a
huge introduction into Microsoft Excel navigation and
it could potentially change the way you interact with
this program entirely. All right, so while we're on the topic of new features being introduced into Excel 2016 let's not stop there. I've got one more amazing
feature I'd like to show you. Even cooler than
potentially the Tell Me bar. Inside this worksheet right
here I've got access to three cells with some content
in it I might not understand. For instance, YoY, now
why oh why would I put that inside of a worksheet? Well why oh why would anybody? Well it's because it means
something to some people. And you might not be entirely
sure what it means either. Now where do you turn if you
don't understand something? Well in 2015 most of us Google it. However, with the introduction
of this new feature that I'm about to tell you
about Excel doesn't need you to jump out of the program
in order to look it up. Go ahead and find a cell, or in this case, in your worksheet type the
words YoY, or the letters. Once you've done that go ahead
and give it a right-click and there's a new tool
that's been introduced into the right-click sub menu. It's called Smart Lookup, so
having highlighted that cell find the Smart Lookup
tool and click on it. And we get a new pane
called the Insights pane to pop up here. And just like that it's identified what this potentially could mean. In this case here, YoY may
refer to the Yoy language however more than likely inside of a spreadsheet that's not the case. As we go a little bit further we see that it's potentially an
abbreviation for year-over-year. Which is an easier way of
identifying how we compare last year's performance to
this year's performance. The Insights pane provides
us with not only definitions but it'll do a web search
to provide top links. Here we go here, Investopedia. So now we get a full
break down of what exactly YoY is if this is exactly what
we're looking for and it is. Let's try another tool, I'm gonna go ahead and
right-click on FY here and I'm gonna try a Smart Lookup again. Now in this case here,
my first two options, the FY postcode area and
Fengyun aren't necessarily exactly what I'm looking for, so let's scroll down a little bit further and see if we can find
some relevant context, and there it is right there. The very top web search a fiscal year, so now I can see that some
of these are finance terms. Let's try one more, Satya Nadella, now a lot of people have heard this name but may not necessarily know
where they've heard it from. Once again, this time
actually instead of doing my right-click and Smart
Lookup, I'm gonna go ahead and navigate up to the Tell Me bar. Now I know that the Tell
Me bar can tell me where these tools are that are available to me, but what happens if I type
something that isn't a tool? Maybe something that I'd like to search. Once it's identified
that I'm typing something that isn't a tool it's
either going to provide me help or offer to do a Smart Lookup on it. So I'm gonna do that. And there it is, Satya
Nadella CEO of Microsoft. It's provided his Wikipedia,
it's also provided a Bing image search,
if there was any doubt about what the web engine
backing all of this was search no further, of course
it's provided by Bing. This is Microsoft finally
stretching its muscles a little bit in terms of
the experience and data that it's collected with its Bing search engine over the years. So there you have it, once
again, to use the Smart Lookup feature simply find a
cell, right-click on it, and select Smart Lookup,
or from the Tell Me bar simply type what it is you're not sure of in our case, Satya Nadella,
and select Smart Lookup from the drop down menu. Go ahead and pause the video
to check that new feature out. There's a couple of other
fundamentals within the Excel interface that we should talk about before we move any further. The first one is really important, in the top left-hand side
you'll see that we have A1 listed in this little box here. Now this isn't a cell this
is actually telling us what cell our current active selection is. You'll notice that I
currently have A1 selected. This is the Name Box,
the Name Box tells us exactly where our current
active selection cell is. So let's say I click into cell C3 you'll notice that now it's changed to C3. This is to allow us to know exactly what cell we have selected no matter where we are in the spreadsheet. While it's relatively
easy when we're talking about A1 or C3 when we get
a little bit farther out like for instance here in say P14 it's a little bit more tedious to have to go all the way to the left to
see that we're in row 14 and then all the way up to the top to see that we're in the column P. So using the Name Box
makes a big difference. As we get a little bit
further, you'll also see when we have certain fields selected if they're named ranges
or tables the Name Box will tell us the names
of those fields as well. To the right of that we have
access to the Formula Bar. The Formula Bar not only
tells us what formulas are happening behind the
scenes within a cell, but it'll also just tell
us what's inside the cell. So for example here if
in cell A2 I just type the number two, the
Formula Bar tells me that that's exactly what's
going on inside the cell. However, if inside the
cell I have one plus one you'll see that while the
cell itself does say two, when I select it the Formula
Bar actually tells me what's really happening behind the scenes. It's calculating one plus
one, so the Formula Bar allows us to see behind the
looking glass if you will, to allow us to understand better what's going on inside
of our spreadsheets. This is also where we can
make changes to the cell, so instead of double-clicking
on the cell to make changes we can click inside the
Formula Bar and let's say that I'd like this formula to be one plus two, once I hit Enter there, you'll see that the cell itself changes as well. The Formula Bar allows us
to make widespread changes without having to bury ourselves within the actual cell spreadsheet. This kind of keeps us a
little bit more focused on the formula and not the content
happening around the cell. On the very bottom of our
sheet we have our Status Bar our Status Bar of course
has existed since 2007. This tells us whether or
not we are in the middle of using a tool or
whether or not there are instructions that we
need to be made aware of. Currently Excel is ready to go. If you right-click on the Status Bar you can add additional features here. For example, maybe I'd like to see whether or not I can Flash Fill, or maybe I'd like to see whether or not signatures are available
in a particular field. This allows me to make a
number of different changes without having to think
too hard about where to go. On the bottom right-hand
side we have access to our View Fields so I can change between my standard view, I can change
to my print preview view, I can change to a number of
different setups within that. To the right of that I
have my Zoom feature here, where I can use my minus or my plus sign, to increase or decrease my
zoom level of the spreadsheet. Now here's a fun little trick within that, if you press and hold
the Control key and use the scroll wheel you can zoom in and out using that feature set as well. In fact that's generally
my preferred method of zooming in and out. And lastly, on the right-hand
side we have our scroll bars now of course our scroll bars
allow us to do exactly that scroll throughout the spreadsheet, either by clicking and
holding the scroll bar and dragging up and down,
however you'll notice that it only let's me go down to row 21, however if I use that downward arrow to keep clicking it will
take me ever further. Additionally, the same
methodology applies to my down scroll bar here allowing me to scroll from left to right, however once again, you'll see that it only
lets me go as far as column R unless I use my arrows, in which case it allows
me to go even further. So there you have it. Go ahead and take this opportunity to try that out and explore the interface a little bit more so before we carry on. There's a lot of different ways to navigate within Microsoft Excel. Of course we've always been
able to point and click to get to the cell we'd like to go to, but it's not always the
most precise methodology to do just that. Alternatively, we've always
also been able to use the arrow keys, so let's say
I'd like to go down a cell simply tapping on the down arrow key allows me to do just that. Tapping on the right arrow key allows me to go to one cell to the right, tapping on the up arrow key
allows me to go one cell up, and the left arrow key allows me to jump to the cell to my left. Now one cell at a time may not necessarily be your cup of tea. For example, maybe you'd like something a bit more extreme that allows you to jump to the furthest reaches of the data set. Do you happen to know how many rows are inside of Microsoft Excel? Well you can find out,
by pressing and holding the Control key and then
tapping the down arrow, you'll see it flings us all the way to the furthest reaches
of Microsoft Excel. In this case we see that
the furthest row possible inside of this program is row 1,048,576. Tapping on the Home key or in this case Control + Home key, allows me to go back to the original cell that I was in. Were you curious to see how many columns are inside of Microsoft Excel? Well we saw that the
right arrow key by itself only takes us one cell to one cell, but pressing and holding the modifier key the Control key and then
tapping the right arrow key flings us all the way to column value XFD, that's pretty far out. Hopefully that's enough cells for you. Once again I'm gonna press and hold the Control key and tap the Home key, and this is going to
bring me back to cell A1. So a couple of different ways to navigate within the spreadsheet,
additionally we're able to select cells in a
number of different ways either using the mouse
by clicking and dragging, but again this might not necessarily be the most precise methodology. We saw that pressing and
holding the Control key enhanced our arrow keys,
what about the Shift key? Well if you press and hold the Shift key and use the arrow keys,
you'll see that I'm able to select multiple fields of cells. Now I'm tapping the down
arrow key in this instance, and once I've done that let's say all the way down to row 10, let's say I'd like to
expand outward to column F. So I'll tap my right arrow key twice, and you'll see that it selects
the values as necessary. All really quick and easy ways to navigate throughout the Microsoft
Excel spreadsheet. Go ahead and pause the video
and try that for yourself. We're starting to see that
pressing and combining keys allows us to enhance the
feature sets of our keyboard. These are called keyboard shortcuts, and they're really
popular among power users and the average everyday users alike. Let's talk about a couple of other ones that we have available to us. For example, let's say
I'd like a new workbook. Well I could go to the
File tab and select New, and then pick the template from there, however that's a lot of steps, when all I really need
is a blank workbook. By pressing and holding the Control key and tapping the N key,
you'll see that it's jumped me into a brand new Excel workbook. I'm now in the generic workbook, book two. Let's say I'd like to open up a file that I already have saved somewhere. Well if Control + N for
new creates a new workbook, any guesses what they keyboard
shortcut for open would be? If you're saying to yourself Control + O you'd be absolutely right,
once again by pressing the modifier key Control
and tapping the O key you'll see it flings us
into the Open dialogue. This is where I have
access to any recent files I might have been working
in, any online cloud drives I might have access to, or
any files I have on my PC. So if Control + N for new
creates a new workbook and Control + O opens up a new workbook, any guesses what save is? If you said Control + S
you'd be absolutely right, once again good guesses. By pressing and holding the Control key and tapping the S key you'll
see it flings me into, well this is interesting,
the Save As dialogue box. In case you missed out on
when I mentioned it earlier when you haven't saved a
file before it forces you into the Save As dialogue box. So let's say that I'd like to Save As now, I'm gonna go ahead and create a location because of course Save can't
create a file only Save As can. So I'm gonna go ahead and
save mine to the desktop and call this Practice Save. So now that I've saved my file
I'll try my keyboard shortcut Control + S again and nothing happens, at least not as far as I can tell. But it is actually saving my file, we just can't see it now
because save is doing its job. It's just overwriting the existing file. Now with that being said, what's the keyboard shortcut for Save As? What if I don't want to
overwrite my existing file I'd like to save it as a new copy. Well in this case here,
up until now we've been served some softballs with
these keyboard shortcuts, Control + N is New, Control + O is Open, Control + S is Save, so what's Save As? It's not Control + SA, it's
actually just the F12 key. So if you tap the F12 key, it'll jump you straight into the
Save As dialogue box. This is also where we
can save this document as any type of file that Excel
is capable of exporting into. Here in the Save as Type
you'll see that it's currently saving it as an Excel workbook. If I click on that you can see all the different types of documents that I'm able to save
this Excel spreadsheet as. I can save it as a workbook. For my power users I can save it as a Macro Enabled Workbook. For those of you who
work with organizations or divisions that might
have older versions of Microsoft Excel they
can't read the newer versions of Microsoft Excel workbooks,
anything from 2007 on can't be read by a Excel 2003
or earlier version of Excel. So in those instances
we might actually need to save it as Excel '97 to 2003 or the dot xls document workbook. We can save it as a web page, when we get a little bit
farther into these lessons you'll see that we have the ability to save these documents as templates. Now there's a lot of other options within that here and of course I encourage you to explore the ones that make the most sense to you. One that of course I'd be
remiss to not point out would be the PDF and XPS file formats allowing us to save these
documents in the PDF file styling. So we see that we have
a lot of different ways that we can export this workbook. In this case here I'm gonna
go ahead and leave it alone. Go ahead and take this opportunity to practice a couple of
those keyboard shortcuts. We'll be introducing them as we go along throughout this lesson,
but it's important to note that quite a few of these file tools do have keyboard shortcuts. In case you're ever not sure if a tool does or does not have a keyboard shortcut if it's inside the Ribbon
here for instance Bold, just go ahead and hover
over it and you'll see that Bold immediately to the right of that has Control + B in parentheses. This allows us to see that
it has a keyboard shortcut. So if I try the Control
+ B keyboard shortcut it would bold the text
that I have selected. Many tools have keyboard shortcuts, so I do encourage you to just highlight some of these for instance
Fill Color does not, however making text bigger,
in this case does not as well. There's one Control + U for Underline. Go ahead and pause the
video for just a moment and try some of these
keyboard shortcuts out and maybe try to see if your favorite tool has a keyboard shortcut all on its own. All right, welcome back, so
we've spent a lot of time talking about the basic user
interface of Microsoft Excel. And you might have seen that up until this point we haven't done a lot of typing inside of
the actual spreadsheet. Well that stops now, what I'd like to do is focus a little bit more so on actually putting this content
inside the spreadsheet. Let's say that we've decided
to strike it out on our own, we're gonna be opening up
an artisanal coffee shop. I live here in San Francisco
and if there's one thing this city totally needs another one of it's an artisanal coffee shop. We'll go ahead and start with the actual name of our business. I'm here in cell A1 and I'd like to just put the name of the business in here. Now I can't think of a better artisanal coffee shop name than the one on the tip of my tongue
here, Birch & Sons, actually it's 2015 Birch
& Daughters, there we go. So I've got my Birch &
Daughters coffee shop. Now when I finished typing
I tapped the Enter key and you might have seen that
my cell jumped down to A2. The reason being is that
the Enter key is actually the equivalent of a down arrow key, especially when you're inside of a cell. Now with that being said, you can also go to the right instead
by tapping the Tab key. So by tapping the Tab key you can finish typing content inside of a cell and go one cell to the right, or again the Enter key to go down. Now within that you can also go one cell up by pressing
and holding the Shift and then tapping the Enter key. So if the Tab key pushes
us off to the right and the Enter key pushes us down, but the Shift + Enter
key brings us back up what do you think Shift + Tab will do? Well if you said it's going
to bring us to the left, you'd be absolutely right. So once again, the Enter
key brings us down a cell the Tab key brings us to the right, the Shift + Tab key brings
us back to the left, and the Shift + Enter key
brings us one cell up. Now let's say that I'm not
done with this name here, let's say that Birch &
Daughters isn't the full name maybe I'd like to add the
word coffee shop to that. If you say the name Birch &
Daughters you might be curious as to what kind of
business that would be too. So if I'd like to add new
content into the cell, without double-clicking on it or going up to the Formula Bar, both of which are things I could do, I'm gonna simply tap the F2 key, and the F2 key is going to bring me back into that cell at the very end. You'll see that my cursor is now active and I can go ahead and add
the rest of my content. And I'll go ahead and
type space, Coffee Shop. Now if I'm done typing inside of a cell, but I don't want it to drop
down or go to the right or go up or to the left,
I can press and hold the Control key and tap the Enter key and it will keep my active
cell inside the content without having to jump
down or go to the right. So again if you don't
want your cursor to move, but you're done editing
the cell press and hold the Control key and tap the Enter key. Go ahead and pause the video
and get to this point with me. All right, so let's go ahead and expand on this menu a little bit more so. We need to know what kind of items we're going to be carrying here. So I'm gonna go ahead and
type Items in cell A3, I'd like to drop down so
I'm gonna tap my Enter key. And I definitely know that
a coffee shop probably is going to carry coffee,
probably going to carry tea, and then maybe a couple of food items. We'll say bagels, crumpets, for all of my tea drinkers out there I know you're about your crumpets. And additionally I'd like to go ahead and enumerate the price here. So how much is our coffee going to cost? Well this is an artisanal coffee shop, so we're probably talking let's say $4.00. So I'm gonna go ahead and type 4.00 and I'm gonna hit Enter. Huh, where did my .00 go? Well we'll talk about
that in just a little bit but I do want to point
out that despite the fact that I did type 4.00 that went away. Let's talk about tea, how
much is our tea gonna cost? Let's go ahead and say $3.50. Once again I'm gonna
go ahead and type 3.50 and I'm gonna hit Enter
and huh, now wait a minute, I definitely typed a zero
right after that five, and I know that I typed
two zeros after that four. So what's going on here? Well the problem is
Excel is trying to keep these cells as simple as possible. And one of the ways it
does that is by removing zeros that might go on forever. For those of us that remember algebra, you might have been
taught that every number has zeros after it, 4.0000000 is still just the number four. And in fact all numbers have that decimal based valuing after it. In order to make sure
that this is the simplest presentation of these numbers Excel actually removes those zeros. To fix that we have to
change the number formatting we actually have to tell Excel
what kind of number this is. So there's a couple of different
ways we can do that here. Now, I'm gonna go ahead
and select that four and I'm gonna right-click on it. Now where can I format
the settings for this? There, Format Cells, I'm gonna go ahead and click on Format Cells
and it jumps me into the number formatting settings. So what kind of number
is this going to be? Is this going to be a regular number? Maybe, or is it going to be currency? Notice that it actually
affix the dollar signs to it. This is exactly what I
want so I'm gonna go ahead and apply my currency and click OK. Much better, now I've
got my .00 afterwards. But, I still don't have it
here, the reason being is that I only made that
change to that one cell. We have a saying at Learn iT and it's that you've got to select to effect. If I want to make
changes to a certain call I need to select it first. So I'm gonna go ahead and
select the three additional cells that I know I'm going to
be typing dollar values into. And this time I'm gonna
show you even faster way. I'm gonna select those
three cells and inside the Home tab I'd like to
draw your attention to the Number Formatting command group here. Currently the formatting
is set to General, so I'm gonna go ahead and change that by clicking on the drop
down arrow and selecting the Currency number
formatting and there we go. So I've now changed the way
Excel presents this information, so I'll go ahead and fill
out the rest of this here. We'll say the bagels are gonna be $2.50 and the crumpets will be $2.75. Go ahead and take this
opportunity to pause the video and catch up with me here. Once again to change the number formatting simply select a cell
and right-click on it, and go to Format Cells and choose the formatting category from there, or alternatively select
the cell and then using the Number Formatting command group click from the drop down and
select from there as well. Go ahead and try that for yourselves. All right, welcome back, so hopefully now you're all caught up with me here. We have our Birch & Daughters Coffee Shop and we're building a menu. Now over the break I ran
this menu by a focus group it was expensive but
it was totally worth it because we've seen that
the focus group told us crumpets are definitely out. The average person doesn't
go to a coffee shop to order crumpets, so I'd
like to remove that item from the menu and
replace it with something a little bit more modern. So in this instance
here, I'm gonna go ahead and remove the word crumpets from cell A7. There's a couple of different
ways I can do that for you, and I'm gonna run that back. The first way I can do
it is simply by pressing the Delete key, so I'm gonna highlight A7 and press the Delete
key, and you'll see that it's deleted the entire
value instantaneously. So that's one way to do it. I'm gonna go ahead and undo that so I can show you another way. To undo you can either use the Undo button on the top left corner,
or alternatively you can press and hold the Control
key and tap the letter Z, either way will work. So once again you can either
press the Backspace key or the Delete key to delete a value, or let's say that I'd just
like to replace the value. Maybe, crumpets aren't
going to do so well, but biscuits a more common
iteration of crumpets might be, or English muffins,
let's do English muffins. So to replace crumpets I'm simply going to start typing, I've got A7 selected and I'm just gonna start
typing there, English muffins. And once I'm done I'd like to get out of the editing field here but
I don't wanna leave the cell. To do that I'm gonna press
and hold the Control key and tap the Enter key once again, and there you go English
muffins is now in the cell. Now, wait a minute here,
where's the word muffin? What's an English M? I don't know, what's happened here? Did I just lose all of my content? Well the answer is complicated
but we'll break it down here. When we start to type
anything inside of a cell we're allotted a certain
amount of space here. In this case here you
can see that the space between A and B is not that large. So you can see that English muffin didn't fit in all the way. But you might be saying to yourself, Sean you typed Birch & Daughters in here and it seems to fit fine. Well it kind of does,
the reason being is that English M, or in this
case English muffins, is being cut-off because cell
B7 does have a value in it. Meanwhile, A1 is allowed
to run on, and on, and on because there's no values in B1 or C1. If I typed anything inside
of these cells right here like the word hello, you'll see that Birch & Daughters Coffee
Shop immediately got hidden because B1 takes precedence. However, if I delete
the value A1 is allowed to run itself over simply
because there is no content it could possibly be impeding on. So how do we fix this? Well we need to re-size the column. The easiest way to do
that is to find the border between A and B, or
in-between any column that has a value you'd like to adjust. And clicking and holing I'm
going to expand the value until muffins fully fits in. Additionally, what I could have also done is find the border and double-click, now you'll notice it went
a lot farther, and this is because of Birch & Daughters
Coffee Shop field here. It auto expanded to the widest
value inside of that column. So depending on the way that
you're trying to expand it this might not necessarily
be the way that you want. I think this is a little far personally. So I'm going to readjust,
so that it allows English muffins to show
without being too big. Of course we can also do the same thing with row based values. We can make row based
values wider as well, or more narrow, once again
by finding the border between any two rows you can double-click and it will AutoFit based on the content of the cells in the rows. Go ahead and pause the video
and try that for yourself. All right, so we've had
some opportunities to enter content and edit content if we need to. Now, let's go ahead and
talk a little bit more about what we can do with
this data once it's input. Now, I've gone ahead and I've added data for weeks one through four,
in terms of total unit sales for coffee, tea, bagels
and English muffins. So this is great, our
business is successful, we're making it but we don't know exactly what's going on with all of this data. At this stage, all we know
is how much we've sold in any one week for any one
of these particular items. There's not a lot of
questions we can answer with this data just yet. For example, maybe I'd like
to know the total amount of units I sold in week one completely. This might give me a better idea of how many customers I might have had. Or how many individual
transactions I might have had. So let's say I would like to calculate the total unit sales for week one. Placing my cursor in cell C8 here, what I'd like to do is calculate all of the values directly above. Now for those of you who have used Excel in the past I'm
sure you know the ability to type equals and add up all
of these different values. But before we get to
all of the hand typing, I'd like to show you
some automated options that we have available to us. In particular, I'd like to introduce to you the AutoSum tool. So go ahead and let's
place our cursor in cell C8 and I'm gonna go ahead and
just click the AutoSum button. When I do it actually does exactly that, it auto sums up the
values directly above it. It's made a guess as
far as what the values I'd like to add together are, in this case it's identified
cell C4, five, six, and seven as those values, and it's saying, "Hey do you want to sum those up?" It's guessed right so
I'm just gonna go ahead and tap the Enter key. And you can see that I sold
in week one 1,625 units. Let's go ahead and do the same thing for weeks two through four. Once again, I'm gonna
place my cursor in cell D8 and click the AutoSum tool. It's gonna select all
the values that I think it's going to be adding together, but if I'd like to edit it I can always just highlight the content myself. Now in this case it's right,
but let's say it's wrong. Let's say it only guessed that one cell, I can click and drag and fix it. Now once I'm done here, I
could tap the Enter key again but I'd like to get just to
the next cell to my right. Who remembers the key that I used to go one cell to the right? If you guessed the Tab key
you'd be absolutely right. I'm gonna go ahead and tap the Tab key and it's gonna take me right over. You can see week two was
definitely better than week one. Go ahead and take this
opportunity to apply AutoSum to weeks three and four. All right, so far we've
calculated the total unit sales for weeks one, two, three and four using the AutoSum feature. What I'd like to do now is
find out the total amount of coffee that I sold in
this particular month. To do that I'm gonna
place my cursor in cell G4 and in the same process
here what I could do is use the AutoSum feature
and the AutoSum feature prevents me from having to go
through the tedious process of typing an equal sign,
typing the word SUM with an open parenthesis and then saying, C4 plus, D4 plus, E4 plus. You can see how this is really tedious even just to watch me do that. So again I'm gonna go ahead
and use the AutoSum tool but this time before I click Enter, I'd like to point out
that it's not perfect. Notice that it actually
includes the pricing value in column B, which is not
exactly what I wanted. In this case, I only want
weeks one through four. Or in this case C through
F, so I'm gonna re-select from C4 to F4 and I'm
gonna be dropping down to the cell below so I'm gonna tap the Enter key and there you go. So go ahead and do that. AutoSum for all of these
individual values here, once again simply click the AutoSum tool, and highlight the values that you would like summed together. Now lastly, what I'd like to do is get a total units sold overall. Now which one do I calculate? Do I calculate the values above, or the values to my left? Well the answer is the
same, no matter which one you use because this one
is simply calculating individual weeks whereas
these values are calculating individual items however
the total units sold will be the same regardless. So overall we sold 6,879
units in a single month, that's not bad, go ahead
and pause the video and take this opportunity
to catch up with me. All right, welcome back,
so we've calculated the total units sold
per week and per item, and we were able to do that using automated features like the AutoSum tool. What we'd like to do now
is focus a little bit more on the real world matters,
we'd like to figure out now that we've got our total units sold, how much did we make? Let's talk dollars for a moment
here ladies and gentlemen. Here in cell H4 I've got a column ready to calculate my subtotals. Now, how do I calculate how
much money I actually made? Well with this data here I can simply take the total units sold and multiply it by how much each individual item costs. Now of course this is
glazing over business costs like how much it costs
to get the raw materials, and blah, blah, blah,
but let's keep it simple. I'd like to just calculate how much income I made selling these items. Placing my cell in H4 what I'd like to do is calculate those values;
units sold times price. So I'm gonna actually need
to write a formula this time. There's no automated tool for this. Now, in Excel we need to wake it up. We need to tell it that it's
gonna be doing some math. The way we do that is with
the universal sign equals. By typing the equal sign
we've indicated to Excel that it's about to do some math. And that it needs to wake up
and start paying attention to the content coming afterwards. So now that I've typed
H4 I'd like to go ahead and calculate total
units sold times price. Now I could type three two six three and
then the multiplication sign using my Shift + 8 key,
and then typing four and it calculates it, in this
case here, I made $13,052. But here's the problem,
let's say I raise the price, let's say that this was incorrectly added and the actual price of
a cup of coffee is $3.95. Did my subtotal change? No, it's still 13,052. The reason being that we told Excel what to do not where to look. Remember when I showed
you the Formula Bar, if I click inside this cell right here, I can see that the
formula isn't looking at this cell right here
to see if it's changed it still says four. So how do we fix that? How do we future proof a formula to account for data changing? Well first things first, let's go ahead and delete that value in cell H4. And I'm gonna start
over with my equals sign and this time instead of telling Excel that it's gonna be
multiplying 3,263 by $3.95, I'm simply going to tell Excel where to look to find those numbers. So in this case here it's going to be G4 selected by clicking on it. You'll notice that it's got
the dancing ants all around it, multiplied by B4. Once again notice that it's got the little dancing ants around it G4 times B4 inside of the cell where I'd like this answer to come out. And I'm gonna lock it in
by pressing and holding the Control key and tapping Enter. And there it is, so I made $12,888.85. And now if I change my coffee price back, to four dollars, voila, my sub total also changes. So now I can update this data
with the latest information and I'm able to see
those numbers reflected in any formulas that are
referencing those cells. Go ahead and pause the video
and try this out for yourself. Once again, in cell H4 what we simply did was multiply the total units
sold by clicking on that cell multiplying it by the price, once again, by clicking on that cell and
pressing Enter to lock it in. Go ahead and try that. All right, so now we've got the subtotals for all four items, and if I'd like to I can actually even calculate
the total dollar value of all the items together. Now we saw that we were able to do it with the AutoSum feature,
let's talk a little bit about how they got that value and how we can build that function ourselves. To do that what I'd like to
do is build a SUM function. Now just like with a
formula I'm gonna start with my equals sign but
this time instead of just jumping right into the math, I'm gonna tell Excel exactly
what I'd like it to do. And in this case I'd like
it to sum up the values. So I'm gonna type the word
SUM and a couple of things pop up here, we see a list of functions, like SUM, SUMIF, and for every
cell that's selected here or every function rather,
you'll see off to the right a description of what this
function actually does. And in this case the SUM function adds all the numbers in a range of cells. If that's the function you'd like to use, and it is, we'd like to
tell Excel this is the one. We do that by inserting
an open parenthesis after the function's name. So I've got equals SUM open parenthesis, I can now select all
of the numbers I'd like added into my SUM function. In this case here, I've got 13,000 through 1,927. I selected all four values
by clicking and dragging. However, you can also click
and hold the Shift key and tap the down arrow key until
all the values are selected. Once that's in place
we'd like to tell Excel that this function is now complete, and the way we do that is
by pressing and holding the Shift key and closing
off these parentheses. Remember every open parenthesis requires a closed parenthesis as well. Once this function is
done I'm going to tap the Enter key and there you have it. Total, we made $24,038 in this particular month's
worth of sales, not bad. Maybe we'd also like to
calculate the average number of units sold, so
in this particular instance maybe I'd like to find out the average number of coffee items sold. So let's go ahead and in cell C10 here, average units, I'd like to
find out how much I sold in coffee, tea, bagels, and English muffins. Go ahead and re-size here a little bit. So how do you calculate the
average units sold for coffee? Well just like we did
with the SUM function, we'd like to tell Excel
to do a certain set of math and the way we're going to do that is starting with the equals sign. So I've typed my equals sign and this time instead of typing SUM, I'm
gonna go ahead and type AVERAGE. Once I've done that I'd like to tell Excel yeah this is the function I want to use, and I'm going to open
up these parentheses. Now all you need to do is select the cells that I would like averaged together. In this case I'm averaging coffee, so I'm gonna select weeks
one through four of coffee and close the parentheses,
tap Enter to lock it in. And I can see that the
average number of units of coffee sold in any one
particular week is 815 units. And if I highlight this cell
I can see in the Formula Bar there's that function that I built. Go ahead and pause the video
and catch up to me here. Let's calculate the average
units for all four items. All right, welcome back, so at this stage we've calculated the average unit sales of each individual item. We've calculated the total units sold of each individual item,
and using that information we were able to calculate
the subtotal dollar value. The actual amount of money we
earned selling these items. So let's keep on keeping
on, with some of this math. What we'd like to do
now is maybe calculate the total amount of taxes
that we're gonna need to set aside for federal
and state and local taxes. To do that we're gonna need the help of our tax rate field up here at the top. Now here in San Francisco,
our tax rate is about 8.75% so we'll bus using that value. If you'd like to substitute
it for your local sales tax rate you're
more than welcome to. Here in column I I've got a taxes field. Now to calculate taxes I'm simply going to multiply the total items,
total dollar value made, by the tax rate, so in this case of coffee $13,052 multiplied by 8.75% to get the total amount of
taxes I need to pull out. So let's go ahead and do that, once again to start
any function or formula I'm gonna need to kick it
off with an equals sign. And I'm gonna go ahead and select cell H4 and multiply it by 8.75% enter to lock it in and I
can see that the total amount of taxes I need to set aside is $1,142. Now once I've built a formula
I really don't necessarily want to have to build it all over again, that seems tedious and it
feels like a lot of work for a software program
that's touted as being the end all be all of doing math for me. So we don't actually have
to retype this formula all over again, we do
have access to something called the AutoFill handle. The AutoFill handle allows us to copy the same formulas over
and over and over again, adjusting for relative cell references. So for example here,
let's say that I'd like to replicate this formula of
G4 times B4 here in cell H4. I'm gonna go ahead and clear
these values out right here, and while in this cell right
here it does say G4 times B4 what I need in each individual cell is the respective row value. So instead of G4 times B4
here, in the cell below, I would need G5 times B5,
and G6 times B6, and so on. So let's see what happens. To engage the AutoFill
handle I'm gonna need to find the little green box in the bottom right corner of my active cell. Hovering over it you'll
see that my fat white cross turns into a thin black cross. Hovering over it you'll
see that once I've got that thin black cross I'm
going to click and hold and drag down to fill the respective cells that I'd like to copy,
notice that the green box is expanding although no
values have appeared yet. Once I release, notice
all of the cells values auto populate, so it surely did, G5 times B5, G6 times B6, G7 times B7, great. So let's go ahead and do
the same thing with taxes. Once again I'm going to hover
over that little green box in the bottom right corner, and click and drag, and release, whoa that is not what you expected is it? What went wrong? (laughs) Well a lot went wrong here. Let's go ahead and talk about it here. Now, here in cell I4 I
see the formula is exactly what I built it out to be, H4 times H1, or the subtotal times the tax rate. Let's see what the cell below it says, H5, or the subtotal, times H2, H2 has nothing in it. Oh I see, so just like in the formula before when I auto filled down, G4 times B4, became G5 times B5, this
formula's value also increased. When you AutoFill down
row-by-row the row value in this case right here four
and one, both increase by one. This is called relative cell referencing when you AutoFill down
it continues to reference those cells relatively based on the positioning of the cell formula. We do however have the ability to control that a little bit more so. By selecting the cell
that we'd like to lock in, in this case here, what cell do we not want to change ever? H1, we don't want the tax
rate to ever stop being H1. In fact we want every
single formula to reference their subtotal and then multiply it by H1. So to lock that in we need to
absolute reference this cell, meaning that we will
always absolutely reference that one point in the spreadsheet. To do that we're going
to engage the F4 key, so by selecting H1, the
cell I don't want to change, I'm gonna tap my F4 key
and you'll see that now H1 is surrounded by dollar signs. Once you've done that
go ahead and lock it in by tapping the Enter
key, and you'll see that the formula itself
didn't change the result. It's still $1,142 but I fixed it, right? All the other cells should be good? Well unfortunately not,
AutoFill is a one time deal, meaning that if I go
from cell-to-cell here you'll see that they're still
referencing H2, H3 and H4. If I would like to replicate
this style of formatting to all the other cells, I'm going to need to AutoFill down again. So one last time I'm going to select I4, now with an absolute referenced H1 cell, I'm going to AutoFill down and there it is, so now H4 times H1 and below that H5 times
the absolute referenced H1. The cell below it H6 times the absolute referenced cell of H1. So when you're trying to
build a function or formula that is going to consistently
reference the exact same cell every single
time no matter what, no matter how many times you
AutoFill or copy and paste, you need to remember to absolute reference that cell using the F4 key. Go ahead and pause the video
and catch up with me here. Remember how we got there,
to calculate the tax rate we multiplied subtotal by the tax rate. However, to allow for
us to absolute reference and AutoFill down, we
remembered to lock in cell H1 in that function. Go ahead and take this
opportunity to do just that. Welcome back, so now that we've got our basic menu up and running
let's take a deeper look. Things look all right, we've got our weeks one through four sales, we've got total units sold, and the cool part about
this entire process is that it's automated meaning that as soon as we update any
of these sales values from weeks one through four
our total units sold changes, our subtotal changes, our taxes change, our average units change,
all because we told Excel how to do what we wanted it to do. Once we've gotten to this point here, maybe we'd like to expand. We're doing so well we
think we're gonna add a new menu item, but
there doesn't really seem to be anywhere for me to add that. We've got coffee, tea,
bagels, English muffins, maybe I'd like to add a third drink. That'd be kind of silly for me to toss it underneath the total
here so I actually need a little bit of room, in
order to create this room I'm gonna insert a new row. To insert a new row go
ahead and place your cursor anywhere below where you'd
like to have your row inserted. In this case, I'd like to
have a new blank row six. So I'm gonna place my cursor in row six. Having placed my cursor
there I'm going to navigate up to the Home tab and
find that Insert button. Go ahead and find that Insert button and select the Insert Sheet Rows. And just like that you've
got a brand new, clean row. Now we could have also
simply right-clicked on the row number six, and
selected insert as well. Both will do exactly
what you want it to do. Go ahead and try that for
yourself and then come back. All right, so we've got our new blank row, our new coffee drink will be our new patent pending Cofftea,
all rights reserved. And our Cofftea will be, I
don't know, let's say $5.00. We'll go ahead and make up
some sales figures here, it's super popular. And you'll notice that as we're going through this process
here all of our totals are updating as well. I'll explain why in just a moment. Now you'll notice that,
our totals down here at the bottom updated, however, our total units sold,
subtotal and taxes did not. The reason being is that
when you insert a row where there's an existing
range of summation in this case we were already summing up these fields right here,
when we inserted a row in the middle we stretched out that range. So any formulas that were referencing say from C4 to C7 if we stretched that out it would automatically grow
to include that new C8. However, when we insert a new
row it does not copy formulas. So you'll notice that here
in cell G5 we have a formula, H5 we have a formula,
I5 we have a formula. Those will not copy when you insert rows, so it is important to keep that in mind. Fortunately, we have
our AutoFill technique, or by highlighting both
one row that does have the formula and one row that does not, using the keyboard shortcut Control + D we can bring that down. Once again, the way we
did that was by selecting a cell that already had
the formula in place that we'd like to copy,
and then auto filling down, using that little green box in the bottom right corner of the cell. Now that we've done that
let's say we'd like to add a couple of columns in
order to give ourselves some breathing room. I think I'd like to have maybe like let's say three columns of space. Just to kind of give myself some room, maybe I'd like to have this field reserved for notes about the week or the month. To insert a column it's
just as easy as it was to insert a row, simply place your cursor where you'd like there to be a new column and then here in the Insert tab, I'm sorry in the Home tab
find that Insert drop down, and select Insert Sheet Columns this time. Now that works for just
one column at a time, however if you'd like multiple columns, let's say I'd like two
additional columns inserted highlight two columns in this
case column A and column B right-click and select Insert. And you'll see now that
two columns were inserted. Go ahead and try that for
yourself and then come back. Fantastic, so we've dropped in a new row, we've inserted a new menu item. We gave ourselves three
extra columns of space, because we'd like to
be able to take notes. Well you'll notice that
when we moved things over a little bit we kind of
lost the organization that we had set up here. For example, our title was
initially supposed to be the first cell you saw
and I'm not necessarily in love with how far off to the right average units is now, so let's fix that. Now there's two ways to move
and copy data inside of Excel. The first way is
relatively straightforward, click to select the cell
that you'd like to move and then hovering over the
border of that selected cell click and hold and then drag to the left, or to wherever you'd like to drop it off. And you'll notice that as I'm moving it I have that green silhouette
with no content in it yet. But as soon as I release, boom. Now let's do something
different with average units. I'd like to move average units
from here to let's say here. This time instead of just highlighting and clicking and dragging, which would be the easiest way. I'm instead going to use my Cut tool, which can be found here
inside the Home tab. You can also use the keyboard
shortcut Control + X, as you can see in the parentheses of that tool tip that's popped up. Having cut the data it's now highlighted with that flashing marquee. I'm now going to place my cursor where I'd like the first cell to paste. In this case here this is the cell where I'd like average units to be. Once I've got my cursor where I'd like I can click the Paste button,
or as the Status Bar says I can press Enter. Using either of those
methods, I'll go ahead and paste the content. Go ahead and try that for
yourself and then come back. Fantastic, so now you've had a chance to see how easy it is to
move, cut and copy data without having to think about it too hard. Now, I'd like to go ahead
and take this opportunity to create my notes section. This is where I'll take any
notes on a week-by-week basis to keep an eye on fluctuations,
annotate whether or not there are reasons for fluctuations in the business up or down. So first things first, I'm gonna go ahead and just type out notes here, and what I'd like to do is just on a day-by-day basis annotate what's going on throughout the week. So I'm gonna start with Sunday here, now I know what comes after Sunday, and hopefully all of you do too. So I don't necessarily
want to have to go through this whole process of
Monday, Tuesday, Wednesday. I already know what comes
after all of those days. Well, so does Excel. As it turns out just in the
same way that we're able to copy formulas like we did earlier. We're actually able to copy certain values inside of Excel just as easily. So I'm gonna take my cursor over here and I'm gonna highlight Sunday. Now, with my active cell on Sunday, you'll notice that we've
got this drop down arrow just like we did in every other instance. This little box in the bottom right corner the AutoFill handle,
I'm gonna take my cursor and hover over it and once again, make sure that your fat white cross becomes a thin black cross. Once I've got that thin black cross I'm gonna go ahead and click and hold, and I'm gonna drag and watch what happens. Look at the little tool tip
on the right, it says Monday. I drop down again, Tuesday. It's actually auto filling
the days of the week. Excel does know, just like
you and I, that Sunday comes before Monday, comes
before Tuesday and so on. All the way through Saturday,
so I've taken it down to row 10 and I'm gonna
release and look at that. So we can use AutoFill
not only to continue the pattern of formulas that we're using but we can also use it
to continue on annotating common knowledge fields. Well what else can we AutoFill? How about something as simple as the date? Today's date I've currently got 2015 and of course I know full
well that after November 25th we've got the 26th, 27th, and so on. But I really don't want
to have to go through this whole process of typing. Well, once again, we've
got AutoFill to the rescue. So I'm gonna highlight the little cursor and once I've got my fat white cross, and I take it and turn it
into that thin black cross I'm gonna click and hold
and I'm gonna drag down and boom, look at that, the tool tip on the right-hand side, 11/26, 27, 28, 29, 30 now this is where you
really have to ask yourself one is their a 31st
day, and if there's not, does Excel know that? Boom, look at that, I'm
gonna go ahead and release Excel actually knew that there
isn't a 31st day in November. And so it jumped straight to December 1st. AutoFill can really save you a lot of time with just these little micro decisions, these little micro checks
to see whether or not you're putting in the right information. Or if it's such common knowledge
that it's beyond mundane AutoFill can alleviate the
whole process and tedium of having to do something that simple. Go ahead and take this opportunity to try this out for yourself. Go ahead and type a familiar common value like Sunday or better yet maybe its abbreviated value of Sun and AutoFill down and watch what happens. You can also use the months
of the year like January or their abbreviation of Jan. It's pretty cool, pause the video and take this opportunity
to try it for yourself. We've got a pretty
good-looking worksheet here. If we wanted to we could
expand a couple of things here and there but right now the thing that's kind of driving me a
little bonkers is the fact that it's not very visually striking, in fact it's kind of difficult to read. So with that having been said, let's try and spice
things up a little bit. Let's make this a little bit more user-friendly should we say. Let's start with something
simple, the title. Now at first glance you might not even know this is the
title, so let's fix that. Now, what does a title look like? That can be a really
difficult thing to determine. And in fact it's a really tedious process to have to go through
this time and time again when it comes to deciding what something should
or shouldn't look like. So instead of having to make
those design decisions yourself let's let Excel do that for us. I'd like to introduce to you
now, the Cell Styles tool. Which has a convenient
series of pre-packaged styles that you can apply to a cell allowing you to take your hands off the
wheel so to speak and allow Excel to change the visual
aspect of cells for you. Once again, let's take the
title, here inside the Home tab find that Cell Styles drop down and as you click on the drop down you'll see there's a number
of different categories. You've got Good, Bad, and
Neutral, Data and Model, Titles and Headings, and then we have our themed cell styles and number formatting. Now where in this might I find a title? It's not like it's just going to be named something so convenient, right? Well, there you go, so let's
go ahead and find that. Click and select cell A1
and then hover over title to get a live preview of
what that will look like. Now of course you're
gonna get a live preview of what all of these title
and headings and cell styles will look like just by hovering over them. In this case here however,
we'd like a visual indication that this is the title, it's bigger, the font styling's a little bit different, I think this is good. Go ahead and find that Title
button and give it a click. And there you go, without having to decide what a title should or
shouldn't look like, we've allowed Excel to do that for us. Leaving us to focus on
something more important like actually filling out the numbers, and things to that effect. Let's take a look at the headings, go ahead and highlight from D3 all the way down to
L3 here, our headers. I'd like some visual indication that these are the headers. So once again, I'm gonna
jump to my Cell Styles, if only there were a headings style. Well as it turns out there are. Heading one a little big,
heading two getting there, I like heading three, something
non-descript, low-profile, and I'm gonna lock it in with a click. You'll notice that we
highlighted everything before we did it, in
most instances in Excel you won't have to but
there are some instances where you will have to. There's a saying we have at Learn iT and it's you've got to select to effect. If you're changing the
visual effects of a cell you've got to select it first. Let's go ahead and do the
same thing for our item names. From coffee down to English
muffins I'm gonna go ahead and highlight those and this time I'll use the cell style heading four. So while it's all well and
good that we're able to use these cell styles to
change the visual aspect of things to make them look good, we can also use them from a
functional standpoint as well. For example, the total row indicating that these are totaled values,
because if I'm just going down week one, I'm not gonna
have any real indication that this cell is any
different than this cell. So let's fix that, using Cell Styles we're going to use the total styling. Which is gonna highlight the total row using bold font styling and borders. Go ahead and find that
and give it a click. All right, this is
starting to come together. What other kinds of functional changes can we implement here? Well as I'm looking at
this here I know for a fact that these are cells
that are going to change. These are cells that we're going to be inputting numbers into. So maybe we'd like to use
some visual indication to ourselves and to
other people using this that these are input cells. Now where oh were, might I find
a cell that indicates input? Oh, well there you go. So highlight all the cells
that you'd like to impact in this case I'm highlighting
all of the sales fields from week one through four, and I'm gonna select the Input styling. Now, this might not mean
anything officially, but if you continue to use
this same naming scheme this same styling scheme,
you're going to know every single time you
see this style of cell that this is a cell that requires input. Much in the same way that
we might also highlight all these cells that
have formulas in them, and call them output cells. Go ahead and take this opportunity to spice up your worksheet
a little bit here. Of course you can use other Cell Styles other than the ones provided
in the Title and Headings, and Data and Model, in fact
maybe you'd like to use some Themed Cell Styles
that change and ebb and flow with the theme which we'll be talking about in just a moment. But go ahead and let's limit ourselves to just these ones for now. We'll talk a little bit more so about Good, Bad and Neutral
in just a little bit. Welcome back, so now
we're starting to see that using Cell Styles we're
able to kind of hand-off control and allow ourselves to focus on the really important things, like the numbers, the
things we're actually putting inside the cells. You don't need to waste
time thinking about how something should
look by simply allowing the Cell Styles to do what they do best, you can focus on what you do best. Now, what other benefits are
there, to using Cell Styles? Because I certainly could have
just used the fill paint can well there's a couple of different things that come in to effect with Cell Styles. Obviously we see that there
are a couple of border changes that take place, in some
instances depending on styling the font color and
font styles change as well. But there's one other thing
that comes into play here that's really important,
and it's actually found here inside the Page Layout tab. A lot of people don't necessarily consider themes to be something that's
relevant inside of Excel. In fact many people don't even know that Excel has themes, but it does. And you're in fact currently
inside of a theme right now. Themes control everything
from the default font styling to the default color scheme. They also control shape
effect, but we don't see too much of that inside of Excel. So let's focus on the top two
for now, Colors and Fonts. Themes are a combination
of these two items. By clicking and dropping
down the themes option here you'll see a series of
options, we're currently inside the Office theme. But if I hover over some of these other tools watch what happens. Wow, it's pretty incredible to see how the entire look and
feel of this document changes simply by
hovering over these tools. This live preview gives you an idea of what these will look like
if we were to choose them. Notice that not only is
the color scheme changing, but so too is the font. By surrendering a little bit of control and using Cell Styles we allow ourselves to change the look and feel of a worksheet in a single click by changing the theme. Let's find a theme that
works well for you. Personally, I like this Ion font theme, so I'm gonna go ahead and find that and give it a click to lock it in. And just like that this is
a whole new worksheet now. It looks pretty good. Now of course I don't have to choose a theme I can always
build my own in a sense. Simply by finding my own
color scheme that I like and choosing my own
combination of font styles. So let's say I like this color scheme, but I'm not in love with the font choices. I can always go to fonts
and get a live preview of what some of these other
options are available. In this case here, I think I like the default Cambria and Calibri. So I'm gonna go ahead and choose just the standard Office
one at the very top here. Calibri Light for the headings
and Calibri for the body. And I'm gonna click to lock it in. And just like that this is now my style. I like the color scheme,
I like the font stylings, and if I want I can even save this setup as my own custom theme. In fact go ahead and do that. Find your own font
combination and color scheme that you like and then join
me here in the drop down where it says themes and
select Save Current Theme. Now it's very important to note that where it's placing us right now,
the Document Themes folder don't navigate out of that,
this is the only place Excel looks for custom themes. So don't save it to the desktop,
don't go to My Documents, stay inside the Document Themes folder. I'm gonna go ahead and
change the file name to Sean's Theme, something
I'm gonna remember. And I'm gonna click
Save, and just like that I've built my own custom theme. If I click on the drop drown inside Themes check it out, there's my theme, pretty cool. Go ahead and try that for
yourself and then come back. Once again what we did was, we decided on our own
personal color scheme, and font combination that
we liked for our workbook. Then, clicking on the drop down arrow, we selected Save Current Theme
to save that combination. Go ahead and try that for yourself. Welcome back, this is
starting to look pretty good. In fact it looks so
good, that I might want to hold on to this and use it every month. Well there's a couple of different things that I need to worry about
if that were the case. For example, I would have to worry about overriding last month's sales data. Now if I were to do this in the real world of course you would
hope that I would go in and create another copy of this file before editing the data. But the reality is most
of us are a little on the forgetful side when it comes
to doing things like that. So let's take a look at some of the solutions that are available to us. First things first, I'm
gonna get rid of all the sales data that wouldn't
be relevant in a new month. Now, one of the things that
you'll want to remember is that we've got formulas
all over the place here. And as soon as new sales data gets put in all of these sales fields
are going to kick into high gear and we're gonna get
all the information we need. So pretty much everything that
we need to worry about here needs to get cleared out, all right. So now that we've done that,
how do we preserve this and make sure that
every time we open it up it opens just as fresh as it is right now? Well in order to do something like that, we need to save it as a template. To save an item as a template it's really just as easy as saving any other file. We're going to start off by going up to the File tab here in the top left. We're gonna go ahead
and click File, Save As, and in this case here I'm gonna go ahead and save it to let's say MyDocuments. Now as it stands, it's
trying to save it as an Excel workbook, however
we're gonna change that. Instead of saving it as
an Excel workbook here I'm gonna find Excel Template,
this is really important and in fact it's one of the most critical things
you'll need to remember. Now, one thing you'll
need to keep in mind here is check out what happened
as soon as I changed it to an Excel Template,
it took me to a whole new folder that I hadn't even noticed, the Custom Office Templates folder. Don't navigate out of this. This is the only place that Microsoft Office looks for
templates, so once again, do not leave the Custom
Office Templates folder. If you don't get automatically taken to the Custom Office Templates folder go ahead and take that
opportunity to Google where that folder is. While it should work all the time, there are going to be occasions where it doesn't automatically do it, so you'll need to navigate there manually. Now for most of us this
should have taken us right where we need to
go, so I'm gonna go ahead and name this Monthly Sales. And from here all I've
got to do is click Save. Boom, and just like that
I've now created a template. Now what do I need to do now? Well first things first
you need to remember you're still inside the template after you've done with that file Save As. So we're gonna close out of this by going to File and Close. Now, let's go ahead
and open that template. We're gonna go to File,
New, and I don't see my Monthly Sales template here. I see all the other
templates we saw earlier, but no Monthly Sales. But you will notice that
we have a new section available that wasn't there
before, the Personal section. This is where Microsoft is going to log all of your custom templates. So I'm gonna go to
Personal and there it is, there's my Monthly Sales template. I'm gonna go ahead and open it up here, and at first it looks exactly the same as everything we've done before. Except all of the fields are
empty, and we've got no dates, and none of the formulas are kicking in. So, what was the difference between this and what we've just closed out? Well take a look at the
name of the file here. Monthly Sales One,
weird, that's almost like it's using the exact same
naming scheme it does when it opens a brand new
Excel workbook, Workbook One, Book Two, Book Three, and so on. So what's actually happened
here is it's created a brand new copy of Monthly Sales. So let's say that I go ahead and fill out all the sales data here
and nothing too crazy, I'll just go ahead and give
it a couple of numbers here. Looks like we have a good week two. Week three maybe this is some
sort of major sporting event or conference is in town. So I filled in all the sales data and we'll say that the notes are going to be for the week of 11/31 if there is a 31. There is not it doesn't
look like it so let's try, there we go. All right, so now the
concern would be of course that if I click Save it's
gonna overwrite my template. So normally you'd have
to go do a File, Save As. But check out what happens if
I click the Save button now. It forces us into Save As, you
can't overwrite a template. And this is kind of
the magic of templates, you can create this
pristine, protected data set that every time you open up
a new copy of you start over. Nothing you can possibly
do would overwrite this template unless you deliberately go and save over the template
using the exact same methodology we used to create it, not bad. Go ahead and pause the video
and take this opportunity to try that out for yourself. Remember, clear out all of
the data that would change and then from there once
you've got everything in what we call boilerplate status, which means that all
of the data is generic and non-time specific, or
non-situation specific, go to File, Save As, and save it as an Excel Template. Go ahead and pause the video and take this opportunity to catch up. All right, so we've now
talked about how we can save these custom templates in order to prevent ourselves from doing more work. Let's keep on that trend. Now Birch & Daughters Coffee
Shop is doing phenomenally well just crazy sales, really great. So great in fact, that
we're actually going to be opening up a new location. How great is that? To make a duplicate of this worksheet here so that we can keep track
of that new location's sales there are a couple of
ways that we can do that. The first way is a little roundabout, by right-clicking on sheet
one, the sheet that currently has this worksheet you
can select Move or Copy and then select Create
a Copy and click OK. But that was a lot of clicks. Let me show you an easier way. By pressing and holding the Control key click and drag sheet one to the right, and release. And you'll notice it instantly creates, Sheet One in parentheses two. And as I navigate from sheet to sheet here you'll notice it is an exact
duplicate in every single way. Now of course we're gonna have
to rename these worksheets, so let's do that, I'm gonna right-click and rename sheet one, this will be our San Francisco location. And our second sheet here
will be our LA location. There you go, and of course we can create additional copies on top of that, once again by pressing and
holding the Control key and clicking and dragging
to the right and releasing. And let's say we'll call
this our New York location. We're doing pretty good here. Now, when we've got multiple
worksheets like this here one thing that's going to
be a little bit problematic is the fact that now if we want to make a change to this entire styling here, we have to go from
sheet to sheet to sheet. At least, that would be the impression that you might have. For example, let's say we'd
like to change the price of tea from $3.50 down to $3.00. We found that sales just
aren't quite what we hoped they would be so let's
drop down the price. Now, I don't want to have
to go from SF to LA to NYC to change that single cell in all three. In fact if I'm making any change like that having to make that same change three times is mind-numbing. Instead of going through that process because these are all the
same style of worksheet I can actually group these together and make the change all at once. To select all the tabs in this workbook, simply right-click on any one tab or pressing and holding the Control key single-click on all the unselected tabs, and you're gonna have all three
selected at this stage here. Notice that they all
have a visual indication of that greet line underneath showing us that they're all selected. Having selected all three sheets, I'm now gonna go to this cell, and I'm gonna change the
price from $3.50 to $3.00, and I'll tap the Enter key to lock it in. And what the heck, maybe I'll also change English muffins to crumpets. We really want to bring up our tea sales. So maybe if we sell
crumpets that will help. Now I'm in the NYC tab here, but let's see if that made the change
in LA and SF as well. Here's my LA tab, crumpets $3.00, SF crumpets $3.00. NYC crumpets $3.00. By grouping our worksheets together either by right-clicking and selecting Select All Sheets, or
by pressing and holding the Control key and selecting
all three worksheets we were able to make
one change and have it propagate across all
those selected worksheets, pretty cool. Go ahead and try that for
yourself and then come back. All right, so keep on keeping on. We're doing really well,
we've got three locations, we've got a template that allows us to inject our sales data and
calculate total units sold, our subtotal, our average and so on. Great, now that we've got
a template and a worksheet that allow us to input the data, let's talk about reading the
data a little bit more so. Now for this I'm gonna go ahead and just jazz up some sales data here. All right, so let's
say I'd like to analyze week-over-week how we're doing
in any one particular item. Now, when it comes down
to analyzing sales data, there's a couple of different
ways we can come at this. Now this is a really small worksheet, so there's not a lot
of problems reading it. But with that having been said, you're going to need
to know how to navigate through that kind of sales data. For example, let's say I'd like to compare my items directly with week four. Now as it stands, week
four is significantly more separated from
coffee and even more so is my total units sold,
subtotal and taxes. In order to fix this, I'd like to be able to bring in let's say total units sold and subtotal all the way over here to compare with my items. Instead of having to go from here to here, tea all the way over. To do that it would be
nice if I could freeze everything off to the left here and scroll everything over here over. As it turns out here in the View tab we have exactly that tool, Freeze Panes. Now, Freeze Panes has
three options available; Freeze Panes, Freeze Top
Row, and Freeze First Column. Freeze Top Row and Freeze
First Column do exactly what they sound like they do. By clicking Freeze Top Row, I can now, scroll down and you'll see
that the top row stays intact. To unfreeze simply click the Freeze Panes drop down again and select Unfreeze Panes. Consequently on the other side of things, Freeze First Column same thing, by selecting Freeze First Column it only freezes the first column. While both of those
are great in most cases they only do a limited amount. If we need to freeze anything more than just the top row or the first column we're gonna need to turn to
this last option, Freeze Panes. Now Freeze Panes it's important to understand how it works, it freezes all rows above a cursor and all columns to the left of it. Meaning that if I've got
my cell here in column F and row four it's going to freeze all rows above row four and to
the left of column F. So let's take a look
and see what that does. I'm gonna place my cursor here in row F4, and I'm going to Freeze Panes. As I scroll down you'll
see that it's frozen rows one through three, all the rows directly above row four. And if I scroll to the right, it has frozen A through E all the columns to the left of column F. So now I can compare total
units sold to my price, to see if there's any
correlation with these items. Or by unfreezing panes
and placing my cursor here in cell E4, I'll freeze
panes again and now, I can control and compare the subtotal of each individual item, so I can see here that coffee sold $4,000,
tea $7,000, cofftea $10,000 we're doing all right in cofftea. Freeze Panes allows us to hold
on to part of the document and navigate to where we need to be and it's a fantastic tool when it comes down to review and compare. Go ahead and take this opportunity
to practice for yourself. One thing I'd also like to
point out before you do, is let's say you don't want
to freeze any rows at all. Simply place your cursor in the row one, meaning there are no
rows above it to freeze. So what will it freeze? All the columns to the left and only that. So if I select Freeze
Panes now and scroll down no rows are frozen, however all columns to the left of column E are. Consequently, on the other side of things, if you don't want any columns to be frozen but you do want rows to be frozen, let's say I'll place my
cursor here in column A meaning no columns to
the left can be frozen because there are no columns to the left. I'll select Freeze Panes
again and there you go. Rows one through three,
the rows directly above my active cell are frozen,
however if I scroll to the right no columns are frozen. So there you go, go ahead
and try that out for yourself pause the video and then come back. While freezing panes is
a really useful tool, sometimes it's not enough. Sometimes there are so
many different things that we need to compare that
by freezing just one portion we're still not getting the whole picture. For example, let's say I just don't need to see average units at all, in fact maybe I don't need to
see weeks one through four. Maybe as the CEO in this
hypothetical scenario I only really care about
what items are selling and how much am I making from them. Meaning that all this is
superfluous, I don't need it. In fact, I really don't
even need the taxes. So, what can I do? How can I hide all of
that without having to impact the usability of this document? Well, I can use something
called Custom Views, which means that I can create views that depending on the person
viewing them and their needs I can change what is
visible and what is not. Let's take a look, first things first, we're gonna need to
create a snapshot of this. To do that let's find
the Custom Views tool which can be found here in the View tab, go ahead and find that
and give it a click. Now you'll see here that
currently it's blank, for now. However, what we're gonna do at this stage is we're gonna create a
snapshot of this current view the exact way everything looks. Currently no rows are hidden, no columns are hidden, nothing's changed. So I'm gonna click Add here on the right, and I'm gonna call this my Original view and I'm gonna click OK. A little anticlimactic nothing
seems to have changed here but what I've done is I've taken a copy of what the view layout
of this is currently. Now let's build this idealistic view, first things first, I don't
need this average nonsense. Some I'm gonna highlight all the rows here and I'm going to
right-click and select Hide. You can also from the Home tab find the Format drop down and inside Hide and Unhide select Hide Rows. Personally, I like the right-click myself, but to each their own. Next, I'm going to hide everything between items and subtotal, so that's columns E though J. Once again, having
highlighted all of those I'm going to right-click and Hide. Lastly, I don't need taxes,
so I'm gonna right-click on that and select Hide. And maybe I'd like to
zoom in a little bit. So I'm gonna zoom in and
let's say to about 160 about 160% zoom and now I'm gonna take a snapshot of this view, this will be the CEO view. What are my items? How much money did I make from them? So I'm gonna click on
Custom Views once again, and this time I'm gonna click Add again. And I'm going to call this the CEO view, and click OK. Now that I've done that,
I'd like to get back to my original view. Here's the scary part with
hiding rows and columns it's easy to forget where
and how many were hidden. And then on top of that you've gotta find where they were, and then you've
gotta find the exact border and right-click and it's so much. Instead we can use custom
views, select the original view, and click Show to revert
back to the original view. The exact zoom level and viewability, meaning no columns or rows were hidden, that we were when we originally started. Now, let's go ahead and
build another custom view. Let's say I've got
somebody who's specifically in charge of the beverage
situation at Birch & Daughters. Meaning, they only really care about how many units we're selling in beverages. We're getting ready to order more drinks. So I'm gonna hide all the
rows that have anything to do with food, in this
case bagels and crumpets. I'm going to right-click and select Hide. Same deal once again, I'm going to hide any columns that don't
mean exactly what I need. So I'm gonna right-click on these first three columns and hide. I'm going to hide price,
and I really don't care about individual weeks sold, I only really care about total units sold. So I'm going to
right-click and select Hide and now I've got items,
total units sold, subtotal, and I really don't need taxes either, or total there you go. So now, I've only got my
beverage information here. In fact let's get rid
of average units too. Wow, we really hid a
lot that time didn't we? But if this is all I need out of that entire worksheet, why should I see anything else? Once again, I'm going
to click Custom Views, I'm going to click Add, and I'm gonna call this
the Drink Order view. And just like last time I'm
going to click Custom Views, Original, and Show to get back to home. Now, I've got two custom views, the CEO view which brings me to the exact view that I need to see, items and how much money
I'm making from them. And I've got my drink order view, which only shows me the item and how many I've sold so I know how
many more I need to order. And once I'm done with all those views I can always go back to the original. That's Custom Views, go ahead and try that for yourself and then come back. Welcome back, so now
it's time to shift gears and talk about the final
stages of presenting any worksheet or workbook
that we've created. When it comes to
presenting there's a number of different ways we tend
to share these files, but they all start with something that should be incredibly important, making sure you spelled
everything correctly. I mentioned it in the beginning, but I'll mention it again, Excel does not spell check in real time. Meaning that any time
you're typing any word into any cell, even if you misspell it it's not going to warn you the same way it would inside of Microsoft Word. We actually have to trigger it ourselves. Go ahead and find the Review tab here at the top of the Ribbon
and on the left-hand side you'll see the Spell Check tool. To trigger the spell check
simply click the button. Now of course you can also
see as I hover over it that the F7 key will
also trigger spell check. So I'm gonna give it a click here, and the first thing it finds
is here Englih muffins. Oops, but it gives me the
appropriate suggestion did you mean English? Yes I did. So I'm going to select Change. Now interesting, notice that it says, do you want to continue checking at the beginning of the sheet? You might not have noticed it before, but my cursor placement was right here. It should have caught
Coffe, shouldn't it have? However, because my cursor
was below that value when I started the spell check it started spell checking everything from here down. It didn't check anything above it. Fortunately, Excel does have the ability to check from the beginning of the sheet once it's checked everything else. So I'm gonna go ahead and click yes. And now it catches Coffe,
suggestions Coffee, I'm gonna select Change, and there I am. My spell check is now complete. Go ahead and spell
check your own document, and then come back. Now that we've spell checked the document, it's time to set things
up for if we're going to print this document. Now print tools aren't necessarily exactly where you'd expect them to be. Many of us anticipate that when we go to File and Print, we'll see
everything we need to see here, but that's not entirely accurate. In fact, we're gonna need to make sure that set some print settings ahead of time using the Page Setup section
of our Page Layout tab. Now you do have access to the Page Setup in a limited capacity from
the Print Preview menu. But it's important to note
that this is the only way to gain access to all the tools available is through this setup process. So find Page Setup and in
the bottom right corner you're gonna see a little pop out button, give that a click. And this is going to give
you the full Page Setup view. This is where we can
control the page orientation whether we're printing
in portrait or landscape, we can also dictate how it scales. Do we want it to be real size? Or do we want to fit
it to a specific page? If we say fit to one page by one page, it will only fit this entire
worksheet onto one page no matter what even if that means shrinking it down to an unreadable size. So do keep that in mind. Margins, of course we can control the margins if we'd like to. If you need a little bit of extra space on the left for punching
holes in to put into a binder. Or maybe a little bit
more at the top for notes. This is where you can input that there. Of course throughout this entire process by selecting Print Preview
you can get an idea of what this will look like. We also have access to
headers and footers, if you'd like to put a
date down at the bottom. Maybe you'd like to put the location or name of the worksheet,
the name of your company, how many pages there are. All these options are available inside the header and footer options. And lastly, the Sheet
the most important part. Now, let's say for example
we don't necessarily want everything to be printed
we just want a select area. For example, let's say we
only want to print the notes. Well by clicking inside the print area I can actually highlight
just the notes field. And with that having been highlighted I'm gonna go ahead and click Print Preview and check it out, that's
all that's being printed. This is called Set Print Area, you can also find the
Print Area button here, in the Page Layout tab. To set the print area from here, simply click on the drop
down, click Clear Print Area to remove any existing print area fields, and let's say I'd like to print just this. I'll highlight all of it, Set Print Area, and now by going to File, Print, you'll see only the selected
area now is being printed. To clear your print area
simply click on the drop down and select Clear Print Area, once again, and now if I'd like to do
anything else more with this, let's say for example I'd like to see what the page view will look like, I can go to the View tab and I can use any of these workbook views either to view specific page breaks, meaning
I'll see any individual page separation, in this case
here because I've got it set to fit to a single page,
I'm only seeing page one. I can also see it in Page Layout, this is what many people consider to be actual print preview,
because it actually shows the page as it will show when it prints. So there's a lot of control
over what we can do here, but it all starts with knowing that these tools are available. Go ahead and take this opportunity to try these options out for yourself. Remember, to set any of
the page setup options we're going to be inside Page Layout. Where we can control the margins, orientation of the page,
how big the page is. For my Excel veterans if
you prefer that pop out box we were using select that pop
out in the bottom right corner of the Page Setup command group, and you'll get the full
set of options available. Go ahead and try that for
yourself and then come back.
