Welcome to Highline Excel
2016 class video number one. Hey, if you want to
download this Excel workbook-- Busn218-Week01--
1 and follow along, click on the link
below the video. Hey, this is exciting. This is Highline's
full Excel class. And we are using
Professional 2016 version. This is our first video. We will cover
Excel fundamentals. Here is a list of our
topics for this video. But before we look
at these topics, let's go over to the
sheet 218 Pre-req. Now this is for
Highline class Business 218, which is called
Spreadsheet Construction. We will cover Excel
2016 Professional from beginning to end. And we will learn how to
make calculations and perform data analysis. In essence, we will learn
all about Excel 2016. Now the prerequisite
for this class is the Highline
class Business 216. Or you can watch the videos
from this Highline class at this YouTube playlist. Now, you must have one of these
versions, either Excel 2016 standalone, Office 2016
Professional, or Office 365 Professional Plus. They actually call it Pro Plus. Now I have links here
to each one of these. The standalone,
that's if you want to buy just the single Excel. Office Professional,
that has the full suite. Or you can get Office 365
Professional Plus or Pro Plus. And the 365 just means
that this version will be updated regularly. And since Office 2016 came
out in September 2016, they've already added some cool
new functions and features. So if you want the best
version, get the 365 version. Now when you get
that version, you're going to have to be sure--
and I'll remind you of this later when we get to the topics
on PowerPivot and data model-- but you want to make sure and
go to File, Options, down here in Add Ins, you want to
select Column Add In, Go. And be sure and select
PowerPivot for Excel. Once you do that, then
you'll have this ribbon tab that we'll get to
talk about later. One other amazing new
thing in Excel 2016 on the Data ribbon tab, there
it is-- Get and Transform. In earlier versions, we had to
download and install something called Power Query. This, Get and Transform,
is Power Query. And we'll get to learn about
that amazing new 2016 feature. Now, let's go over and
look at our topics. This video video,
number one, we're going to go over how
we're going to do things in this class, what
types of things we're going to do in this
class, and then we're going to talk about
data, data sets, formatting, including the ever
important number formatting. Now let's go over
to the sheet E and E because the goal
of this class is to develop effective and
efficient solutions in Excel for making calculations and
performing data analysis. Now, in order to
understand what this is, we're going to define
effective and efficient. So effective just means, hey,
accomplished the stated goal. Efficient means
accomplished the goal with the minimum
number of resources and have the accomplished
goal have the ability to adapt to future changes. So every solution
we make in Excel, we will learn how to do it
efficiently and effectively. Now effectively
is pretty much did we get the right answer, right? So if our goal is to count the
number of Fords from over here, well, that is not an
effective solution. Because I can count, it
looks like there's two Fords. And if I put this
formula in edit mode, it will simple mistake, right? We didn't highlight
the whole column. But that is not an
effective solution. The effective
solution, of course, is highlight the full column. Another example of
effective-- if our goal is to calculate the deduction
at a 7.65% tax rate, notice this formula, gross times
tax rate rounding to the penny, and this one both give
us the correct answer. So this is effective. But that right there
has to decimals hidden. That is not an
effective solution. When I look here, I should
be able to visually see exactly what the tax rate is. This is a simple fix. I can simply come up
to the Home ribbon tab and increase the decimal. But that is an example. Not effective based on number
formatting, not effective based on building an
incorrect formula. Now let's scroll down
and talk about efficient. Remember, efficient is
accomplished the goal with a minimum
number of resources and have the accomplished
goal have the ability to adapt to future changes. Example one, if our goal is
to count the number of cars that each sales rep sold,
well, if I'm looking over here at Joe. So Joe sold a Ford,
a Ford, and a Toyota. So over here, all three--
one, two, three-- solutions show the effective answer,
the correct answer. Joe sold two Fords, zero
Hondas, and one Toyota. Ah, but this is not efficient. Typing criteria into a formula. This actually violates
Excel's golden rule, which says any time a
formula input can change, you put it into a cell and refer
to it with a cell reference. But look at this, this person
had to type all of this in. That takes a long time. If our resource is time
to create a solution, this is not an
efficient solution. This also is not efficient. This person forgot to
lock the ranges here. Both of these-- look at this. Every one of these formulas had
to be typed out individually. Not efficient. It takes us a long time
to create the solution. Whereas over here,
F2, we have the ranges locked so we can
copy it down and over for both of the ranges. And we refer to our
criteria or conditions with cell references. This one formula can be
copied down and over. And it works in all cells. That is an efficient solution. Let's look at
example number two. If our goal is to calculate
the deduction at 7.65%, well, this formula is effective. It gives us the right answer. And it's efficient. This one is not. Not only does this violate
Excel's golden rule, that number is a
number that changes. So we should put it into
a cell and refer to it with a cell reference. The problem with a
spreadsheet filled with-- and this is called
a hard coded value-- but if you have a spreadsheet
with hard coded values, it's just too hard to change
and update the solution later. I can see this on the face
of the spreadsheet, which is easy for me to understand. But even better, if I need to
change it, I simply type 8.75%, and instantly, that updates. This does not. Now I'm going to
Control-Z. This set up here has the ability to easily
update when things change later. Another example of not
efficient-- that formula is not efficient. It's adding this column. This one is. Now, this one takes
too long to create. This one, much faster. But also, this will not
adapt to future changes. So if I were to insert
a row-- right click, Insert-- and watch this. I'm just going to
type the number one. I haven't hit Enter yet. Watch these two formulas
when I hit Enter. That one, of course,
is efficient. It's allowing us to change the
structure of the spreadsheet. And it totally updates. That one has no idea that I
just updated by inserting a row. Escape, and now I'm
going to Control-Z-Z. Now, let's go look
at our third example. And I'm going to
scroll over here. We're talking about efficient. Our goal is to add this column. This is what I don't want to do. I don't want to type
S-U-M, open parentheses, and then highlight
the entire column. Wow, that took a long time. And then I'm going to scroll up. And then I'm going to put
close parentheses and enter. Yes, it's effective. It gets me the right answer. But it's not efficient. I'm going to use keyboards,
because keyboards are fast. If we're building
a solution here, total that column and use
the keyboard Alt-= to open up autosum. I click in the top cell to
highlight all the way down to the bottom. I use Control-Shift
and down arrow. And now, to jump back
to the active cell, I use Control-Backspace. That is efficient. I hit Enter-- efficient
and effective. So all the solutions
we build in this class will be efficient and effective. Now we want to go
over to the next sheet and talk about the two
things that Excel does. I'm going to the
sheet What Excel Does. It's very simple. Broadly speaking, right,
Excel makes calculations like calculating the
tax rate or a deduction and does data analysis,
converting raw data into useful information. Here are examples
of calculations. And notice, this formula,
F2, calculates the deduction. Two cells multiplied
together and rounded. That is a numeric calculation
or a number formula. Excel can also
make calculations, F2, a logical formula. I'm asking the question
with this formula, is Meryl over the
maximum call limit? That is a logical formula that
delivers a true or a false. If I change this to 12,
that logical formula only has two possible answers. Control-Z. Down here,
we have a formula that's joining first and last names. So when I hit F2, that is an
example of a text calculation or a text formula. We're joining one cell,
a space, and another cell using the join
operator, the ampersand. Those are examples
of calculations. Examples of data analysis and
the definition of data analysis is very simple. Convert raw data into
useful information for decision makers. We will have a proper data set
with records in rows and field names at the top. And either with a pivot
table or with formulas, we can go from all
of the raw data into some useful
information that will help us, in
this case, determine the amount that each sales
rep sold for each product. Raw data into useful
information for decision makers. Those are the two
things that Excel does. And we will spend
the whole quarter making calculations
and performing data analysis in an
effective and efficient way. Now let's go over to
the sheet Structure. Just to remind you, in Excel,
there are columns and rows. Columns are letters. Rows are numbers. The intersection is a cell. In this case, it's B8. All of the cells make up the
worksheet, or simply sheet. Down here, we have sheet tabs. Each one of those tells
us the name of the sheet. All of the sheets together
create our workbook. That workbook has a file name. And it is up here. Now, two things
about these sheets. We can actually use
keyboards-- Control-Page Down or Control-Page Up. So I'm on structure. If I use Control-Page Down,
I'm moving to the right. If I use a Control-Page Up,
I move through each sheet moving to the left. Now one totally awesome
trick in these huge workbooks with lots of sheet tabs is
we come over to the arrows. And this moves the sheets,
but not the active sheet, just exposes more
sheets down here. Watch this, I can right
click those scroll arrows. I right click. And instantly, a huge list
of all of my sheets come up. And I can click on any one
and jump to any one of these. If I go to Custom Number
Format Notes, click OK, instantly, I'm on the
sheet Custom Format Notes. Right click, jump back to
Structure, and click OK. All right, I'm going to use my
scroll arrows to expose more, but not move, the active sheet. Now as we've already
talked about in terms of efficient Excel solution
creation, Keyboard sheet. We are definitely going
to use keyboard shortcuts in this class. Alt-=, click in the top cell,
Control-Shift-Down Arrow, Control-Backspace, and Enter. If I want to highlight
this whole column, Control-Shift-Down
Arrow, Control-1 to open up Format Cells. I can select Currency to
decimal symbol my negative and click OK. Or any highlighted
button in a dialog box can be enacted
with the Enter key. Now I can come down to the
bottom, Alt-=, and Enter. There are some other
important keyboards. But I do want to
mention Alt keyboards. Now we are going to use a few
Alt keyboards in this class. But let me show you what's so
amazing about Alt keyboards. Since there's not a keyboard
shortcut like Control-P for pivot table, normally, if
we wanted to make a pivot table, we have to go to Insert. In the Tables group, we
have to click on Pivot Table to open up Create
Pivot Table Dialog Box. Well, if this is
something you do every single day or
many times every week, Microsoft made Alt keyboards for
everything in the ribbon tabs. And you can teach
them to yourself. Now watch this. I'm seeing the Insert. In fact, I'm going
to click on Home. And my goal is to get to Insert. Watch this. Hit the Alt key. And what happens? The screen tips
pop up and tell you which key you have to hit
to get to whichever ribbon. Now these are succession keys. So I hit Alt and then let go. And now watch this. I'm going to hit N and let go. And now I can see there's a V.
So V, in succession, Alt-N-V. So I'm going to try
it-- Alt-N-V. That's how it will open Create
Pivot Table Dialog Box. All right, so we'll use
keyboards in this class. Now we're scrolling along here. We're going to talk
about some terminology and what raw data is
in Excel and data sets. But I want to first start by
clicking on the Data Analysis Terms sheet. Now, data analysis and
business intelligence terms. We're going to be using these
terms throughout the quarter. Data analysis is defined
as converting raw data into useful information
for decision makers. Business intelligence,
which has approximately the same definition, but
with a few extra words, business intelligence
is converting raw data into useful, actionable
information, oftentimes in the form of a dashboard
for decision makers in a business situation. So really, the difference
between these two is the word actionable. And that just means you
can act on the information to make some decision. Dashboards is just a final
end user report, oftentimes with charts and tables. And I added for decision
makers in a business situation because we're always going to
be in the business context. So really, I see both of
these terms-- data analysis and business intelligence--
as synonyms for each other. It really always is how do
we get useful information to make decisions or take
actions based on the raw data. Now raw data is defined as
data in its smallest form that allows Excel data analysis
features and Excel data analysis techniques to work. Now, it's not hard to
understand raw data. Let's go over to
the sheet Raw Data. Here we have a column
with addresses. It would be very hard
to sort by zip code or do a pivot table by zip
code or extract using Filter with zip code or city or state. Whereas when we
take that raw data and break it into
a smallest part, are features like sorting,
filtering, pivot tables, and things like that,
and formulas that do data analysis type actions work when
we have data in smallest bits. Now let's go back over
to Data Analysis Terms. We have raw data. The proper data set-- none
of our Excel data analysis features are going to work
unless we have a proper data set, also sometimes called
a proper table format. And it simply means field
names in the first row and records in subsequent rows. Let's go over to the
Excel Proper Data Set. Now in Excel, there
is one extra thing we have to be careful of--
field names in the first row. That means for any
data set, we have to have a name at the top
of the column that says what data goes in this column. Numbers for sales go here. Text items for sales
rep go in this column. Not only is it so we put the
data in the correct column, but later like pivot
tables, Power Query, there will always be
a field list listing each one of our field names. That way we can ask
questions and query and do data analysis
upon particular columns or combination of columns. Records and subsequent
rows-- each one of these is a record, or in this
case, a transaction. And in Excel, it's
very important. Number three, we have empty
cells or Excel row or column headers around the
entire data set. Now here's Excel row
headers that are numbers. But notice, the rest of the
data set has empty cells. Now, if we had
extra data over here that was like a node or a
formula or a calculation, data analysis features like
sort, filter, pivot table, and Power Query may
not work correctly. So Control-Z. We're always
going to leave those empty cells around our data set. Now two things about
a proper data set. This is a proper data set. And so is this. But notice, this has a unique
identifier or primary key in the first column. There are no duplicates. We are collecting bits of raw
data for each transaction. In Business 216 when we
studied Access, the database, we talked about primary
keys and unique identifiers. If you're a student at
Highline, your unique identifier is your student ID. That way in the first
column with student IDs, they will not mix up your
grades with somebody else. So definitely, this
is a proper data set. But look at this. This is also a proper data set. There is no unique identifier
in the first column. There are definitely
duplicates in the first column. Hey, for us in
Excel, either type of proper data set, whether it
has a unique identifier or not, will work. Now, in some cases,
as we'll see later, when we're building
relationships between tables, then it's going to
become important that the lookup table
has a unique identifier, a primary key, or no
duplicates in the first column. All right, now let's go back
over to Data Analysis Term. Raw data, proper
data set-- well, that's the ingredient for us
before we do our data analysis. But guess what. When we get the raw data
sitting in our proper data set, sometimes we need to
clean the raw data or transform the data sets. Now cleaning the raw data
means fix unusable raw data so that it can be used
to perform data analysis. Examples-- removing unwanted
characters like extra spaces, add needed characters,
split data apart, join data together, or
other cleaning goals. So oftentimes, we're going to
have to clean our raw data. And as we mentioned at the
beginning of the video, Get and Transform, also
known as Power Query, is just brilliant
for doing this. Also, transforming data sets. That means fix
unusable data sets so that it can be used
to perform data analysis. Examples-- sometimes we need
to filter out some of the data, combine multiple tables, merge,
append, or unpivot data sets. We might have to add, remove,
or filter columns in data sets. And there's other
transformational goals we'll have also. So any time we're talking about
data analysis and business intelligence, we
usually import the data, clean it, transform it,
then we have our raw data sitting in the proper data set. And actually, I put
import data down here. This is usually, at
least in Power Query, they Get and Transform
feature we're going to use-- we actually
will connect to external data sources to import it. Then Power Query will allow
us to clean it, transform it, before it imports it into Excel. But importing data
is very simple. We import data from
external sources-- and it can be a single table
or multiple tables or sources-- into either Excel or
Power Pivot's data model, or also we'll see an example of
Power BI Desktop, another data analysis business
intelligence tools. All of this-- there
is all of our terms. That's what we're going to
do for much of the quarter. And the goal of data analysis
and business intelligence is always create useful,
updatable, actionable information for decision makers. Now, before we go on to talk
about number formatting, we want to talk about a
couple more important aspects of data and tables in Excel. Now, I'm going to jump ahead. I'm going to go to
Excel Data Types. Now, Excel doesn't really
have a real data type like we learn in
Business 216 when we were doing Access databases. But there is a
type of data type. And there are a few
different data types. We can have text. So we have the word "Excel"
here or the word "rad", right? Notice that it's always going
to be aligned to the left. That is the default
alignment for text. As soon as we see something
aligned to the left, we're going to assume that
Excel considers it text. Now there is no problem. You can do stuff like this. Actually, it is a problem. I'm going to Control-Z-Z. We
will never, or very rarely, use alignment. Because we lose the ability
to see what type of data Excel thinks it is when we
start applying alignment. The only time we'll
apply alignment is on the finished reporting
product, the useful information end of it. The other data type is numbers. Those, we could type
in a number like 12. It's always going to be
aligned to the right. So that's our visual cue
from the default alignment that Excel thinks
these are numbers. We also have logical or
Boolean values, true or false. They will always be
centered and all caps. It doesn't matter if you
type them in lowercase. When you hit Enter,
it will always capitalize and be centered. Errors are something we
have to deal with in Excel. And sometimes, they are in data
sets or a result of formulas. They are always going to be
centered and capitalized. This one just means divide
by 0, that's not allowed. Next video, we'll see
some examples of errors. Another type of data
that we're going to have to deal with in Excel--
and it's not really data-- it's an empty cell. So there's just
nothing in there. And that can sometimes cause
our data analysis or formula calculations to do funny things. So empty cells we're going
to consider a thing in Excel. So text, numbers,
Booleans or logicals, errors, and empty cells. Now, let's just look at
why this is important. Notice, I add in this column. It's working. The sum function
knows what to do. There's our visual cue. It's to the right. So we know Excel
thinks it's a number. We're assuming that there
is none of this up here. Now what happened here? I'm using the same sum function. But since these numbers
are considered text, the sum function is
programmed to ignore them. And this happens
sometimes when we import data from a
database or copy and paste from a text file or something. Another use of this idea
of data types and default alignment-- if I type 15/2/2016
into Excel, when I hit Enter, it's aligned to the left. Well, dates are supposed to
be serial numbers, right? Well, there's no 15th month. So if I meant February 15,
I would have to change this. /15-- now watch this. When I hit Enter,
the default alignment says, OK, that's a legal date. Because it's aligned
to the right. That means it's
considered a number. Control-Z. As soon
as I type that in and it's aligned
to the left, that's your visual cue that
something's wrong with our date. Same with time-- 8:00-- and
I'm going to forget the space. That is immediately
aligned to the left. We know Excel thinks it is text. As soon as I add
the space, now I'm entering a legal time value. So when I hit Enter, that
alignment to the right tells me it is a number. Other examples are
20-- and I accidentally type two decimals, right? And so that is immediately
aligned to the left. That's my visual cue
that I had an error in entering the values. If I get rid of it
and hit Enter, boom. Now, next we want to
look at a common mistake. Here is some data
and some formulas. And what they've done is they've
used the alignment over here. And as soon as they
do that-- and again, centered is one of the more
common alignment mistakes that I see-- but
as soon as you do that, you lose your visual
cue for tracking down errors. I'm going to Control-Z.
So what's happening here is that some function
is trying to add this. But there are some numbers over
here that are considered text. Similarly, there
is another number. Control-Y, which
is undo the undo. That is centered and may
be fine for printing. But we lose our ability
to track down the errors with our visual cues. Control-Z. In general, if I
have formulas or the source data is raw data for
pivot tables or charts, I don't use the alignment. Now, it's certainly all
right for the final product. Here's an example of some stocks
and some statistics over here. And when you highlight
this, you can see clearly we've used center. But this is something
we're going to print out. It's easier to read this way. We're not going to
use these numbers as any inputs for formulas. So that is fine. All right, so alignment
is very important. Even though we don't
have a proper data type like we do in a
database like Access, here are the different
types of datas. And by the way, one
last final note. If you remember back to our
study of databases and Access, when we had a
column, we were not allowed to have text and
numbers in the same column. We had to define the
whole column or field as a particular data type. Now, when we get to Power
Query, Get and Transform, and PowerPivot, we
do have the ability, some of the new features,
to define a data type for a column. Now, we want to go over
to the sheet Excel Table and talk about the amazing
Excel table feature. Now, we have a proper data set. And what we'd like
is I'd like to have a formula here, just
the sum function, adding all of these numbers up. But if I add any new
records, I want the formula to update and include a
total of any new records that I add or delete. The way we can do
that in Excel is by converting this proper
data set into an Excel table. Now, you click in a
single cell in the table. And to get to the
Excel table feature, you go Insert, Tables,
and right there. Now, we're not going to
use the ribbon method. We're going to use that
keyboard listed right there. And you can see it says
Control-T. So you ready? Control-T. Now, it has
highlighted the correct fields and records because I have
empty cells all the way around. Now, the OK button
is highlighted. So I simply hit Enter. Now I have a bunch of new
aspects to this table. Now before we look at them,
I want to immediately notice that we're in the Table
Tools Design ribbon tab that popped up when I
converted this to a table. We always want to go over in
Properties and name the table. Now, since I make
tables all the time, as soon as I do
Control-T-Enter, I use the keyboard to name, which
will jump my cursor up here. It's Alt-J-T-A. And there
you can see it's highlighted. I'm going to call this
Product Sales, so ProdSales and Enter to register that name. Now a number of things happen. We get formatting. We get drop downs that
have sorting and filtering. But the most amazing
thing is that if we have a formula or a chart or
a pivot table or Power Query, any of the other features
that point to this, the ranges will update
when we add new records. So let's go ahead
and try this, Alt-=. Now watch what happens. Remember we named the table. And this column is
called Sales right here. So watch as I'm going
to click and drag. And as soon as I
do that, it puts in what's called the table
formula nomenclature. It's also called a
structured reference. There is the name of the table. And the field name will
always be in square brackets. If you remember
back to Access, we had square brackets for our
field names in Access database. Now when I hit Enter,
let's just come down here. Now there's two ways
you can add new records. One way is to simply
click in the cell directly below the table. And I'm going to type 1/5/2015. And when I hit Tab, you could
see the table automatically expanded to include that. The sale was $300. Now before I hit
Tab, look up here. Now when I hit Tab,
instantly, it updates. It is now looking at that new
number in this new record. This was a Quad. And notice when I type a Q,
it's got an auto-complete. Now, the other
way to add records is notice I'm in the last
cell in the last record of the table. When I hit Tab, instantly,
it creates a new record. 1/6/2015, Tab,
100, Tab, and Tri. Now, I'm not going to hit Tab. Because I don't want
to add a new record. I'm going to hit Enter. Now, let's go up and hit
F2 to put it in edit mode. And sure enough, that
is an expandable range. Let's make a pivot table. I'm going to click in
a single cell, Alt-N-V. And I want to put it
on this existing sheet. So I'm going to click Location. I'm going to click right over
to the side and click OK. And just like that,
we get our field list. There's our one,
two, three fields. I'm going to drag the
product name down to Rows. And in a pivot table,
when you do that instantly from the products column,
it shows a unique list, one of each item. Now I drag Sales down to Values. And instantly, because it's a
number I'm dropping down here, it will default to
the sum calculation. Now, I don't like row labels. So I immediately go up
to Design, Report Layout, and Show in Tabular or Outline. I'm going to choose Tabular. Now I see the field name. I'm going to leave it that way
without any number formatting. But here it is another object
pointing to this table. Now, you ready? I'm going to click below the
last record, type 1/7/2015, Tab, 500. Now watch what happens
when I hit Tab. We already know
the formula update. But this won't update yet. But let's do a Tab. And then this was for Carlota. So I type a C. Auto-complete
looks above and fills in Carlota. And now I hit Enter. Now, the sum function updated. But for pivot tables and later
for Power Query and PowerPivot, we will have to refresh. I'm going to come
over and right click. And because I'm inside the
pivot table, I see Refresh. When I click Refresh,
instantly, it updates. So Excel table feature,
we will be using it a lot. Now I'm going to use
my scroll arrows. And we're going to scroll over. And now we're going to
talk about number format. We're going to click on the
sheet Number Formatting. Now we're going to look
at a couple examples and then talk about what
number formatting really is. Here we have some sales numbers. And up in the Home ribbon,
in the number group, there's a drop down
that's pretty convenient. We can select in this case
either Currency or Accounting. I'm going to select Currency. But notice down here,
there's a bunch of decimals. And some numbers don't
have any decimals. So as soon as I click
Currency, what happened? Where did those dollar
signs come from? Where did that decimal
and zero zero come from? Well, let's click in this cell. And on the surface
of the spreadsheet, I see how many characters? One, two, three,
four, five, six. But look up into
the formula bar. What is really in the cell is
simply two characters-- a five and a zero. So there can be a disconnect
between the surface of the cells and what
we see and the number that lies in the cell underneath
the number formatting. Now, look down here. I'm going to click on this. It looks like there's one,
two, three, four, five, six characters in the cell. But look at up here. There's is one, two, three,
four, five, six, seven characters in the cell. Now in this case, if we were
never going to add this column, it's OK to display the number
to look as if it's rounded. It is not rounded. That number, $57.26,
is not actually the number in the cell. And if we added them, it
would not be adding $57.26. It would be adding all of
those extraneous decimals. And we'll talk about what to
do if we need to round later. In our prerequisite
class, you learned all about the round function. And we'll talk about
that in a little while. Now here's another example. What about over here? 25 plus 50 plus 10 plus 15. Well, of course, it's 100. But in Excel, we
get to do magic. 25, 50, 10, and 15,
the exact same numbers, hey, we get to make it equal 99. There is no way. As soon as you see
this situation, you have to suspect
number formatting. Now, I'm going to highlight
all these numbers. And the eraser for
number formatting is we go up to Number. And there's the dropdown,
general number formatting. general number
formatting will wipe away any number format
applied and show you what really sits in the cell. Now I'm going to
click on that General. And sure enough, over here
there were some decimals. And all we did was
decrease the decimals. So when I come up and
go Decrease Decimals, that is a number formatting
that displays fewer decimals. If I were to click on this
50, it's not really 50. It's 49.50. Formulas will not look
at number formatting. They always will look at
the underlying number. So sum is functioning on
whatever numbers there are plus the decimals. But over here, same thing. It just happens to be that
the numbers are displayed as they also sit in the cell. So our conclusions about number
formatting-- number formatting can display numbers on the
surface of the cells that can be different than the
underlying numbers that sit in the cell. Right here, we're displaying
the numbers exactly as they sit in the cell. Over here, we're displaying
the number differently than it sits in the cell. Our second conclusion for
number formatting-- formulas make their calculations
on the actual numbers in the cells, not the
numbers that are displayed on the surface of the cell. So that sum function is
looking underneath that number formatting and adding
all of the numbers, including all the decimals. Remember, you can
think of it this way. Number formatting is a facade. A facade just means
something on the surface. Like your house is painted
beautifully, but on the inside it's messy. At Halloween, you might be super
sweet, wonderful person inside. But you might have a
Halloween witch or zombie mask on the surface which is
different than the inside. Remember, what you see is not
always what is in the cell. Another important example
of number formatting-- we're going to learn the
keyboard for today's date. If you're ever entering
transactional data and you're entering stuff for
today's date, it's Control-;. So Control-;, and boom. It puts 4/5/2016
right in the cell. Now actually, watch this. I'm going to click Escape. Because notice, the
number formatting is general before I do anything. In fact, general is the default
for all cells when you open up an Excel workbook unless
they've been altered, of course. All right, so you ready? Control-;. As soon as I-- and I'm
going to use Control-Enter because I want to put
that thing in the cell and keep the cell
selected-- so ready? Control-Enter. Wow, look at that. So when I enter today's date
whether using a keyboard or typing it out, it will
always apply the date number formatting. Now I'm going to come down
here, Control-;, Control-Enter. And now, instead of going up and
applying general to see really what is in the cell like this,
I'm going to use the keyboard. It's Control-Shift either
Tilde or Grave Accent. This key is to the left of
the standard number 1 key. So you ready? Control-Shift-Grave
Accent-Tilde. What? 42,465 sits under April 5, 2016. Yeah, and you all know this
from your prerequisite class. That is the number of days
since December 31, 1899. And why is it? Well, it's so we
can do date math. Now, before we do some
examples of date math, if I have the number
one, two, or 41,521, these are the actual dates if
we were to apply date number formatting. Now, just a second
ago, we did a date. And then did
general to erase it. But you can do the
reverse to prove it. These numbers, if we apply
date number formatting-- and I'm going to use the short
date-- boom, there it is. Control-Z. What this
means is under every day is a number that we
can do date math. Remember, if this is
8/26/2013 and this is 8/21, this date is further
through history, which means the serial
number underneath is bigger. So we're allowed to
calculate the number of days an invoice is late-- equals the
larger date minus the earlier date. And when I hit Enter,
it tells me five days. Now, the reason-- and
I'm going to highlight Control-Shift-Tilde to look at
the underlying serial number date, the number of days
since December 31, 1899-- of course, bigger number
minus smaller number, there's exactly five
between the two. Now, Control-Z. When
you're doing invoices, of course when I
take 26 minus 21, the 21, which is the
first date, is subtracted. That 21 is not included. And that is the way
standard invoicing works. Now, if we have a
different situation, project start date
and project end date, if we want to figure
out how many days total for the project,
equals-- and watch this. I'm going to use my arrow keys
to get the cell references. And then I'm going to type
subtraction and arrow, arrow to get that other
cell reference. The rule is if your cell
references are close in, instead of using your
mouse to get them, you should use your arrow keys. Now, that won't give
me the correct date. Because the 21st
will not be included. So any time you have
the first day included, you have to add one back in. Control-Enter. And so total days for
the project is six. Another great use--
and look at this. I had this left in there. I didn't want to
have it like that. If we have the loan
issue date, 2/20/2016, and the loan is outstanding for
10 days, I can do date math. I can take the serial
number date that sits underneath that
date number formatting and just add a
regular old number. And so now if I took the
loan out on the 20th, I add 10 days, Control-Enter,
that is not number of days. That is called
the maturity date. That is the date that I
must pay back this loan. So date math, number formatting,
incredibly important in Excel. Now let's go look
at another example. And this we did in our
prerequisite classes also. Any time you type a time-- so if
I come over here and type 8:00, and I forget the
space-- we talked about this earlier-- that
is text aligned to the left. But if I include
the space, Excel knows that this
should be a number. And by the way, look. I haven't entered this
number as a time yet. So it's still general. But when I Control-Enter, time
number formatting, custom time number formatting, is applied. That number is
aligned to the right. It is a time value. Now time values represent the
proportion of one 24-hour day. So underneath 8:00 AM
is 8 divided by 24. Now if I apply general,
Control-Shift-Tilde or Grave Accent, sure
enough, 8 divided by 24 is 1/3 or 0.33333. Control-Z. So all
times are going to be whatever the
time is divided by 24. Now this is important to know. Number formatting,
underlying number. Because if we go to
create a formula, remember formulas act on
the underlying numbers, not the number formatting. So if I were to take equals the
later time minus the earlier time, Control-Enter, it will
add that custom time number formatting. It looks like it's right, right? 3 and 1/2 hours. But if I have a formula
over here that says, hey, $25 times 3 and 1/2 hours, that
should be something like $82.50 or something. This person's going
to be totally unhappy. $3.65 for 3 and 1/2 hours? Well, we're going to wipe
away the number formatting to see what's underneath--
Control-Shift-Tilde or Grave Accent. Of course, 3 and 1/2
hours divided by 24 is exactly that decimal. So anytime you are
doing time math and you want the
actual number of hours, we force subtraction
to happen first. Remember, our order
of operations, right, by parentheses? And we multiply times 24. Now, 24 is an example of a
number that will never change. So we can just type it
right into our formula. And instantly, I
get, hey, 3.5 hours. And oh, I can't do
math in my head. I said $82.50. It's $87.50. So number formatting
is a facade. This is yet another
important example. Our formulas act on
the underlying number. So in this case, we had
to know that and create a formula that was with respect
to the underlying number. All right, other examples
of number formatting. But first, I want to ask the
question, what is a percentage? Now, all of you should
know what a percentage is. There's the pre-requisite
class Business 216. Also, if you had Business
Math, Business 135. But a percentage is simply a way
of displaying parts out of 100. So I always think
of it this way. Let's think of tax. If the tax rate is 9.95%. I think that's the
tax rate in Seattle. So I'm going to say,
hey, for every $1.00, I have to pay 9.95 pennies. Now, how many
pennies are in $1.00? 100. So the question is how
many parts out of 100 are we required to pay? Well, it's simple division. I say 9.95 divided by 100. And so what I get as a decimal. And that is 0.0995. Now, sometimes it's hard to
quickly see and understand a decimal, right? Especially in terms
of the tax rate. So we can display
this underlying number as a percentage. Now, here's what it does. And we learned this
in many other classes. But when you apply percentage
number format, even when you were taught how to do
it by hand, remember, you had to take the
decimal and slide it two positions to the right. So the decimal should be
right after that first nine. Then we add a percentage symbol. That is the process to
display the underlying number as a percentage. Hey, I'm just going to
go up and I am not going to use that button right there. That button when I click it,
sure, it adds a percentage. But it displays no decimals. That is a huge source of
errors for people who don't know about number formatting. Now I'm going to Control-Z. For
us, it would have been easy. We could have just
increased the decimals. But since oftentimes
we're dealing with two decimals
for a percentage, the built-in in the Number
Group dropdown is Percentage. And it will always,
if you click this, show percentages
with two decimals. And sure enough, that
number formatting displaying on the surface of
the spreadsheet or the cells is displaying the decimal
and added a percent symbol. Now, many times you will
get a number like this. It is perfectly all right
in all of your calculations for budgets and taxes
and invoice and payroll to leave it like that. But if you want to
apply percentage number formatting, no problem. We simply go up and use
that if you want to. Or we can Control-1 to
open up Format Cells. And the Percentage
number formatting is one of the options here. And sure enough, we can
change the decimals. I'm going to leave it as two. There is OK. I'm going to hit Enter to
enact that highlighted button. Now I want to specifically
look at a number of important examples
for percentages. Oftentimes, you see the
number 0.03 in the cell. Or maybe you even
type it in because you know the tax rate is 3%. So you type that in. And then you come up and you
apply the number formatting. So there it is. The big mistake-- and this is
one of the more common mistakes throughout all of
Excel history-- the person doesn't know
about number formatting. So they type a 3,
fully expecting when I go up and apply
number formatting that I'm going to get 3%. But no way. That number
formatting obeyed you. Control-Z. It took the decimal
which is after the three and went-- slid it two
positions to the right and added the number symbol. So we don't want to do that. If you're going to type the
number in, first type 0.03. Now, there is a way
to avoid that if you pre-format your cells. And I'm going to
go up and apply. And notice, we don't
have a preview here. But it will always from this
dropdown apply two decimals. Now watch this. If I-- and I can see it's
pre-formatted-- if I type a 3, that percentage symbol pops up. It means that the cell
has been pre-formatted. Now I'm typing what looks like
an integer or a whole number 3. But when I enter that,
underneath is 0.03. And it's properly
displayed as 3% So I pre-formatted. I'm totally allowed to type a 3. You're also allowed-- and I
can see the pre-formatting-- to type 0.03. And when you hit Enter,
that will work also. Now in either case, when
I typed a 3 or 0.03, because percentage number
formatting was applied, if I Control-Shift-Grave
Accent-Tilde to look at what's under the number
formatting, they both have three
parts out of 100. Control-Z. A couple other things--
you can format as you type. So if you want 4.5%,
right now, if I enter it, it is 4.5, whole
number 4, 0.5 decimal. But if I type a percentage
symbol-- and you can look up here. I haven't hit Enter. So it was general
number formatting. But I've typed a
percentage symbol. So when I Control-Enter,
I'm telling Excel to format this as a percentage. As I type, I can see clearly
this is a percentage. Underneath that number is 0.045. One other important
aspect-- and we already saw this-- this one
of the most common errors in Excel. Somebody has a proper decimal
for a tax rate or budget or whatever it might be. And they come up and they
use this button here. Boom. If they don't know about
number formatting as a facade, they're totally tricked. They think it converted
their number to 3%. Of course, if I look at 100
times 3%, I should get $3.00. But that formula is not going to
look at that number formatting. Totally going to look
underneath at that 0.025. I could clearly fix that by
simply clicking the Increase Decimal. That's how we want to leave it. Remember, as we
talked about earlier in the video, effective
and efficient solutions. We do not leave something
that will confuse the user of the spreadsheet. If it's 2.5, we want
to leave it 2.5. Hey, we've talked a lot
about number formatting. Most of this is review. But guess what. We're going to talk about
something new that has to do with number formatting. We're going to click
on the sheet CNF. And CNF stands for
Custom Number Formatting. That just means
instead of using one of the built in categories
for number formatting either from this drop down or
Control-1 on the number tab from this list of categories,
we can create our own custom number formatting as
long as we type the code. Now, I have a whole sheet
with notes over here with all the code. But at this juncture
in the class, we're just going to look
at date, time, decimals, and percentage
number formatting. And we're going to start with
the easiest number formatting to learn, which is the
number formatting for dates. Now, when we get to typing
out our code, it's easy. M means month. D is day. And Y is year. Now let's just go
learn a good trick to teach ourself about
custom number formatting. There's already date
number formatting applied on this cell. If I Control-1, I can see
on the Number tab Date. I've selected this top one. And by the way, there's a
bunch of subcategories here. You can pick whichever
one you'd like. And notice up at the
top, there is a preview. But we're going to
select this one. And if we want to know what
the custom number formatting is for whatever built in category,
we just simply going down to custom. And in the Type text
box, there it is. It's simple-- m/d/yyy. Now I'm going to click Escape. And we're going to
click on the cell below. And now we're going to try
this for ourselves, Control-1. We come over to
Number, down to Custom. And in the Type text box, we
simply highlight-- and watch this. This is always going to give
us a sample or a preview. I'm going to delete this. And sure enough, if there's
no custom number formatting, it shows us the
underlined serial number. Now let's have a little fun. Let's type M. Wow, even
though the serial number is in the cell, if
I type a single M, it'll just show me the
number for the month. Type another M and
a third M. Wow, it shows us the three letter
abbreviation for month. Let me type a fourth
M. And no way, it will display the
entire month name. Now let's backspace. If I type a D, just the day. Now, two Ds shows
us a leading zero. And that will work
for month also. 3 Ds gives us the abbreviation. And four Ds gives us
the full day name. Let's backspace. If I take just a Y, it shows
me a two-digit abbreviation for year. If I type three, it gives
me the full year, or four. Now what I want to do is
simply in this cell right here display just the day. So I'm going to take DDDD. That is our first custom
number formatting. We are displaying a serial
number to show just Thursday. So when I click OK, it
displays on the surface of the spreadsheet Thursday. Underneath, yes there is a date. And under that, there
is the serial number. Why would you ever
want to do that? Maybe you're making a
schedule or calendar and you want to
display the day name. Also later on in
the class, we'll see that it's
important sometimes to figure out how many events
happened on a particular day. And custom number
formatting will help us figure that out from a
serial number what day it is. Now let's try a different one. Click in the cell below,
Control-1, come down to Custom. And in the type, I want
to show the entire day. So I put four Ds. And I'm allowed to type
a comma and a space. And now I want the abbreviation
for month, so three Ms, Space, the day, so a single D,
comma, space, and then YYY. And you can combine this however
you want to get your own custom number formatting. So when I click
OK, it is displayed as if there is text for
Thursday and November. But really, underneath,
there is that serial number. Now, time-- H is for hour. M is for minute. S is for second
separated by a semicolon. So here I'm going to
Control-1 and look at what the custom
number formatting. An H, a colon, and an AM/PM. If I were to delete
this, sure enough, there's the serial number. If I type just H, it
shows me just the hour. Just M, just the minute. Just an S, we don't have any. So it shows zero. Now in this case, I want
to display a leading zero. So I'm going to type
HH and a colon, MM. And now I see 09:57. Now I want to show
either AM or PM. So I do a space,
AM/PM, and there we go. When I click OK, it displays the
leading zero, hours, minutes, and an AM or a PM. If I type 3:00 PM--
by the way, remember, if you don't type
in times correctly, that becomes a text value. But as soon as you
add that space, it is a time value
aligned to the right. And that works. Now, what if you didn't
want to show AM/PM, but you wanted military time? No problem. Control-1, I come
down to Custom. I highlight this. I simply typed HH:MM. And that will give us military
time, three hours past noon. Click OK. Now, if someone decides
to put 3:00 space PM, guess what, it'll enter. But it will display
as military time. You can see up in the formula
bar it actually is always going to be stored with the AM or PM. But our number formatting will
display it in military time. Now we want to talk about
displaying number of decimals. This is similar to clicking
these buttons over here. Click in the cell, Control-1. I'm going to come
down to Custom. And now, highlight General,
type a zero, a decimal. And watch this. If I type four zeros, that's as
if we clicked Increase Decimal four times. So in this case, I want to show
always the number of pennies. Now what this zero
says is please show me significant and
insignificant digits. So the 4 is significant. The 0, since we don't
have a digit there, is considered insignificant. Click OK. Now it displays as, 1.40. But remember, now
that we have those two 0's there, if I
were to type in 1.755, this will be displayed as 1.76. Control-Z. Now let's see what
happens if-- Control-1, down, and Custom. There's that 1.35. If I typed in 0.0, I'm limiting
the number of decimals. Click OK. So even though we see displayed
1.4, up in the formula bar is 1.35. Now, the 0 shows significant
and insignificant digits. If you only want to
show significant digits, you can use the pound sign. So let's try it-- Control-1,
come down to Custom, and in the Type text
box, I type you #.##. Now notice, I can
already see the preview. That pound is there in case
I type an extra decimal. But it will not display it
if there is nothing in there. So when I click OK, 1.4. If I type 1.35, the
35 is displayed. If I type 1.455, again, because
I only have two pound signs, it's only going to show
me two significant digits. Control-Z. The last custom number
formatting we want to look at is percentage. And we will use the 0's. We simply have to add
a percentage symbol. Now this is 0.3575. If we format it as a
percentage, it's 35.75%. Control-1, come down to Custom,
and in the Type text box, 0.00. And notice that without
the percentage symbol, it's just showing how
many decimals to display. But as soon as I type that
percentage symbol, boom, it slides the decimal over and
adds the percentage symbol. Click OK. Similar to our
0's for displaying only a certain number
of decimals, Control-1, we can come down to Custom. And in the Type text box,
0.0 and a percentage symbol. And you can already
see the result there. Click OK. It is displayed as 35.8%. But really, 35.75%. And under that is 0.3575. So this is our first glimpse
at custom number formatting. Now we have a little
bit more power to display the
numbers however we want on the surface
of the spreadsheet. We saw back over
here-- remember, the number one idea or
concept is number formatting is a facade. Hey, now, we want to go over
and talk about style formatting. So I'm going to click on
the sheet Style Format. Now, style formatting
is simply adding things line bold, fill color, font
color, borders, and more. Now we have some guidelines
for style formatting. Number one, use
the minimum amount to effectively
deliver the message. Be consistent. That means if you're going
to pick a particular color and font for field names,
be consistent and always use that particular
style formatting. All numbers should have
the same number of digits. That's, again, being consistent. And units must always be
indicated with either number formatting or labels. So if it's money, you need
to point out it's money. Percentages, use percentages. Now there's two
schools of thought for stylistic formatting. Minimalism school--
that school says, hey, I'm going to have bold
for the field names at the top. But why do you think they
have those gray lines? I'm not going to add
any extra borders. And I'm going to very
rarely use fill or font color or things like that. The more than minimal
school says, hey, I'm not just going to add bold. I'm also going to add perhaps
font color or fill color and maybe I'll
add black borders. All right, here is
the minimal school. I'm going to use the keyboard
Control-B to add bold. And I am going to add
some number formatting. These are accounting numbers. So I'm going to highlight
all the numbers, Control-1. Here's the Format
Cells dialog box. I'm going to choose Accounting
and choose to show two decimals with a dollar sign. Now, we didn't talk about
accounting number format. But there are a
couple things that distinguish it from currency. One is negative numbers
show up in parentheses. 0's show up as dashes. Not only that, but
the dollar sign will always be
fixed on the left. Now this doesn't look
too minimal to me. So I'm going to highlight the
minimal numbers and Control-1 and choose to show
no dollar sign. Click OK. So I've indicated the
unit up here at the top and for the bottom line number. That is an example of minimal. Now let's do less than minimal. I'm going to Control-B,
come up to Font group. And for fill, I'm going
to use the Fill Bucket. I'm going to use dark blue. And then for font color,
I'm going to use white. Now, I'm going to
Control-Z-Z. There's another way we can do this. When we highlight
we can right click. And look at that. Up pops the mini toolbar. I can select Fill from
here, and then Font Color. The rule for fill
and font color is there has to be a big
enough value difference. So for example, if I choose
red fill with black font, that is not going
to print correctly. If you squint your
eyes at this and you can't see a very big
difference in value between the font
color and the fill, you know the difference in
values is not big enough. I'm going to go back to my
dark blue and font white. Now I'm going to highlight the
whole table using the keyboard Control-*. And I'm going to use the
asterisk on the number pad. If you're using the
normal number pad, you have to do Control-Shift-8. I like Control-* on the
number pad because instantly, it will highlight
the current table. Now, I'm going to do Control-1
to get to Format Cells. And really, Format Cells
dialogue box has everything. It has number formatting. It has alignment. It has font, all sorts
of things, borders, fill. And I'm going to
go back to Borders. And I'm going to select Outline. That does just the outside. You can see right here
it gives us a preview. And then inside, that does
all the vertical lines and all the horizontal lines. Click OK. Now I'm going to add some
other types of borders. In accounting, oftentimes
we have a line, a dark line, right there saying I just did
a calculation on the numbers above. Because this is a formula
adding the total expense. And so I'm going to highlight
just the total expense line, Control-1. And on the Borders tab, I'm
going to select the border. Then I'm going to
select the color. In this case, I'm
keeping it automatic. And then I'm going
to come over here. And notice, I want
the line on the top. So I'm going to
click the very top. Similarly, you could have
used that button right there. Click OK. Well, that didn't change. Oh, I have to click
off to the side. So there, that dark
line in accounting means I just did a calculation
on the numbers above. Similarly, down here,
there is net income. We did a calculation
on the number above. Notice, that line
doesn't mean adding. Because it could be
adding in this case. But certainly, it
could be subtracting, or in cost accounting,
there's all sorts of different
calculations you can do. I need that dark
line at the top. And then I need a double
line at the bottom. That double line means
this is the bottom line. This is the number I
was trying to calculate. Control-1, and now I
have two different lines. I'm going to select the
line, select the color, and then draw it. I can either click that button
or click right on the top. Now I need to go back to
get my next border-- Border, Color, and then Draw. In this case, I could
click that button. Or I always like to
click and draw it myself. Click OK. Off to the side,
and there we go. Now I'm going to highlight
all the numbers, Control-1. On the Number tab, I'm
going to select Currency. That's the type of
negative number. I need to see the dollar
sign and two decimals. Click OK. Now the difference between
currency and accounting-- well, accounting has fixed dollar
sign, zeros are dashes, minuses are parentheses. For currency, we have the choice
of how to display our minus. 0's show up as 0's. And that's called a
floating dollar sign. Notice it floats with
how wide the number is. Now, I'm going to do the same
trick on the inside numbers. I don't want it as cluttered. Control-1, and the symbol,
I'm going to say none. Click OK. Now there's all sorts
of different types of style formatting. This is just two
examples-- a minimal and a less than minimal. Now, I actually had
some notes up here. It says minimalism school says--
more than minimalism school says-- I actually have notes. This is video number one. And so for every single
video, there will be notes. And right next to the Excel
file that you download, you can download these notes. And they're pretty
detailed notes. This is the table of
content up at the top. If I click on this
style formatting, it will jump to that section. And there are the full notes. And so these notes have a lot
more detail than the notes you see in the spreadsheet. So be sure and download these. And they correspond
pretty exactly to what you see in the videos. All right, we have one
last stylistic example. Here's a data set. And right off the bat, we
have field names at the top. But notice, the column
widths aren't wide enough. So I'm going to highlight
with my downward pointing black arrow
the G and click and drag all the way to I. And I'm
going to see what happens if I double click to best fit. Now sometimes that'll work,
sometimes that won't work. And why didn't it work
for this G column? Because best fit
will always best fit to the biggest
thing in the column. So in this case, I probably
want to manually click and drag this. Now, if we want to wrap text--
that means have employee and then name on the next
line, sales and amount on the next line--
we could actually come up and use the
automatic button Wrap Text. That's a type of alignment. Now notice, it
didn't do anything. It won't do anything until
I change the column width. Now once I do that, I have
to come over and double click between 14 and 15. And instantly, now I have
wrapped text in the same cell. Now, if you would like more
control than the Wrap Text button, meaning perhaps
I want product and name on the next line, we can
manually set line break or wrap text. I'm going to put my
cursor, double click right before the P, Backspace. And the keyboard to do a
manual line break or wrap text is Alt-Enter. Now, when you
Alt-Enter, you still have to have wrap text
to get it to wrap. If I didn't have
this turned on, you could see that wouldn't work. So I'm going to wrap text. And instantly, that worked. But the break will always happen
after the word "boomerang". Let's try the same thing over
here-- Backspace, Alt-Enter. Right here--
Backspace, Alt-Enter. Now I'm going to add a little
bit of formatting here. And I do want to show you
one really cool thing. Let's just format
this first cell here. I'm going to do fill, dark
blue, font color, white. And then I'm going to click. It just happens to be
the right border there. Notice I could select all sorts
of different border options. But I'm going to
click that button. Now what I want to
do is save that. In fact, I'm going to
Control-B. So now we have fill color,
font color, bold, and an outline for the cell. What I'd like to do is
save that as a cell style so I can use it later. That took four clicks, right? But if I save it
as a cell style, I can simply have a single click
to apply all four formatting elements. So I've formatted the cell. I come up to the Styles
group, click the More button, and then down here, it
says New Cell Style. Now, it automatically
picked up everything. If you wanted to do more,
you could click here and go through the
Format Cells dialog box and do whatever you want. I'm going to click Escape. But what I'd like to do is
call this Field Name Blue. And now I can click OK. Now, I'm going to highlight both
cells, come up to the dropdown. And notice, I have a
bunch of them here. Because it's picking up
other times I've done this. But there's the new
one that I've selected. That is pretty convenient. And instantly, it is applied. All right, so a little bit
about stylistic formatting. We'll do lots of
stylistic formatting throughout the
rest of the class. Now I want to come over. And our last topic
is page setup. So here we have an
amortization table. Control-Down Arrow,
looks like it's 365 rows. Control-Home jumps
up to cell A1. And now I want to look
at what this would look like if I didn't do page setup. Control-P-- and
sure enough, there's a preview I can click through. And there it is. I would like to do
things like center this. And I'd like to repeat the field
names at the top on each page and have page numbers. There are some options here. I'm going to click Escape. But I'm going to come up
and go to Page Layout. Page Setup Group-- and
there's some options here too. There's the dialogue launcher. If I click that, it
gets me to Page Setup. But I want to learn the
keyboard-- Alt-P-S-P. Now, how do you determine
portrait and landscape? Well, this is much
taller than it is wide. So I'm going to select portrait. You could adjust. Now for us, we're going to
print this on many pages. So I'm not going
to use this adjust. But if you had one
page and you wanted to blow it up or shrink it
down, you could do Adjust To. What we want to do
is say, hey, Fit To. Now, I always want it
to fit to one page wide. You can already see there's
a dashed line down here. That means it will
not fit onto one page. So I definitely want
to say one page wide. But I don't know
how many pages tall. So the trick is highlight
what's over there and delete it. By deleting it, it
will automatically print out as many as there are. If I were to add more
rows or delete more rows, it would always know to print
out the right number of pages. So one page wide by
however many pages tall. Now, this was the Page tab. Now let's go over
to the Margins tab. We definitely can set top,
bottom, left, and right, and even where the headers are. We don't need to do that here. So I'm simply going
to check Horizontal. And that will automatically
center it on the page. Now I want to go over
to Header and Footer. Let's start with the header. There's the preview. There is nothing. There's the dropdown
for built-in headers. And there's the Custom
button for custom header. Now, we have three sections. We're not going to
put anything here. So I'm going to click Escape. But for the header-- Preview,
built-in, fully customizable. For the footer, I
don't see a preview. Here's the dropdown. I am going to select Page 1 of ? The page 1 of ? simply means it'll print
out-- if there's five pages, 1 of 5, 2 of 5, et cetera. If later you have 10 pages,
it will say 1 of 10, 2 of 10. And we can see down
here in the preview, it's got our built-in footer. Now I want to
customize this further. So I'm going to come
up to Customize Footer. There's the code for
that number of pages. Now we start on the left. If you want to show
the date, that's the code to automatically
print out today's date. If I do a space, dash,
and space, notice, I can mix type and stuff with
the code from these buttons here. Now, I'm going to
click on this button. And it puts in
the code for time. So it will always
print out the time that I print this workbook. Now I'm going to come
over to the right section. I could put the full file path. I could put the workbook name. I could put the sheet name. Now, I'm going to put
the sheet name here. Now what's nice about
this is it's dynamic. It's linked. If I change this later,
this will update. That is a great button if you
have, for example, a budget with January, February, March. You do the page setup
on the first page. And every time you
copy the sheet over, it will automatically pick up
whatever sheet name there is. All right, I'm
going to click OK. Header and Footer, Preview,
Built-in, Customize. Now let's go over
to the Sheet tab. Print Area-- this
is great if you wanted to exclude some
part of the sheet. So for example, if I only
wanted to print out this part, everything else
would be excluded. Now that is
especially useful when you have calculations or notes
off to the side which are not part of your final report. Now, we don't have a print area. We're going to print everything. So I'm going to delete it. What we want to use is
Rows To Repeat At Top. Watch this. I'm going to click right
on Row Header Four. It will automatically
print out those field names at the top of each sheet. You could also do columns
if you had a table that was really wide and you wanted
to repeat names or company names or categories, you could
highlight a particular column. There are some other settings
here that you can read through. You can also determine
which way if you have many tables to print. That's it for us. All we want is rows
to repeat at top. I'm going to click OK. And now, when I
Control-P, look at that. It is center. I have my footer. When I click Next
Page, look at that. It repeats just that row four. That is quite spectacular. There is our page numbers. All right, I'm going
to click Escape. I'm not going to print. Let's just come down here. I'm going to double click and
call this Amortization Table, and Enter. Now, when I
Control-P, boom, that is totally dynamic and updates. Escape. All right, so that was a
pretty epic first video. We talked about page setup. We talked about
style formatting. We talked about number
formatting as facade. Sod We talked about
Excel data types. Here's the list right here. We talked about the amazing
Excel table feature. We talked about terminology,
Excel proper data set, field names at the
top, records and rows, empty cells all the way around. We talked about raw data. We defined some data analysis
and business intelligence terms. We talked about keyboards. We talked about the
structure of Excel. We talked about the
two things Excel does-- calculations and data analysis. We talked about our
goal in this class-- developing effective and
efficient solutions in Excel. And we started off the class
talking about the version of Excel we're using. This is Professional 2016. All right, last thing. Let's come down to the
scroll arrows and right click to open up the dialog
box to navigate through all of our sheets. And we want to scroll down. And I want you to click on the
Homework sheet and click OK. So at the end of
each workbook, there will be homework
problems for you to do. You click on the blue one,
read the yellow cells, complete the problem. The red ones have the answers. But don't you dare look at
them until you've completed. So in this chapter, there's
six homework problems. All right, now next
video, we'll talk about more Excel fundamentals. We'll talk about
formulas and functions. All right, we'll
see you next video.
