Categorical scatter plot in Excel

hello everybody in this video tutorial

i want to discuss with you how we can

generate

what is called a categorical scatter

plot in excel

and i discovered that this is

surprisingly challenging

apparently the good people from

microsoft have no thought about

something like that so what am i talking

about

right so let's say we do an

experiment and we collect some data it's

a very small data set

so for example we want to see

if some how seedlings are

growing so we measure the growth of

seedlings

we measure that in centimeters so for

example we let them grow for

a day or two or something like that and

then measure

how much have they grown and we've got

three kind of

parameters for that so we have the

control that would be the untreated

seedlings

we have treatment number one that might

be with some

nutrients and treatment number two with

another set of nutrients and we

want to plot these data so that we can

immediately see

what's going on and ideally we also

would like to

show our average uh growth

and also maybe the standard deviation

so what do we want our plot to look like

so we would probably want to have

something like that

here on the x-axis that's our

independent variable we want the

categories so we want the control

we want treatment number one

and we want treatment number two

and on the y-axis that's the dependent

variable

that would be the growth and

here we've got that in

centimeters let's do it like that

and uh because we've got only a very

small set

of data we want to

show each data point so we would

probably we would like to have something

like

this where we have three data points for

the control

like we've got here three data points

and

for treatment one we might have

something like that

uh i'm not looking at the data at the

moment

we also might want to have say

the mean of the data

indicated so for example if here is the

mean

of the data we want to indicate it like

that

and we might want to have some standard

deviation some error bars with standard

deviation

so that is what we would like to have

our our plot at the end

what it should look like so i'll

remove that to

get some space here so

but we we keep that in mind that this

is what we actually want to have

so how are we going to deal with it

now the first thing that we do

is we calculate

we calculate the average

so i need to go in here

and select okay

so here we calculate the average for our

treatments

so that would be the mean and in excel

we can easily calculate that that would

be

equals average and i start typing so it

comes up with a suggestion i

left mouse double click on it

and i just simply highlight the numbers

that i want this to

continue in in the bracket to calculate

the mean so that would be

from b3 to b5 here that looks good

close the bracket and here this is the

mean

and for these data now what i can do

is i can calculate the mean for

treatment 1 and treatment 2

and because i'm lazy i just

go down here and you see this

black cross here in the bottom corner

left mouse button clicked and i just

simply drag it

over and it will

do the calculation of the average

for the other treatments and what we see

is that

actually the mean

for the growth in the control seems to

be

the the largest whereas the treatment

one

has the lowest growth and treatment too

well is sort of in between so it looks

like that

the seedlings do not like treatment 1 or

treatment

2. so this is now

our data set that we can use

and now we need to figure out how we can

actually plot this and as i said this

is surprisingly challenging

because excel doesn't really have

a good option for dealing with

something like that so what can we do

we need to prepare our data a little bit

and

it will look a little bit odd so what i

do in the first place

is i highlight the data

for treatment 1

and pull them

down move them

so that it looks like something like

that

so you notice the

treatment one here for the controls

that is now empty and we've got

treatment

1 here and we do exactly the same thing

for treatment 2 and

do it like that now important

just to keep track of our last data

point

these were the mean so here we had the

mean

for control here we have the mean for

uh treatment one and here we have the

mean

for treatment

two we can also calculate standard

deviations

and i do that down here so the

standard deviation and i just simply

abbreviate it like that

so we can calculate the sample standard

deviation

that's again that's very easy we just

start typing and we are looking for the

sample standard deviation which is

abbreviated with these

standard dev s so

we highlight that because this then will

give

our error bar at the end so that's our

standard deviation

for the control here we do it for

the s the standard deviation

for our treatment one

and we do the standard deviation

for treatment two and now we are pretty

much

set up with whatever we need

and we see the standard deviation for

control 1

for control and treatment 1 are pretty

similar

whereas the treatment 2 the standard

deviation is quite large

okay now let's plot so what

are we going to do we highlight

control we highlight our data

like this with the left mouse button

clicked

and then we move to

a line plot now this is probably the

only

situation where a line plot is really

useful

so let's click on that and we go

to a line plot where we have the data

like this so we want to have it

in this way so this is now

our plot let's not worry about the chart

title

and and things like that because to be

perfectly honest it

doesn't really look good

we are not interested in these

weird things so what we need to do now

is

we need to switch row and column

so that's up here and let's switch

row and column and here we get

our data points control treatment

and treatment too we don't really

need the legend here so we can

remove that and now we've got

our data points here and they are nicely

stacked up

and if we look carefully we see that

we've got

four data points so these these are the

three data points plus the average that

we have

and of course we want to highlight the

average a little bit more so what we can

do

is we can try to figure out which one is

the

is the uh is the mean so

here it tells us even if we hover

over it so this is series point control

4 with a value of 0.64

and that actually was our our mean

so what we can do is we click on it and

it shows us that we have the mean

and what we do is if we double click on

it

we will open another window here and now

we can format this data point

so what do we do with it so we

go to this bucket here and to format

this data point

we go to marker that's this one here

and we look at the marker options

now i usually like to have a slightly

different

shape for the mean so we go to build in

and i would like to have let's say

this one here and

i would like it to be

black

i don't want a fill but i want a solid

line

so this gives us our first

average how does it look like okay

that's the average

the other three are the data points and

now we can actually also

include error bars into that

how do we do that or actually let's do

the averages for the other data points

as well

so here our treatment 1

has a mean of 0.22

let's see

i think that's this one here click on it

yes that's the right one

now i want to do the same thing here

i want to change the marker

marker option built in

i want this one here i

want no fill but a

solid line okay i've got this

and for treatment number

two i go for this one here

yep we've got the right one

marker options again the

built-in option i want the

star again

that's this one i don't

want a fill but i want a

solid line so now i have

labeled all my averages and it might be

a good idea to

note that here then in the legend and

now how we

how can we add error bars

that's also uh pretty straightforward

so what we can do is we can double click

on the average this one here

and let's remove that and we get these

little sidebars here so click on the

plus

and we go to error bars we check this

box

and go on the arrow

and here under more options

we choose custom

we get a large error by here but that

indicates that

we've got something and here we want to

specify the value that's this

the value that we've just calculated

with the standard deviation

so here we just put in

this value and

here we also put in for the lower one

this value okay

so we've got our standard deviation

let's quickly do that

for the other ones as well

so we go to the

error bars here for this one

more option we customize it

specify the value and here we've got the

standard deviation

here and the standard deviation

also for the lower one

okay oops we

apparently made a mistake yes here's the

mistake

okay that's the error bar here

and last but not least let's do the same

thing

for treatment two

and error bars

custom options again we want to

specify this one

and also this one here

and we need to be careful not to make

the same mistake

again that we've done before

and this should now

look pretty decent so that's

what we needed to do of course we want

to give it a chart title

that is growth of seedlings

growth of seedlings

and we probably need to also

give it a title here on

the y-axis so again we click on the axis

we say axis title and in this case

we want to give that as

growth

and we have this in centimeters

okay so we don't need this axis title

down here so this is how we can actually

prepare uh relatively

decent categorical

scatter plot and then we have these

uh data here where we use the different

categories and of course you can have

more than just these three categories

you can have as many categories as you

like

but then it gets a little bit wild you

can also have larger

data sets but then again it gets a

little bit

wild so i think you should not have more

than five

six seven uh data here but

at least we can see how our data

actually

look like so i hope this makes sense and

thank you very much for watching