Check out the last article if you have missed out the Data Visualization with Excel 2016 New Chart Part 1. In this article, we’re going to explore the other new charts that is being introduced in Excel 2016.
We are going to talk about histogram, pareto and box & whiskers.
Let’s start with the first new chart Histogram.
I. Histogram Chart
If you take a good look at histogram, it does look similar with column chart. (below shows and example the comparison side by side of a histogram and column chart). Histogram being used to visualize the frequency of distribution of a set of data while column chart is use to visualize data base on categorical variable. Besides, you should also realize the way histogram is plotted without gaps in between the columns while column chart has gaps.
The horizontal axis which is also known as the x-axis is being label by a series of interval and then count how many of the values within the dataset fall into the intervals. It is commonly used in statistics. The horizontal axis (aka x-axis) representing the “bin” (amount of column) sized of the dataset. The “bin” size [number 1, number2] is the group of salary from the dataset. It shows that among 48 people, there is 12 people falls under the group of salary range from 1000-2900 (according to the figure above). The “bin” size can be adjusted through the horizontal axis panel. The higher “bin” size, the smaller the range gets.
The figure below shows the bin size is adjusted to 10, and the range shows [1000,1690].
With the same dataset, the bin size is adjusted to 5, the range shows [1000,2380].
If you want to have more controls over the “bin” size which is the range, the horizontal axis panel does provide the flexibility to change it. With the same dataset, I have change the “bin” size to 6, underflow (any number below this threshold, will being placed in this bin); overflow (any number above this threshold will being placed in this bin).
Bin size: 5; Overflow:6500; Underflow:2000
I. Pareto Chart
Pareto chart is a great chart to use to highlight the problems or issues face in an organization or department. It is named after Vilfredo Pareto, an Italian economist. With Pareto Principle, majority of the issues/problem is caused by small percentage of causes. He then came out with this ruling, 80/20. With this 80/20 principle, it says that 80% of effect comes from 20% of effort. If you manage to identify and solve 20% of the cause, you have practically solve 80% issues faced.
Examples of Pareto Principles:
80% of your sales revenue is actually coming from 20% of your customer base; 80% of complaints is causes by 20% of the factors and many other example that you could come out with. Just so you know that, this is just a principle, which may not necessarily as absolute as 80/20.
Below figure shows an example of the reason why an employee is being late for work.
Pareto chart are very similar with Histogram, the difference in between the both is Pareto chart’s horizontal axis is by categorical and being sorted from highest to lowest. Referring to the Pareto chart above showing the reason of why an employee is being late to work and the frequency is arrange from the highest to lowest. Using Pareto chart, we’ll be able to highlight the majority of the problem or reason of issue faced. If the first reason (Stuck in the traffic) out of the 5 reason is being solved, it will immediately reduce the amount of people being late to work by large percentage.
I. Box and Whiskers
Box and whiskers chart may seems a little intimidating, but it is one of the chart where it will quickly highlight the mean, media and percentile from dataset.
The example below illustrate what are mean, median and percentile.
A group of dataset below shows the price of Honda car from low end to high end.
1st Quartile shows the mean price of the 25% of the n items while 3rd Quartile shows the mean price of the 75% of the n items. On average, the price for Honda car is $175,291.25 according to the survey dataset. High point and low point illustrate the highest and lowest car price of the dataset while median shows the price being arrange in the middle, if there are 2 number is being arrange in the middle, then you will have to find out the mean of these 2 numbers.
Median: (136,000+145,000)/2 = 175,291.25
The graph above shows us that for Honda car, the price could vary from $76k to $315K overall. For the low end Honda car price could vary from $76K to $140K while the high end Honda car price vary from $140k to 315k. The average car price for low end (1st Quartile) is $85k while average price for high end (3rd Quartile) is $296k.
Related article: Data Visualization with Excel 2016 New Chart (Part 1)