10 Chart Types
Charts are used to visualize data and to compare numbers easily. Excel has a wide variety of chart types. Which type can best be used in which situation is not discussed here.
Despite a large number of chart types, there are essentially only a limited number of basic types.
All these basic types have many variants so you can get confused quickly at first. It is important to choose a good chart type for visualizing the data. Ask yourself questions like:
- What should the chart tell?
- What facts are being compared?
10.1 Column Chart
If time units like years, quarters, months, weeks, and days are part of the data, then column charts are the most suitable type. Don’t use too many data points. Five to six values are maximum. When you have more than 6 values on the horizontal axis then it is better to choose a line chart.
Figure 10.2 shows the market shares for some supermarket chains for the years 2012-2016 in the Netherlands (source: Distrifood).
This data should be displayed in a column chart as shown in Figure 10.3.
Task 10.1 File: Chart_Column.xlsx
Open the file.
Select any cell in the data area.
Choose Insert > Insert Column or Bar Chart (group Charts) > Clustered Column.
Change titles:
- Chart title:
Market shares supermarket chains - Vertical axis title:
Market share (%)
- Chart title:
Choose Insert > Text Box (group Text).
Draw a rectangle with the mouse in the bottom right corner and then type the text
Source: Distrifood.Select text, right mouse click > Font > tab Font > Size 9 > OK.
10.2 Bar Chart
Bar charts are widely used to make clear differences in ranking. The importance (priority) of certain matters at the same moment can be expressed.
The results are usually sorted from highest to lowest, so the highest result is displayed as the first bar.
Figure shows the percentage of smokers by age group in 2009 (source: CBS).
The data should be displayed in a bar chart as shown in Figure 10.5.
Task 10.2 File: Chart_Bar.xlsx
Open the file.
Sort the table on the percentage of smokers from largest to smallest.
Select any cell in the data area.
Choose tab Insert > Insert Column or bar Chart (group Charts) > Clustered Bar.
The bar chart appears in the worksheet. The longest bar is at the bottom. This is Excel’s default procedure. This order must be changed, together with changing the title.
Select vertical as, right-click > Format Axis. The task panel Format Axis is displayed.
Axis Options > select Categories in reverse order.
- Change chart title in
Percentage Smokers in 2009.
10.3 Pie Chart
Pie charts are mainly used for displaying the percentage distribution of data within a group, such as election results. Too much information can make a pie chart cluttered. To keep it clear you should have at most 6 or 7 sectors in a pie chart.
150 pupils have been asked what their favorite pet is. Table 10.1 displays the results.
| Pet | Frequency |
|---|---|
| Cats | 70 |
| Dogs | 38 |
| Birds | 22 |
| Fish | 13 |
| Others | 7 |
The data should be displayed in a pie chart, as shown in figure.
Task 10.3 File: Chart_Pie.xlsx
Open the file.
Select any cell in the data area.
Choose Insert > Insert Pie or Doughnut Chart (group Charts) > Pie.
Add data labels, position Outside End.
Select the data labels, right-click and select Format Data Labels. The task panel Format Data Labels is displayed.
In the task panel, select Category Name and Percentage and deselect Value.
Remove the legend.
Change chart title in
Favorite pet of 150 pupils.
By choosing to add data labels as Data Call Out instead of Outside End, you get immediately the category names and percentages. Try it.
10.4 Line Chart
You use a line chart to visualize a trend in data over intervals of time. For line charts, the time unit is plotted along the horizontal axis, the category axis. The measured variable is plotted along the vertical axis. A line can be drawn between the points on the graph to visualize a trend in data over intervals of time.
In Figure 10.11, you can find annual temperature data (oC) for Amsterdam Airport Schiphol (source: https://en.tutiempo.net/climate/ws-62400.html).
- T = Average annual temperature
- Tmax = Annual average maximum temperature
- Tmin = Average annual minimum temperature
The data is to be displayed in a line chart, as shown in Figure 10.12.
Task 10.4 File: Chart_Line.xlsx
Open the file.
Select any cell in the data area.
Choose Insert > Recommended Chart (group Charts) > Line.
Change titles:
- Chart title:
Annual Temperatures Amsterdam Schiphol Airport - Vertical axis title:
Temperature (C).
- Chart title:
Select the chart and change the style through Chart Design > Chart Styles. Choose a style you like.
10.5 Scatter (XY) Chart
A scatter chart (XY diagram) is very suitable if you want to analyze and show the relationship between two numeric variables. It also allows you to determine a trend line. In the chart, the values of two numeric variables are plotted against each other. Each pair makes a point in the chart.
To investigate whether there is a relationship between body length and weight, the data is measured for 10 students. The measurement data is shown in Figure 10.13.
The data should be displayed in a scatter chart, as shown in Figure 10.14.
Task 10.5 File: Chart_Scatter.xlsx
Open the file.
Select any cell in the data area.
Choose Insert > Insert Scatter (XY) or Bubble Chart (group Charts) > Scatter.
Change titles:
- Chart title:
Relation body length - weight - Horizontal axis title:
Length (cm) - Vertical axis title:
Weight (kg)
- Chart title:
Change the scale of the horizontal axis so that it runs from 160 to 190, with increments of 5.
Change the scale of the vertical axis so that it runs from 40 to 80, with increments of 5.
Select axis > Right click > Format Axis > Axis Options
10.6 Doughnut Chart
A doughnut chart is an extension of the pie chart. This diagram can contain one or more rings and each ring represents a data series. A doughnut chart can be used if you want to show the percentage distribution of multiple data series.
In Figure 10.15, the quarterly sales of a company are shown for the years 2008 and 2009.
These data should be displayed in a doughnut chart, as shown in Figure 10.16.
Task 10.6 File: Chart_Doughnut.xlsx
Open the file.
Select any cell in the data area.
Choose Insert > Insert Pie or Doughnut Chart (group Charts) > Doughnut.
There are still some adjustments to be made. Some of the formatting in this example will be done by applying a predefined chart style.
Change the chart title in
Sales 2008-2009 by quarter.Select the chart and change the style through Chart Design > Chart Styles. Choose a style you like.
10.7 Area Chart
Area charts are based on line charts. The area between axis and line are commonly emphasized with colors or hatchings. As with a line chart, the size of a variable is plotted against time. In a stacked area chart multiple data series are placed one above the other, so the sum of the data can be seen. With an area chart can you make trends visible.
A company sells three products: A, B, and C. In Figure 10.17 the sales quantity during the first half-year is shown.
The data is to be displayed in a stacked area chart, as shown in Figure 10.18.
Task 10.7 File: Chart_Area.xlsx
Open the file.
Select any cell in the data area.
Choose Insert > Recommended Charts (group Charts) > Stacked Area > OK.
Change the chart title in
Number of items sold.Select legend, then right-click and Format legend. Change the position of the legend from Bottom to Right.
Select legend > Right click > Format Legend > Legend Options
10.8 Bubble Chart
A bubble chart can be used if you want to display the relationship between three numeric variables.
A bubble chart is an extension of a scatter chart. In a scatter chart two numeric variables (X and Y) are plotted against each other. At a bubble chart, a third variable (Z) is used. The point in the scatter chart is now replaced by a bubble (or circle). The center of the bubble is determined by the variable X and Y and the size (radius) of the bubble by the variable Z. Other characteristics of the bubble, like the color, can also be used to make additional differences. These differences are based on qualitative, non-numeric values.
Figure 10.19 shows how the market share is related to turnover and the number of products.
The data is to be displayed in a bubble area chart, as shown in Figure 10.20.
Task 10.8 File: Chart_Bubble.xlsx
Open the file.
Give the values for turnover and market share a proper format.
A good layout of the source data ensures that you also get a good format in the chart.
Select any cell in the data area.
Choose Insert > Insert Scatter (XY) or Bubble Chart (group Charts) > 3-D Bubble.
Make the following changes:
- Chart title:
Market shares - Horizontal axis title:
Number of products - Vertical axis title:
Turnover - Scaling vertical axis: 0 to 70000 (set minimum on fixed 0 and maximum on fixed 70000)
- Color bubbles: orange/golden
- Data labels: position centered, display percentage of market share
- Chart title:
The added data labels are not the ones you want. By default, the Y value (in this case, the revenue) is displayed. This must be changed to the bubble size, which in this case is determined by the market share.
- Select at Label Contains only Bubble Size.
10.9 Radar Chart
In a radar chart, multiple data series (categories) are plotted along separate axes starting from a point in the middle, the origin. The diagram looks like a spider web and is therefore called a spider chart or star chart. The angles between these axes are equal. The data points on the axes are usually connected with a line.
In Figure 10.21, you see the scores of a self-evaluation, according to a 5-point Likert scale.
The data should be displayed in a radar chart, as shown in Figure 10.22.
Task 10.9 File: Chart_Radar.xlsx
Open the file.
Select any cell in the data area.
Choose Insert > Insert Surface or Radar Chart (group Charts) > Radar.
Change the chart title in
Self-evaluation.
10.10 Pareto chart
A Pareto chart is a combination of a column chart and a line chart. For the column chart, the values are sorted from largest to smallest. The line chart is on top of the columns and shows the cumulative percentage. The column chart uses the normal Y-axis on the left. For the line chart, there is a second Y-axis on the right with values from 0% to 100%.
A Pareto diagram is often used in quality control to identify the most important factors.
Figure 10.23 shows the main causes of death in the Netherlands in 2009 (source: CBS).
The data should be displayed in a Pareto chart, as shown in Figure 10.24.
Task 10.10 File: Chart_Pareto.xlsx
Open the file.
Select any cell in the data area.
Choose Insert > recommanded charts > tab All Charts > Histogram > Pareto.
Change chart title and add data labels.
10.11 Control chart
Control charts are used in statistical process control (SPC) to verify that a process variable is under control. The value of such a variable must remain within certain limits. A control chart is really nothing more than a line chart of the variable’s measurements with horizontal lines for the control limits.
Limits
UCL - Upper Control Limit
LCL - Lower Control Limit
The control limits are calculated from the data and are set at 2-3 standard deviations from the mean, depending on the type of process. Often you will also see a horizontal line on the control chart that represents the mean. This line is referred to as CL (Central Line).
Task 10.11 File: Chart_Control.xlsx
As an example, a continuous chemical process in which the temperature of the reaction mixture is monitored every hour. Proper control of this process requires that the temperature remain within 2 standard deviations from the mean.
Open the file. The file contains a table for hour and temperature data.
Enter in the cells H1 and H2 a formula for the mean and standarddeviation of the temperature data.
Enter the following formulas
In column CL:
=$H$1In column UCL:
= $H$1 + 2*$H$2In column LCL:
= $H$1 - 2*$H$2
Select all data in the table and insert a line chart with markers.
Adjust the layout as desired.
On the internet you can find a lot of information about SPC and control charts. A solid article is Control Limits and Specifications: The Four Process States
10.12 Line or Scatter Chart?
Line charts and scatter charts look quite similar, especially when a scatter chart is displayed with connecting lines. However, there are important differences in the way how the data is plotted along the horizontal axis (x-axis) and the vertical axis (y-axis). So mistakes can be made easily and it’s important to make the right choice.
Example 10.1 Annual sales data
In Figure 10.26, the annual sales data of a company are plotted with a line chart and a scatter chart.
The difference in scaling can cause incorrect conclusions. In the line chart, it looks like a strong growth in the first years and a stagnation in more recent years. This is not the case. The real growth can be seen in the scatter chart.
Line chart
- vertical: value axis
- horizontal: category axis
The horizontal axis has evenly spaced categories of data (text or dates). A date axis displays dates in chronological order.
A line chart is good for displaying the change of a variable over time. Examples: sales, turnover, profit, price, etc., by day, week, month, quarter, year. The time unit is always along the horizontal axis and the value of the measured variable along the vertical axis.
Scatter chart
- vertical: value axis
- horizontal: value axis
The horizontal axis can display numeric values or date values. And you can change the scaling options of both axes. The chart displays points at the intersection of the x value and the y value.
A scatter chart is used to examine the relationship between two variables. If one variable becomes larger or smaller, what happens then with the other variable, does it become larger or smaller? The data values are displayed as separate points on the chart.
Although you can connect the points with lines, it is not a good idea, because it suggests that the changes are proceeding according to these lines. It is better to show a relationship via a trendline. This is a line that reflects the relationship between the two variables best. The measured points can be on this trend line, but usually, they are distributed around (above or below) the trend line.
Scatter charts are widely used in science and technology. In management reports, you also can find scatter charts, for example, whether there is a correlation between price increases and sales.
10.13 Translation chart type names
| NL | EN | DE |
|---|---|---|
| Bellendiagram | Bubble chart | Blasendiagramm |
| Cirkeldiagram | Pie chart | Kreisdiagramm |
| Kolomdiagram | Column chart | Säulendiagramm |
| Lijndiagram | Line chart | Liniendiagramm |
| Ringdiagram | Doughnut chart | Ringdiagramm |
| Radardiagram | Radar chart | Netzdiagramm |
| Spreidingsdiagram | Scatter (XY) chart | Punktdiagramm |
| Staafdiagram | Bar chart | Balkendiagramm |
| Vlakdiagram | Area chart | Flächendiagramm |
| Watervaldiagram | Waterfall chart | Wasserfalldiagramm |