Create and edit chart in Excel

The goal of a presentation is to inform the audience and, at best, to convince them of your idea. This usually includes numbers. To create a successful presentation, you need to see your slides from your audience’s perspective*. This means that they are seeing the numbers and information for the first time and have no desire or patience to understand tedious jumbles of numbers. When used correctly, charts can help illustrate statements, trends, or relationships quickly and easily.

Create a line chart

first we mark the area to be represented in a chart. In our example, we have a chart of invented rents per square meter in three different cities. We compare four measurement points to sketch a progression. These progressions are to be visualized with a line chart.

After the data has been selected, we click on the small black arrow next to the button for line charts in the Insert tab in the Charts section.

Tip: With the key combination Alt and F1 we can immediately create a diagram

A menu opens with an overview of line and area diagrams. For our example, we select the first one at the top left.

If the data is arranged as in the example above, Excel automatically recognizes the data structure and forms a chart.

Change diagram type

Data can be represented in many ways. To change the chart type, we click the Change Chart Type button on the Design tab in the Type area.

Which diagram do I use? Read this guide here

The Change Chart Type dialog box opens, which consists of two sections. On the left side all available chart types are listed and on the larger right side the subcategories of the selected chart type. In our example, we select the Column charts (green arrow) and then click on the Grouped columns (red arrow). Then we click OK to confirm.

Very important: With diagrams, the overview and quick understanding of the figures is very important. Therefore, 3D variants should be avoided almost without exception. The findings should attract attention, not playful 3D graphics.

Very good! The line chart has become a bar chart.

Change row and column

In the previous charts in the lower x-axis listed the individual quarters and the cities were given their own color within each quarter. We would rather have the individual cities as a grouping and compare them. To achieve this, on the Design tab in the Data group, click on the Switch row/column button.

Excel immediately adjusts the chart and now shows us this view:

Add diagram elements

Excel, PowerPoint and Word have reordered chart elements since the 2016 version. We get access to the individual chart elements in the Design tab in the Chart Layouts > Add Chart Element area.

After clicking the button we see a menu with the individual diagram elements.

Data labels

The second, perhaps faster way to insert the chart elements is as follows: we select the chart with one click and click again on the green cross to the right of the chart. In our example we add data labels. Data labels are useful to display the individual, exact values within the number series. To activate them, we click on the Data Labels option in the Chart Elements menu. Then there are several options to determine the position of the labels. In our example, Centered is selected.

This is what data labels look like in the chart. They are centered above the columns.

Short digression: Data labels without vertical axis

For individual data labels, the y-axis can be dispensed with because the values are displayed directly. By omitting the y-axis, the diagram becomes clearer without sacrificing information. How do we proceed? First we click on the vertical axis to select it. Then we right-click on the axis and select the Delete option. Alternatively, the marked axis can be removed with the DEL key on the keyboard.

And this is what the diagram looks like without the y-axis.

Legend positioning

A legend is a diagram element that is located within a diagram and describes the individual elements similar to a map. This helps especially with more complex data sets that need to be explained a little more precisely in order to be able to assign them better. To insert a legend into the chart, we select the Legend option in the Chart Elements menu. Then we determine the position of the legend. In our example we select Right.

Note: To remove the legend we click on the checkmark next to the Legend element in the diagram elements menu. When the checkmark disappears, the corresponding element disappears as well.

The diagram with legend.

Other diagram elements

MS Office offers many other options that can be found in the chart elements menu. For example, data table, error indicators, trend line, multiple axes, etc. Just try the options.

Select data

The core of any chart is the numbers that are visualized. At the beginning you saw how data is marked and a chart is created. In this chapter we will adjust the data by expanding, reducing and relabeling. In our example, we will add another column to our chart: column E, Frankfurt.

Note: Make sure that the data is as complete and correct as possible. Your claim should always be to be able to at least roughly classify every single number in the data set.

To expand our data set, we click the Select Data button in the Design tab (Chart Tools) Data section.

A dialog box opens, which is the starting point of the adjustments in the dataset. We click on the Add buttons (red arrow).

In the Edit data series dialog box, we first select the series name. That is, we explain to the program how the data series should be named. For this reason we click on the marked symbol (red arrow).

Then the dialog box minimizes and we click on the cell that determines the name of the row. In our example, the cell E1.

Next, we select the appropriate values for the number series. For this reason we click on the button (red arrow).

Now we select a range. In our example E2:E5.

Note: If we select only one cell, the record will consist of only one value

Then we click OK to add the new data to our record. We also see that on the left side our new data is already entered (Frankurt).

Horizontal axis labeling

Next we change the horizontal axis label. Often Excel suggests us a consecutive numbering (1,2,3,etc.), which we can adjust by more precise values. We click once on the Edit button (red arrow).

In the Axis Labels dialog box, we click the button again (red arrow).

Now we mark the area. The axis labeling starts from A2 and ends A5.

After the area is set, we click OK to confirm our changes.

Und so sehen unsere neuen horizontalen Achsenbeschriftungen aus: Quartal 1 bis Quartal 4. Mit einem finalen Klick auf OK übertragen wir die Änderungen auf das Diagramm.

Done. The chart now has the data series Frankfurt (our column D)

Excel offers numerous possibilities for the use of charts. This tutorial shows how to create and customize charts. The look and layout of a chart are very important, but what is even more important is the core message. What do you want to say with this data? Even before you start, think about what your core message and goal is – what do you want your numbers to illustrate? What recommendations for action can you give? What is the result of your analyses?

It is best to imagine that you are going to present this slide in front of a critical and knowledgeable audience and while you are showing this diagram someone asks “And now?” (so what?). Try to have an answer to this question and the answer to the question “so what?” already on the slide.

Image source below: kasto[at]123rf_com