Box plots are a useful way of visualising the statistical distribution of a particular variable. They can be especially useful if there are multiple variables to compare against each other.
One feature that QuickChart offers that some other charting add-ins don’t is the facility to have the boxes cross the chart axis. You also have the choice about whether to include outliers within the “whiskers” of the plot, or whether to view them separately.
Outliers (defined as more than 1.5 times the Inter-Quartile Range from the “box”) are shown as green circles, while extreme outliers (more than 3 times the IQR from the box) are shown as red triangles.
If a column is added to the left of the data with labels for each point, then the outliers can also be labelled. There is also the option of plotting the mean of the series, with or without the outliers included.
To create a boxplot, just organise the data with different series in different columns, then select the whole table and simply press the Boxplot button on the Ribbon.
The single-click boxplot is not “live”, however, because the actual data being charted is a set of summary statistics and not the raw points themselves. This means that it won’t update with changing data. However, to achieve this, you can use the “=QC.BOXPLOT(…)” formula.
This formula only has a single input parameter, which is the same data table as before (including optional title row). The output is a table with all of the constituent parts of the boxplot. Unfortunately, the outliers cannot be broken out in this formula because the number of outliers varies with changing input data. The height of the output array will be 11 rows while the width will be one greater than the number of separate series.
Like most of the other QuickChart formulae, this is an array formula, so you must use “Ctrl-Shift-Enter” when you complete it, and select the full range required (although the most recent versions of Excel provide much better support for array formulae). More details on array formulae here.
Once you’ve entered the formula, just select a cell in the formula range and press the “Box Plot” button on the ribbon (as you do to make the single-click version) or right-click and the “Make Box Plot” option should be at the top of the context menu.
The settings for varying the appearance of the boxplot are accessible through the “Settings” button in the dropdown Boxplot menu on the Excel Ribbon.
- Changing between horizontal (shown below) and vertical orientations of the plots
- Plotting the distribution mean as a separate series
- Plot the distribution mean if outliers were to be excluded as a separate series
- Show the outliers (one-click boxplots only)
- Label the outliers (one-click boxplots only)
- Showing or hiding the chart legend (usually unnecessary on boxplots but might be useful if mean values are plotted)