10-charttypes.qmd

# Chart Types {#sec-charttype}

```{r, file = "R/webex.R", include = FALSE}
```

::: callout-intro
-   Chart types.
-   Line chart vs Scatter chart.
:::

Charts are used to visualize data and easily compare numbers. Excel offers a wide variety of chart types. This section does not discuss which type is best suited for each situation.

Despite the large number of chart types, there are essentially only a limited number of basic types.

![Basic chart types.](images/charttypes/charttype-basic.png){#fig-charttype-basic}

All these basic types have many variants, which can initially cause confusion. Choosing a good chart type for visualizing data is important. Ask yourself questions like:

-   What should the chart convey?
-   What facts are being compared?

## Column Chart {#sec-charttype-column}

Column charts are most suitable if the data includes time units like years, quarters, months, weeks, and days. Avoid using too many data points; a maximum of five to six values is ideal. If you have more than six values on the horizontal axis, a line chart is preferable.

@fig-column-data shows the market shares of some supermarket chains in the Netherlands from 2012 to 2016 (source: Distrifood).

![Market shares for some supermarket chains from 2012-2016.](images/charttypes/column-data.png){#fig-column-data}

This data is best displayed in a column chart, as shown in @fig-column-result.

![Column chart of market shares for some supermarket chains.](images/charttypes/column-result.png){#fig-column-result}

::: {#prp-charttype-column}
[File]{.smallcaps}: `Chart_Column.xlsx`

1.  Open the file.

2.  Select any cell in the data area.

3.  Choose [Insert \> Insert Column or Bar Chart (Charts group) \> Clustered Column]{.uicontrol}.

4.  Change the titles:

    -   Chart title: `Market shares of supermarket chains`
    -   Vertical axis title: `Market share (%)`

5.  Choose [Insert \> Text Box (Text group)]{.uicontrol}.

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

7.  Select the text, [Right mouse click \> Font \> tab Font \> Size 9 \> OK]{.uicontrol}.
:::

## Bar Chart {#sec-charttype-bar}

Bar charts are widely used to clearly show differences in ranking. They can effectively express the importance (priority) of certain items at a specific moment.

The results are usually sorted from highest to lowest, so the highest result is displayed as the first bar.

@fig-bar-data shows the percentage of smokers by age group in 2009 (source: CBS).

![Percentage smokers in 2009.](images/charttypes/bar-data.png){#fig-bar-data}

The data should be displayed in a bar chart, as shown in @fig-bar-result.

![Bar chart of the percentage of smokers in 2009.](images/charttypes/bar-result.png){#fig-bar-result}

::: {#prp-charttype-bar}
[File]{.smallcaps}: `Chart_Bar.xlsx`

1.  Open the file.

2.  Sort the table by the percentage of smokers, from highest to lowest.

![Percentage of smokers in 2009, sorted from highest to lowest.](images/charttypes/bar-data-sorted.png){#fig-bar-data-sorted}

3.  Select any cell in the data area.

4.  Choose [tab Insert \> Insert Column or bar Chart (Charts group) \> Clustered Bar]{.uicontrol}.

The bar chart appears in the worksheet with the longest bar at the bottom. This is Excel's default setting, but the order and the title need to be changed.

![Default bar chart with the longest bar at the bottom.](images/charttypes/bar-default.png){#fig-bar-default}

5.  Select the vertical axis, [Right mouse click \> Format Axis]{.uicontrol}. The *Format Axis* task pane appears.

6.  In [Axis Options]{.uicontrol} select [Categories in reverse order]{.uicontrol}.

![Format Axis task pane.](images/charttypes/bar-axis-options.png){#fig-bar-axis-options}

7.  Change the chart title to "Percentage of Smokers in 2009".
:::

## Pie Chart {#sec-charttype-pie}

Pie charts are mainly used to display the percentage distribution of data within a group, such as election results. Too much information can make a pie chart cluttered. For clarity, a pie chart should have at most six or seven sectors.

@tbl-survey-pet shows the results of a survey asking 150 pupils about their favorite pet.

| Pet    | Frequency |
|--------|-----------|
| Cats   | 70        |
| Dogs   | 38        |
| Birds  | 22        |
| Fish   | 13        |
| Others | 7         |

: Favorite pet survey results. {#tbl-survey-pet}

The data should be displayed in a pie chart, as shown in @fig-pie-result.

![Pie chart of favorite pets.](images/charttypes/pie-result.png){#fig-pie-result}

:::: {#prp-charttype-pie}
[File]{.smallcaps}: `Chart_Pie.xlsx`

1.  Open the file.

2.  Select any cell in the data area.

3.  Choose [Insert \> Insert Pie or Doughnut Chart (Charts group) \> Pie]{.uicontrol}.

4.  Add data labels and position them [Outside End]{.uicontrol}.

5.  Select the data labels, right-click and select [Format Data Labels]{.uicontrol}. The Format Data Labels task pane appears.

6.  In the task pane, select [Category Name]{.uicontrol} and [Percentage]{.uicontrol}, and deselect [Value]{.uicontrol}.

![Formatting label options.](images/charttypes/pie-label-options.png){#fig-pie-label-options}

7.  Remove the legend.

8.  Change the chart title in "Favorite Pet of 150 Pupils".

::: callout-note
Choosing [Data Call Out]{.uicontrol} instead of [Outside End]{.uicontrol} for data labels immediately displays the category names and percentages. Try it.
:::
::::

## Line Chart {#sec-charttype-line}

Line charts visualize trends in data over time. In line charts, the time unit is plotted along the horizontal axis (the category axis), and the measured variable is plotted along the vertical axis. A line connects the points on the graph to visualize the trend in data over time.

@fig-line-data shows annual temperature data (°C) 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 at Amsterdam Airport Schiphol.](images/charttypes/line-data.png){#fig-line-data}

The data is to be displayed in a line chart, as shown in @fig-line-result.

![Line chart of average annual temperature for Schiphol.](images/charttypes/line-result.png){#fig-line-result}

::: {#prp-charttype-line}
[File]{.smallcaps}: `Chart_Line.xlsx`

1.  Open the file.

2.  Select any cell in the data area.

3.  Choose [Insert \> Recommended Chart (Charts group) \> Line]{.uicontrol}.

4.  Change the titles:

    -   Chart title: "Annual Temperatures Amsterdam Schiphol Airport"
    -   Vertical axis title: "Temperature (°C)".

5.  Select the chart and change the style using [Chart Design \> Chart Styles]{.uicontrol}. Choose a style you like.
:::

## Scatter (XY) Chart {#sec-charttype-scatter}

A scatter chart (or XY diagram) is suitable for analyzing and showing the relationship between two numeric variables. It also allows you to determine a trend line. The chart plots the values of two numeric variables against each other, with each pair forming a point in the chart.

@fig-scatter-data shows data from an investigation into the relationship between body length and weight for 10 students.

![Investigation of body length vs. weight.](images/charttypes/scatter-data.png){#fig-scatter-data}

The data should be displayed in a scatter chart, as shown in @fig-scatter-result.

![Scatter chart of length vs. weight.](images/charttypes/scatter-result.png){#fig-scatter-result}

:::: {#prp-charttype-scatter}
[File]{.smallcaps}: `Chart_Scatter.xlsx`

1.  Open the file.

2.  Select any cell in the data area.

3.  Choose [Insert \> Insert Scatter (XY) or Bubble Chart (Charts group) \> Scatter]{.uicontrol}.

4.  Change the titles:

    -   Chart title: "Relationship between Body Length and Weight"
    -   Horizontal axis title: "Length (cm)"
    -   Vertical axis title: "Weight (kg)"

5.  Change the scale of the horizontal axis to run from 160 to 190, with increments of 5.

6.  Change the scale of the vertical axis to run from 40 to 80, with increments of 5.

::: {.content-visible when-format="html:js"}
`r hide("Hint scaling axis")`

[Select axis \> Right click \> Format Axis \> Axis Options]{.uicontrol}

`r unhide()`
:::
::::

## Doughnut Chart {#sec-charttype-doughnut}

A doughnut chart is an extension of the pie chart. It can contain one or more rings, with each ring representing a data series. Use a doughnut chart to show the percentage distribution of multiple data series.

@fig-doughnut-data shows the quarterly sales of a company for 2008 and 2009.

![Sales by quarter.](images/charttypes/doughnut-data.png){#fig-doughnut-data}

This data should be displayed in a doughnut chart, as shown in @fig-doughnut-result.

![Doughnut chart of sales by quarter.](images/charttypes/doughnut-result.png){#fig-doughnut-result}

::: {#prp-charttype-doughnut}
[File]{.smallcaps}: `Chart_Doughnut.xlsx`

1.  Open the file.

2.  Select any cell in the data area.

3.  Choose [Insert \> Insert Pie or Doughnut Chart (Charts group) \> Doughnut]{.uicontrol}.

Some adjustments are still needed. In this example, some of the formatting will be applied using a predefined chart style.

4.  Change the chart title to "Sales 2008-2009 by quarter".

5.  Select the chart and change the style using [Chart Design \> Chart Styles]{.uicontrol}. Choose a style you like.
:::

## Area Chart {#sec-charttype-area}

Area charts are based on line charts. The area between the axis and the line is typically emphasized with colors or hatching. Like a line chart, an area chart plots the size of a variable against time. In a stacked area chart, multiple data series are placed on top of each other, showing the sum of the data. Area charts effectively visualize trends.

A company sells three products: A, B, and C. @fig-area-data shows the sales quantities during the first half-year.

![Sales quantities by month.](images/charttypes/area-data.png){#fig-area-data}

The data is to be displayed in a stacked area chart, as shown in @fig-area-result.

![Stacked area chart of sales quantities by month.](images/charttypes/area-result.png){#fig-area-result}

:::: {#prp-charttype-area}
[File]{.smallcaps}: `Chart_Area.xlsx`

1.  Open the file.

2.  Select any cell in the data area.

3.  Choose [Insert \> Recommended Charts (Charts group) \> Stacked Area \> OK]{.uicontrol}.

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

5.  Select the legend, then right-click and choose [Format legend]{.uicontrol}. Change the legend's position from [Bottom]{.uicontrol} to [Right]{.uicontrol}.

::: {.content-visible when-format="html:js"}
`r hide("Hint legend")`

[Select legend \> Right click \> Format Legend \> Legend Options]{.uicontrol}

`r unhide()`
:::
::::

## Bubble Chart {#sec-charttype-bubble}

A bubble chart can display the relationship between three numeric variables.

It is an extension of the scatter chart. While a scatter chart plots two numeric variables (X and Y) against each other, a bubble chart adds a third variable (Z). The point in the scatter chart is replaced by a bubble (or circle). The center of the bubble is determined by the X and Y variables, and the bubble's size (radius) is determined by the Z variable. Other bubble characteristics, such as color, can indicate additional qualitative, non-numeric differences.

@fig-bubble-data shows the relationship between market share, turnover, and the number of products.

![Market shares.](images/charttypes/bubble-data.png){#fig-bubble-data}

The data is to be displayed in a bubble chart, as shown in @fig-bubble-result.

![Bubble chart of market shares.](images/charttypes/bubble-result.png){#fig-bubble-result}

:::: {#prp-charttype-bubble}
[File]{.smallcaps}: `Chart_Bubble.xlsx`

1.  Open the file.

2.  Format the values for turnover and market share appropriately.

::: {.callout-tip title="Hint formatting"}
Proper formatting of the source data ensures proper formatting in the chart.
:::

3.  Select any cell in the data area.

4.  Choose [Insert \> Insert Scatter (XY) or Bubble Chart (Charts group) \> 3-D Bubble]{.uicontrol}.

5.  Make the following changes:

    -   Chart title: "Market shares"
    -   Horizontal axis title: "Number of products"
    -   Vertical axis title: "Turnover"
    -   Scale the vertical axis: 0 to 70000 (set the minimum to fixed 0 and the maximum to fixed 70000)
    -   Color bubbles: orange/golden
    -   Data labels: position centered, display the percentage of market share

The added data labels are not the desired ones. By default, the Y value (turnover in this case) is displayed. Change this to the bubble size, which represents the market share.

6.  In[Label Contains]{.uicontrol}, select only [Bubble Size]{.uicontrol}.\
    ![](images/charttypes/bubble-labels.png)
::::

## Radar Chart {#sec-charttype-radar}

A radar chart plots multiple data series (categories) along separate axes that start from a central point (the origin). The chart resembles a spider web and is also called a spider chart or star chart. The angles between the axes are equal, and the data points on the axes are usually connected by a line.

@fig-radar-data shows the scores of a self-evaluation on a 5-point Likert scale.

![Self-evaluation on a 1-5 scale.](images/charttypes/radar-data.png){#fig-radar-data}

The data should be displayed in a radar chart, as shown in @fig-radar-result.

![Radar chart of self-evaluation.](images/charttypes/radar-result.png){#fig-radar-result}

::: {#prp-charttype-radar}
[File]{.smallcaps}: `Chart_Radar.xlsx`

1.  Open the file.

2.  Select any cell in the data area.

3.  Choose [Insert \> Insert Surface or Radar Chart (Charts group) \> Radar]{.uicontrol}.

4.  Change the chart title in "Self-evaluation".
:::

## Pareto Chart {#sec-charttype-pareto}

A Pareto chart combines a column chart and a line chart. The column chart displays values sorted from largest to smallest, while the line chart, placed on top of the columns, shows the cumulative percentage. The column chart uses the regular Y-axis on the left, and the line chart uses a second Y-axis on the right with values from 0% to 100%.

Pareto charts are often used in quality control to identify the most significant factors.

@fig-pareto-data shows the main causes of death in the Netherlands in 2009 (source: CBS).

![Causes of death in the Netherlands in 2009.](images/charttypes/pareto-data.png){#fig-pareto-data}

The data should be displayed in a Pareto chart, as shown in @fig-pareto-result.

![Pareto chart of causes of death in 2009.](images/charttypes/pareto-result.png){#fig-pareto-result}

::: {#prp-charttype-pareto}
[File]{.smallcaps}: `Chart_Pareto.xlsx`

1.  Open the file.

2.  Select any cell in the data area.

3.  Choose [Insert \> recommanded charts \> tab All Charts \> Histogram \> Pareto]{.uicontrol}.

4.  Change the chart title and add data labels.
:::

## Control chart {#sec-charttype-control}

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 essentially 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 typically set at 2-3 standard deviations from the mean, depending on the process type. Often, a horizontal line representing the mean (CL - Central Line) is also included in the control chart.

::: {#prp-charttype-control}
[File]{.smallcaps}: `Chart_Control.xlsx`

For example, consider a continuous chemical process where the temperature of the reaction mixture is monitored hourly. Proper control of this process requires the temperature to remain within 2 standard deviations of the mean.

![Control chart for the temperature in a chemical reactor.](images/charttypes/control-result.png){#fig-control-result}

1.  Open the file. The file contains a table with hour and temperature data.

2.  Enter formulas in cells H1 and H2 to calculate the mean and standard deviation of the temperature data.

3.  Enter the following formulas

    -   In column CL: `=$H$1`

    -   In column UCL: `= $H$1 + 2*$H$2`

    -   In column LCL: `= $H$1 - 2*$H$2`

4.  Select all data in the table and insert a line chart with markers.

5.  Adjust the layout as desired.
:::

::: {.callout-tip title="SPC information"}
You can find extensive information about SPC and control charts online. A useful article is [Control Limits and Specifications: The Four Process States](https://www.spcforexcel.com/knowledge/variation/four-process-states)
:::

## Line or Scatter Chart? {#sec-charttype-line-scatter}

Line charts and scatter charts appear quite similar, especially when a scatter chart is displayed with connecting lines. However, there are significant differences in how data is plotted along the horizontal axis (x-axis) and the vertical axis (y-axis). Therefore, it's crucial to make the right choice to avoid misinterpretations.

::: {#exm-charttypes-line-scatter}
**Annual Sales Data**

@fig-line-scatter tshows the annual sales data of a company plotted with a line chart and a scatter chart.

![Sales per year. Line chart on the left, scatter chart on the right.](images/charttypes/line-scatter.png){#fig-line-scatter}

The difference in scaling can lead to incorrect conclusions. The line chart suggests strong growth in the early years and stagnation in recent years, which is misleading. The scatter chart accurately represents the growth.
:::

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

Line charts are suitable for displaying the change of a variable over time. Examples include sales, turnover, profit, price, etc., by day, week, month, quarter, or year. The time unit is always on the horizontal axis, and the value of the measured variable is on the vertical axis.

**Scatter chart**

-   Vertical: *value axis*
-   Horizontal: *value axis*

The horizontal axis can display numeric 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.

Scatter charts are used to examine the relationship between two variables. They help determine how one variable changes in response to changes in the other. The data values are displayed as separate points on the chart.

While you can connect the points with lines, it's generally not recommended because it implies that the changes follow those lines. It's better to represent the relationship with a trendline, which is a line that best reflects the relationship between the two variables. The measured points may lie on or be distributed around the trendline.

Scatter charts are commonly used in science and technology. Management reports may also include scatter charts, for example, to analyze the correlation between price increases and sales.

## Translation of Chart Type Names {#charttype-translations}

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

: Dutch, English, and German names of some chart types. {#tbl-charttype-translations}