Why and how to use recommended charts in Excel

There are two types of charts. First type is a chart which you create to interpret data more effectively. The second type is more common. These charts are blindly created billions of times everyday – why? Because boss wants it that way!

In this article, I am going to attempt to change the mindset of all users of Charts. Please spend 5 minutes of your time reading this article and trying out a new thought process. I am confident that it will add value to your life.

Excel Recommended Charts

Photo credit: Elsie esq. / Foter / CC BY and salendron / Foter / CC BY-NC-SA

The Problem

The problem is that there is no problem. Everyone is happily producing reports and charts the way they have been asked to. If you try to find out who decided that the format of the charts should be like this, most people don’t even know the answer. Some people say – boss wants it that way. If you ask the boss (which nobody does, but I DO), she says my boss wants it that way.

Another variation is that when I took over this role, this is how the previous person used to look at reports. I was new in the role so I continued using the old formats without modification.

Sometimes, long back some consultant had suggested the chart / report formats and those just continue to trickle down across generations of people.

In short, in most cases, there is no specific, well thought out logic behind why particular data should be shown as a specific type of chart.

Fine. Now we know the problem. So let us solve it.

Wrong Solutions are very easy to implement

This is how we get distracted by the hype of technology. Many people will take one or more of following actions when faced with the above problem:

  1. Stop using Charts pasted in PowerPoint
  2. Send the MIS team for advanced Excel training
  3. Hire a Big Data Consultant or Data Scientist
  4. Buy new, latest, shiny BI tool (and recreate existing reports at additional cost!)
  5. Arrange an off-site for Top Management with a Data Coach
  6. Engage with an external Data Discovery partner
  7. Hire a management consulting firm to highlight global best practices in data visualization followed by revamp of existing reporting processes

Enough I think. None of these are addressing the real problem. What is the real problem?

Because we have NEVER looked for the most appropriate representation of available data, we have not found it yet. That’s All. No need to panic.

The solution is obvious – let us look for it.

Dealing with long-standing habits and bias

Bosses (SLT, Top Management, CXOs or whatever else you call them) and subordinates are used to their routine. They don’t want any discontinuity in it. Any attempt to change things is met with resistance. We all know this.

So the idea is NOT to stop doing what we have been doing – nor to revamp it overnight. We must continue to generate those older reports and charts exactly as we were doing before.

The only change is – in addition to creating standard stuff – try to find out is there a better way of visualizing and interpreting the information.

Good idea. Of course this idea can be applied to all kinds of analytics. But in this article, I am going to restrict it to Chart based visualization only.

The Approach

There are many types of chart. We have to find out which chart is suitable for the data I have in hand. So try to apply each chart and see for yourself. Initially, there will be a lot of trial and error. But over time you will learn.

Yes. Conceptually, this approach is good. But it is time consuming. But before we explore the solution to this problem, let us also notice a bigger problem.

The Hidden Danger

There is another danger lurking behind this exercise. It is not an obvious danger – but almost everyone has already fallen pray to it.

The danger is – we tend to hard-code solutions where there is no hard-coding required. This needs more explanation.

Let us assume that you know all the chart types.
Now the only question left is : Which chart type will be suitable for which data?

Think a little and try to form an answer in your mind. I am keeping some of this page empty so that you don’t see my answer immediately. Scroll down few times to continue reading the article….












































































Well – what is your answer? Is it something like “… depends upon the type of data?”

If yes, your answer is WRONG. Why is it wrong? Because the same data may have multiple of charts equally applicable. There is absolutely no need to have ONLY ONE CHART for one piece of data.

This has a deeper meaning than what you are currently thinking about. We are  not just applying different chart types to the same data just because it is technically available. We are doing it because each type of chart provides DIFFERENT and ADDITIONAL useful information. If two charts show the same information, they can be eliminated – only one of them is enough. But in most cases, each type of chart arranges the data differently. That helps our brain look at it from different points of view and decipher things which are otherwise not so obvious.

Therefore, we must consciously learn to think like this:
I will use ALL APPPROPRIATE charts with each piece of data I get so that I can learn all possible useful information about it.

How to do it in practice

This means trying many different charts repeatedly, one after another, to the same type of data. Many of them may be irrelevant – for example for data with more than one series, Pie chart is useless. For data which more than two scales, no chart will represent the data satisfactorily (it requires SparkLines or separate charts).

Fortunately, Microsoft has already thought of this – in Office 2013 version. It introduced a brilliant feature called…

Recommended Charts

You just select the data and click on Insert – Recommended Charts. Excel will analyze the kind of data you have and decide which charts will depict it in an effective manner. As we have discussed earlier, there are usually more than one charts which fit the bill.


So Excel shows all those charts to you. What should you do now? NO. You SHOULD NOT select one of those charts and discard others. You should look at the data by applying Each of the Recommended Charts and interpret it. Finally, for presentation purpose you can choose the most appropriate chart. But while analyzing it yourself – don’t shortlist one type.


So much for the theory. Now let us put it in practice. Here are recommended charts for 1, 2 and 3 columns of data…

image image image

Click on each chart to see a bigger version of it. This is a great feature. But very rarely have I seen people use it. Most of them don’t notice it at all and even if they do, they are forced to utilize the chart formats as per organizational norms!

Recommended Approach

  • Create charts which your boss wants and present / deliver them as usual
  • Spend more time on the data and look at more chart types and interpret the data yourself
  • Think about the business context and find useful information
  • Document that useful information along with each chart
  • Next time you are sending / presenting to your boss – send the standard chart as well as your additional findings
  • Soon they will ask you – how did you find out this additional information
  • That is the time you demonstrate the benefits of the approach we have discussed here – with specific data and charts
  • Once boss is aware of the benefits of this approach, everyone can start using it and interpret data more effectively

More in-depth coverage of Recommended Charts.

There is another feature called Quick Analysis which helps you visualize data quickly. Read these articles for details:
Quick Analysis in Excel – Have you even noticed it?
Quick Analysis should NOT be done quickly!

Best of luck!

Queries | Comments | Suggestions | Wish list