Data Visualization with Excel 2016 (Part 3)

Sparklines

Sparkline is not a new feature in Excel. In fact, Sparkline was introduced to Excel since version 2010, AKA tiny chart. It is helps to visualize and highlight the trend of data over a certain period without having all the clutter in it. I love how tiny it is and it simply does one job, showing trends. This tiny chart is so tiny it does not take up a lot of space. It simply fit comfortably within a cell, just a cell is enough for it. Below shows an example of sparklines.


“Sparklines enable us to create visual representations of data with one click. It makes it easier to grasp data, and it’s helping our CFO analyze trends on the spot.” Matt Stuckey, IT Director of Levick Strategic Communications.


Sparklines are located at the last column of the table.

Continue reading “Data Visualization with Excel 2016 (Part 3)”

Data Visualization with Excel 2016 New Chart (Part 2)

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)

Data Visualization with Excel 2016 New Chart (Part 1)

If you have been keeping up with the new features with Excel 2016, you should realize that a lot of the traditional chart especially 3D chart has been secretly removed (in fact it is being discontinued) from your Insert Chart command. Instead Microsoft has good news for you, new charts. They are definitely better and easier. I remember back then, I struggle hours just to create waterfall chart, and not just that, to create it, maybe I’m fine. But, to maintain it …. It’s a “ oh no “ situation !

3D charts that has been discontinued from the Insert Chart command.

The visual below is the screenshot capture using Excel 2007.

Let’s begin to get to know the new awesome charts.

I. Waterfall chart

So, finally Microsoft has heard our prayer. They came out with Waterfall chart option and now finally everybody can insert waterfall chart at ease.

Waterfall chart is a great way to visualize your monthly/quarterly/annually cash flow. Below shows an example of how waterfall chart looks like if you have not seen one. The look very similar as column chart, the only different is the middle column are mostly floats. Continue reading “Data Visualization with Excel 2016 New Chart (Part 1)”

Microsoft Office – Buffet of limitless knowledge

Microsoft Office-Unlimited

Unlimited Learning, Unlimited Growth

New year is about a new beginning that you would like to make a big change or a step towards something that you have always wanted to achieve but yet to achieve.

Make this unlimited learning as your new year resolution, whether you are looking at building an impactful powerpoint presentation or turn a boring looking excel report into an extraordinary graphs and charts, we have all the relevant suite for you at one go.

Check out our promotion for selected Microsoft Office Application. Attend all you can, that’s why we call it ‘Knowledge Buffet’ at only RM1,250 per person only.

Reach out to us at techcenter[at]info-trek.com should you need further information on the courses.