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

so for this video

you had some homework

right remember

I asked you to copy and paste the summary equations

the summary functions that we used

for daily average low temperature

copy and paste those over to the right

into the daily high temperature column column d

so I'm gonna do that right now

I'm gonna do this one at a time at first

and then I'll do a whole bunch of them all at once

so control C to copy

moving over to the right

control V to paste

and something different clearly happens here

right so in fact

let's just pause right here

and as I asked you to do

I suggested that you click up here

so that we can get that color coded information

on what the arguments are for the sum function

what information is in that sum function

and we see that instead

of maintaining the reference to cells C2 through C13

as soon as we move that information over to the right

the cell referencing information

moved over to the right by the same amount

and what it did is it kept the same

number of rows

the same vertical sequence essentially right

and so it took that rectangle

and when we shifted the function over to the right

Excel shifted everything over to the right

and if we do that with these other functions as well

and we can do the all three at once

just using copy and paste

we see the same thing happened every single time

every single time is

updated to that new range of data so

this is one of those things that Excel does to help you

but whenever

remember I've said this before

whenever Excel does a shortcut to help you

it's making an Assumption

Excel makes an Assumption that if you move a function

if you move an equation over to the right

or if you move it anywhere

that you want to change what cells

that equation or function is referring to

what this example shows is

it shows the principle of relative cell references

and this is the default

cell referencing system that Excel uses

what we mean by relative cell references is in this

some function

for example

it looks like we're asking Excel to some cells

C2 through C13

and in fact

that's what we've told Excel to do

but what Excel is actually thinking is

it's going to sum 12 cells that are four cells above

the cell where the function is 1

2 3 4 and then it's going to sum all 12 of those cells

over here that works

one two three

four and then it's going to sum all of those cells

relative cell referencing is what this is called

and again it's a fantastic shortcut

we can do it again

what if we want to know the average of um

the average precipitation that we get in my area

every months

right across a year

average precipitation per month

really quickly

we just copy and paste that one cell

it brings over the equation with it

it brings the function with it

and it updates to that new location

it has the same relative location

this is thinking sort of spatially

it has the same spatial distance

to where we entered in that equation

this is fantastic

it saves a lot of time

it doesn't always work

so for example

what if we actually want to move the value 89.96

repeating if I copy and paste that

I'll just go over one more cell there

now we're going to get an error

because what's happening is

now Excel is looking at an empty column

to try to do a calculation

when really we don't want it to do that calculation

really all we want is to try to move that number over

and that's where things

can get a little bit complicated

is if you want to do what Excel thinks you want to do

then it's going to work beautifully

this relative cell referencing system

but for other types of operations

other things we might wanna do

we're gonna need to make some changes

and so what we're going to explore next

now that we understand this concept of relative

cell referencing

you're going to explore how we can move values around

without that equation updating

how we can just move that value by itself

the number the answer by itself

or how we can move that equation and function

and have it still refer back to where it

was referring original

so we can break that relative cell referencing system

with something called absolute cell referencing

so that's what we're going to tackle next

and this is really going to help you

master spreadsheets

because both of these tools

relative cell referencing and absolute cell referencing

are going to come in very handy

especially when you're using equations and functions

so we just learns 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 dude

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

and 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 seal 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 in Excel

most recently

we've been working with functions

and getting a more advanced

understanding of cell references in Excel

and right now

we're gonna take a little bit of a break

from working on formulas and functions

and we're gonna start looking at some different ways

to manipulate and reorganize

and pull meaning from our spreadsheets

don't worry

we're going to be coming back to functions

and learning more advanced functions

that we can use in formulas but these

these sets of tools that I'm gonna be talking about now

sorting and filtering

are some of the most powerful tools that we have

and they're just a few things that

you need to make sure you think about and master

before you start engaging in sorting and filtering

to make sure you're doing what you think you are

and to maintain the integrity

of the data you have in your spreadsheet

for sorting and filtering

we're gonna start with our weather data

and then we're gonna move on to some larger

more advanced data sets

again it's useful to have a small data set

where we can see everything that's going on

and a data set where we're familiar with it

so we know when something is going wrong

now the sort function in Excel

as I've said

is one of the most useful things in Excel

sometimes we want to be able to identify the largest

smallest value

the earliest or latest value

organize things alphabetically

or any other way

and we can do all of that through sorting

there are few key things to keep in mind

and that's the point of this video

on some of the exercises you're going to be doing

now we're on the home menu

we're on the home tab

home tab and we see our ribbon

of all our different commands here

if we look way on the right here

we have a sort and filter button

I think it's more useful to go into the data tab

where we have more details

more complete sort and filter functions here

so that's what we're going to be working with

our first job

is that we're going to sort our weather data

by the daily high temperature

and basically

we're going to put this in order

where we're looking at

which months have

the lowest daily high temperature on average

and which months have the highest daily

temperature on average

so we wanna sort our data set

so that temperature is ascending

so it's climbing

we're gonna do that by sorting on

by clicking on the sort button here now

this is one of the most important things to think about

if we select our column of interest

as I have our daily high temperature

and then we choose to sort it

Excel immediately gives us a warning here

it's suggesting we expand

the selection from this one column

because it's suggesting that we probably wanna sort

all of our days together

and not just this one column

and this is absolutely correct

this is a place where we do wanna

listen to Excel's advice

think about what will happen if we simply sort

temperature

we only sort temperature

then those temperatures

are no longer going to be associated with

the correct month

they're not going to be associated with

the correct precipitation

and basically

we've messed up our data set

and so this is a really critical thing to think about

even when we wanna sort

buy one column

we want to sort our entire data set usually

so we're gonna take Excel's advice

and we're going to expand the selection

once we do that

now Excel is going to ask us what we want to sort by

we're going to sort by

daily high temperature

we're gonna use the cell values

the numbers in the cells

as our sort criteria

and we're gonna go from smallest to largest

we're going to increase

temperature as we go down in the spreadsheet

it's gonna be an ascending sort

the data has headers

Excel recognize this and so yes

we are not sorting

we're not including that first row of data

that's fantastic

because that would be confusing also

so let's go ahead and click okay

and see what we have here

we know sort

did something

because we see our index values are now out of order

this is where having an index column

can be really useful

it tells us when our data are in some order

other than the original order that we had them in

we could tell that by looking at once as well

but it's not quite as intuitive as looking

at those numbers

and we do indeed

see that our

month with the lowest daily high temperature

around here is January

about 51 degrees Fahrenheit

and our highest is August

I'm sorry it's July

with about 89 degrees Fahrenheit

so this worked very effectively

and every other data point

sorted along with that

so our data

still has integrity

we can say that this first row

is all associated with January

last row is all associated with July

and that's really critical

when we're thinking about sorting

now often when we sort

this is a temporary way of manipulating our data

we wanna make sure we can

get it back to the original format

we want everything to get back into order

there are several ways we can do this

if sorting was the last thing we did

then we can just

use our undo

keyboard shortcut

control Z to really quickly

put everything back into the proper order

so that's one option

is using Control Z

just to undo that sort

but that won't work

if you sorted your data

a while ago

and did a whole bunch of things in between

so I've just re sorted it

so we can look at some other ways

what are some ways

you can think of

where we can

resort these data back into the original order

I can think of at least two ways

now we're seeing the value of that index column right

if we want us

put this data set back into the original order

we can just resort

but we're going to do it by index this time

we will reorganize those from smallest to largest

and boom fantastic

we now have our data back in the original order

this is part of the reason I like to have an index

or an ID column in almost any data set I'm working with

with it can really help you solve issues

like the one we just solved here

we'll do one more thing

and this is a slightly more advanced

option within the sort menu

so we know that

our data were originally in the order of months

and so let's see if we can sort order based on month

now the first option we're given here is to sort a to Z

that's not really gonna give us what we want

we don't want our months sorted alphabetically

maybe we would at some point

but that's not what we want

we want them back in calendar year order

so can we do that

the answer is yes

if we choose the drop down on the order menu

we can go here to custom list

and this is a little bit hard to find

you wouldn't necessarily know this is in here

and if we look at this custom list

we can create our own list and put any order we want

or as you see

there are some standardized lists

days of the week

months so let's go with this list of full months

that's the order we want

things in the order will be January

February March and so on

and we click okay

and voila our order

our data goes back into the original order

so it's another way of unsorting

and going back to that original data format

again with Excel

it's always useful when you're manipulating

when you're changing your data

making sure you have some way to

get it back to the way it originally was

that's a really important data management practice

and even when we're sorting

we need to be thinking about that

gonna give you some exercises

so you can practice this yourself now

so at this point

a great thing for us to be doing

is actually combining some of our skills together

so that we can really integrate what we're learning

that's gonna lead to much more

advanced use of Excel in the future

is thinking about how we can integrate these tools

and use them all together

so this is why I asked you to create this temp diff

column where we're calculating

how variable the temperatures are in each month

I asked you to write a formula to do this

and I started you off here by reminding you

that we would always start with an equal sign

there are number of ways we can do this

but I think maybe the easiest way

is to just click on our individual cells

and say that we wanna take the difference

between the high temperature and the low temperature

and that's all it is

it's just a simple arithmetic equation

that we can write using the minus sign

so we don't always have to use functions

we can use math equations

we can use plus

minus divide

and multiply

and a lot of the time

that's going to give us something that we want

we see that that temperature

that variability in the month of January

is 21 degrees Fahrenheit

that's not a whole lot

and remember

what we can do is there are multiple

different ways that we can copy this value down

but if we do that

Excel is going to assume

because these are relative cell references

Excel is going to assume that we're always want

going to want to be looking immediately to the left

and then subtracting the value

that's two cells to the left

and so if we just paste that in right here

then we can see that we get the same value

and every time we click on a cell

we're always looking in column D

minus column C

but as we go down

the row number is updating automatically perfect

and this is interesting right

because we actually see that

where I live

that temperature variability is

about the same every single month

it's right around the low twenties

as low temperatures go up

high temperatures also go up

where did we see this before

we saw this when we made a scatter plot

so we're seeing how a lot of these different ways

of looking at our data

are starting to come together

so let's go ahead

and we'll sort our data set now

based on this new value that we just created

and I'm going to say okay

we're gonna use temptive cell values and no

we're not gonna do it based on month order

we're gonna go smallest to largest

our least variable month is the month of August

our most variable month

in terms of temperature

is the month of April

and again if we wanted to undo this

we could use undo

we could sort custom on the months column

or we can just sort numerically

based on our index column

all of these would get us what we wanted

so I hope you were successful in

calculating that difference

in doing these sorting functions and soon

we'll be moving on with a close companion of sorting

which is filtering

so we just did some really great work with sorting

and now we're gonna move on to a skill

that I consider a close companion of sorting

sorting and that's filtering

so let's quickly compare and contrast

what's going on with these two things

with sorting

we're changing the order of our data

but we're keeping it all there

we're just looking at it in a different order

and maybe that's because we want it alphabetical

or an increasing order

or month order

something like that

with filtering

we're actually only looking at some of our data

removing some of our data from view

that we're not interested in

so that we can focus on

on data that we are interested in

so these are slightly related

but slightly different ways of organizing

and drawing different kinds of meaning from our data

I say that these are closely related

and it looks like Excel agrees with me

because we see that filter is right next to sort

in the data menu here

and so it's going to be easy to find

let's do some practice filtering to see how it works

and to see what we can get out of filtering

now notice them already clicked within our data

that's important

because now that my cursor

I have a highlighted cell within our data

I can just click on this filter button

and what that's going to do is

it's going to add in

pull down menus to each of our columns

this is really interesting

we didn't get a pop up window

we got something else

we got these pull downs

so let's see what we can get here

let's assume that we want a filter based on month

and we only wanna look at the winter months around here

where I am and so

winter months around here

I'm just gonna say

we consider December

January and February to be winter

and the problem with the data right now is

those months are separated

we have January and February together

December is different

it's down at the bottom

because it's in order of months

and so maybe

we wanna make sure we can look at all those together

so we'll filter out everything

other than that

we'll just click on this little pull down menu

and you immediately see that

we also have our sort options here

and so this is one way that you could do sorting

we won't do it now

because we've just been practicing with it

but if you wanna apply the filter

to your entire data set

and then use these pull down menus to sort

you could do that

so this is really great

it's a comprehensive data reorganization window

that we have here

what you see

is that everything that's shown in our data set

has a checkbox next to it

so as you recall

we only now wanna look at winter months

we only wanna look at December

January and February

think the easiest way to do this is to unselect

everything now

we're not gonna see anything in our data

but we'll go ahead and reselect December

February and January those three months

now I'm gonna click okay and we'll see what happens

this is a little scary

the first time it happens

it looks like you just lost all your data

but what it's doing is

it's hiding data that you said you're not interested in

and only showing you the data

that you said you are interested in

how do we know our other data is still there

well we could undo this and get it back

and that's really easy

but we can also see

when we look at our row numbers

it's telling us we're missing a bunch of rows

it's only showing us here

highlighted in blue

row 2 row 3

row thirteen

it's telling us we have other rows of data in there

they just happen to be him so

that should make us feel a little bit better about it

we now have our information

just for our winter months

how can we get all of our information back

you see that

there's a little filter icon here in this pull down tab

that's telling us that we're filtering on month

so we would go back into month

to clear that out and restore data set

you can see

we also have this option of clearing out that filter

up here on the data menu

and we can do that in either place

and as soon as we clear that out

we get all of our data back

like nothing happened

and so hopefully

that's reassuring

that we're not actually changing or losing data

we're just changing what Excel is showing us

temporarily if we want

what are the kinds of filtering

can we do in a data set like this

well we've got a lot of numerical data

and we just did a filter based on text and says

let's take a look at how we can filter based on numbers

we're dealing with temperature

and let's suppose that we want to have an outdoor event

maybe a party or a wedding

and we wanna make sure the weather is neither too hot

nor too cold to have this outdoor event

which months might be good for this

I'm gonna arbitrarily define not too hot

not too cold is somewhere between 60 degrees Fahrenheit

and 80 degrees Fahrenheit during the day

that sounds like really good temperatures to me

so how can we filter our data

so we can only see high temperatures between 60 and 80

let's go into our pull down menu here

and figure this out

now if we approach this

the way we approached our text filtering

we'll see that all we can do is

we can just select or deselect individual

specific temperatures

and so this is the same thing as filtering by months

really we're just gonna eliminate those specific rows

and so there's no point

in doing this manually

that's not gonna help us

especially if we have a really big data set

and remember

most of these tools are gonna become most useful

when we have large data sets

so what else can we do

we can go into this number filters menu

a sub menu and now we have all these different features

equals does not equal

greater than less than

and here is one that's really gonna be helpful between

as I just said

we want to find days that are warmer

or 60 degrees

and

cooler or 80 degrees

so we want values between 60 and 80

including those numbers we'll just say

and now if we click that

fantastic our data set gets smaller

that's exactly what we wanted to see

we wanna see that sub set of months

that meet the criteria that I set

for the kind of party that I wanna have outside

and so now we know

we can schedule these events pretty confidently

in March April

May October

or November

so that's nice to know we've got these options

and as usual

we can just really quickly clear this out

and get all of our data back

so filtering as you see

is a really

really great way to draw new meaning from our data

to simplify our data

and to answer specific questions

this is very similar to using some more

advanced functions in Excel

and after you do some practice with filtering

we're gonna move on to those more advanced functions

where we can answer similar questions

before we move on from filtering

let's look at another application of filtering

and you may remember

that when I was showing you how to plot

and first introducing the idea of charts and graphs

and looking at a bar or column chart

I was actually

in real time

adjusting what I was showing you

this is one potential application of filtering

that can be really

really useful

now what I was actually doing

when I was doing that demonstration is

I was manually changing which values I was showing you

just by dragging the selected data

and the chart updates in real time based on that

and so that's a neat tool

that anytime you want

you're not sure which data

you should be showing in your chart

you can just change your selection

and the chart will update in real time

and that's really helpful

but another thing we can do

is we can apply a filter

we can apply a filter to the data

that are used in our chart

and if we do that

then the chart will update in a similar fashion

so if I go back to what we just did in filtering

and I say okay

actually we only wanna look at those winter months

here we have those winter months filtered down

to just those three

everything else has been removed

and our chart has updated

just as we expected it would

now we would wanna change some things about this

it's really squat and wide

and so we would make some modifications

but this is a great way to really easily assess

the effects of including different data sets

and data points in your chart

and looking at how you can change it

to show different things

so again this is an important integration

of a few of the different skills we had

you can use filtering

when you're making a chart

to show different things

and learn different things about your data

we're just working with filtering

filtering is one way of reducing our data

and only looking at our data

if it meets certain conditions

there's another way we can do this

we can have conditional functions

and conditional functions will do the same thing

except conditional functions

can sometimes be more powerful

because they can apply

some kind of mathematical function to our data

that only meet certain conditions

just as we're using filtering to find winter months

or find months with temperatures within a certain range

we can do the same thing

using these conditional functions

I'm gonna show you these

because they're a very powerful tool

for understanding what's going on with our data

as I mentioned early on

with functions

there are hundreds of functions in Excel

and there are even several conditional functions

we're going to use count

if we're going to count

how many data points match the criteria

we're setting

we could also do some if

we could add data points to each other

if they meet conditions

average if we could take averages

but only for some of our data

this is a very similar idea to filtering

it just adds in this additional step

of adding some math to it

if we'd like to do these

additional mathematical functions

in this case as I said

we're going to be focusing on count if

and I'm going to show you

how these conditional functions work

as you know

when you're putting a function into its cell

we immediately start getting clues

on what kind of information it needs for its arguments

for count if

it wants to know what range it's looking in

and because we're saying right now

in this case

we're going to be looking for good weather months

which I'm defining as months with a low temperature

greater than 40 degrees

so we'll select

the range of data associated with low temperature

now how am I going to define this good weather month

I have it right there next to it

we want low temperatures to be greater than 40 degrees

we put the condition

we put the criteria in quotes

and now as soon as I hit enter

Excel's going to do this count for us

and it's going to say okay

there are seven months that meet your condition

of having low temperatures greater than 40 degrees

remember we could be doing this ourselves

this is a small data set

and that's the point

you can check this visually

if you want to confirm that Excel is correct

but these functions become very powerful

when you don't wanna be counting at yourself

when you're looking at rose

with hundreds of data points

that's what we're going to be doing soon

now another condition that I'm going to say

is having good weather months

I don't like really

really hot weather

I like it warm

so I'm also going to say

we only want once

where the high temperature

and so now we're selecting

this high temperature range

is going to be

less than 80 degrees Fahrenheit

and there are eight of those months

so this is one quick example

leading you into understanding

how these conditional functions work

we just counted up months

that meet our condition of having lows greater than 40

we counted up months

leading our condition of having highs less than 80

but what we haven't done yet is

we haven't figured out if any months meet both criteria

that is what's going to come next

after you have a little opportunity

to practice using some of these conditional functions

so we've been doing a great job

working with these conditional functions so far

have a good understanding of count if

presumably you've gotten to feel

for the fact that you could use some if

and average if also

and what we looked at is

we looked at how we're setting conditions

for this count function right

but we still have more work we wanna do

we actually wanna look at

two conditions at the same time

we know in my area

that we have seven months

or low temperatures are greater than 40

and we have eight months

or high temperatures are less than eighty

but how many months fit both of these criteria

both of these conditions

we can't do this with count if

this means we need to work

with another set of conditional functions

these are the IFS functions

there's an s on these

and so I'll just type this in here

I will type it in properly

and as we see here

count IFS will count

cells specified by a given set of conditions

so not just one

so we can use this to tackle our problem here

and this is a really neat set of functions

I actually use these quite a bit

when I'm summarizing data

as an alternative to filtering

which is another way of doing this

as usual we wanna set our range for our first criterion

and again that's low temperatures

so I select that column

and as we know

we want those temperatures

to be greater than 40 degrees

but now we're looking at our second condition

that we're setting

still within the same function

but it's a separate condition

so we need to be looking in the separate column

because now we're talking about high temperatures

we want those high temperatures to be less than eighty

count ifs 4

check both of these boxes at the same time

January is the low

greater than 40

is the highest

than 80 yes or no

and it'll go down the line

and so let's see what we get here

three months total

across this data set

that meet both of those criteria

so now we can be confident

that we have three months across the year

where lows are greater than 40

and less than 80

we could have done this manually

but even just checking

two different things at the same time

can be a real challenge

so these count IPS functions

can be very

very useful

they're great alternative to filtering

or something

that you can use in combination with filtering

and remember

something I've been encouraging you to do more and more

now that we're getting more advanced material

is be thinking of ways

that you can integrate

these different skills

and use them together

and that's when you're going to become

a very powerful Excel user

we're almost finished with Week 3

soon I'm gonna wrap up

and recap what we've done this week

and then you'll move on to an assignment

where you'll have the opportunity

to integrate

many of these skills

you've been working on

great job so far