10 Chart Types

  • Chart types.
  • Line chart vs Scatter chart.

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.

Basic chart types.

Figure 10.1: Basic chart 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 Task: 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).

Market shares for some supermarket chains in 2012-2016.

Figure 10.2: Market shares for some supermarket chains in 2012-2016.

This data should be displayed in a column chart as shown in figure 10.3.

Column chart of market shares for some supermarket chains.

Figure 10.3: Column chart of market shares for some supermarket chains.

File: Chart_Column.xlsx

  1. Open the practice file.

  2. Select any cell in the data area.

  3. Choose Insert > Insert Column or Bar Chart (group Charts) > Clustered Column.

  4. Change titles:

    • Chart title: Market shares supermarket chains
    • Vertical axis title: Market share (%)
  5. Choose Insert > Text Box (group Text).

  6. Draw a rectangle with the mouse in the bottom right corner and then type the text Source: Distrifood.

  7. Select text, right mouse click > Font > tab Font > Size 9 > OK.

10.2 Task: 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).

Percentage smokers in 2009.

Figure 10.4: Percentage smokers in 2009.

The data should be displayed in a bar chart as shown in figure 10.5.

Bar chart percentage smokers in 2009.

Figure 10.5: Bar chart percentage smokers in 2009.

File: Chart_Bar.xlsx

  1. Open the practice file.

  2. Sort the table on the percentage of smokers from largest to smallest.

Percentage smokers in 2009, sorted from highest to lowest.

Figure 10.6: Percentage smokers in 2009, sorted from highest to lowest.

  1. Select any cell in the data area.

  2. 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.

Default bar chart with the longest bar at the bottom.

Figure 10.7: Default bar chart with the longest bar at the bottom.

  1. Select vertical as, right-click > Format Axis. The task panel Format Axis is displayed.

  2. Axis Options > select Categories in reverse order.

Task panel Format Axis.

Figure 10.8: Task panel Format Axis.

  1. Change chart title in Percentage Smokers in 2009.

10.3 Task: 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.

Table 10.1: Favorite pet survey 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.

Pie chart favorite pet.

Figure 10.9: Pie chart favorite pet.

File: Chart_Pie.xlsx

  1. Open the practice file.

  2. Select any cell in the data area.

  3. Choose Insert > Insert Pie or Doughnut Chart (group Charts) > Pie.

  4. Add data labels, position Outside End.

  5. Select the data labels, right-click and select Format Data Labels. The task panel Format Data Labels is displayed.

  6. In the task panel, select Category Name and Percentage and deselect Value.

Formatting label options.

Figure 10.10: Formatting label options.

  1. Remove the legend.

  2. 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 Task: 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
Average annual temperature Amsterdam, airport Schiphol.

Figure 10.11: Average annual temperature Amsterdam, airport Schiphol.

The data is to be displayed in a line chart, as shown in figure 10.12.

Line chart with average annual temperature for Schiphol.

Figure 10.12: Line chart with average annual temperature for Schiphol.

File: Chart_Line.xlsx

  1. Open the practice file.

  2. Select any cell in the data area.

  3. Choose Insert > Recommended Chart (group Charts) > Line.

  4. Change titles:

    • Chart title: Annual Temperatures Amsterdam Schiphol Airport
    • Vertical axis title: Temperature (C).
  5. Select the chart and change the style through Chart Design > Chart Styles. Choose a style you like.

10.5 Task: 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.

Investigation body length - weight.

Figure 10.13: Investigation body length - weight.

The data should be displayed in a scatter chart, as shown in figure 10.14.

Scatter chart length - weight.

Figure 10.14: Scatter chart length - weight.

File: Chart_Scatter.xlsx

  1. Open the practice file.

  2. Select any cell in the data area.

  3. Choose Insert > Insert Scatter (XY) or Bubble Chart (group Charts) > Scatter.

  4. Change titles:

    • Chart title: Relation body length - weight
    • Horizontal axis title: Length (cm)
    • Vertical axis title: Weight (kg)
  5. Change the scale of the horizontal axis so that it runs from 160 to 190, with increments of 5.

  6. 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 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.

In figure 10.15, the annual sales data of a company are plotted with a line chart and a scatter chart.

sales per year. At the left in a line chart, and at the right in a scatter chart.

Figure 10.15: sales per year. At the left in a line chart, and at the right in 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.7 Task: 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.16, the quarterly sales of a company are shown for the years 2008 and 2009.

Sales by quarter.

Figure 10.16: Sales by quarter.

These data should be displayed in a doughnut chart, as shown in figure 10.17.

Doughnut chart sales by quarter.

Figure 10.17: Doughnut chart sales by quarter.

File: Chart_Doughnut.xlsx

  1. Open the practice file.

  2. Select any cell in the data area.

  3. 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.

  1. Change the chart title in Sales 2008-2009 by quarter.

  2. Select the chart and change the style through Chart Design > Chart Styles. Choose a style you like.

10.8 Task: 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.18 the sales quantity during the first half-year is shown.

Sales quantities by month.

Figure 10.18: Sales quantities by month.

The data is to be displayed in a stacked area chart, as shown in figure 10.19.

Stacked area chart for sales quantities by month.

Figure 10.19: Stacked area chart for sales quantities by month.

File: Chart_Area.xlsx

  1. Open the practice file.

  2. Select any cell in the data area.

  3. Choose Insert > Recommended Charts (group Charts) > Stacked Area > OK.

  4. Change the chart title in Number of items sold.

  5. 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.9 Task: 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.20 shows how the market share is related to turnover and the number of products.

Market shares.

Figure 10.20: Market shares.

The data is to be displayed in a bubble area chart, as shown in figure 10.21.

Bubble chart for market shares.

Figure 10.21: Bubble chart for market shares.

File: Chart_Bubble.xlsx

  1. Open the practice file.

  2. 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.

  1. Select any cell in the data area.

  2. Choose Insert > Insert Scatter (XY) or Bubble Chart (group Charts) > 3-D Bubble.

  3. 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

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.

  1. Select at Label Contains only Bubble Size.

10.10 Task: 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.22, you see the scores of a self-evaluation, according to a 5-point Likert scale.

Self-evaluation on a 1-5 scale.

Figure 10.22: Self-evaluation on a 1-5 scale.

The data should be displayed in a radar chart, as shown in figure 10.23.

Radar chart self-evaluation.

Figure 10.23: Radar chart self-evaluation.

File: Chart_Radar.xlsx

  1. Open the practice file.

  2. Select any cell in the data area.

  3. Choose Insert > Insert Surface or Radar Chart (group Charts) > Radar.

  4. Change the chart title in Self-evaluation.

10.11 Task: 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.24 shows the main causes of death in the Netherlands in 2009 (source: CBS).

Causes of death in 2009 in the Netherlands.

Figure 10.24: Causes of death in 2009 in the Netherlands.

The data should be displayed in a Pareto chart, as shown in figure 10.25.

Pareto chart of death causes in 2009.

Figure 10.25: Pareto chart of death causes in 2009.

Bestand: Grafiek_pareto.xlsx

  1. Open the practice file.

  2. Select any cell in the data area.

  3. Choose Insert > recommanded charts > tab All Charts > Histogram > Pareto.

  4. Change chart title and add data labels.

10.12 Translation chart type names

Dutch, English, and German names of some chart types.

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