Page 7 / 9

Charts

Next, let's familiarize ourselves with the diagrams. You can already open the Chart sheet from your Excel practice workbook.

You can use charts to illustrate the information presented. A chart makes it easier to compare and relate data. A suitable chart gives a quick overview of the data but also contains additional information so that you get a good mental image of the data.

Kuvassa on valikko, jossa on paljon erilaisia kaavioita, mistä valita

In Excel, much like Word and PowerPoint, you can use many different chart types.

The Objective

In the exercise, we will create a chart. Its type will end up being Stacked Column, where we have added data labels. Note that the value points have been formatted to use a thousands separator, but not decimals.

Video: Formatting with a thousands separator

Task: Add thousands separators to the value formatting

Navigate to the Chart sheet.

First, format the thousands separator for the values entered on the sheet.

When the values are large, the thousands separator design makes them easier to read.

You will also notice during the task that the formatting you want for the chart should also be added to the sheet.

Task: Fix ###### cells

If Excel doesn't have the necessary space to show the cell using the desired formatting, the content will appear as ######. An example is where the value 123456789 has been set to use the thousands separator.

#### Insufficient space	#### The column width extended
On luotu pylväskaavio	On luotu pylväskaavio

Fix the sheet Chart by extending the column width until the ###### becomes a numeric value.

Mac: Recommended charts and clustered column chart

The macOS version of Excel differs slightly from the Windows version regarding recommended charts.

In addition, the charts are named slightly differently.

Task: Insert chart: column chart

A handy way to create a chart is Recommended Charts, which will provide chart types that match the data you select. Next, let's practice using the tool.

Make a column chart by month and region, based on the data from the Chart spreadsheet.

Model:

Info: All the charts

Kaikki kaaviot -välilehti sijaitsee Lisää kaavio -ikkunan ylhäällä

If you can't find what you're looking for in the list of recommendations, you can choose from a wider range of alternatives on the tab All Charts tab.

Task: Move the chart and increase its size

The chart is placed on the same sheet where the data is. Lay out the diagram neatly. Model:

Task: Give the chart a title

Give the chart the title “Sales tracking”. Model:

Mac: Changing chart type and stacked column chart

On the Mac version, finding a stacked column chart is a bit more difficult because the All Charts window is missing.

Task: Change the chart type

Continue with the task in the Chart table.

Change the column chart you made to the chart type Stacked Column so that the columns are for months and they are split into parts for regions.

Model:

Mac: Adding data labels

In the Mac version, there are no buttons associated with the chart, so the data labels are added from the selection ribbon.

Video: Adding Data Labels

Task: Define data labels in the chart

The next step is to add data labels to the chart.

Model:

Task: Format the data labels

Format the data labels by removing decimals, so that the numbers look neater on the chart.

Model:

Info: Chart filters

Suodattimien alhaalla ovat painikkeet Käytä ja Valitse tiedot

You can filter out some of the data from the chart.

Click on the Apply button to enable filtering.

The Select Data button opens a window where you can change the position of the source data, change the order of the data, delete some data or change the row/column direction of the data in the chart.

Keskellä Tietolähteen valitseminen -ikkunaa on painike Vaihda rivi tai sarake

Extra task: Change chart colors

Continue with the task in the Chart sheet.

Mac: Deletion of data

Unfortunately, the data deletion discussed in the following task is so cumbersome to use on the Mac version that we do not recommend using it at all.

The operation itself can be found in the same place with the same name. But while in the Windows version the data can be hidden out of sight, in the Mac version the data is permanently deleted. It is no longer the same handy tool, and we recommend that you skip the next additional task.

Extra task: Remove data for Eastern Finland from the chart

Let's take a look at how you can manage the data presented in the chart. Sometimes it is handy to delete individual records temporarily.

Remove the data for Eastern Finland from the chart. Model:

Extra task: Change rows to columns and vice versa

Let's flip the chart so that everything that was in the rows goes in the columns and vice versa. In practice, each column represents a region and the parts of a column represent a month. Model:

Extra task: Insert a pie chart

If you wish, you can continue the task in the Chart table by adding a pie chart. The task is voluntary.

Make a pie chart of the 1st quarter sales by region based on the data in the “Chart” sheet of the Excel workbook and write “Sales by region in the 1st quarter” as the title.

The instructions below walk you through the process of creating a pie chart in six steps

Choice of area
Creating a pie chart
Deleting the legend
Adding data labels
Moving data labels outside the chart
Changing the title

Model:

Kuvassa on käyttäjän maalaamista erillään olevista tiedoista muodostunut ympyräkaavio

Nice

Select a region

Select the data of which you will make a chart. The area to be selected is “disconnected”, i.e. it is in two different places in the sheet. We can paint disconnected areas using the Ctrl key.

First, paint the first area as usual.

Then press the Ctrl key to the bottom and paint the next area while holding down the key. Your choice should look like the following:

Creating a pie chart

After painting, the chart is inserted using the Charts group on the Insert tab. You can then place it in the appropriate position as described earlier.

Deleting a legend

Now the chart has been created. Next, we format the chart. First, we remove the legends at the bottom of the chart. Legends are removed using the item Chart Elements. In the menu that appears, deselect Legend.

Selite on kolmas valintaruutu kaavion osissa

Adding data labels

Data labels are added using the item Chart Elements. Click on the arrowhead on the right of the option Data Labels to open a menu. Select from the menu the option More Options….

Arvopisteiden otsikot on valikossa toinen ja Lisää asetuksia on valikossa alin

In the menu that opens on the page, make sure you are at Label Options. Specify here Label Contains so that Category Name and Percentage are selected.

Luokan nimi pikanäppäin on u ja prosentin pikanäppäin on r

Moving the data labels outside the chart

You can move the value points off the chart into “speech bubbles” via the Chart Elements menu. In the Data Labels menu, select Data Callout.

The data caption is the fifth element of the Scores headings menu.

Changing the title

You can change the title of the chart by clicking on it and entering a new name overwriting the old one. Write the title “Sales by region in the 1st quarter”.

Info: Deleting a chart

Once a chart is selected, it can be deleted by pressing the Delete key.

You are now fully familiar with Excel's charting function. Charts are a useful tool for visualizing information, both for yourself and for others.

Shall we move on?

Mark as complete: