Box&Whisker

Box & Whisker charts are used for a statistical analysis of the data set. They come along with Excel 2016, and serve to show the statistical distribution of data according to quartiles, emphasizing the mean, maximum and minimum values. In business, they can be used, for example, to analyze the turnover of an item byclass or sales group. Then we analyze the value distribution, based on which we can make conclusions about future values.

First, let’s remind of some important statistical concepts. Arithmetic mean is the average value of a given data set. Median is a value that statistically divides the set into two equal parts, and quartiles are values that divide it into quarters. The first quartile shares a set of 25:75, and the other 75:25. Maximum and minimum are the highest and lowest value in the set.

The Box & Whisker chart literally consists of “box” and “whiskers”. “Box” is a rectangle that begins with the first quartile, and ends with third quartile and represents all the values between. “Whiskers” are lines with boundaries stretching from the minimum to the first quartile or from the third quartile to the maximum. This chart should show us how much the set values are moving to. For future values it is certain that they will be “in the box”, and it should suppress or shift expectations to the “mustache” border.

To show you how to make a Box & Whisker chart I made a table that tracks the turnover of articles in one year according to quarters and classification. First, select second and third column (ARTICLE CLASSIFICATION, TRAFFIC), then in the Insert toolbar, find the Recommended Charts option. After its launch, the dialog window will open, where we first need to click on the All Charts tab. In the list of chart types we will find the option Box & Whisker. Finally, we confirm the selection of the chart by clicking OK.

In the worksheet, a Box & Whisker chart will be displayed in which we can see the distribution of traffic values according to product classifications. For each classification, a “box” is displayed, which shows us in which bounds lies turnover values, as well as the minimum and maximum of turnover. By right clicking on a series, and choosing Format Data Series, on the right side of the window, a panel will open where we can change settings which statistical values will be displayed in the graph.