Choosing The Correct Chart Type – MS Excel Charting Tutorial Part 2

Excel Charts

In previous post, we learnt about different types of charts in excel. In this post, we will learn how to choose the correct chart type.

Choosing The Correct Chart Type

Different charts are to be created for different data types. Therefore it is very important to know the data type and the type of chart which should be used in each case.

First of all it is important to understand the message that we want to convey through our charts. To understand this concept better, let us consider a sample database of college students. To keep it simple we have used variants of the same example to explain different messages.

Message Type 1: Comparison

Let us suppose that we have a small student database which contains the names of students and the marks scored by them.

Student Database for selecting Excel ChartIf we wish to compare the performance of the students through a chart i.e. if our message is “Comparison” the correct chart types are:

Correct Chart Types for ComparisonIn our example these charts illustrate a comparative performance of the students. Column Chart, Line Chart and Pie Chart are ideal for comparing a set of values and making interpretations.

The chart which should not be used in this example and also generally for “Comparison” is Bubble Chart and Scatter Chart.

Incorrect chart type for comparison

We can’t use the bubble chart for “Comparison” because a bubble chart has area and scatter information. It does not depict the message clearly and it is difficult to make out the scores in this case and compare them. So, Bubble Chart and Scatter Chart are incorrect charts for comparison.

Message Type 2: Distribution Of Values

For distribution let us suppose that our college has organized a cultural fest. Suppose the registration period is 5 hours from 9:30 AM to 2:30 PM and the event manager is interested in understanding the distribution of students registered in this 5 hour period. The chart which we use, should help us in understanding the number of students which register each hour and which hour has the maximum no. of students (Peak hour). This will in turn help the event manager in capacity planning with respect to increasing the number of registration desks to enhance efficiency during the peak hour.

Student Registration data for distribution chartIf our message is “Distribution” the correct chart types are:

Correct Chart Types for DistributionColumn Chart, Line Chart and Scatter Diagram show the hourly distribution of students registered very well.

Pie Chart and Bubble Chart will be incorrect chart types for distribution.

Incorrect chart types for distributionAs we can make out these charts do not illustrate the distribution of students properly and the message here is difficult to understand.

Message Type 3: Parts Of The Total Value

To understand this message let us suppose that we have a database of the branches/streams in our college which contains two fields: The name of the branch and the no. of students in each branch.

Student database for part of total value chart messageNow, if we wish to depict through a chart which branch constitutes what specific percent of the total number of students in the college or if our message is “Parts of whole” the correct charts are:

Correct chart types for parts of total value messageColumn Chart and Bar Chart illustrate the “Parts of the total” message very well and we can clearly make out the share of each branch.

Bubble Chart, Scatter Chart and Line Chart however are the incorrect chart types for Parts of the total message.

Incorrect chart types for parts of total values messageMessage Type 4: Trend over Time

Let us suppose that we have a database of the placement records of students of each branch from 2005-2008.

Student database for trend over time messageIf we wish to show this information in the form of a chart it is important to represent to the time on the “x” axis as time is usually perceive from left to right. The correct charts are:

Correct chart types for trend over time messageColumn Chart and Line Chart help us understand the trends in placement of students from 2005-09. We can derive useful conclusions out of these charts concerning the trends in placement.

Bubble Chart, Scatter Chart and Pie Chart don’t capture information completely. Therefore they are not suitable for representing “Trend over time”

Incorrect char types for trend over time messageMessage Type 5: Deviation

To understand this let us suppose that students need to be registered for an event. We wish to graphically depict the wait time for each student or the time which is required for processing each registration and find out the “Deviation”. Correct chart types are:

Correct chart types of deviation messageColumn Chart, Line Chart and Bar Chart are correct type of charts when message is deviation. These charts clearly indicate the message of “Deviation”.

Incorrect chart types for deviation messageThese charts don’t indicate the message clearly and some of these charts like “Bubble chart” and “Scatter Chart” don’t even capture the complete information.

Message Type 6: Relationship

Let us suppose that we our student database has 3 fields: Names of students, No. of marks scored by each student and the attendance.

Student database for relationship message

If we are interested in using a chart to depict the relationship between the attendance and no. of marks scored by each student we are trying to convey the “Relationship message”. For depicting this we require charts which show both the fields “student marks and attendance” in one graph clearly.

Correct Excel Charts for Relationship

Line Chart & Scatter Chart clearly show the relationship between “Student Attendance” and his/her “Marks”. The relationship which can be derived from these charts is “Higher the attendance, more the marks”.

Incorrect chart types for relationship messageThese charts don’t depict the required relationship and thus they are not appropriate for this message.

So, in this post, we learnt how to choose a correct excel chart type based on a given scenario. In next tutorials, we will learn about how to create an excel chart and baisc and advance formatting on excel charts.

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">