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.

The floating columns                  : Representing the positive (income/ gain) and negative (Loss/spent)

Spacer                                              : Spacer are usually the beginning and ending. Sometimes, the spacer representing the subtotals.

Waterfall chart allow user to quickly have the overview of the gain and loss of their company/department over a certain time of period, monitoring the flow of cash of every item.

When waterfall chart should be used for visualisation? Below are some idea…

  1. Evaluating Profit from the beginning to the end of time
  2. Show the changes of budget over a period of time
  3. Comparing sales and loss

II. Treemap Chart

Treemap is a chart where it help us visualize all items is being categorize compare to others. It provides an overview of patterns showing which item is more important than the others in a hierarchy view.

Take a tour with me and see why Treemap chart is useful.

Example a sales manager would like to have a glance of which type of movie is ticket is being purchase in the month of January. To do that, a simple pie chart can easily show the manger which type of ticket generate the highest sales.

What if the manager then would like to see the overall sales by different types of ticket over the weekend and weekdays?

A pie chart will have difficult in showing 2 different series of data. Your chart will ended up looking as what shows below:

Alright, let’s play a little game here, put your timer at 10s, and as the timer runs, have a look at this chart. When the time is up and if you still don’t understand what story the chart is trying to tell, this chart is a fail chart.
“You should not take more than 10s and stare at a chart and have no idea what story it is trying to tell” – Chris Dutton, Best Seller Excel e-learning instructor

What about we use treemap to visualize? Take a look at the chart below and again time yourself 10s.

We can immediately see that the Family Hall ticket is more popular over the weekend compare to weekday while Normal Hall Ticket is more popular over the weekday. The larger the piece in a category, the higher the value/the more important is it. For those item which has lesser value or priority will being squeeze into the right bottom corner and the value/item can barely be seen. If you want to take good look of the category, mouse over the area, a tooltip will appear and shows you the item and value.

Below shows more example of an overview of how the company utilize/spent comparing year 2016 & 2017.

The treemap chart above shows in the year of 2016, majority of the spending spent of employee learning and development, 51% of the overall spending goes into training while the year 0f 2017 the training only took up 16% of the overall spending.

Here are some more idea about when treemap is being used:

  1. Sales by category and genre
  2. Clicks of advert from facebook over twitter
  3. Spending by area and category
  4. Traffic of customer compare to brick and motar, mobile or desktop over the quarters
  5. Many more……..

III. Sunburst

If you have read the preview section of this article, we talked about treemap chart. Some of you may still not very much satisfied/impressed with what treemap can display. Once reason being when you have more than 2 layers of category, Treemap may not be the best chart for you to visualize your data. Instead, maybe you can give Sunburst chart a go. Sunburst chart allow user to have similar view like Treemap, but better. Similar data as the previous article, but this time the data is brokem down to whether the purchase is made through Walk-In or Mobile-App.

More example below shows when you may apply Sunburst Chart.

The sunburst chart above shows the travel trends of how Malaysian travel to outstation. We can see that majority of them will choose rail, especially Train over the rest.

Leave a Reply

Your email address will not be published. Required fields are marked *