All language subtitles for 5Topic 2-2 Absolute vs. Relative Cell References Week 3 Manipul-1

so we just Learned that

cell references in Excel are always

relative cell references right

they're always spatial

references and so just like I have highlighted here

as a reminder

we're looking at the cells

in this range

above where our equation is every time we move that

that range of cells is gonna move with it

how do we change things to be absolute cell references

and what I mean by absolute cell references

is for example

this average equation where we're averaging the

daily low temperatures for every month across the year

how can we make sure that we're always

absolutely always referring to that set of cells

and it's not gonna change

to make our cell references absolute cell references

we add dollar signs in between each column and row in

the arguments of the equation and so right up here

I can do it manually

I'll show you another way to do this we can just

add in dollar signs before each column and row

and now magically

this has become

an absolute

cell reference because of those dollar signs

I can now move this

equation move this function around wherever I want to

and it's always going to point back to these original

cells so we'll test this right now

you see over here on the right I've created a

new location for these summaries

so this will be a good way to test this so

if I copy and paste remember when I copy

the cell it brings the underlying

equation the underlying formula with it

if I copy that over here

oh look at that

click up here and it's referring to those

cells those original cells

and we get the same answer we

get 48.7 just like we had before

remember what it would be doing if it was still using

relative cell references

they would actually be looking way up off the top of

the sheet right would be looking up for cells and then

for a range of 12 cells up above that

but we fix that now

let's do this also with mean daily

high temperature for months so we'll come over here

I'll show you a shortcut

for doing this we can just highlight that

equation in the formula bar

instead of typing in

the dollar signs

we'll just hit F4

and F4 automatically

converts that relative cell reference to

absolute cell reference

now this is not gonna change we can do the same things

we can copy

we can paste that to the daily high

and it's going to update just like that

so this is a

very important thing to

understand is

you start working in more advanced

tasks in Excel I'm working with other

people's spreadsheets

where they're probably referring to different cells

and you need to be able to know if

you're dealing with an absolute

or a relative

cell reference

very very important

in more advanced

applications in Excel

I'm gonna show

you a couple other

ways that we

can solve the same

problem though

let's now look

at this average precipitation

per month 88

points 9 6 ish

millimeters per month

around here

and so what we wanna do

now is we wanna look

at different

ways that we

can move this value over so

one way we can do is we

can take advantage of something that's called paste

special so I just copied

now I'm going to right click and

you may have noticed that we have these paste options

these paste

special options and

one of these

options is we

can just paste

the values so

let's do that

click on that

and what happens here

is now only

the value has been moved over

so if we click we

see that there's no longer an

equation there's no

formula underlying that

there's only

the value so if we only wanna move the value

the result of an

equation we

can do that

paste special paste values

there's a drawback to that

so let's say we

let's say we delete June

from our data set

you can see temperature updates

but over here on the right

precipitation does

not update it changed here because we lost

the value for June

it does not

update over here because we only pasted that number

so this isn't necessarily

the best option I'm going to

undo those I'm hitting control Z

to undo those deletions I did

pasting those values by themselves is

not always the best option

because now that value will never update

maybe that's how we wanna do it sometimes but

we need to be careful

and we need to make sure that's really what we wanna do

so I'm gonna show you one

other option here we can do another copy

and we're gonna do another paste special what we

can do is we can paste a link

and what we've just done

is we now are linking

to this cell

we don't have the equation in there anymore

we're saying it is equal to cell E20 so cell

M4 is always going to

equal E20 and we know it's always going to equal E20

because we're using an absolute cell reference there

because we see those dollar size

as I mentioned

understanding this distinction between relative

and absolute cell references is

very very important as you start working in more

advanced spreadsheets and especially when

you start working with other people's spreadsheets

and you need to figure out

how those cells are referring to each other

and what people

are doing in their equations functions and formulas

we'll have some applications of this very soon

but now you know the difference between

relative and absolute cell references and Excel