How can two people with the same skills on excel, have completely different results?
Similarly in excel too, the situation can be very similar.
However on the contrary,even if you had lesser knowledge of millions of charts, you could still pull of better results.
A bar chart is drawn to represent numerical data under various categories across categories or time. There are three major types of bar chart,namely stacked bar,clustered bar or bar chart with line.
Allen Aravindan,CFA
Table of Contents
- The Secret Behind Visualisation
- Types of Charts & Their Uses
- Bar Chart
- Pie Chart
- Cone Chart
- Tornado Chart
- Best Practices
The Secret Behind Visualisation
So this is how it goes! The real work is not in excel but before using excel.
To summarise, the following questions need to be jotted down.
What Data Type
To illustrate this, let me ask you a simple question! Would you make a chart looking like pizza with 100 slices?
For example above the chart might not look, that bad! However think about the one using this chart, trying to make sense!
Truly horrific!
Specifically! He would be straining his eyes, trying to get hold of the specifics.
Types of Charts & Their Uses
So, here is the simplified framework for step one.
| Data | Data | Compare |
| Numerical | Histogram | Bar Chart |
| Categorical | Bar Chart | Bar Chart |
| Unstruct | Word Cloud | |
| Categories | Heat Map | Bar Chart |
| Time | Line | Line |
Alternatively decide without selecting the chart, on how you would want your audience to see your communication?
The Most Important Tip
If someone asked me, what’s the single most important tip?Above all its going to be, the story!
So, what’s the story you want to tell in your charts?
Moreoever, is the audiance educated in your subject matter or a layman?
Besides, how good is the chart, if it doesn’t to what its supposed to do easily?
Write your primary, secondary and tertiary communication phases
Story Framework
Firstly who is the end user, write down in brief about that user. May be its your boss or may it’s the customer.
Secondly,what does he need to know? What information do you intend to register with him?
Finally,how do you think its best communicated?
The Bar Chart
I call it the father of charts, for a very good reason. They are so common, have so many variations and yet can be very ugly if not done right.
Nonetheless, the bar chart should be used in case of categorical data sets, with the following sub-objectives;
- Displaying Relationship
For example: We want to show the relationsip between interest rates and housing sales.
- Time
For example: I want to show how sales has performed across quarters.
- Summarizing
May be I want to comment on the entire data set, using a distribution.
Hence, based on the above sub-objectives we can again look at some variants of bar chart.
Stacked Bar Chart
First, the stacked bar chart which as you can see I have used it to show the number of large,midium and small size companies in different industries.
In fact you could clearly identify that financial services industry, has the highest proportion of mid and small size companies.
Now imagine if I had made the same illustration in a pie chart.
You decide which is communicating faster and more precisely!
Clustered Bar Chart
The clusterd bar chart, is good to use if you have a time element to the data.
For instance, you would like to show how various industries have performed across three consecutive periods.
Above you can see, how I have shown the performance of three years and the communication is easy to understand.
In addition, some people might have this question;
” How to sort bar chart in ascending order in excel?”
Well there are two ways in which we can achieve that.
Firstly, we could click on the chart and select the table to sort it. Else we could click on the chart area and reverse the order.
Bar Chart with Line
I love this chart variation and there is a very strong reason for it.
Earlier, we talked about knowing the data type and using charts based on it.
In the same way, a bar chart could be used along with a line chart to show different types of data sets.
Above is a chart I made showing how a hedge fund performance across time. Moreover the chart also shows the value $1000 over the same time period.
So, how is the bar chart drawn this way?
Firstly select your data, which should consist of two different scales. Above in my example, the line chart is in currency and the bar chart is in percentage.
Secondly click on any of the line in the chart area and select change series chart type.
Also select one of the data type as bar chart and click on secondary axis.
Pie Chart
The thing about pie charts is that, it may seem like a no brainer and uncomplicated visuation. However there is a good chance, that we oversimplify its use.
So when should you use a pie chart? Firstly, when you have a pie or proportional data. Secondly,when the proportions are large enough to be visible.
Above you can notice, that the pie chart clearly communicates the major dominant players.
So, the takeaway is limited categories and a clear majority.
Cone Chart
This is nothing but a little show off to show that you know some detailing.
However beyond that, its nothing real magical in terms of its ability.
Above, I have shown start up funding requirements using a cone chart.
So how can you make this?
- Select the data to create chart
- Select 3D Coloumn chart in the bar chart category
- Once you see the 3D Type chart, right click on the bar and select format data series
- Select the type of cone you want to show.
However there is no necessity to only use the dimension like I have shown. You could alsouse 3D coloumn charts which is horizontally oriented
Tornado Chart
I am not quite sure, why this type of chart is called as a tornado chart but you be the judge of it.
However the important question is, where to use this chart?
Above you might have noticed that the chart shows the comparison between online store sales versus physical store.
So depending upon the business or function you are working on, you could use it for any comparitive data.
At mentor me careers, we teach this chart in valuation summaries, in the financial modeling course.
Again, a point to note! The chart used not because its fancy but because its easy to understand the values dervied from different methods.
So, how to make this chart?
- First arrange the data of two coloumns in the form of comparison
- After this, select the data and select stacked side bar chart
- Select the first variable in the blue color, right click on it and change series chart type. After which select secondary axis
- Now, select the axis and select format axis. Secondly change the minimum and maxmim bound depending upn the minimum and max value of your data set
- Now select the secondary axis and fill in the minimum and maximum bout and also select values in reverse order
- After that you want to select the primary axis and again change the minimum and maximum values
- Now select the lab axis show the names and right click on it. After that select lab position as low
- Delete primary axis, the scale that was below the chart
So, you have successfully conquered the tornado!
Best Practices
Now,I will summarize the best practices while dealing with charts.
- Never use charts when you could use tables. Sometimes simply a simple table can get the job done
- Never use too many colors and complicated charts. You don’t want the attention to be on the chart but the information
- Follow a color theme, which is sober and according to the industry you represent
- Always get feedback from colleagues on what they understand from your charts
I hope, you learnt something from this article. Please feel free to comment and send your questions.