Hi, Jason Morrell here.
Box and Whisker charts were a brand-new chart type that came in Excel 2016.
Prior to that you could create them but you had to create a stacked column chart and then
make your changes from there.
These days there's a dedicated chart type for
Box and Whisker.
Now statisticians love these.
They'll jump out of bed in the morning to make these because they're very
good for statistical analysis.
Here's how they're done.
So here we have some rainfall data month by month for the
years 2000 to 2017.
Over here we have an example of a Box and Whisker chart that's been generated for that
data, at the top showing the legend for each those years and then down here these are what
Box and Whisker charts look like.
So the X in the middle of the bar shows the median value for what we're looking at so
in this case it's the average monthly data.
The little line at the top indicates the maximum data value.
The line at the bottom indicates the minimum data value.
The beginning of the box here and the top of the box here represents the first and third
quartile in your data range.
So statisticians often use this technique to show the regular range of data and any
outliers that may be in the data.
I'm going to show you how to produce this Box and Whisker chart from the original data.
So let's go back to here.
Now what you need to do is to write some formulas for your data that works
out your minimum and maximum values, your median value and the value of the first and
the third quartile.
So down here I'm going to add some extra rows to calculate those quickly.
Firstly, type a label that says MIN, Quartile 1, Mediam, Quartile 3 and MAX
It doesn't really matter what they're called because you don't see those labels on the
chart itself.
In here I'm going to use a function called QUARTILE.INC.
This formula - you specify a range of cells to look at, that you're going to analyse.
Then you specify a value between 0 and 4, that's five values 0, 1, 2, 3 and 4.
Zero represents MIN.
One represents the first quartile.
Two represents MEDIAN.
Three represents the third quartile and ...
Four represents the maximum value of your data range.
Yes, you could use MIN and MAX and the MEDIAN functions if you want to but just for simplicity
we may as well use the same function all the way through, to generate the values for us.
In this cell, I'm going to write the first formula using QUARTILE.INC.
Open the brackets ... I'll continue up here so you can see what I'm doing.
First of all I select the range that I want to use.
Now, because I want to copy this formula across the table in just a minute, I'm going to fix
the row numbers by pressing F4 twice but not fix the column letters
because that needs to change as I go across the table.
Comma ...
Then the Quart is that value zero one, two, three or four
that I mentioned before.
Now rather than typing in zero and then having to change it
to one, two, three and four for subsequent formulas I'm going to use the ROW function
and just pick a random cell on this row, let's say the one in column A, close the bracket
- that's going to return 16 - I'm then going to subtract 16
from the result to give me the zero, and zero as you can see represents the minimum value
that's required for this quartile function but as I copy this formula down
to rows 17, 18, 19 and 20 in just a minute, that's going to calculate the values 1, 2,
3 and 4 in those subsequent cells.
So let's close the bracket for the quartile function and press Enter.
That's the minimum value for this range of data.
If I then autofill this down, not forgetting to Fill Without Formatting, and then autofill
the whole block across, and again, don't forget to Fill Without Formatting.
And what you get on each of these rows -
The MINIMUM value shows the smallest value in each data range for each column ...
The MAX shows the biggest
The MEDIAN shows the median value and so on.
This is the data we're going to use for our Box and Whisker chart.
Now, the range here is what I need for the chart data but I also need to select the year
numbers across the top so that it's got some labels in the chart.
I then choose INSERT, go to my charts gallery here.
The Box and Whisker chart doesn't have its' own icon here but if you go to RECOMMENDED
CHARTS and then the ALL CHARTS tab you've got full access to all the chart types down
here.
If you come down towards the bottom of the list you'll find Box and Whisker, so let's
choose that and click OK.
Here's the chart you start with.
Here's all your Box and Whisker data here.
What I'm going to just to make things easy to explain
and see is to move the chart to its own sheet.
So, Chart Tools, Design tab, click on MOVE CHART and put it on its own sheet.
Now initially, everything's bunched up in the middle.
The first thing I'd like to do is to spread those out.
If you double-click anywhere on the chart you'll get a side
panel and on the side pane you get all the options to manipulate different bits of your
chart.
I'm going to change these PLOT AREA OPTIONS to any of these series.
Any change you make here affects them all.
Under SERIES and then SERIES OPTIONS I'm going to change that Gap Width from 75% down to
something really low like 5%.
That way it spreads everything out full width on your chart.
That's normally what you want.
The CHART TITLE obviously needs to describe what the chart itself is doing, so just click
in there and make your changes there and make sure you describe what your chart is.
The SCALE currently goes from zero to 300.
If you notice down the bottom end, everything kind of starts at about 35 to 40 and 250 here
is the maximum value.
So what I'm going to do is to go to the options over here and go right down to the
bottom to where it says VERTICAL VALUE AXIS, and in here if I choose the
AXIS OPTIONS and open that up you'll see the MINIMUM and MAXIMUM values.
I'm going to change the minimum to 25 the maximum to 275 and that way you kind of make
more use of the vertical space available in your chart.
Now looking at the chart itself these 'whiskers' (as
they're called) kind of stick out of the main block.
These show the outliers, the maximum and minimum values and it helps you to spot things like
over here, where we've got a value that's kind of way out of normal range it helps you
to spot those very quickly.
The one last thing I'd probably put here, because there's nothing to indicate which
year is which, and that's just a simple case of turning the Legend on.
So we go to the Chart Element options here, switch the Legend on here, and now you've
got a color code representing your bars down here.
You could also do it with Data Labels but because there's
five values per bar, if you want to include those and which year it is, it gets really
busy down here, and that's why it's just best to keep the main chart
simple.
Have a legend at the top indicating which color refers to which bar.
So just to recap on some of the main points here.
You need your data handy.
You need to include or write some formulas to generate this extra data calculating the
minimum and maximum values for your data and your medium value and the first quartile and
the third quartile values.
Don't forget you can use the QUARTILE.INC formula.
Specify your range and specify the values 0, 1, 2, 3 or 4.
Each of those numbers represents one of these options down the bottom.
That means you can use the same formula all the way
through to generate your values.
Let me close this down.
You then need to make sure that you select your years in this case, and these will appear
as legend items on your chart.
And also select your data which forms the main basis for your chart.
Then you go to the INSERT tab, go to RECOMMENDED CHARTS and ALL CHARTS and choose your Box
and Whisker from here.
From there you just set your chart options as we discussed before.
Go get 'em!
No comments:
Post a Comment