Hello and Welcome! In this tutorial, we’re going to attempt
to use Excel to construct a Boxplot. Excel is not primarily designed for constructing
Boxplots but we’re going to trick it to do so.
To construct a boxplot we need these five numbers: minimum, first quartile, second quartile,
third quartile, and the maximum. To obtain the minimum, click on this cell
here and type =MIN( Click on A and ENTER
For maximum, =MAX( Click on A and ENTER
For the first quartile, = (start typing quartile) Now I like the QUARTILE.EXC because I want
the first quartile to be the number that separates the bottom 25% from the top 75%. That is,
I want the bottom 25% to be less than the first quartile.
So I’ll click “EXCLUSIVE” Double click
Click on A, comma, Excel gives you an option, pick 1 (for first quartile)
Do the same thing for the second quartile =QUARTILE.EXC
And then click on A, comma 2 And then =QUARTILE.EXC
Click on A, comma 3 And then we have our numbers.
To trick Excel to constructing a boxplot, we need differences here
First we start with 31 (the smallest number there)
And then we do = (then we click on the 54.25 minus the first number 31)
And then you can just click and grab the handle and drag all the way down here.
No highlight these 5 numbers (the differences) and click on INSERT.
We want the horizontal bar chart so click here and then select the second one (the stacked
bar chart) Next click on DESIGN, click on Switch Row/Column.
First, click on the orange one (the second one here) and then click on the + button next
to the chart. Click on Error Bars
Click on options here and click on More Options Click on the Minus because we want it to go
to the left Click on custom and then Specify Value
Since we clicked Minus, get rid of whatever is in the negative error value cell
The number we want there is this 23.25 (the second number here). So you can either type
it in or click on that number. Click OK. We can see the line showing up here
for it. Next we want this yellow cell (we click on
the yellow cell). We want this last number here.
So click on the + button again, click on error bars, more options.
This time we click on Plus And then again, Custom, Specify Value
Change the positive value to the last number here (13.75) which you can either click or
type it in. Then click OK. Next we need to kind of get rid of the other
rectangles. So right click, Fill, No Fill
Right click on the second one, Fill, No Fill. Right click on the last one, Fill, No Fill.
And then if you don’t want the colours, you can click on the first cell, click on
FORMAT, you can choose this first option here. Click on the second one, click on FORMAT and
click that again. And that is your boxplot
Thanks for watching.