How to Make an Excel Graph on a Mac

Sharing buttons:

hi there physics students this is a

little tutorial on how to make a graph

specifically using a macintosh computer

uh we're going to be using excel and so

if you have a pc or if you have a mac

it's really going to be

basically the same it's a more difficult

on a mac

and more students in our classes have

mac so i'm going to i'm going to give

you the tutorial here

but on the pc you follow along the same

ways it's just a little bit easier

okay so this is the end result this is

what we want

we have some made-up data axe which i

represent with the

variable a bleeps which is b and

severals which is c

and then down here in parenthesis are

their imaginary units

okay and let's say that we've collected

some data on whatever this

is and we're going to make a graph of

severals and

x okay and the

sizzles is along the y axis and the x

are along the

x axis and we can see here we get some


we get a trend line we're going to plot

the we're going to plot the graph we

have a title we have

axis titles and then down here we're

going to do a little analysis

with the slope and the y-intercept all

right so let me show you how that's

how that's done we're going to just show

you the raw data now

so here's the raw data and now let's put

in the

um put in the graph all right so here's

how to do it

i i want you to click away from the from

the data

a lot of you i know are going to want to

highlight the the graph or highlight the

data and then

press the graph and then poof magic


magic is not great in science okay so

with science we want to be the ones in

control we want to say what it is we're


and so this is i think the best way to

do it it's up to you different teachers

do it different ways

but this is how i want my students to do

it okay so we're going to click away

up here we're going to click on the

insert tab

and then we're going to choose scatter

graph we're not going to connect the


we're not going to connect them with

lines we're always going to almost


going to do a scatter graph in this

classroom all right

and if the graph pops up over your data

that you can't so you can't see all the

data you need to move it out of the way

now what i want to do is i'm going to

select the data i'm on the chart design

so if you click off

you can always click back on again and

you'll see chart design

and then select data you want to

add a series here ignore these boxes

over here

you want to add a series there's the

series we can give it a name if we

wanted to

but because the uh we only are we're

only going to graph one

series then we don't really need to name

this okay so now we choose the

x values and that's going to be the

horizontal axis and if you remember that


x a c k s this data over here

so what we're going to do is we're going

to press this little button which is

going to minimize this window

okay and it's going to allow us to

highlight numbers

on the x-axis this is just the data you

with me

okay we're not highlighting the header

information just the numbers i

i take the button i blow it back up

again and there's my raw data

for the x-axis for the y i'm going to do

the same thing

i recommend that you press this button

to shrink it

and then you can highlight the values

along the vertical or the y-axis

and this would be the sizzles so i've

done that

and when you're done you can press ok

and now we have a nice

graph which shows the sizzles here and

the x here

of course you're going to forget what's

being graphed along the x and the y axis

so it's really important that we

add those those titles the access titles

at this moment

so if you have a pc there's a little

plus setting out over here you press the

plus and

then you choose axis titles for those

with the mac it's a little bit more

complex click on the chart

and come over here to add chart element

and we choose axis titles we want the

primary one the horizontal primary

and we want to click it again and do

access tiles primary vertical

the data table should have a name should

have each column should have a name

should have a variable it should have a


the same goes for the axis titles so the

axis title needs to have a name

again this is the vertical so that's


see and this is a text box so i could

just hit enter to go to the next line

and add centimeters and are not

centimeters but czs rather

whatever that unit means okay

next i click on this guy here this is my

triple click to highlight all the words

and then x the variable a

and the unit here is a k again

something made up students often ask

what should go in the chart title

i think this is a pretty straightforward

way to do it i like to do

the y-axis

settles versus what's on the x-axis

and then of some object of a ball

rolling down an incline plane or


so um you know of a you know

clock falling on zork

or whatever this is all just a made up

thing right

so y-axis versus x-axis

of some phenomenon

so now that we have the title done take

a look at this

if you click on the chart you'll see

that the data

is actually highlighted here on the data

table the data you're graphing is

highlighted on the table

and these little pink and blue boxes and

that tells us what

data is actually being graphed that's a

good little check to make sure we're

actually graphing what we think we're


if we wanted to we could grab these

corner pieces

let's say we didn't want the first three

data points for whatever reason they

were maybe bad or maybe they were

taken you did those incorrectly for

there's there's got to be some

scientific reason you get get get rid of

your data

so let's say that there is you can get

rid of it by simply

not showing it on the data table or not

showing it on the graph rather

the other thing you can do is if you

grab the edge not the corners but the

edge of these boxes

you can move them around and so for

example if i wanted to graph

axe versus bleeps i could just move that

box let it go

and there it is i can i can quickly

change when i'm when i'm graphing of

course i would have to change the axis


for that here we go back to here

okay so we can we can see that this is a

this is a

quad we could qualitatively look at this

we can see that the trend

is kind of linear that it is increasing


x that sizzles get bigger and bigger and

bigger with x at least for

for the data that we have here but we

can't really tell

a whole lot else about it we can see

that it crosses the

the x-axis around 5x we can't really

tell exactly where it's going to hit the

y-axis we can't really see the


we can maybe interpolate and go i don't

know it looks like 25 or so

negative 25. but we don't really know

we're doing qualitative analysis and we

want to do

quantitative analysis with numbers

because you know we're in the science


so let's let's do that by adding a trend

line to the data and this is

the data itself is beautiful right it's

a beautiful little picture

and it tells us something by looking at

it but we

need to really think about the qual

the quantitative the quantitative the


analysis and so we're going to click on

chart design click on chart and chart

design if you're not there

we're going to add a chart element and

we want to add a trend line

okay don't just click linear i know it's

linear i can see it's linear

but i want to add more trendline options

okay so click that

on a on a pc you're not going to see

this window you're going to see

the window that you're supposed to see

on a mac you have to actually

click trend line options again so click

on that little tab

and it opens here on a pc you'll

actually see this window

here you can change what's being graphed

if it's polynomial or

logarithmic or linear or whatever and so

it looks pretty linear to me so let's

try linear

and the other thing you want to do is

display the equation onto the chart

there's the equation the relationship

the numerical relationship

between severals and x and we also want

to display this thing called the r

squared value

okay so the r squared value um

well let me let me first of all make it

bigger so you can see it this is just a

text box here

so i can go to home and i can increase

the size of our of the text box

okay and this gives us the relationship

between what's on the

y-axis to what's on the x-axis

let me say a quick word very quick word

about this r squared value

r squared values is a statistical um

analysis that tells you how good your

your trend line

is to the data an r squared value of one

means that your data is exactly

the trend line exactly agrees with the


so this is absolutely this data is 100

linear if i were to change a data point

say make this 4.44 or something like


and hit enter my data would change my

data point would no longer be co-linear

and my r squared value would not be

exactly one

okay it would be very close to one we

would be happy with this data it looks

pretty good

the trend line seems to match the data

pretty well

and it seems to be linear but it's not

exactly that

so in the arc class we hope to get

numbers that are kind of like this 0.99


if you get one that means you're either

really really good

or you're you're probably cheating a

little bit

or you're maybe you're fudging your data

so we want to get close to one

the y value here is sezzles so

we want to change that we want to see

what we want to see that this

is not y but rather c because that's

along the y

axis and then along the x axis is our a

so this gives us the relationship the

mathematical relationship between

sezzles and x and i can then

type in 20x which is not even on our


and we can predict what the sizzles

would be

if we were to if we wanted to know that


all right so that is pretty much it as

far as the graph is concerned

it is kind of nice though to have to

have some analysis

before i before i quit this

i just want to show you how the next

step of the analysis

is going to be so because we have lots

of different data that we could be

graphing here


maybe versus x okay

i want to type slope here and i want to

also figure out what the y-intercept is


okay and i'm going to

right align these because i want my

data to go here make a little border

the slope of this is this number here


okay and i could just type 5.2 if i

wanted to

but really there's a nice equation here

it's the slope equation so you type

equals slope

parentheses and that gives you a little

hint highlight your known y

values well what's along the y axis oh

sizzles are along the y

axis so i'm going to highlight those and

the next thing if you look really


is a little comma and then you're going

to highlight your known

x's that's the x hit

enter now 5.2 should agree with that

and if i change this value here

okay notice that this number changes

but this number does not change that's

because you've you've altered

the the equation you've turned it into a

text box it's no longer y versus x

but it's rather c and a so this value

will change

okay but this the the equation on the

data table does not

okay so undo is our friend ctrl z

command z

and then the next thing is the

y-intercept so that's equals to

intercept parentheses known y's

once again comma known

x's okay

because these are equations i like to

highlight them

in yellow so that i know that they're an


maybe center them lastly these are

numbers so they have to have units the

slope is rise over run so this is

c z c z

versus ack and so now the unit for the y

intercept is c z notice that

it's trying to interpolate and think

what we want to type in

and maybe the same thing we just typed

in so you have to press delete

or press the space bar to get rid of


slash a k it's only c z

again the y-intercept is the is the

it's worth trend line here we'll

intercept the y-axis

and that number here is going to be

around maybe 25 or so we can see here

the statistical analysis tells us it's

negative 26.1

but what are those units 26.1 what's

oh it's got to be cz so that's on that

that's what the unit is here for the

y-intercept for the slope it's rise over


and so rise c z's over run

divided by a k c z divided by 8 k

so before we put this lab to bed we

really need to consider

the precision and the uncertainty

of our of our measurements so if we look

here we see that this has

we can report the slope to two sig figs

and we can

report the y-intercept to three sig figs

and that may seem

perfectly doable actu actually you know

the computer that's what it gives us so

why don't we believe it how precise we

can report our data

is really dependent on how precisely we

measured our data

already and so these are these are just

numbers i made up i actually typed in

just one two three four five six

obviously i didn't do any measuring

measurements but let's say i did let's

say i actually did and i i can only get


right to one sig fig this one has one

sig fig

this has three sig figs this one has one

this only has one

okay so our data is littered with one

significant figure throughout

if that's the case that that truly is

what we measured

okay and maybe it's not because we were

bad but maybe it was because the

instruments weren't great or the

conditions weren't great and so we're

limited to one sig fig

regardless we have one sig fig here um

and so therefore we only have one sig

fig here so we need to decrease the

decimal for example

i can only have one sig fig in the

y-intercept as well i could decrease

that decimal but that still leaves me

with 26 that's two

two sig figs and i need it to be one so

here we have to just simply

type in you know change that y-intercept

okay all right let's pretend

though that i actually measured it to

a much more you know measured it much


precisely maybe something like like this

how about how about

that yeah okay so

it's a 1.00 right and that's that's a

hundred times more precise

as just one so here sig figs when you

collect data

make a big difference and they really

matter in the laboratory

so here is three sig figs here's four

sig figs

here's three sig figs uh this is two

two sig figs so it looks like if we look

at the data

yeah it looks like this number right

there is our weakest link and so we're

limited to

two uh significant figures in our data

two sig figs in our data so what that

means is that we can live

with this the 5.2 is okay because that's

two sig figs but we can't live with this

we need to decrease the the y-intercept

down to that and so that's how we would

report this

data if we were to measure as precisely

as possible

i hate it but that's the way it works in

the in the big world of science

um and that's also why sig figs are

really important

all right i hope i hope you learned

something um this is what you're going

to do

for almost every single lab that we that

we do this year

so make sure you understand this use

this as a little tutorial

take notes on it if you want to but this

is what we're doing every time

to recap we're taking our data table

we're graphing something in our data


or graphing something along the y-axis

something along the x-axis

the chart title is y-axis versus

x-axis of some phenomenon we're going to

plot a trend line to that data we're

going to take

the trend line and add the equation of

that trend line to our graph

we're going to plot the r squared value

all right

and then in our analysis and we can kind

of clean up the analysis here a little


you know make it look nice

the analysis is also going to be done

almost every single time we're going to

we're going to record what the slope of

the graph is we're going to record

what the y-intercept of the graph is


with units with the correct number of

sig figs

the slope and the y-intercept you don't

realize it now but the slope of the

intercept you're going to tell you a lot

of things about the physical world

and that's why we're always going to

report the slopes and the y-intercepts

of our

almost always of our graphs okay with


if you have any questions about this

please contact me

come see me and um let me

let me know how you do i hope i hope

this was this was fun for you

i really uh enjoy this i think it's i

think it's great

you know it's kind of it's a it's my

artistic release for us

as a scientist all right y'all take care


see you bye