Welcome to Highline Excel
2016, video number four. Hey, if you want to download
this file, Busn218-Video04Start file, there's also
the finished file. And don't forget
there's PDF notes for each one of these videos. Now, if you want to
download any of these files and follow along, click on
the link below the video. Now, these PDF
notes have 14 pages of all sorts of amazing
notes for our topic, which is the nine different
types of references that we can have in Excel. We can have relative
cell references, absolute, mixed cell
references with the row locked, mixed cell
references with the column locked, we can have
sheet cell references, workbook cell references, 3D
cell references, defined names, and of course, table
formula nomenclature. Now let's go over to
the sheet references. We have 25 amazing examples. Now the four basic
types of cell references are relative, absolute,
mixed cell reference with the column
locked, or mixed cell reference with the row locked. Now we actually do not
need to consider what type of basic cell reference
we have if we're never copying the formula. But as soon as we copy a formula
that contains cell references, then we need to consider which
one of these cell references we need. Now our first two
examples we're going to look at-- cell
references when you're not copying the formula. So here's our first example,
we have revenue and expense. We need to calculate net income. It's a pretty easy formula--
revenue minus expenses-- so I click in cell
B9, equal sign-- and I'm going to use my
arrow keys-- up arrow, up arrow, minus up arrow. Revenue minus expenses but I
am not copying this formula, so I'm not even going
to think about what type of cell reference it is. I'm just going to hit Enter
and there it is, 13,338. Now our second example
is a finance example. We have $3,500
which we are going to invest at the end of each
year for our retirement. We're going to do
this for 15 years and we're going to assume a
constant interest rate of 4.5%. Now if we were investing in a
bank CD or a guaranteed fund, then this amount would
be a contractual amount and it would be 4.5% every year. If you're investing
in the stock market, this would then
have to be your best estimate of the
rate you're going to earn over the next 15 years. Now Excel has all sorts of
amazing financial functions and if we're putting this
money in for 15 years, our goal is to calculate
what's called the future value. That means, at the
end of 15 years, how much will we
have in our account? Well, future value-- they
named the built-in function for future value smartly, fv. Now, you can read screen tips
before you actually hit Tab and accept it from
the dropdown list. This screen tip will tell
you what the function does. Now let's read this. Returns the future
value of an investment based on periodic
constant payments and a constant interest rate. That's exactly what
we have and that is a typical type of financial
investment situation. I'm going to hit Tab. Now the future value
function has 5 arguments, and these arguments are
very similar across many of the financial
functions in Excel. Rate simply means
the period rate. For us, we have an annual
rate, so our period is one year, so we put the
cell reference for that 4.5%. Now this formula is never
going to be copied anywhere, so I don't need to worry about
what type of cell reference that is. Now I simply type
comma n per-- that means total number of periods. For us, our period is one year,
so we have a total of 15 years, so I click on that
cell reference. I don't need to worry about
what type of cell reference, I just put it in
the n per comma. PMT is the universal finance
variable for payment. That means some equal
amount that we're putting into our account each period. For us it's $3,500. Now, when I click
on $3,500-- sure, I do not have to worry about
what type of cell reference because I'm not copying it,
but in finance, you always have to worry about cash flow. Remember this is us. We are giving this
money to the bank. So anytime you have
a cash flow, in order for all of the different
financial functions to work in Excel,
you have to know whether this is a negative cash
flow or a positive cash flow. So here's the trick. You ask, hey, is this cash flow
coming into my wallet or purse, or is it coming out
of my wallet or purse? Now don't worry
about whether this is your money in the
investment, it's just cashflow. So watch what happens. If you're holding your purse
or wallet in your hand, this cash is coming out
of your purse or wallet, and into the bank. So for us, this cashflow has to
be negative-- out of our wallet or purse, into the bank. So we always have to come here,
if it's a negative cashflow, and put a minus. These functions don't work. All of finance is
based on cashflow-- either positive or negative. By the way, if you were
doing the same calculation for the bank, then
of course, the bank, when you give this
to the bank, it would be from their
point of view. They would be receiving
it as a positive. All right, so we have one,
two, three cell references. These other arguments
have square brackets, and as we learned in our
prerequisite class and earlier in this class, if you
know what the defaults are for those square
bracket arguments, you don't have to put them in. We don't have any
present value-- that would be how much we
already have in the account. We don't have any. And the type means,
hey, is that cashflow happening at the beginning
of the period or the end? For us, it's always
at the end and that is the default. All right,
I simply close parentheses. I don't need to worry about
what type of cell reference, and Enter and whoa, check
that out-- $72,744.19. Now, if you actually
get this on the day you retire, or 15 years
later, the question is, how much
interest did I have? Well, first we have to figure
out how much we have invested. Well, this is another formula. We are not copying it anywhere,
so I'm going to say equal sign and then a bunch
of up arrows to get my amount, times 3 up arrows
to get a number of years. That's it. We put $3,500 in 15
times, that will give us our total investment. And we're not copying this
anywhere, so I just hit Enter. I don't got to worry about
what type of cell references those are. Now we can calculate
total interest. Hey, equals the amount
I get to pull out after 15 years minus the
total amount I put in. We're not copying
this formula anywhere, so I simply hit Enter. Wow, $20,000 in interest. All right, so our first two
formulas we were not copying. But let's scroll
down and we're going to look at our same two
examples-- income statement and making an investment--
but in this case, we have to copy our formula. So we have to think about
what type of cell references they are. All right, here we are. January to May,
revenue minus expenses. So I do my formula-- equals up
arrow, up arrow minus up arrow. But wait a second,
I'm definitely going to enter this and copy it
over, so I need to think about, do I want this relative,
absolute, or mix? Well, for us, since
we're copying it, we're always going to need to
go one, two cells above to get the revenue, subtracting one
cell above to get the expenses. These are called
relative cell references because it means, from the
formula's point of view, where you always look? You always look one, two cells
above and one cell above. So Control, Enter to put
the formula in the cell and keep the cell selected. I point to my fill
handle, an when I see my cross hair or angry
rabbit, I click and drag. I go to the last cell, I hit
F2, and sure enough, those are relative cell references. One, two cells above
minus one cell above. All right, Enter. Now let's do our
finance example, and this will be an example
of relative and absolute cell references. We still have our
$3,500, 15 years, but if we're investing, for
example, in the stock market, I don't really know exactly
what rate I'm going to get. So I might want to see
what the future value will be over various rates. Hey, watch this. I'm going to say, equals
fv, Tab-- the rate. Well, that's always going to be
relative cell reference-- one cell to my left-- so as
I copy the formula down, it will always get the 3,
then the 4, then the 5, comma. N per means total number of
periods, that's going to be 15. Now, as I copy this down,
I need it locked on B32. Right now, if I
didn't do anything, it would always look one,
two, three cells above. So if I was in the
last cell here, it would be getting the
number of years one, two, three cells above. That's not what I want, so
I lock it with the F4 key. Now notice it put two
dollar signs in-- one in front of the column
reference, B, and one front of the row reference, 32. That means this cell will
remain locked on B32, even if I copy it across the
columns, the letters, or down across the rows, in our
case, across the numbers. So n per locked on 15. That is fine. Comma, then our
periodic payment. Remember, this is
cashflow coming out of our wallet or purse
and going to the bank, so we have to put a
minus sign to indicate that that cashflow is negative. I'm going to lock this
one with the F4 key, also. Two dollar signs, one
for the column reference, one for the row reference. Now the default for present
value, 0, and type of payment, end of the period,
are the default, so I can close parentheses. Control Enter to put
the formula in the cell. Keep the cell selected. I point to my fill handle, and
when I see my angry rabbit, I double click and send it down. I immediately come to
the last cell and F2 to verify that I have a relative
cell reference for our rate, and n per and payment
are both locked as absolute cell references. And Enter. Now, I want to go look at our
next example, example five, and it's going to illustrate
relative and absolute cell reference. Now our goal-- this is
an income statement. We have revenue, four expenses,
total expenses, and net income. Our goal is to
calculate each expense as a percentage of revenue. These will be called
expense ratios. And we will use them
in next year's budgets. Now each one of these numbers is
the average over the last five years and if we can calculate
each one of these expenses as a percentage
of revenue, we can use the percentages to help
us estimate our projected numbers in our budget, which
will be our next example. All right, I'm going
to click up in cell C48 and our percentage
formula will be equal sign, one cell to my left--
that's a relative cell reference-- divided by-- and I
need revenue, which is in B48, and I need to lock
it with the F4 key. Now when I Control
Enter, that cell has already gotten
number formatting-- you can see it's got
the percentage number format up here. Now I want to copy
it down, but I'm not going to use my fill
handle because I don't want a zero right there. Control c to copy that cell,
then I'm going to click in C50, hold Shift, and to highlight,
I'm using my down arrow. Now I can Control v and
it pastes that formula all the way down. Now it actually removed
the border formatting that I pasted, so I'm going
to come down here, click the dropdown on the smart tag,
and say, Paste formulas only. That way, I won't
lose my formatting. Now let's look at this formula. F2, sure enough, it got
relative cell reference, cost of goods sold expense,
divided by revenue. So for each one of
these, we now have a percentage that we can use
in our budgeting process. Now one thing about ratios. This 46.46%-- one
way to interpret it is this, for every $1 that
comes into this business, 46.46 pennies will go
to cost of goods sold. For operating, every $1 into
the business, 19.48 pennies will be used up for
operating expense. Now a great trick, when
you're doing ratios, is this. Notice I just did the
numbers, but if we also consider what the units are--
this, of course, is dollars, but it's also cost
of goods sold. This is dollars,
but it's revenue. So notice, in the
numerator, I have a certain number of
dollars in cost of goods sold and in the denominator,
I have a certain number of dollars of revenue. So if you see over
here to the side, I've written out the
meaning of each ratio. When I did the
initial calculation, if I kept the units, cost
of goods sold and revenue, and I did this, well,
of course there's always a one in the denominator. But by leaving the one in
the denominator, the units in the denominator
and the numerator, I have the meaning of the ratio. This is 46 pennies
for cost of goods sold for every $1 of revenue. Now this trick of
leaving the units when you're creating
a ratio like this is helpful for revealing
the meaning of the ratio. And particularly in
accounting and finance, those fields are just
filled with ratios. You've probably heard
of things like the debt to equity ratio
or current assets compared to current liabilities. Those are examples of ratios
and you can use this trick. When we do division
or calculate a ratio, if you keep the units in both
the numerator and denominator and keep a one in
the denominator, the meaning of the
ratio is revealed. I don't know how
many times, when I've done complicated financial
analysis and accounting analysis with ratios,
this trick helped me figure out the meaning
of those required ratios. All right, we have our
four expense ratios here and now we want to go down
and look at example six, where we're making a budget. And example six, we'll
have our first example of mixed cell references
and assumption tables. Now down here we
have our four costs and here are those percentages
we just calculated above. Here's our budgeted
income statement, and this is, in essence,
our projections for January through May. We already have our
revenues, and I already have the formula for total
expenses and our net income. So the only formula
we're going to do here is to calculate cost of
goods sold, operating, administrative,
and other expenses. Now the way that most people do
this, even the textbooks that teach Excel, is, they'll
do something like this. I'm going to take
January revenue, and since I'm copying it
down and I need it locked, I'm going to hit the F4 key, and
then we're going to multiply. And because we have
cost of goods sold, operating,
administrative, and other in the same vertical order as we
do down here in our assumption table for formal
inputs, I can simply click on a relative cell
reference, 10 cells below. And when I Control Enter-- and
I'm not going to double click, I'm just going to drag it
down-- go to the last cell and hit F2, that works perfect. It's locked on the
January revenue and we have each one
of our percentages. Then we'd come over here,
lock the revenue for February, and get our cost of
goods sold relative cell reference, Control
Enter, and copy it down. Now in this example,
we would only have to create one, two, three,
four, five different formulas. For the full year, we would have
to create 12 separate formulas. We do not want to do that. If we know how to use
mixed cell references, we can create one formula
there, copy it down and over. Now I'm going to
delete these and I'm going to make a basic formula
that looks at January revenue, and I'm going to multiply
times our first expense ratio, down in our assumption table. Now I would like to copy this
down one cell and to the side, and just look at how these
cell references will move, because I have put dollar signs. So right now, they're relative. Control Enter. Now I'm going to
copy it down one. It looks like I got
some sort of answer, but now I'm going to click
in the cell and hit F2. That is not what I want. Range finder clearly
shows us that we have cost of goods sold from
January, not January revenue, but look at this, the
percentage is correct. So let's go back
here, F2, notice that B61, when I copied it
down because it was relative, it changed to B62. The fact that we can see
in this formula something changed and we didn't
want it to change, we wanted that to remain at 61. That means, if we're just
putting the minimum number of dollars signs in, we
can come up to cell, F2, and I can stop
the 61 from moving to 62 by putting a dollar sign. Now let's try that. Control Enter, and I'm
going to copy it down. Now when I come here and
hit F2-- oh, look at that. It looks like it's working. It is locked on B61. And really, that makes
sense, because look, that dollar sign says,
uh-uh-uh, row 61, you're always
going to remain 61. Now I'd like to
take this formula and copy it to the side,
and see what would happen. I'm going to copy
it to the side. Now I'm going to click
in the cell and hit F2. Now we can see,
well, oh, that B61 is allowed to move to C61
because there was no dollar sign back here for the
B. But look at that, C72, we really wanted it B72. So notice the c moved and
we didn't want it to move. So now I'm going to go back
to the original formula, F2, and I'm going to put a dollar
sign in front of the thing I want to stop from moving. So I'm going to put a
dollar sign right here. Now I'm going to try this. Control Enter and
when I copy it over, the b is now forced
to remain locked. So I'm going to copy it over,
put the cell in edit mode, and look at that. We prevented the b from moving
to c by putting a dollar sign. Those are examples of
mixed cell references. Now I'm going to delete
this cell and this cell. Here is our formula. It says, when I copy B61
down, it will remain on B61, but when I copy this cell
reference to the side, across the columns, it
will move relatively-- the b will become c. For this one, when I copy
it down, across the rows, there's no dollar sign
in front of the 72, so that orange pointing to
row 72 will move to 73, 74, and so on. So copying down, it
moves relatively, but that B72, when I copy
it across the columns, I'm locking it down. So it will remain b
all the way across. Now let's try it. Control Enter and
I'm going to copy it down-- I didn't double click--
and this is a struggle when people are learning. You cannot copy
it down and over. Notice I'm trying to get it
through the rectangular range in one sweeping motion. It will not work. You have to do it one direction,
let go, and then regrab the fill handle with your
angry rabbit and drag it over. Now here's the moment of truth. To this day, I'm still amazed. When I go diagonally
all the way across to check the formula with F2,
that is absolutely Excel magic. That prevented me from creating
five different formulas. It allowed me to
create one formula and complete the
task of calculating the expenses five times faster
because I knew mixed cell references. Look at that. The row header is perfect,
the proper cell reference down in our assumption
table is perfect. Now I want to delete this
and I want to show you a different way, because a
lot of the times, the method we used by copying to the side
and seeing which one moved and which one didn't, that's
called the sledgehammer method. We don't want to do that. We want a way to
look at our formula and figure out which cell
references we're going to need. So I'm going to build
my base formula. Hey, January revenue times
the proper cost of goods sold expense ratio. Now I'm going to ask two
questions of each cell reference and I'm going
to start with B61. And my first question
is, what do I want that cell reference
to be when I copy it down across the rows? Now, I want it to
be locked up here, so I always look over to
the side and I'm saying, OK, I'm going to copy it
down across the numbers. And if I want it
locked, the fact that I'm copying
across numbers tells me to put the dollar sign
in front of the number, so I'm going to put dollar sign. Now I have a second
question for B61. When I copy it
across the columns, do I want it to move relatively? Or do I want it
locked on January? Well, I want it to
move relatively. I want the blue one to move
to February and then March. So that means no dollar
sign in front of the b. And then I move on to
the second cell reference and I asked the
same two questions. What do I want
that cell reference to do when I copy down? Well, I want it to
move relatively, so I'm not going to
put any dollar signs. Second question, how about when
I copy it across the columns? Well, I need it locked. I don't want the orange one
to start moving over here. So I look up and I say, I'm
copying it across the letters, and because I want
it locked, that reminds me to put the dollar
sign in front of the letter, and there you go. Control Enter. I'll copy it to
the side this time. You can do it in
either direction. Over and down. I'm going to click
in the last cell, F2, that is absolutely beautiful. Now I'm still going to show you
two other tricks in relation to mixed cell
references being copied through a rectangular range. I'm going to hit delete
key and come up to the top. Now, in the last example, I
typed the dollar signs in, but that's not
usually the way you do it if you want to be fast
in creating your formula. I'm going to say
equal sign, up arrow, and if I know that
I need the dollar sign in front of the number,
I want to use the F4 key. Watch what happens
when I hit F4. Well, of course, we already know
it gives us two dollar signs, but if you hi F4 a
second time, oh, it goes to row reference
lock, but not column. When I hit the F4
key a third time, now the dollar sign is just in
front of the column reference. And when I hit it a
fourth time, it actually goes back to relative. So if I hit the F4 key, it
toggles through the four basic types of cell references. I like to call it the
merry-go-round key because it merry-go-rounds
through all of the cell references. Now I'm going to stop with
the dollar sign in front of 61 to lock row 61,
times-- and now I'm going to use my arrow
key to arrow down to get that cost of goods
sold expense ratio-- and now I'm going to use the F4
key one, two, 3 times to lock the column but not the row. Control Enter. Copy it down. Remember, it's a 2-step
process when you are copying through a rectangular range. Now I'm going to click
on the last cell and F2. Boom, there we go. Now still one last trick when
you're creating a mixed cell reference formula through
a rectangular range, and I'm going to
hit the Delete key. Now this time, notice I've
highlighted the entire range. Every cell in this
highlighted range is going to get
the same formula. So in the active cell,
I can build my formula-- equals up arrow F4, F4,
times-- and down arrow to get my cost of goods
sold-- F4 three times and now, with my formula
in the active cell, to populate it through
the entire highlighted range I use the
keyboard Control Enter. And now, if I want to
jump through the corners to get to this corner, I can
use the keyboard Control Period. Now Control Period always will
go through all four corners, so I'm going to
Control Period and hit F2 to verify that my mixed
cell references are working. Now I want you to
notice something. I actually have the choice, when
creating my formula up here, I had the choice between
this assumption table and this assumption table. Well, guess what? If I tried to use a
horizontally oriented assumption table with a vertically
oriented formula table, I couldn't use mixed
cell references. It is 100% impossible
if you have vertical and your assumption
table is horizontal. The only way you can use
mixed cell references is if the labels in the
formula table are vertical and the labels and the numbers
in the assumption table are also vertical. Now, the rule is, you can
do horizontal, horizontal or vertical, vertical. Whichever way you do it, the
orientation in the formula table and the assumption
table has to be the same. Now why is this so important? Well, the problem is, not
only in lots of textbooks will you see the labels in
the formula table vertical, and then they'll set up the
assumption table horizontal, but also templates out
in the working world, you'll see vertical
in the formula table, horizontal down here. As soon as you see that,
you cannot use mixed cell references. So whenever possible,
you reorientate the horizontal table
so that it is vertical. All right, so mixed
cell references. Notice this B61, when I copy it
down, it is locked or absolute. When you copy it to the
side, it moves relatively. This B72, notice when we copy
it down it moves relatively, but when we copy it
across the columns, it is locked or absolute. Now to properly
finish up this budget, I'm actually going to edit the
formulas all simultaneously. So I highlight in the active
cell in the upper left corner. I hit F2 and I
want to round this. I am multiplying
decimals here, so I'm going to round the number. Here's the formula,
comma two for the penny, close parentheses. And to populate the formula
through the highlighted range, I hold Control and Enter. I also want to make sure, since
there are no decimals here, I'm going to
decrease the decimals here, and there where we go. All right, our next example,
and this is example 7, we're going to look at relative
and mixed cell references again, but we get to create a
common-sized income statement. Now a common-sized
income statement simply requires that for
each period-- and we have October,
November, December-- we compare each
account to revenue. Oh, wait a second,
we already did this when we did our expense
ratios for our budget. But here, we want
to convert each one of the numbers for each one
of the periods to percentages. We will then know
how many pennies there are for each account,
as compared to $1 of revenue. Ah, but another great use
for these types of ratios is that we can compare
numbers-- for example, net income numbers-- that
have different magnitudes. So by converting each account
to a percentage of revenue, it's easier to compare
the different numbers, especially when they
have a large magnitude. Now, if I think about the
formula I need in this column, I need to take all
of the accounts as a relative cell
reference and compare it to my October revenue. When I'm over in this column, I
need a relative cell reference to look at every account,
and the denominator has to be locked on November. Now, if I'm thinking about
this from the start, that means this whole
column needs October, but when I copy
the formula over, it needs to move to November. No problem. We can use mixed cell
references in the denominator to accomplish this. All right, you ready? Equals relative cell
reference for the numerator. As I copy the formula
down and over, it's always one, two,
three cells to my left. Then I divide, and I
need October revenue. Now as I copy that B90
down, [TRILLING NOISE] it needs to be locked on row 90. When I copy it to
the side, it needs to go from B90, which
is that, to C90. No problem. I'm going to put
my cursor so it's touching that
denominator, B90, and hit the F4 key, one, two times. Notice I'm copying it down,
across the numbers or rows, and so there's dollar sign in
front of the number to lock it. When I copy this formula to the
side, that b will move to c. So that one formula will work
throughout this entire range. You ready? Control Enter. Copy it down. It's going to wreck
the borders there, so I immediately
point to the smart tag and say fill without formatting. Now I regrab my fill
handle and drag it over. I go to diagonally furthest
one away, click and F2 and sure enough, look at that. It got exactly the right account
and the correct denominator. If I look at any
intersecting cell, it did exactly what we want. Three cells to my left,
locked on November revenue. Now I want to add some
number formatting, so I'm going to highlight
all the cells, control one. On the number tab, I'm
going to select percentage. Two decimals is fine. Click OK. There, I have converted
all of the numbers to common size percentages. It makes it easy, for example,
to compare our October net income to December. If we're comparing
$15,000 to $55,000, it's hard to really compare. How much of an
improvement is it? Well, when I have
my percentage, which says net income for October
was 15 pennies for every $1 of revenue, over
here in December it was 23.88 pennies
for every $1 of revenue. So we can see that there's
about an 8 penny increase for every $1 of revenue. So sometimes it's easier
to use percentages to compare than it is to compare
the direct dollar amounts. All right, so that is
mixed cell references to do a common-sized
income statement. Now let's go look
at a finance example for mixed cell references
and absolute cell references. Now here's our same
finance example we've used a few times so far. We are investing
$3,500 every year but we're not sure the rate
that we're going to get, and we're not sure the number
of years we're going to invest. So by putting years as column
header variable and annual rate as the row header, we can use
our future value function-- equals fv, tab. Our rate-- notice that's
going to be for this row, it's going to be 2%. As we copy it down,
though, it needs to move. So when I copy it down,
I need it to be relative. I need it to move
from 2% to 3% to 4%. When I copy it to the
side, I need it locked. I'm going across the column,
so I hit the F4 key one, two, three times. I'm locking column reference
a, but not the number. That is our rate comma
n per, total number of periods, that's going to be
our 10 years for this column here. Now notice, as I copy
this formula down, I need it locked on the 10. So as I copy it down across
the rows, or numbers, I need to lock it. So I'm going to hit the
F4 key one, two times to lock the row
but not the column. Now, when we're copying
the formula to the side, because we have
not locked the b, that cell reference will
move from B109 to C109, D109. So that works perfect. Comma pmt-- that is the
annual payment that we're making at the end of each year. That's this $3,500. Now our two questions. What do we want to do
with that cell reference when we copy down? Well, we want it locked. How about when we
copy to the side? We need it locked. So we need both
dollar signs here. I hit the F4 key once. Present value is zero because we
don't have anything in the bank right now. The type is end of the
period for the cashflow. That's the default,
so I can leave it out. Close parentheses,
Control Enter. I'm going to copy it
to the side-- remember it's a 2-step process-- and then
double click and send it down. Now notice we made an error
here because we made the formula from the bank's point of view. At the end of 50
years, if we earn 10%, the bank would have to
pay out minus $4 million. right? So no problem. We need to edit this formula. Now I have already highlighted
this, so watch this. This a great trick. In the active cell,
we hit the F2 key. And what did we do? For our cashflow, we didn't
consider whether it's negative or positive cashflow. So this is our point of
view, that's our money, but guess what? From our wallet or
purse, it is going out. Negative cashflow
from us to the bank. So from our point
of view, this is a minus, a negative cash flow,
out of our wallet or purse. Now I've edited the formula
and here's a great trick. To populate it throughout
the entire range, even though there's
formulas already in them, we can simply hold Control
and Enter, and there we go. There is the
correct sign numbers but I still need to
go to the corner. I'm going to Control Period
Period F2 and look at that. Our rate is the row header,
looking at the right, 10%. Our n per is looking in the
correct column header, 50 years, and our pmt is looking
at the correct absolute cell reference. That is pretty amazing. And realize, we
created one formula and copied it down and
over instead of creating lots of individual formulas. Mixed cell references
are awesome because they help us build formulas quickly. Now, let's go look at
yet another example. All right, here's example nine. We're going to see a formula
with mixed cell references and relative cell reference. Our goal is to calculate profit
for each markup price schedule. So here's Mortimer's
price schedule. Here are the items. Here's the cost. So the sofa costs $275,
the lamp $125, and so on. And we've already
calculated a set of prices. So at a markup of 50%,
those are the prices we're going to charge for
each one of these items. At a markup of 55%, those are
the prices, markup of 60%, those are the prices. And we need a
dollar profit amount for each one of our prices. All right, this is
going to be relatively straightforward because
notice, this is profit at 50%. Well, there's the sell price, so
we need sell price minus cost. That formula will be there. We're going to copy
the formula over, then we're going to
get 55% markup price, minus the same
cost, and then 60% markup price minus the cost. When we copy our
formula down, then we need our whole new row prices
minus locked on our cost. All right, you ready? Equals-- hey, we're going
to take our price, one, two, three cells to the left,
and notice when I copy it down and over, every
cell will always be looking at the price
three cells to the left. And now we subtract our cost,
but we need that $275 sofa cost in this entire row,
but when we copy it down, we need it to move
down to the lamp cost. So here we're
asking the question, what do we want it to do
when B26 is copied down? I need it to move relatively. So no dollar sign, but now when
I copy it across the columns, I need it locked. Notice I'm in B26. If I didn't lock it,
B26 would move to C26, which would be right here. That would be incorrect. So I come and I touch my cursor
somewhere in the cell reference and I hit F4 key one,
two, three times. I've locked the column
but not the row. Now I can Control Enter,
copy it to the side, and then copy it down. Now, you know, I just copied it
down and I'm not sure if that copy action removed the border,
so I'm going to click off to the side and it didn't. So that's good. I go to the last cell, I hit F2. Can you believe it? It got exactly the right price. And notice, for this
entire row, it's getting the right price
locked on the cost, however, so each one of
the prices are correct. When I look up a row,
sure enough, it moved. That is amazing. That's using relative
and mixed cell reference with the column
reference locked. All right, let's go look
at our next example. Example 10. All right example
10, we're going to see mixed cell references
and relative cell references, but they will be part of what
is called an expandable range. Now, an expandable
range just means, as we copy the
formula down, it'll have a single cell in the range. Then it will have two cells. Then it will have three. The range will be expanding
inside our formula as we copy the formula down. Not only that,
but in this trick, we want to learn about
the rows function, and it's going to be used to
create sequential numbers-- like one, two, three, four. Also, sometimes
you'll hear the term, we're going to use the rows
function to increment numbers, meaning we're always
going to be adding one. Now why would you
want to do this? Well, sometimes you
want an actual formula. So when you copy
the formula down, it'll show one, two,
three, four, five. Not only that, but over here,
we'll see inside a formula. We're going to use
the large function and the large function needs to
know which number to pull out-- the first one, the second one,
the third one, the fourth one, and so on. All right, let's look
at the rows function. We have never seen
this one before. It's really straightforward. If I give the rows
function three rows-- in this case 150 to 152-- rows
will just count-- 150, 151, 152-- there are three rows here. If I were to give
it that many, it would look and count five rows. All right, here we go. Expandable range inside
the rows functions. Now watch this. We're going to do something
we've never done before. I'm sitting in A150. I'm actually going to type the
cell that the formula is in, right into my formula. A$150-- notice
that 150 is locked, that's the row reference-- then
I'm going to colon an A150. That is an example of
an expandable range. As I copy the formula down,
the first part of this range is locked on 150, but notice
we left the dollar sign out on the second 150. So as I copy down, it will
go to 151, 152, 153, etc. Now normally, if we have
the actual cell reference inside the formula
that is the same cell as the formula sits in, we get
a circular reference, but not with rows function because
rows function is just counting rows in a range. All right, you ready? Close parentheses
and that is rows function to increment numbers
or create sequential numbers. Control Enter and now I'm going
to copy this down and sure enough, it created one,
two, three, four, five. Now let's look at how cool
this expandable range is. Oh, the first row reference was
locked, but not the second one. Enter F2. Now it changes to 152, but
that 150 is still locked. All the way down you could
see the blue range expanding. Now that's pretty cool. That's just a way to
create automatic numbering, and we want the actual
number to sit in the cell. But sometimes, we want
our number incrementor inside another formula. So a great example is
the large function. Now we have some points
over here and often times, in scoring, we want the top
five, or in sales numbers, we want to see the top five. So large function, we
saw two videos ago, we can give it a whole
range of numbers-- and I'm in a locket with the
F4 key, that's an absolute cell reference-- and then,
for k, we give it one-- it will get the max. We give that k2, it'll
give us the second biggest. Three, the third biggest. Now, if we actually had
the number right here, we'd just click on a
relative cell reference and copy the formula down. But sometimes you do not
have the numbers 1, 2, 3, 4, 5 in the cell
directly to the left, so you need, inside
your formula, that number incrementor. So I'm using rows and
now I'm sitting in B150. So I have B$150 colon B150,
there is our expandable range. 150 locked, 150 not
locked-- it will expand and rows will count one, two,
three, four, as it copies down. Now notice that rows number
incrementor is sitting in k, so that k will tell
large which large to get. Close parentheses,
Control Enter, double click and send
it down, and you can see it got the first biggest. Then it got the second biggest,
and look, there was a tie, so it got boop and boop. If we look at our formula,
look at that orange range, it's expanding as we go down. Right here, if we were
to click in this cell, click on the screen
tip k and hit F9, just to evaluate and see
what rows is delivering. Sure enough it's delivering a 3. I'm going to click
Escape to revert back to what was in the cell
before I put it in edit mode. F2 and I'm going to do
that same trick again. Click on the k, F9 to
evaluate, sure enough rows is incrementing numbers
directly inside our formula. Escape. All right, so examples
one to 10 were looking at the four
basic cell references-- relative, absolute, mixed
with the column locked, mixed with the row locked. Now, we will have a bunch
more reference examples in this video and we want
to look at, in example 11, how to save formula inputs
with the scenario feature. Now notice, here's
our budget example. Here's our expenses. Here are our expense ratios. And what I want to do is, I
want to save this set for one of our budget assumptions,
then change the numbers and save that set
of assumptions. Then I want to save it
so I have just a button I can click, boop, and
it will instantly change all of the inputs. All right, you ready? Let's highlight the labels and
the numbers in the assumption table. And we have to find Scenario
Manager in the ribbon tab. Now it used to be, in
2013 and before, data ribbon tab, data tools. But they moved it in 2016. Now it's the new forecast group. There it is. What If Analysis and sure
enough, right at the top is Scenario Manager. Now, all it's going to do
is memorize these cells and what we have in the cells. I'm going to say add. I'm going to give it a name. I'm going to call this set one. There's the cells
that is saving. It's not only saving the cells,
it's saving the actual content, also. I'm going to click OK. And look at that,
now this scenario values dialog box comes
up and you can actually change them here. I actually never use this. I always use the cells. I'm going to click OK. There it is. Later, we can come back here
and select from our list. I'm going to close. Now, let's change these. Under a different
set of assumptions, I'm going to say this one
is 30, this one is 6%, this one is 10%, this is 19.5,
3.25, Enter, and 11%, Enter. Now I'd like to add this second
set to the Scenario Manager. Now the keyboard in
2016 is pretty long. In older versions, all
the way back to 2003, there was a much
shorter keyboard. So Alt, T, E will go to
forecast group, What If, Scenario Manager. So I'm going to do it. Highlight the range, Alt, T,
E, and this one's going to be called set 02, Enter,
Enter, and then close. All right, now we're
going to do set three. 29, Enter, seven, Enter, 11,
Enter, 25, Enter, four, Enter, and 12, Enter. Highlight the range. I'm going to use the
keyboard, Alt, T, E, add, and this one will
be called set three. Enter, Enter. Now before I click Close,
that button right there, show, is how we can change
our assumptions. So to show set two, I say
show, and instantly, the items in our assumption table changes
and the formula changed. If I select set one
and say show, instantly everything updates. Now, what I'd really
like to do is close this and I'd like to have a button
that I can just click, click, and everything changes. Now we can add
that button, but we have to go up to the
Quick Access toolbar and you can see this one is at
the top in this sea of green. I want to right click and
say show Quick Access Toolbar or the QAT, below the ribbon. So there it is. Now it's easier to see. I want to edit it. Right click, Customize
Quick Access Toolbar. Now here we are. Quick Access Toolbar. Choose Commands from. I want to click the dropdown
and say, give me all commands. This is like 1,300 something
commands that Excel can do. I'm going to click in the
list because guess what? I'm going to jump
down to the bottom where there's a
scenario button, then I'm going to add it to
the Quick Access toolbar. So to jump down to the S's,
I'm going to type the letter S. Now I'm going to scroll down
with my wheel and sure enough, there's Scenario Manager. That opens up that dialog
box but what I want is the drop down for Scenario. When we click add, it's added
to our Quick Access Toolbar. I click OK and now, you're
not going to believe this, there's a drop down
with my scenario. So I can simply say
set two, set three, and instantly,
everything in the table changes based on
our assumptions. So scenarios when you
have sets of assumption? An awesome, efficient trick. Now let's go look
at our next example. Example number 12. We need to talk about
sheet references. Now all sheet
references means is this-- I can make a normal
cell reference, right? Boom. That means, on this sheet,
please go look at D192. But if I want to point this
formula to a different sheet, I actually have to have the
sheet name before the cell reference. All right, now there's two ways
to create sheet references. One is to simply click on
the sheet and then hit Enter. The other way is
to actually open up a second copy of this workbook
and work between sheets in different windows. Now in our first
example, example 12, we're simply going to see the
easiest method, which is just to click on a separate sheet. Now our goal in this
example is to check if the schedule of
accounts receivable-- this is a schedule of
accounts receivable-- we need to check if that total
matches the accounts receivable controlling count
over on the CAR sheet. That's the number that we
need to bring back over to this sheet. All right, so controlling
account balance? Here's how you do
a sheet reference. Equal sign, you have to
click on the sheet you want to get a cell reference
from-- so I clicked on CAR-- and notice, up in
the formula bar, there's the syntax
for sheet reference. You've got to put the
name of the sheet and then exclamation point. That exclamation point
is telling the formula that that text there is not
"text," like in double quotes, and it's not a function
name, it's a sheet reference. As soon as you see an
exclamation point next to text, you know it's a sheet reference. Now I'm not clicking up there. I'm simply going to click
on A5 and there it is. That's our first
sheet reference. Now what you want to
make sure not to do is, do not click back
on the reference sheet. As soon as you have your sheet
reference and cell reference, you hit Enter. Now I'm going to put this
into edit mode with F2 and there it is. Now notice one thing
about sheet references, and this will also be true
about workbook references, we don't see the rainbow
color coded rangefinder. It's always going to
be a black reference. Now, we want to learn a
second important feature in sheet references. Notice name, exclamation point. Let's just hit Enter
and go see what happens if we put a space in our name. So I'm going to double
click this, and write between C and AR, I'm going
to type a space, Enter. Now let's go look at
our sheet reference. Click in the cell, F2-- oh. If you have spaces, you must
have single apostrophes. Now notice two things. One is, our sheet
reference updated. Any time you change the name of
the sheet, your formulas that are using sheet references
will automatically update. Also notice that we didn't
have to type this out. We just clicked on the
cell and then hit Enter and it put all of the proper
syntax for a sheet reference into our formula. All right, so we have
that formula there. With the formula, I want to ask
the question, hey, equal sign, is the controlling
account balance equal to our schedule
of accounts receivable? When I hit Enter, of
course, it says false. Now we can see
this with our eyes. We know in other
situations there might be a number
formatting problem, but a logical formula to tell
us if two things are in balance is quite helpful. Now that's our first
example of sheet reference. Now let's go look at another
way to enter sheet references. Now, example 13,
our goal here is to calculate January and
February commissions-- here's our two columns of sales
numbers-- when the commission rate's on an assumption sheet. Now instead of an
assumption table, sometimes people like to
isolate their assumptions on an assumption sheet. I'm going to click on the
sheet and sure enough, we have our January commission
and February commission rates on this assumption sheet. Now I want to go back
over to our reference and here's the deal, instead
of clicking back and forth between two sheets, I
actually want to open up a second view of this workbook. And it's not
duplicate, it's just seeing the same
workbook in two windows. That way, we can adjust the
sheets on the two windows and click back and
forth with ease. To open up a second view of
this workbook, I go to View, and in the window
group, I say new window. Now wait a second, watch
the title bar up here. I'm going to click New
Window, and just like that, the title bar says colon two. That means this is a second
view of that workbook. I'm going to use
Control Tab to jump back to the other workbook. Now this one says colon one. Of course, down here
in the task bar, we can see there are
two windows open. All right, so let's
go back up to View and arrange all to
arrange our two workbooks. I click, tiled is fine. When I click OK, look at that. We have two views of
the same workbook. Now in the second
workbook, I'm going to click on the
assumption sheet. Now notice, here
it says two and I have assumption sheet in view. Over here, here's view one, and
there is the reference sheet. Now let's build our formula. Equal sign, relative cell
reference one, two to my left-- as I copy it down and
over it will work fine-- times. And now I can click
over-- once to activate the sheet, second to
get the sheet reference. Notice I can see it up
in this formula bar, up in this formula bar, and
I can see it right here. When I click once to
activate this workbook-- oh, check that out. That is so cool. I have the ability to
click back over here and that sheet name
did not change. Now notice something about A2. When I copy January
commission rate-- from A2 over here-- when I
copy it over a column, I want that A2 to move to B2. But when I copy it down,
I need that A2 locked. So another important feature
for sheet references, is they can be any one of the
four basic cell references. In this case, I'm going
to hit F4 once and twice. I'm going to lock the row
reference but not the column reference. Remember, A2, when I copy
over, needs to move to B2. So there you go. Control, Enter, copy
it over, copy it down. Go to the last cell,
F2, and sure enough, it got B2 correct from
the assumption sheet, and it has the correct
February sales number. So by opening up two views
of the same workbook, it's much easier to
work back and forth when you're doing sheet references. Now I'm going to come
over to the second view and in the title bar,
I'm going to close. Now to activate this
window I'm going to click, and to maximize
it, I'm going to use Window, up arrow. That's the same as
the Maximize button. All right, I'm clicking
in the top cell and here, too, since we're
multiplying decimals, F2, and I need to round to the
penny, comma two, close parentheses, and
watch what happens. Right now, we have
this total here-- Control Enter-- I'm
going to copy it down. We can already see
the total is changed. So remember, any time you're
multiplying or dividing decimals with extraneous
pennies-- and in this case then you have some
subsequent calculation that relies on the
accuracy and rounding of these calculations-- you've
got to use the round function. All right, sheet references. Quite useful. All right, let's go
to our next example. Now example 14 we have to talk
about-- workbook references. Yes, that means we
can actually have a formula that points
to a cell reference in a different workbook. Now, when you
download the files, one of the files you can
download is May net income, and I need to get May net
income from May Net Income.xlsx workbook over into cell B229
in the Busn218Video04Start.xlsm workbook. Now it's as simple as this. If I type an equal sign,
now I need to jump over to the other workbook. So I can either click
down on the Taskbar and then click on the
workbook, or I can use the keyboard Control Tab. That jumps over to the
other Excel workbook file. Control Tab is
different than Alt Tab. Alt Tab jumps between
windows, Control Tab jumps between Excel workbooks. Now you can see the formula
doesn't have anything yet, but when I click on cell B10,
there is our first workbook reference. I'm going to hit Enter and we'll
talk about the syntax for it over here. We're back in our
Busn218Video04Start file. So I click in the cell and F2. Now the first thing you
notice is, by default, the cell reference is locked. You can absolutely change that. The four basic cell references
are still available. If I hit the F4
one, two, three, it is still our
merry-go-round key that toggles between the
different cell references. I'm never going to copy this
anywhere, so it doesn't matter. Now the rest of the
syntax, square brackets contain the full Excel
workbook file name, and then there's our
single apostrophes wrapped around the workbook
filename and the sheet name. Then there's our
exclamation point that means May New Income is
the name of the sheet, and B10. So this formula is
read, B10, on the sheet May Net Income in
the Excel workbook file May Net Income.xlsx. Now I'm going to hit Enter. I'm going to get my equals and
I need to make a formula here to calculate net
income for June, so I click on that cell
reference minus SUM Tab up arrow, up arrow, hold
Shift and up, up, up, up. Then I can close parentheses and
I've calculated, in this cell, net income for June. Now I can calculate
the difference. Equals the latest net income
minus past month net income. That will give me the change. Now I want to look
at this, F2, that is how the workbook reference
looks when you create it, but now I want to go close
that file, and guess what? The two files are
communicating with each other through this workbook reference. All right, notice. Enter. Let's go over to the
other file, Control Tab. I'm going to close
it-- either the X in the upper corner or Alt F4. Now, can you guess what's
going to be in our formula? If these two files
are communicating, it better be the
full file path name. So when I click in
the cell and F2, there it is, the full file path. That is the only way that
they can communicate. Now I'm going to
Control S, and I'm going to close this workbook--
either with the X or Alt F4. I've opened up May
Net Income and now I'm going to change one of
the numbers and Enter. So really, the May net
income should be $657. Now we're going to
close this and save it. Now notice, I'm in
Windows Explorer. These two files are
communicating with each other. If you ever want to cut and
paste this somewhere else, it may not be able to
communicate anymore. So in general, I like to
keep the workbooks together if I'm using
workbook references. Many times that's not
possible, but just be aware that they
are communicating. Now when we open up this
file and there's a workbook reference, if our
settings are in such a way to give us a warning,
we'll see a warning. So I'm going to open
this file, and there is our polite warning. It's asking us, do you want
to update or not update? Now I'm going to say not update. Most of them time I say
update, because they're communicating with each
other, I want them to update. But if you say don't update,
well, there it is. $557. We know in that other
workbook it's $657. Now we need to see where we can
work with workbook references. Data, Connections, and
there it is-- Edit Links. Now Edit Links allows us to
do a few different things. There is our workbook. I could say update values. I could say change source
and let's look at this. Change source-- this allows
you, if the file has moved, to navigate and redirect it. Now I'm going to click Escape. Open source, that will
open the source file. Break Links, that's when
you're no longer want a link. And down here, Start Up Prompt. I'm going to click on this. This is how you decide
what message to get. I'm going to say,
let users choose to display to display
the alert or not, that's the message you
get when you open it up. Now let's go ahead
and update it. So update values and sure
enough, you can see down here, it has updated. OK, so workbook references. Sometimes they are helpful. Now let's go look
at our next example. You're not going
to believe this. We have to get our 3D
glasses out for this one. No, no, not really. We're going to do 3D
references without 3D glasses. Now, I already have
the answer here so I'm going to delete this. Now here's our set
up for this one. We need to add, for our
corporate totals, all of the products
for all the months. There's a bunch of numbers
and if you look over here, there's an Oakland sheet. And notice something
about the Oakland sheet. The template is exactly
the same set up. If I go over to the Sea tax
sheet, exactly the same set up. Tacoma sheet, exactly
the same set up. If that's the case, you
can use 3D references. Now let's go back to
references, and there it is. I'm in cell B238. Now it doesn't matter
that there's a B238 and that January quad
on these other sheets actually sits in
a different cell. What matters is that the
actual shape and structure of those templates on the other
sheet are exactly the same. If that's true, you can
use 3D cell references. Now here's our goal. For January quad, I need to
get, from the Oakland sheet, January quad, then from the
Sea Tax sheet, January quad. Tacoma? January quad. And then, on the
corporate sheet, I need all of the proper cells
from each one of the templates. For example, here I need
to add April Tri Fly. Now, what it means
by 3D-- and I'm going to go back to the Oakland
sheet-- notice there's a B3. Notice on Sea Tax,
it does matter that this template is
in the same cell, B3. And over on Tacoma, B3. What I want to do is add up all
the B3s from sheet Oakland all the way to Tacoma. Let's go back to references
and see how to do this. Now before we do it,
I need to remind you-- and you should all know this
from our prerequisite class-- if I come over to the side,
I've just selected H243. Before I click on H245, if
I hold down the Shift key, and then click,
that's the method to highlight everything from
one book end to the other and everything in between. That's going to be necessary
as a keyboard mouse trick when we highlight the
sheets from Oakland all the way to Tacoma. All right, here we are. Notice I'm on the
reference sheet. I'm in the cell for January. I'm going to use the
keyboard for the auto SUM. Alt equals-- it
doesn't know where to go cause there's
no numbers anywhere, but let's start
our 3D reference. I'm going to click
on the Oakland sheet. You can see up in
the formula bar it says Oakland exclamation point. Now, I'm going to click on B3. So far, it's just Oakland,
exclamation point, B3. But now, watch this. Before I click on Tacoma,
what am I going to hold? The Shift key. Then, when I click
on Tacoma, it's highlighted everything from
Oakland, all the way to Tacoma. And if you look up
in the formula bar, there is our 3D reference sheet. Oakland all the way to Tacoma,
please add up all of the B3s. When I hit Enter,
that is amazing. F2. And what's so amazing
about 3D references, when it says Oakland colon Tacoma--
and I'm going to click Escape-- if I were to ever insert
any sheets between Oakland and Tacoma, this-- F2-- this
formula would totally update. Just like regular cell
references, like A1 colon A5. If you insert a row for
those normal references, everything updates. Same with 3D references. Insert a sheet and
it totally updates. All right, ready? Control Enter, copy
it down, copy it over. Go to the last cell, hit F2. That is amazing. 3D cell references. All right, hit Enter. We want to look at
our next example and we're going to
talk about example 16. We've got to talk about the
amazing defined names feature. Now we talked
about defined names in the prerequisite
class, Business 216, but we want to remind
ourselves here. Now all it means
is, if I'm going to use that cell
in a formula or I'm going to use this whole column
over in a formula over here, I can name that cell. So instead of B252
or this entire range, B263 all the way to 271,
we can give it a name. And then we can use those
names in the formula. Now, the basics of
creating a name. If I want to name this cell
Amount, I'd click in the cell, and look, here's the
formula bar, and all the way to the left, if you
hover your cursor, that's the name box. I can click in it, and notice
that B252 is highlighted, and I'm going to call
this Amount, and enter. Now that cell is named Amount. If I click over here
and then back, notice it no longer shows
B252, it's Amount. Now we do want to talk about
the naming conventions. The first character of a
name must be either a letter, underscore, or backslash. Characters allowed-- letters,
numbers, periods, underscores. Maximum of 255 characters,
no spaces, and guess what? If I come up here and try to
name that cell right there, Return On Equity 2016--
so I'm going to try it. Return On Equity--
that's a common ratio used in accounting and
financial analysis-- and we wanted to
name that cell 2016s because that's the
cell that maybe had Return On Equity in 2016. Watch what happens. And actually, this will
reveal an awesome trick for the name box. It'll actually jump
to cell ROE2016. So when I hit Enter--
you've got to be kidding me. There's a column called
ROE, and of course, there's a row called 2016. So you actually can
never use cell names. Now, I actually want
to use that same trick. I'm going to click
back in the name box and there's a dropdown--
I can select Amount and now I'm using the
name box and defined names to jump to a different
part of the workbook. Look at that. Instantly, I've jumped
back to references. Now actually, example
18, is use defined names to jump to other places. I want to show you
something here. Sometimes people have, on the
cash sheet and the AR sheet, they have accounts like this. So watch this. In cell where the
account number is, I'm going to come up to the
name box and type AR and enter. Now I'm going to come over to
Cash and in the account number cell, I'm going to type Cash. And now, if you had all sorts
of sheets with accounts, you can simply use it
as a go-to mechanism. So I'm going to say AR and
instantly I jump to AR. Cash, I instantly
jumped to Cash. Amount and I jump
back to the example we were just working on. Now I actually want
to delete that name. And not only that,
I want to see where I can go to and edit names
because sometimes you make mistakes. Sometimes you don't
need your names anymore. Hey, up on the
Formula Ribbon tab, there's a whole group called
nothing but Defined Names. Here's the Name Manager. I'm going to click Name Manager,
and there are our names. Dog tags are the icons to
indicate defined names. That little table icon
means these are named tables in our Excel workbook file. I can expand the
values, refers to, there's also a scope column,
which we don't see here. Scope is when you have the
same name on multiple sheets. Now I want to click on Amount
and, if I wanted to edit, I could click Edit. That's how, if you make a
mistake, you could change it. I'm going to click Escape. I want to click on
Amount and delete it. Click OK. Click Close. The reason I want to delete
it is because, in this case, I want to name this cell
Amount, this cell Years, and this cell Annual Rate. I don't want to
name these manually. I want to highlight the names,
which are sitting to the left, and the three cells. Because I've
highlighted the cells and there's a name to the
left for each one of them, I can simply come
up to Defined Names and Create From Selection. Boop! Now, be sure to read this
because sometimes, it will come checked
with multiple checks, and you have to say
where the names are coming from-- top
row, left column, bottom row, right column. It's definitely the left column. And this is pretty
good at guessing because these are numbers
and these are text, right? When I click OK, instantly
in my dropdown I have Amount. And notice what it
did to Annual Rate. We are not allowed to have
spaces, so it was polite. It put an underscore. And if I go to Years,
there it is, Years. Now I want to build a
formula and this example is exactly why
people like names, because in their formula,
it will be explicit. We will be able to, in
essence, read the formula inputs in natural language. Equals-- and this is our
future value calculation. Now I'm going to get the
rate, and notice I click on it and it puts the
Defined Name in, comma, n per, total number
of periods, that's 15-- I mean Years-- comma, and
the pmt, that is called Amount. That is a natural
language formula. If you come and
look at this, you can read it from left to right. Now we've seen this. This is the third or fourth
time we've seen this already in this video. Present value, that means the
amount in the bank-- we don't have any right now-- and
type, this is an end payment, so that default will work. Close parentheses. Now before we hit Enter, we want
to make sure this-- Amount is a cashflow. This is from our point of view. It's coming out of our wallet
each period and into the bank. So it's a negative
from our point of view. So I'm going to put
minus and then Enter. Now I want you to
notice something. If I hit F2, I've already
built formulas pointing to these recently named cells. So watch this. This is an awesome feature. I can highlight these,
go up to Defined Names. Defined Names and there
it is-- Apply Names. And it's pretty smart. It knows that the cell
references in those formulas recently had names added,
so when I click OK, it just applied those names. Years, times Amount. I just love it. That is an easy to read formula. And down here, well, of course
that one didn't have names. Now let's see how
to name a column. Now in this case, I'm going
to use that same feature, Create Names from
Selection because I want this column to be
called Sales and this column to be called Product. I'm going to use the
keyboard, Control Shift F3. Now be careful here. It's trying to name all of
these number cells to the right. That would be terrible. I want to uncheck that. I only want the names to
come from the top row. Now when I click OK, from my
dropdown up here, I have Sales and I have Product. Now I can come over and
use this in my formula. Alt equals, and I could
come over and highlight-- and as we saw before,
it puts it in-- but I want to show you another trick. Sometimes you're not near
the cells and you're like, man, I'm forgot
what the name is. Well, the keyboard
F3 is paste name. And I can look through, and
if it prompts my memory, I can then select Sales,
click OK, and that is applied. Now I'm going to go equals Max,
tab, and then S. And notice, from our dropdown, we've
only seen function names so far but Defined Names and
also Table Names will show up in this dropdown. I'm going to hit Tab and Enter. Equals MIN, Tab, S, Tab, Enter. So I've calculated the total max
and min using the sales column. Now another really awesome
use for defined names is the fact that,
by default, they're absolute cell references. Now my goal here is
to add all the sales for each one of these products. So I'm using the
SUMIFS function. Sum range, S, Tab, comma. Criteria range, I'm going
to a PROD-- and notice, there is a function called
PRODUCT which multiplies everything in a range. So you've got to be
careful, sometimes, if you have the same name. I'm going to down arrow
and accept the gold dog tag and Tab, comma, left
arrow, and there we go. The beauty of Defined
Names for this example, is that they are
absolute by default. So when I Control Enter and
double click and send it down, go to the last cell, F2,
sure enough they are locked. If you ever wanted
to change that, which is a rare occurrence,
you could come up to Name Manager, and of
course, click and Edit and then edit your
cell references here. All right, Defined
Names, quite helpful. Don't forget there's
notes here and those PDF notes have a bunch of notes
on Defined Names, also. Now let's scroll down and
talk about our next topic. And now examples 19,
all the way to 24, are going to involve table
formula nomenclatures and the Excel table feature. Now we've already talked
about this Excel table feature and table formula
nomenclature before, but here we're going to see a
number of examples, including how to do relative
mixed and absolute cell references when you're using
table formula nomenclature. Now we need to convert
this to a table. We either go up to Insert
Table, Group Table, or we click in a
single cell and use Control T. When I hit Enter,
that is an Excel table. It has dropdowns with sort
and filter formatting, and most importantly
is, if we add records below or new columns,
all the ranges are dynamic. So anything pointing
to the Excel table, like formulas or
pivot tables, will update. Now we have to name this table. Table Tools, Design,
Properties, Table Name-- or we can use the keyboard,
Alt, J, T, A-- and I'm going to
name this Sales, and we're going
to have a problem. When I hit Enter,
it's polite, it reminds me, I already have
a Defined Name called Sales. So I'm going to click
Escape and now I'm going to call it
Sales Table and Enter. Now notice that we just
created our table name. Hey, there's the dropdown
for defined names we were using just
a few minutes ago, but there's our table name. Now I can create a formula
that points to the Sales field. Alt equals, and
when I highlight, we can clearly see we get
Table Name and Field Name in square brackets. Enter. We can also point
to multiple columns, and when we copy table
formula nomenclature down, the ranges will be locked. Now later, we'll see
an important difference between defined names and
table formula nomenclature when you're copying
a locked range. But right now, we're
just going to put SUMIFS. Sum range, there it is--
Table Name and Field Name in square bracket. Comma, criteria
range-- I highlight Table Name, Field Name in
square brackets, comma, and I have relative
cell reference. Now I can close parentheses,
Control Enter, double click and I copied that formula down. When I go to the last cell
and hit F2, sure enough, the ranges are locked, that
cell reference moved relatively. So when you copy table formula
nomenclature with fields down a column, they are locked. Now we want to see a
great example we did not see earlier in the class. Earlier in the class, we
saw how to add records to an Excel table and
then everything updates. Ah, but let's add a new column. I need to calculate
based on this commission rate-- the commissions
for each one of our sales. So right next to
the Excel table, I'm going to type
commission dollars. And when I hit Enter, a new
column is added to our table. Now we want to actually
create a formula that looks at the sales in this row
times locked on the commission rate. So you ready? Equals-- and this is going
to be the first time we've seen a relative cell reference
in table formula nomenclature. You see, I'm
clicking in this row, and there it is-- the
syntax for relative cell reference in a table. You've got to have the square
brackets, the at symbol, and the name of the field. Because it has an at symbol,
it will not get the whole sales column like it did over here. It'll always go and get the
sales number for this row. Times-- and now I'm going
to click on this cell. This is, of course, outside the
table, so I hit F4 to lock it. Now I can Enter. And many times, we
will automatically populate the column. This didn't do it, so I'm
going to point to the dropdown and say, overwrite all cells in
this column with the formula. And just like that,
it is copied down. I'm going to go down to
the last cell and hit F2 and you see, sure enough,
relative cell reference. When you're inside
the table and you're referring, on the same
row, to that sales column, that's the syntax for
relative cell reference. Enter. Now we're going to have
to do the same thing. We're multiplying with a chance
of having extraneous decimals. In fact, we can see the
extraneous decimals. And these formal results are
being used by other formulas, so we have to round--
R-O-U-N-D-- round the number. That little formula bit right
there, comma, number of digits, two, close parentheses,
Control, Enter, double click and send it down. Come to the last cell,
F2, looking good. All right, now let's
go to our next example. We want to learn about mixed
references in table formula nomenclature. Now here I need to add from
this table, which is a defects table-- January,
February, March-- I just need to add January,
February, and March. Now there is no April. The reason that I
put April here is, I want to show you a very
strange characteristic for column reference
from a table. If I have a formula
that is pointing to the whole column-- that
means table name and field name-- when you
copy to the side, it actually moves like a
relative cell reference. Up here, we saw when you
copy it down it's locked. So every one of these
are locked on Sales. Locked on sales. But if you, in this
case, point to a column and copy it to the
side, it assumes that you want the column
reference to move. And I put April here
because something strange will happen when I copy
the formula over to the end of the table, to March. When I copy it one
cell further, it jumps back to the first column. So let's do this. Alt equals and I'm going to
highlight. [TRILLING NOISE] So far it's looking fine. The name of the table, field
name in square brackets. Control Enter. Copy it to the side. And now I'm going to
click in February, F2. Whoa, the field
changed to February. You can see it right there. When I go to March,
table name lock, but when I copy the column
field reference to the side, it moved to March. And finally, since March
is the last column, when I keep copying it,
it's programmed to jump back to the first
column, which of course doesn't make any sense here. Now in our 24th example, which
I'm going to jump to right now, we're going to see, if
you didn't want it to move relatively, how to lock. So example number 24 here. Now it's kind of
a strange syntax and there is no
F4 key to lock it. So we're actually going to
have to manually type this out. It's very clunky but if you want
to use the Excel table feature because you have
expanding records or you might add columns
or something like that, then we do need to learn how
to lock a reference in an Excel table. All right, ready? Our goal here is, we're
running a dog kennel where we take care of dogs and
sometimes we buy them food, accessories, boarding. Here's all the transactions. So for the dog we're
taking care of, Mandy, we had various transactions. One for food, one for
accessories, one for boarding. So we need a summarization table
where we have the dog names down the side, and then we have
the categories for expenses along the column headers. And we need a formula that
we can copy down and over that will always add from
the amounts column, based on whichever dog,
from the dog column, and whichever
category for expense, from the category column. So you read? Equal SUMIFS, sum range, and
I'm going to highlight Amount. Now when I highlight it, that is
not an absolute cell reference. We already saw, if we copy
it to the side right now, it would jump to the front, but
if I copy down, it's locked. But I need it locked everywhere. So I'm actually going
to highlight this, and I have to do
this-- I don't know how to do this any
other way but manually. Square bracket, come to
the end, colon, Control V, close square bracket. That is just crazy. How cumbersome is that? But that's how you lock. That means, no matter--
you copy down and over, it's always locked on Amount. That's the sum range
comma criteria range. Well, I'm going to need
to pull something from dog and then from category. So I'm going to start with dog. Right now, I'm going
to come to the end, and a second square bracket,
and I'll do it manually. Colon-- and watch what happens
when I type a square bracket. Oh, all of the elements
from the table pop up. Dog is there, so I
hit Tab, and then I have to close square bracket,
close square bracket. Wow. So that one's locked. Comma, and now we
need the criteria. There's our dog for this row. Now I need that locked when
I copy it across the columns, but I need it to move
relatively when I copy down. So I'm going to hit the F4
key one, two, three times. I'm locking column reference
G, but not 329, comma. Criteria range, too. I need to go from the
categories column, so I highlight, second
start square bracket, colon, open square bracket, C,
tab, close square bracket, two of them. So now, criteria
range two is locked. Comma, and now I
need column header-- And for this, I need it
locked when I copy down, but it needs to move
relatively side to side. So I'm going to hit
the F4 one, two times. Lock the row but not the column. Oh, my heavens. Close parentheses. Control Enter, double click
and send it down, copy it to the side, go to the last
cell, F2, and sure enough, that is the most complicated
reference formula that we've done in this class. Everything is locked down for
columns, and our row header and column header criteria for
this adding works perfectly. Enter. All right, now we want to
actually see two more examples and I'll save the best one
for last, which is example 23. And you can see up
here I left 20 out. So I actually want to go over
to TFN on a different sheet. Remember, this table
is called Sales Table. So I'm going to click on TFN
and on a different sheet. And now, for example
20, I'm going to do table formula nomenclature
from a different sheet, and this is one great benefit
of using table formula nomenclature. I want to do that same
calculation over here, so I'm going to types SUMIFS. For the sum range,
I need Sales Table, so I'm going to type in S, and
notice-- there's our 3 icons. Gold dog tag means Defined Name,
table icon means Excel Table, and f of x icon means function. So I'm going to
down arrow and tab. Now any time I type an
open square bracket, the dropdown is polite. It gives me all the elements
from the table-- field names and then elements like
everything-- field names and all the records. Just the data, that's
the inside part. Just the headers, that
would be just the headers. Total rows, which we haven't
used yet in this class. Now I want Sales, so I'm
going to hit Tab, close square bracket, comma. Criteria range, it's the same. S, down arrow, tab,
open square bracket. So one of the advantages of
table formula nomenclature is, if you're on some
other-- you can-- [INAUDIBLE] --select whatever you want-- From this dropdown-- --as soon as you type
an open square bracket. I need product, so down arrow,
tab, close square bracket, comma. And then for criteria,
I hit left arrow, and there it is--
relative cell reference. Now I can Control Enter,
double click and send it down. Now a strange use, which
sometimes has benefit, is if we want to count fields. That means we need a
formula that will always look at that table, and if
we add or subtract columns, it will always tell
us, on this sheet, how many fields
are in the table. So equals, and I'm
going to COUNTA, because fields are text,
Then I'm going to type s down arrow to get my Sales
Table, open square bracket and-- watch this-- I'm
going to use headers, those are the field names,
close square bracket. Now remember, we
added a third column. So this should tell us
three when I hit Enter. If I were to go back over
to the reference sheet and, just for a moment, point
to this corner-- and I'm going to click and
drag, knowing that I can come back and undo that later. When I come back over
here-- oh, look at that. It totally is counting fields. Go back over here, I'm
going to Control z. Now it's back, part of the
table, and there it is. It totally updates
counting headers. All right, we have
one last example, and I saved the best for last,
for table formula nomenclature. Now here we have an invoice. And what I'd like is, I
would like automatically to have a dropdown
arrow that points over to our lookup table. I want to be able to select,
from a dropdown, any product I have in this table. Then I want the view lookup
function to look up the price. But if I add new records,
I want everything in the invoice to update. I'm going to come over
to the lookup table. Control t, Enter, Alt,
J, T, A to name it, up in the properties table name. I named it Product
Lookup Table and Enter. Now I want to highlight
all these cells, and I would like to use data
validation list to always look in the first column, and
then give me a dropdown so I can select from
my list of products. I go up to Data, Data Tools,
and there's Data Validation. I can click the button and the
default for data validation, of course, is any
value, but I want to validate what data
goes into these cells, and I want a dropdown. So I allow only
items from a list. And now in the source, I'm going
to highlight the first column. Now notice, it doesn't put
table formula nomenclature in, but that range will work inside
of Data Validation Source. It will totally expand
when the table expands. When I click OK,
there is my dropdown. That's pretty sweet. Now just for our example
here, I'm going to type five because the person
is buying 5 quads. And now I want to
VLOOKUP to look up the price based on the quad. Equals, VL Tab, lookup value,
relative cell reference, comma, and the table array. Watch what happens
when I highlight the inside of the table. Oh, look at that. Product Lookup Table. And that part of table
formula nomenclature is locked in all directions. And if we added
any extra columns, it would totally expand. Comma, what am I looking up? Well, I'm looking up the
quad, finding a match in the first column,
and then I need to tell the VLOOKUP function
which column, one or two, has the thing I want to go and
get and bring back to the cell. Well, one, two,
the second column has the price I want to
go and get and bring back. So I put, in column index
number, I just type a two. Comma, the lookup
range-- this is exact match because I want
to find an exact match. Q-U-A-D? Q-U-A-D in the first column. Now I can put false or zero. That tells VLOOKUP we're
doing an exact match. Close parentheses, Control
Enter, and copy it down. Now I do not want
N/As to show up, and the reason why, of course,
is it's trying to find nothing and it can't find
nothing over here. So since 2013 there's a
great function built exactly for this reason. If-- not if function
because then we'd have to put in a logical
test and give it two options-- it's IFNA. So I enter that and the value
is, in our case, VLOOKUP. When VLOOKUP does
not come to N/A, it'll just put the
result of VLOOKUP. But when VLOOKUP delivers an
N/A, I come to the end, comma. And right there,
it says value IFNA, I'm putting in the
syntax for show nothing, double quote, double quote. Now technically, that is
a zero length text string, but it is also the syntax for
our formula to show nothing. Close parentheses, Control
Enter-- and notice, I actually have to
copy this down and then remember to copy it up. Now the total for
this row equals-- and I'm going to use
round because I'm not quite sure if there's ever going
to be extraneous pennies-- I'm going to say, hey, units
times price and comma two, because I'm rounding
to the penny. This is an invoice that
has to do with money and we don't have
partial pennies, so I'm rounding to the penny. Close parentheses. Control Enter, and I
can copy this one down. Oh, look at that. Same thing. If I look here, it's
trying to multiply-- well, it looks like zero times
zero, but remember, right here, IFNA is dumping
a zero length text string into the cell. I'm not allowed to
multiply something that's text times-- this cell
actually does have nothing, which Excel thinks is zero. So I have to change
this formula. I come to the top, F2,
and instead of IFNA, ever since Excel 2007,
we have IFERROR. And IFERROR is similar to IFNA. Whatever is in the value, if it
doesn't evaluate to an error, it just puts whatever this
evaluates to in the cell. But I come to the end,
comma, and now it's saying, what do you want me to put
in the cell if it's an error? Same thing. Double quote, double quote. That's the way we
can show nothing. Close parentheses, Control
Enter, and copy it down. Now we have an invoice. If I come over here and
select Carlota and type 10, that is amazing. Now here's what's
really amazing. If I come down here
and I type Aspen, as soon as I hit Tab and
then enter a price, 25, watch what happens. I come over here. That is a dynamic dropdown
that totally acknowledges that the table has expanded. When I select
Aspen, look at that. VLOOKUP totally had an
expandable table, also. I can say 25, Control
Enter, and boom. There we are. Everything from our
data validation dropdown to our VLOOKUP is
dynamically working. Wow, that was an epic
video all about references. We learned in examples 24
to 19 about table formula nomenclature, including
how to do relative, mixed, and absolute references. Examples 16 to 18, we
talked about Defined Names. Example 15, we talked
about 3D references. We talked in example 14
about workbook references. Examples 13 and 12, we
did sheet references. Example 11, we talked
about the scenario feature for saving formula inputs. And then examples 10, Control
Home, all the way to one, we talked about the four basic
cell references-- relative, absolute, mixed with
a column locked, and mixed with a row locked. Now you can go ahead to
the end of this workbook and there are homework
problems for you to practice. All right, next video
we'll get to talk about making
calculations with formula with multiple
conditions or criteria. All right? We'll see you next video.
