We have a lot of charts in excel but each chart has its own way of representing some information better than the others.
In this chapter we will explore different kind of charts and will also look into their various formatting options.
Different charts require different kind of data set, you data has a role to play when it comes to what could be the best representation of that data in the form of a chart.
Before we begin, let’s review a few key points you need to consider before creating any chart in Excel.
The first is identifying your idea or message. It is important to keep in mind that the primary
Purpose of a chart is to present quantitative information to an audience. Therefore, you must first decide what message or idea you wish to present. This is critical in helping you select specific data from a worksheet that will be used in a chart. Throughout this chapter, we will reinforce the intended message first before creating each chart.
The second key point is selecting the right chart type. The chart type you select will depend on the data you have and the message you intend to communicate.
The third key point is identifying the values that should appear on the X and Y axis. One of the ways to identify which values belong on the X and Y axis is to sketch the chart on paper first. If you can visualize what your chart is supposed to look like, you will have an easier time using Excel to construct an effective chart that accurately communicates your message.
Time Series Trend
The first chart we will demonstrate is a line chart.Line chart is used to monitor the trend over a period of time say week or month or minutes.
Suppose we have the stock markets point values by each our and we would like to see how the market is performing over a period of time in a day.
Remember that to create a line chart we must have data points at every interval of time.
In column A of the data we have time and in column B we have the actual market value.
We will see how we could create a time series trend using this data
Step 1
Select entire data as shown in the pic below and go to Insert tab and then Line and then select the first option in the Line Chart.
Step 2
You will see a time series trend displaying the market actual values by hour of the day as shown in the pic below.
Step 3
Now we want to see the actual market values at every hour, so will have to add data values to the graph by clicking on the line chart and then go to Layout tab->Data Labels and then selecting the position of the data labels that you would like them to appear, in this case I have selected Above option so I have got data labels just above the line chart.
Comparison of two metrics
In the previous section we have seen how we could create a standalone line chart which basically gives us the idea as to how the metric is performing over a period of time.
What if we want to see a correlation between two metrics over a period of time, both the metrics may not necessarily be in proportions in terms of magnitude but we could look for some kind of correlation between them.
One such example could be how one stock market could affect another stock market.
Let’s see how NASDAQ’s behavior could affect Dow Jones.
In the data below we have Time in the column A, NASDAQ’s data in column B and Dow Jones’s data in column C.
Step 1
Select entire data as shown in the pic below and go to Insert tab and then Line and then select the first option in the Line Chart.
Step 2
You will see a time series trend displaying the market actual values by hour of the day as shown in the pic below.
There are different aesthetics involved like we could change the line colors, formatting style and other stuffs too in the Design and Layout tab that automatically appears when you select the line in the graph.
To show the actual data value, follow the same process as we did when we explained the first standalone graph in the beginning of the post.
Column Chart
A column chart is a common way to show trends over time if the time over which the data is distributed over not more than 15-20 points or time interval, another common use for column charts is to show frequency distributions.A frequency distribution count of the occurrences of values within a particular group or interval. For example, a common frequency distribution would be the number of students falling in a grade (A+ 20 students- 12 students etc).
We have data below which has grade as a category in column A and number of students in each of the grades in column B.
Now we will see how we could create a column chart to represent a frequency distribution.
Step 1
Select entire data as shown in the pic below and go to Insert tab and then Column and then select first option in the 2D column.
You could see that we have got the distribution displayed in the form of column graph in excel with X axis showing the grades and Y axis showing the number of students.
You could display actual data values and change the position of the legend and other stuffs as explained earlier.
Pie Chart
A pie chart is circular in shape which represents 100% and has parts that represent proportions of the whole.
Suppose if an item has a weightage of 50% in the whole, then this item will occupy half of the pie chart.
Each item will be represented by a slice of the pie.
Pie charts use one data series.
We will take the example from above number of student and grade data, the only difference is that we have calculated percentages as pie chart represents percentages of whole.
Below is the Percentage data.
I have calculated the percentage based on the total number of students which I calculated in yellow.
Now select the Grade and Percentage of Student column and insert a pie chart as shown below.
Color of the legend represents proportion of the student falling under that grade.
Select the pie chart and go to Data Label to add data labels as shown below.
Now Data Label is added to the pie chart.
