Prepping Your Data
Your data will need to be ordered in a specific manner in order for the chart to work properly. Follow along and you should end up with a great looking box and whisker chart.
Your data will need to have five series to indicate the Median, 1st quartile, Min and Max values and 3rd quartile. Under Column A, add the following in each row: “Median", “Q1", “Min", “Max" and “Q3".
Next, obtain your data set. If all you have are raw numbers you can easily calculate the values for median, min\max and quartile values.
Median – To calculate the median, use the MEDIAN function and select the data you with to use. Hit enter to obtain the median value. Do this for each remaining set of data.
The Min and Max values can be easily found using the MIN and MAX functions.
The quartile can be found by using the QUARTILE.EXC or QUARTILE.INC functions. The ‘.exc’ and ‘.inc’ indicate whether or not to exclude or include the median value in the calculations. To use this formula, enter ‘=quartile.exc(DATARANGE, 1)’ for the 1st quartile and ‘=quartile.exc(DATARANGE, 3)’ for the 3rd quartile.