Save content
Have you found this content useful? Use the button above to save it to your profile.
Man using computer in a coffee shop.
iStock_Peter Charnaev_user

Excel 2016: Explore the new chart types

by
18th Mar 2016
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst investigates the three new Excel 2016 charts: Histograms, Pareto charts and Box and Whisker charts, which are all in the Statistical Charts section.

This is the fifth part of our series looking at the changes and new features in Excel 2016. In the first part we looked at an overview of the changes and covered those that related to the Office interface. Then in Part 2 we used the tools in the new Get & Transform group of the Data ribbon to construct a list, based on merging two other lists, that would automatically reflect changes when refreshed. Part 3 looked at the Forecast Sheet feature and Part 4 covered the first three (and easiest) of the new chart types: Treemap, Sunburst and the long-awaited Waterfall chart.

This time we will investigate the remaining three new charts which are all in the Statistical Charts section: Histograms, Pareto charts and Box and Whisker charts. Also, in dramatic late news, we will cover yet another new Chart Type. A January Excel 2016 update, currently only available to Office 365 users, includes the Funnel chart.

Histogram and Pareto

Histograms show the distribution of individual values in a data set. The chart can organise the data by a category within the data or by allocating the values themselves to 'bins'. In this example, we have a list of products showing individual invoice line totals. Because of the structure of the data, if we choose Insert ribbon tab, Charts group, Recommended Charts, Excel will show a Histogram and a Pareto chart in the Recommended Charts tab:

(click to expand)

By default, the Histogram is organised into bins of equal 'width', starting with the lowest invoice line value and ending with the group that includes the highest value. In this example the Format Axis pane shows the Automatic option creates a Bin width of 260 and a Number of bins of 61:

(click to expand)

We can set a threshold for items to be allocated to an Overflow bin and an Underflow bin. In our case there are a relatively small number of items in the bins from about 3,000 upwards. If we turn on Overflow bin Excel will propose a value of 3,495. We can set the Overflow and Underflow values manually as well as choosing our own Bin width or Number of bins. In this case, we have set the Bin width to be 250, the Overflow threshold to 3,000 and the Underflow threshold to 0, causing the number of bins to be automatically set to 13:

(click to expand)

Incidentally, we don't need to use a chart to allocate data to bins in this way, the Excel FREQUENCY() function allows a set of bin upper limits to be entered and then the number of items in a data set to be calculated for each bin. FREQUENCY() needs to be entered as an array formula using Control+Shift+Enter. Here we have set up bins for each 500 to analyse the same set of Product data:

(click to expand)

Returning to our chart, so far we have only been using the ExtendedPrice field to calculated the frequencies of the values. We could also use our ProductName field to show us the distribution by Category. To do this we just need to turn on the By Category option in our Bins Options within the Axis Options section of the Format Axis task pane. The Histogram will then sum (rather than count) the ExtendedPrice values for each different ProductName:

(click to expand)

This is getting close to the Pareto version of a Histogram, just requiring our categories to be sorted in descending order by sum of values and a cumulative value line to be added. If we select out chart and choose Chart Tools ribbon tab, Change Chart Type we can choose the Pareto chart option from the Recommended Charts tab. This sorts our chart and adds our cumulative line, showing the number of products that contribute different overall percentages of our sales total:

(click to expand)

Box and Whisker

The Box and Whisker chart shows the distribution of values by quartile and can also highlight mean values and outlier values. The boxes show the number of values in the first to third quartiles, while the line in the box shows the median and the crosses show the mean.

The whiskers are used to show the variability outside of the quartiles shown in the boxes, with outliers being values beyond even those limits. Like the Histogram charts, Box and Whisker is included in the Statistical Charts dropdown in the Charts group of the Insert ribbon tab. Here is our same data set shown using the Box and Whisker chart type:

(Click to expand)

It's a bit easier to see what's going on if we just select a few products. Here we have filtered our Table to just show the first three products. We have turned off the display of Outliers, turned on Data Labels and used the QUARTILE.EXC() function to calculate the Quartile positions for our first product to show how the Box element of the chart shows the position of the first to third quartiles. Where there are no Outliers, the upper whisker shows the maximum value and the lower whisker the minimum value. Outliers are defined as values that fall beyond the 1.5 times the range of the box, so in the case of Alice Mutton a 'high' value Outlier would be greater than 1147+((1147-371)*1.5)= 2311.

The crosses show the Mean value in each case.

(Click to expand)

The Format Data Series pane, Series Options section allows you to choose which of the various Box and Whisker elements you wish to display. Here we have turned Outliers back on and chosen to include a line connect the mean values:

(Click to expand)

Stop Press – January Update introduces Funnel chart

Funnel charts are often used to show the values remaining at each stage of a process. For example, a sales process going from the number of initial leads through to completed sales, or students attaining progressive levels of proficiency. If you have been able to install the January 2016 update, you will find the new chart type in the Insert Waterfall or Stock Chart dropdown of the Charts group of the Insert ribbon tab:

(Click to expand)

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.