- In today's video,
we're going to talk about
references in Microsoft Excel.
References are a key concept in Excel
and whenever you use Excel,
you will almost always use references,
which is why it is so important
to understand them well.
But I will not only show you how to work
with references, I will
show you a special view
in Excel, a special notation
called R1C1 notation
that will really help you understand
how Excel works and thinks under the hood.
And at least for me, once
I first learned about this,
this was little revealing moment
and after which I understood
Excel much better.
Welcome to another coffee break here
on my channel Firm Learning.
My name is Heinrich
and on this channel, I want to help you
to become successful in the
first years of your career.
But now without further ado,
I have my laptop here in front of me,
so let's jump in and I will
show you how Excel works
under the hood.
So here we are now in the middle of Excel
and I've just prepared now
a very basic simple example.
Let's now imagine we
have different companies
or different subsidiaries of a company
and they have had sales figures here
for the different quarter
and now you want to sum
up these sales figures.
So I trust almost all of you
would be able to do that.
There's different ways to do it I guess.
You can just set up a basic
SUM function like this.
You mark then the range
that you want to sum up.
And then now here you have the
sum of these values included.
Now what most people will do now
to really get it down until the very end
is just to copy this formula
and then just insert it
here below in these fields.
And now what you can see
is that the formula
automatically adjusted here
now really to change the range
to the relative range down here.
And at least when I started to use Excel,
while I had kind of an
intuitive understanding
that you could just kind of
drag down formulas in this way,
I often struggled to really understand
how exactly does it work?
Because Excel somehow seems to recognize
that here there are some
fields below as well
and then can adopt the
range automatically.
And now let's just contrast that
with just the made-up
little other example.
So let's imagine here you have workers
who work different shifts.
And these are now hours
they work in this shift.
And now the Worker 1 has
here these two shifts
that he worked.
And now, of course, if
you just copy that down,
Excel is not able to directly recognize
that now actually the
fields with data switch
and change, right?
So here, Excel doesn't
give you the correct number
that you want.
And overall, I just perceive
this to be rather random
and probably this was just due
to my naivety at the beginning
but I just didn't seem to figure out
how exactly these changes
in reference work.
And what had me really understand
this on a technical level
is to learn about the
different reference styles,
the different notations that Excel uses.
And what we are all familiar with
is the so-called A1 notation.
And this means that the columns
are referenced in letters,
so A, B, C, D, E and so on.
And then the rows are
referenced in numbers,
one, two, three, four, five and so on.
This is called the A1 notation.
But you can actually
change this reference style
to something that is
called the R1C1 notation,
R for rows and C for columns
and this makes Excel show
you all the references,
both for the rows and for
the columns with numbers.
And while I would not recommend you use
this alternative reference
style on a daily basis,
there are some things to learn from it
how Excel actually works under the hood.
So let's just check it out.
And to get there, you
need to click here on File
and then on Options and
then in the Options menu,
you click on Formulas
and then you need to
check this checkbox here
for the R1C1 reference style.
And let's activate it.
And now if you click
here into the formulas,
you directly see that the reference styles
are somewhat different.
They don't look like they looked before.
But now before we jump into this,
let me now explain to you
on a conceptual level how this works.
And what you need to understand
is that now the reference style
is always relative to the cell
that you're currently in.
So if you're now typing
in a formula in this cell,
and now you want to make a reference
to another formula, you now always need
to define both the rows
and the column relative
to this one cell.
And how it works is for the columns
that columns to the right
of the reference cell
are referenced with a plus.
So this would be plus
one, plus two, plus three,
plus four and so on.
And columns to the left
would be referenced
with the minus.
So this would be the column
-1, -2, -3, -4 and so on.
And the same thing now for the rows.
If you go up, this is
actually a minus reference.
So this would be row -1,
-2, -3, you get the idea.
And if you go down, this is a plus.
So this will be the row plus
one, plus two, plus three
and so on.
So let's make an example.
If you now are in this cell
and you actually want to
reference this one here,
this would be, you start with the rows,
so this would be the row
one, two, so minus two,
comma and then the columns,
this would be now the column one, two
because it's two columns to the left.
So minus two as well.
And now another example
if you want to reference this field here,
this would be in the rows
one, two, three, four,
so plus four.
And then one on the column
because it's one to
the right, so plus one.
So I hope this makes sense.
Now let's just see in
practice how this looks like.
And I just prepared here just a sheet
with some random ones just
to reference a few numbers.
If you now would want to
reference this field here,
now it links, you see
that it returns me one.
It's now referenced R minus
one, column minus two.
So here, one up in the rows,
minutes one and then two to
the left with the columns
and you see now that you
always write this first
with the R and then later with a C
and then you put the numbers
in these rectangular brackets here
just ready to reference this.
Now, another example would
be, for instance, this field.
Now you see it's one, two,
three, four, five rows
and two columns to the right.
And then this is what you get.
Now what happens if you
actually reference a field
in the same row, in the same column.
Then you see after the
row, you put nothing
but then it's only the columns
minus two that you have
because it is, indeed in the same row.
And of course, the same
is true for the columns.
So if you now reference a field
that is in the same column,
you just specify the row
and you need to specify
nothing for the columns.
I hope this is clear.
Let's now jump back to our example here.
Now I guess you can also
more clearly understand here
the new references that we see here.
So here, this field gives us again the sum
and now the sum range
is specified as RC -4,
so this would be here.
And then the endpoint is RC -1
and this would be here.
So I guess this makes sense.
This is exactly what we saw.
But now what happens if we copy this down?
And maybe you're already
aware what happens
because now if we copy down this formula,
it gives us the very same text.
So the actual content of the cells here
does not change at all.
And this is now the key
to understand now also
this A1 reference style
because what you have here
is a so-called relative reference.
Well, this formula does it.
It always looks at the current field
and then relative to this field,
it will then specify
the rows and the columns
because you defined the
rows and the columns.
Always relative to this
field or cell here.
And indeed, in the A1
style that we had before,
you just were not able
to directly see that it
was a relative reference
because here then the value's changes.
If you now go back to the style,
if we now deactivate the R1
two one reference method here,
now again we see that
actually here it does look
like there references
changes more or less randomly
but it's not really the
case because below the hood,
Excel actually works with
these relative references.
It always specifies the range relative
to the starting position.
So now back here in the
RC reference method,
I hope that you can see
how this makes sense.
And now, here as well.
Now, if we know that this is how it works,
that it always references
the cells relative
in exactly this way,
then it is also completely obvious
by, for instance, in this situation,
Excel is not automatically
now shifting it over.
But now, of course, what we
do need in some instances
is not a relative reference
but an absolute reference.
And of course, Excel
provides a reference style
for this absolute reference as well.
So let's now look into another example,
which is pretty similar.
We have now the quarterly
sales data from the companies
and then the sum.
But now you want to forecast
what these companies will
make in the next year.
And for that, you have set up
here this basic row factor,
so you predict that the
sales of these companies
will grow by 10% next year.
Now, of course, what you can do,
you take the previous year's sales figures
and then you multiply it with
one plus this growth factor.
Now to provide you with this new figure.
So basically increasing
this figure by 10%.
Now, of course, if you just
copy this formula down,
you can directly see that this
is not giving you the results
that you expected.
And why is this the case?
Well, it's the case because
due to this relative reference,
this field on top of there,
it always moves down.
But of course, what you
wanna do is you want
to always keep it fixed.
You don't want this to
be a relative reference
but an absolute reference.
And yes, you can do this
with the RC notation as well
because what you do is you type R
and then you directly type in the number
of the row or of the column
that you wanna reference.
And what you can now see
is that once you change
to the RC reference style,
here the letters actually
switch to numbers.
So also the columns now don't
have the letters A, B, C
and so on, but they have numbers.
So now we defined first the row
and this field here, of course,
is in the row number one
and you can directly see
how this switches up.
And now for the column,
you put in here the column number three
and now it gives you
here this column number.
So now you see you do not
now define this anymore
as relative to this field,
which is here always indicated
with these rectangular brackets
in the RC notation
but you just directly type in the number
of the row and of the column,
just directly without
this rectangular bracket.
And now if you do this
and if you copy it over,
you see this why, this
here now, of course,
this blue field moves this red field here
on top always stay fixed.
This is now how you include
an absolute reference.
And now, of course,
you can also do partial
absolute references.
If you here now move to another example,
what we now here want to do
is actually we want to now
project the growth rates
of the different quarters independently.
So we want to predict that
Q1 would grow with 5%,
Q2 with 10, Q3 with 15,
Q4 with five and so on.
So now, of course, if you just would do it
in the same way that I just told you,
so if you reference here
this with the relative field
and now you put one plus
and now here, R1 column 17
or sorry, column 18,
which would be exactly this field.
If you do this,
then this will not give
you the correct result
because well, here of course,
it now gives you some numbers.
It always keeps this 5% fixed.
But now, of course, what
is also not the solution
is just to highlight this
as a relative reference
because if you now copy this over,
this also doesn't give you the right value
because now everything changes, right?
So while here in this
first row, it looks good,
once we now go one row down,
now, of course, the
reference has changed as well
and now it gives you all these errors
and it's just not what
you're supposed to do.
So what you actually need to do here
is that for the rows,
you do want to have an absolute reference
because you want the growth factor
always to be fixed here in the first row.
But now for the columns,
it's less clear, right?
Because for the columns now,
you do want a factor, which
is now here six columns
to the left because this is now the factor
that you want to keep
relative and flexible.
And if you now include
this and copy this down,
now you see that it works correctly.
So this one, of course, references here
and now this one, of course,
even though it's another row,
it still keeps the row correct.
It moves the growth factor
across the columns correctly
but it keeps the row fixed.
So in other words, what you can do
is you can keep the row reference absolute
but the column reference relative.
And of course, you can do it
the other way around as well.
So let's now switch
back to the A1 notation
to looks like how you actually do this
in the A1 notation as well.
So we now deactivated
the R1C1 reference style
and what you see here now is,
and this is probably something
that you might already be familiar with.
Here we have now the dollar signs
in front of C1 or in front
of this growth factor.
And again, if we just take them out,
if you just copy them over
without the dollar sign,
as we also saw earlier,
now, of course, this reference
becomes a relative reference
as it moves.
But once you now click in here
and you insert the dollar signs,
and actually, you can do that in Windows
with the F4 key, then it
includes this absolute reference
and now if you copy this
down, it keeps this fixed.
And of course, you can also
do partial absolute references
in the A1 reference style as well.
So here now you can directly see
that the T, so the column reference,
this doesn't have a dollar sign
and indeed, if you here move around,
you see that this changes.
So here it's an R, S, T, U and so on.
But actually, the row reference, the one,
this one has a dollar sign
and it doesn't change independent
of which field you're in.
So here you can do exactly this.
You can now switch around
and again, if you just toggle
the F4 key several times,
you can see that you can
toggle around these elements.
So here, this would be a
completely relevant reference.
Clicking once would give you
the complete absolute reference.
Once more, it just fixes the row
and once more, it just fixes the column.
So that's it.
I trust that many of you already knew
about absolute references
with the dollar signs
and probably had an
intuitive understanding
of many of these things
but I hope that this
video really helped you
to really jot that down,
to really understand on
a more technical level
how this works and why the
formulas and references
change the way they do in the A1 notation.
As always, if you have any question,
leave me a comment below
in the comments section.
Please, if you took any value at all out
of this video, hit the Like
button for the YouTube algorithm
and also subscribe to my channel
to stay up to date on all my content.
If you want to see even more from me,
I also have an Instagram.
My Instagram handle is @FirmLearning
and later today,
I'm probably going to
release my very first IGTV.
So stay tuned for that
to see what this is going to be all about.
In addition to that,
I also have a mailing list,
which has even more content
prepared for you guys.
There's a signup link for
that mailing list below
in the video description.
It was a pleasure for me
again to do this video.
I release new videos every single Saturday
and sometimes even bonus videos
throughout the week.
So looking forward to talking
to you again next Saturday
and until then, great
weekend to all you guys.
