09-charts.qmd

# Charts {#sec-charts}

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

::: callout-intro
-   Parts of a chart.
-   Creating charts.
-   Adjustments of chart parts.
-   Location and size of a chart.
-   Formatting a chart.
:::

Charts are an important tool in analyzing data. They can display information clearly, and their power should not be underestimated. Trends and patterns can be easily visualized, and abnormalities or fluctuations become readily apparent.

Excel offers a wide range of chart types, making it important to choose the right one. When Excel is installed, the Grouped Column chart type is the default, but this setting can be changed.

To easily create a chart in Excel, the data should be well-organized in the worksheet, ideally in a table format.

Charts can be located in two ways:

-   As an object in a worksheet. This is called an **embedded chart**. The advantage is that you can view and print the chart alongside the data.
-   On a special worksheet dedicated to the chart, called a **chart sheet**.

The chart's location can be changed later.

## Chart Elements {#sec-charts-elements}

A chart consists of many elements, not all of which are included by default in Excel. They can be added, moved, or deleted as needed.

![Elements of a chart.](images/charts/chart-elements.png){#fig-chart-elements}

The most important parts of a chart are:

Chart area (1)

:   The entire chart, including all its elements.

Plot area (2)

:   The rectangular area bounded by the axes, containing all data series. In the figure, this is the area with the light-colored background.

Data series (3)

:   A row or column of numbers from your worksheet that are plotted in the chart. The example chart has two data series: the blue and brown colored sets. Data series have names (here, "2009" and "2010"), which form the legend.

Data point (4)

:   An individual value (bar, column, point, etc.) in the chart. Each column in the chart is a data point, with a total of eight data points shown.

Category axis (5)

:   The horizontal axis or X-axis. The text labels along this axis come from cells in the worksheet. In the example, the categories are "Q1," "Q2," "Q3," and "Q4."

Horizontal axis title (6)

:   This title identifies the type of data along the horizontal axis. For example, if it's not clear that "Q1, Q2, Q3, Q4" represents quarters, you can add a title to clarify.

Value axis (7)

:   The vertical axis or Y-axis, which usually contains numbers.

Vertical axis title (8)

:   This title is important for identifying the units along the axis.

Chart title (9)

:   A short text that indicates the chart's subject. It usually appears in a larger font near the top of the chart.

Legend (10)

:   A box that identifies the patterns or colors assigned to the data series or categories.

Gridlines (11)

:   Horizontal or vertical lines in the plot area.

Data labels (12)

:   Numbers associated with data points, providing their actual values.

## Creating a Recommended Chart {#sec-charts-create}

Excel offers many chart types, and choosing the most appropriate one can be challenging. The [Recommended Charts]{.uicontrol} command can help by suggesting suitable charts for your data. Always select the data first to use this feature effectively.

:::: {#prp-charts-create}
[File]{.smallcaps}: `Bakery.xlsx`

![Sales bakery per year by quarter.](images/charts/bakery-data.png){#fig-bakery-data}

1.  Open the file.

2.  Select any cell in the data area A1:D5.

::: {.callout-note title="Selecting data area"}
If the data area is contiguous (like A1:D5 here), selecting any cell within it allows Excel to automatically recognize the entire data area. If the area is not contiguous, you must select the whole data area.
:::

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

The Insert Chart dialog box appears. The left side displays a list of Excel's recommended charts for your data, and clicking on a chart shows a preview on the right.

![Recommended charts. The selected chart type is highlighted in the left column.](images/charts/chart-recommended.png){#fig-chart-recommended}

4.  Choose [Clustered Column \> OK]{.uicontrol}.

The chart is embedded in the worksheet, and the [Chart Design]{.uicontrol} and [Format]{.uicontrol} tabs appear on the ribbon. These tabs are only visible when the chart is selected.

A frame surrounds the chart to indicate it is selected. In the upper-right corner, three buttons allow you to add or change::

-   ![](images/common/button-chart-elements.png) Chart Elements
-   ![](images/common/button-chart-styles.png) Chart Styles
-   ![](images/common/button-chart-filters.png) Chart Filters
::::

::: {.callout-tip title="Quick Analysis"}
An alternative, often quicker, method to insert a recommended chart is:

1.  Select the data, including the headings.
2.  Click the [Quick Analysis]{.uicontrol} button ![](images/common/button-quick-analysis.png) at the bottom right corner of the selected range.
3.  Click [Charts]{.uicontrol} and select the desired chart.
:::

## Chart Operations {#sec-charts-operations}

You can perform various operations on chart elements, depending on the specific element. For text elements, you can change the size, font, color, etc. To perform an operation, you must first select the element and then choose the operation from the menu or the right-click shortcut menu.

**Selecting a chart element**

This is usually straightforward, but it can be tricky when elements are close together.

After selecting the chart, you can select a chart element in the following ways:

-   Click on the chart element.
-   Use the menu [Format tab \> Chart Elements arrow (Current Selection group)]{.uicontrol}.

![Current selection on the ribbon (tab Format). With the arrow you can select the desired chart part.](images/charts/chart-elements-currentselection.png){#fig-chart-elements-currentselection}

::: {.callout-note title="Selected chart elements"}
1.  The [Current Selection]{.uicontrol} always displays the selected element. In @fig-chart-elements-currentselection, the [Chart Area]{.uicontrol} is selected.

2.  Selected parts are marked with sizing handles.\
    ![](images/charts/chart-elements-sizinghandles.png)
:::

**Moving an element**

Select the element, hover the mouse pointer over its edge (not on a sizing handle), and drag it to the desired location.

**Changing dimensions**

When a resizable element is selected, it is surrounded by sizing handles. Drag a handle to change its height or width.

**Formatting an element**

Select the element and choose [Format tab \> Format Selection (Current Selection group)]{.uicontrol}. A task pane appears on the right with formatting options.

You can also access the task pane by right-clicking on the element and selecting [Format ...]{.uicontrol}.

**Applying a chart style**

Excel provides predefined formats called styles. You can apply a style using the ![](images/common/button-chart-styles.png) button.

**Deleting a chart**

Both embedded charts and chart sheets can be easily removed.

Choose one of the following options:

-   Embedded chart: Select the chart and press the [Delete]{.uicontrol} key.

-   Chart Sheet: Right-click on the chart sheet's tab and select [Delete]{.uicontrol} from the shortcut menu.

## Switching Rows/Columns {#sec-charts-switch-dataranges}

The source data for a chart can be arranged either with data series in columns or rows. You can easily switch between these arrangements.

In the bakery's sales data (@fig-bakery-data), the annual data is in columns, and the quarterly data is in rows. You can compare years, with each year as a data series (@fig-chart-recommended), or compare quarters, with each quarter as a data series. When creating the chart, Excel tries to guess whether the columns or rows should be the data series. If Excel's choice is incorrect, you can easily change it.

::: {#prp-charts-switch-dataranges}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

1.  Open the file.

2.  Select the chart by clicking within the chart area.

A double border appears around the chart, and the mouse cursor changes to a cross with arrows ![](images/common/mouse-pointer-drag.png).

3.  Choose [Chart Design \> Switch Row/Column (Data group)]{.uicontrol}.

![Sales bakery, now with quarters (rows) as data series.](images/charts/bakery-column-quarters.png){#fig-bakery-column-quarters}
:::

## Changing Chart Location {#sec-charts-move}

You can create a chart on the same worksheet as the data (an **embedded chart**) or on a separate sheet that contains only the chart (a **chart sheet**). You can move an embedded chart by dragging it or move it to a chart sheet. This task demonstrates both methods.

:::: {#prp-charts-move}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

1.  Open the file.

2.  Select the chart by clicking within the chart area.

A double border appears around the chart, and the mouse cursor changes to a cross with arrows ![](images/common/mouse-pointer-drag.png).

3.  Press the left mouse button and drag the chart to the desired location.

::: {.callout-tip title="Snap to grid"}
Holding down the [Alt]{.uicontrol} key while dragging snaps the chart to the worksheet's gridlines.
:::

4.  Click outside the chart in the worksheet to deselect it.

5.  elect the chart again and choose [Chart Design \> Move Chart (Location group)]{.uicontrol}.

![Dialog box Move Chart.](images/charts/chart-move-window.png){#fig-chart-move-window}

6.  Select [New sheet]{.uicontrol}, name the new sheet, and click [OK]{.uicontrol}.

You can also move a chart from a chart sheet to embed it in a worksheet.

7.  Select the chart in the chart sheet.

8.  Choose [Chart Design \> Move Chart (Location group)]{.uicontrol}.

9.  Select [Object in]{.uicontrol}, choose the worksheet, and click [OK]{.uicontrol}.

The worksheet with the embedded chart is displayed and the chart sheet is deleted.
::::

## Resizing Charts {#sec-charts-dimensions}

You can change a chart's size by dragging its sizing handles. These handles may appear as small circles, squares, or dots. When an object is selected, a rectangular frame outlines it, and sizing handles appear at each corner and the middle of each side. Corner handles allow dragging in any direction, while middle handles restrict dragging to horizontal or vertical directions. You can enlarge or shrink the chart or specify exact dimensions.

:::: {#prp-charts-dimensions}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

1.  Open the file.

2.  Select the chart by clicking within the chart area.

3.  Hover the mouse pointer over a corner point until it turns into a double-headed arrow ![](images/common/sizing-handle-diagonal.png).

4.  Click the left mouse button and drag to the desired size.

::: {.callout-tip title="Snap to grid"}
Holding down the [Alt]{.uicontrol} key while dragging snaps the chart to the worksheet's gridlines.
:::

To specify exact dimensions:

5.  Select the chart.

6.  Choose [Format \> Height / Width (Size group)]{.uicontrol}.

![Height and width of a chart.](images/charts/chart-dimensions.png){#fig-chart-dimensions}

7.  Enter the desired dimensions.

8.  Click outside the chart in the worksheet to deselect it.
::::

## Chart Title {#sec-charts-title}

When you create a chart, the title appears above it by default. You can select the title text and type your desired title, hide the title, or change its location.

:::: {#prp-charts-title}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

1.  Open the file.

2.  Select the chart.

3.  Choose [Chart Design \> Add Chart Element (Chart Layouts groep) \> Chart Title]{.uicontrol}.

![Options chart title.](images/charts/chart-title-options.png){#fig-chart-title-options}

4.  Choose [Above Chart]{.uicontrol}.

5.  Double-click the title text "Chart Title" and replace it with your own text for example "Sales Bakery".

::: {.callout-tip title="Chart title options"}
-   Selecting `None` removes the chart title.

-   You can move the chart title by dragging it.

-   You can format the title by selecting it, right-clicking, and choosing [Format Chart Title]{.uicontrol}.
:::
::::

:::: {#prp-charts-title2}
**Faster method**

1.  Select the chart.

2.  Click the Chart Element button ![](images/common/button-chart-elements.png) next to the chart to see a list of chart elements.

3.  Check [Chart Title]{.uicontrol} to immediately add the title to the chart.

![Selecting and deselecting chart elements .](images/charts/chart-elements-title.png){#fig-chart-elements-title}

::: {.callout-tip title="Chart elements options"}
-   An arrow next to a chart element indicates a drop-down menu with more options.

-   Checked elements are present in the chart. Unchecking an element removes it.
:::
::::

The following tasks will primarily use this faster method.

## Legend {#sec-charts-legend}

A legend identifies the patterns or colors representing data series in a chart. By default, if applicable, the legend appears on the right. You can remove it or drag it to another location.

::: {#prp-charts-legend}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

The [Chart elements]{.uicontrol} button ![](images/common/button-chart-elements.png) provides the following legend options:

-   Right
-   Top
-   Left
-   Bottom
-   None (by deselecting)

Like the chart title, you can drag and format a legend.

Experiment with the options.
:::

## Axis Titles {#sec-charts-axis-titles}

Axis titles are labels along the horizontal and vertical axes that help explain the data.

::: {#prp-charts-axis-titles}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

The [Chart elements]{.uicontrol} button ![](images/common/button-chart-elements.png) provides the following options for Axis Titles:

-   Primary Horizontal
-   Primary Vertical

1.  Add titles for both axes.

2.  Change the horizontal title in "Time".

3.  Change the vertical title to "Sales (x 1000)".
:::

## Data Labels {#sec-charts-data-labels}

Data labels are numbers at the data points that display the actual values.

::: {#prp-charts-data-labels}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

The following options are available for data labels:

![Options for data labels.](images/charts/datalabels-options.png){#fig-datalabels-options}

Experiment with these options. In @fig-bakery-datalabels) you see the display for the [Outside End]{.uicontrol} option.

![Data labels outside the columns display the sales for each quarter.](images/charts/bakery-datalabels.png){#fig-bakery-datalabels}
:::

## Gridlines {#sec-charts-gridlines}

Gridlines are horizontal and vertical lines that extend from the axes across the chart's plot area. They make the data easier to read by providing visual guides. You can display gridlines for major or minor intervals on the axes.

::: {#prp-charts-gridlines}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

The following options are available for gridlines:

![Options for grid lines.](images/charts/gridline-options.png){#fig-gridline-options}

Hover the mouse over the options to preview the results in the chart.
:::

## Plot Area {#sec-charts-plotarea}

The plot area is the rectangular area of the chart that displays the charted data. It is bounded by the axes and includes the axes but not the titles. You can format the plot area with a colored background and border.

::: {#prp-charts-plotarea}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

1.  Open the file.

2.  Select the plot area, right-click, and choose [Format Plot Area]{.uicontrol}. The [Format Plot Area]{.uicontrol} task pane appears.

3.  Open the [Fill]{.uicontrol} option and select [Solid fill]{.uicontrol} with a light gray color.

4.  Open the [Border]{.uicontrol} option and select [Solid line]{.uicontrol} with a black color

![Plot area with a background and border.](images/charts/bakery-plotarea.png){#fig-bakery-plotarea}
:::

## Adding and Removing Data Series {#sec-charts-data-source}

It's common to add a new data series to an existing chart or remove an old one. The procedure depends on whether the data to be added or removed is directly adjacent to the existing data set. You can easily determine this by selecting a chart; Excel will highlight the corresponding source data in the worksheet with colored boxes.

New series joins the highlighted area

:   Expand the data range by dragging the selection.

:   Delete a series at the beginning or end of the highlighted area by dragging.

New series does not join the highlighted area

:   Use the ***Select data source*** dialog box.

Both techniques are covered in this section.

::: {#prp-charts-data-source1}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

**Add a new 2011 data series by dragging**

1.  Open the file.

2.  Add the new data for year 2011 in the range E1:E5, as shown in @fig-bakery-data-2011).

![Bakery turnover data supplemented for the year 2011.](images/charts/bakery-data-2011.png){#fig-bakery-data-2011}

3.  Select the chart. The source data is selected on the worksheet, showing the sizing handles.

![Marked data source of the chart.](images/charts/bakery-data-marked.png){#fig-bakery-data-marked}

4.  Drag the lower-right sizing handle to include the new data.

![Bakery sales, extended with 2011 data.](images/charts/bakery-chart-2008-2011.png){#fig-bakery-chart-2008-2011}
:::

::: {#prp-charts-data-source2}
**Delete the 2008 dataset by dragging**

1.  Select the chart.

The source data is selected on the worksheet, showing the sizing handles.

![Marked data source 2008-2011.](images/charts/bakery-data2011-marked.png){#fig-bakery-data2011-marked}

2.  Drag the lower-left sizing handle to exclude the data for 2008.

![Chart with data series 2009-2011.](images/charts/bakery-chart-2009-2011.png){#fig-bakery-chart-2009-2011}
:::

:::: {#prp-charts-data-source3}
**Add the 2008 series and delete the 2011 series via the dialog box**

1.  Right-click on the chart and choose [Select Data...]{.uicontrol}.

::: {.callout-tip title="Alternative method"}
An alternative is [Chart Design tab \> Select Data (Data group)]{.uicontrol}.
:::

![Select Data Source.](images/charts/bakery-select-datasource.png){#fig-bakery-select-datasource}

2.  Click the [Add]{.uicontrol} button.

![Dialog box for adding a new series.](images/charts/chart-edit-series.png){#fig-chart-edit-series}

-   [Series name]{.uicontrol}: The cell that contains the name of the data series.
-   [Series values]{.uicontrol}: The cell range that contains the data values.

3.  Place the cursor in the [Series name]{.uicontrol} box and then select cell B1 in the worksheet.

4.  Select the contents of the [Series values]{.uicontrol} box and then select the range B2:B5 in the worksheet.

5.  Click [OK]{.uicontrol}.

The 2008 data series is added to the chart and the Select Data Source dialog box. However, it's added at the end, making the order illogical.

![Dialog box data source. Here you can change the order of the data series.](images/charts/bakery-datasource-moveup.png){#fig-bakery-datasource-moveup}

6.  In the dialog box, select the [2008]{.uicontrol} series and click the [Move Up]{.uicontrol} arrow three times to make it the first series.

7.  In the Select Data Source dialog box, select the [2011]{.uicontrol} series and click the [Remove]{.uicontrol} button.

The 2011 data series is removed, restoring the initial situation with the 2008-2010 data series.
::::

## Task: Changing Chart Type {#sec-charts-type-change}

You can easily change the chart type. This task demonstrates changing a column chart to a line chart.

::: {#prp-charts-type-change}
[File]{.smallcaps}: `Bakery_Chart.xlsx`

1.  Open the file.

2.  Select the chart.

3.  Choose [Chart Design \> Change Chart Type (Type group)]{.uicontrol}.

![Change chart type.](images/charts/chart-type-change.png){#fig-chart-type-change}

4.  Choose the [Line with Markers]{.uicontrol} type and click [OK]{.uicontrol}.

![Line chart with markers.](images/charts/bakery-line.png){#fig-bakery-line}
:::

## Case Share Prices {#sec-charts-case}

[File]{.smallcaps}: `Stocks.xlsx`

@fig-case-data shows the average monthly share prices of four companies for January through March:

![Average monthly share prices.](images/charts/case-data.png){#fig-case-data}

Create a line chart from this data.

Later, the entries for April are published:

![Average prices for April.](images/charts/case-data-april.png){#fig-case-data-april}

Add this data to the chart.

The final result should look like this

![Chart with average monthly share prices.](images/charts/case-chart.png){#fig-case-chart}

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

-   The chart type is a line chart (line with markers).
-   When you create a chart, Excel might use the rows as data series instead of the columns. Correct this by switching rows/columns.
-   Add a chart title. Format the title text in Palatino, 14 pt, bold.
-   Add a vertical axis title.
-   Scale the vertical axis so that the primary unit is 10.
-   Add a legend below the horizontal axis and stretch the legend to display all texts in one row.
-   Add horizontal dotted gridlines.
-   Chart dimensions: width = 4" and height = 4"

`r unhide()`
:::

## Exercises {#sec-charts-exercises}

:::: {#exr-graf001}
**Third-degree chart (graf001)**

Create the following chart. Make the layout as similar as possible.

![](images/charts/graf001-chart.png)

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

First, create two columns in a worksheet with values for X and calculated values for Y. Create a scatter chart and apply the formatting.

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

::: {#exr-graf002}
**Travel bookings (graf002)**

[File]{.smallcaps}: `Graf002.xlsx`

The following table lists the travel booking data for the years 2008-2010.

![](images/charts/graf002-data.png)

Enter the data in a worksheet and use formulas for the totals. Then, create the following chart.

![](images/charts/graf002-chart.png)

:::

::: {#exr-graf003}
**Sales figures (graf003)**

[File]{.smallcaps}: `Graf003.xlsx`

The following table shows the estimated and actual sales figures per quarter for 2010, as well as the difference between them. The average percentage of the differences is also calculated.

![](images/charts/graf003-data.png)

Enter the data in a worksheet and use formulas for the differences and the average. Then, create the following chart.

![](images/charts/graf003-chart.png)

:::

::: {#exr-graf004}
**Age structure (graf004)**

[File]{.smallcaps}: `Graf004.xlsx`

The following table lists the percentages of the Dutch population's age structure for the years 1950-2010, divided into nine age classes (source: CBS).

![](images/charts/graf004-data.png)

Create the following chart from this data.

![](images/charts/graf004-chart.png)

:::

:::: {#exr-graf005}
**Relationship between production time and cost (graf005)**

[File]{.smallcaps}:`Graf005.xlsx`

A toy manufacturing company suspects that the price of toys largely depends on production time. To investigate this, they measured the production time of the toys. The results and associated costs are shown in the following figure.

![](images/charts/graf005-data.png)

1.  Create a chart that plots cost against production time (the independent variable).

2.  Add a linear trend line to the chart, and include the trend line's equation.

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

![](images/charts/graf005-chart.png)

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

::: {#exr-graf006}
**Counting agarics (graf006)**

[File]{.smallcaps}: `Graf006.xlsx`

The number of agarics in a given area is counted annually. The following table shows the number of agarics per hectare for several years.

![](images/charts/graf006-data.png)

Create the following chart from this data.

![](images/charts/graf006-chart.png)

:::