Creating graphs

It is often the case that we want to display the information in a spreadsheet as a graph (e.g. a bar chart, a curve chart or a pie chart) to make it easier to grasp. On this page, we will look at how you can convert the information in an Excel spreadsheet to graphs, i.e. how to:

  • create
  • move
  • resize and
  • modify graphs.

Creating a graph

To create a graph, first select the cell area you want to make into a graph (including the row and column names so that they are entered into the graph as titles). If necessary, use the feature for ‘disconnected area selection’ to avoid unnecessary values in your graph; you can select areas that are not connected to each other by pressing down Ctrl while selecting the area (the painted area have to be the same size, though, i.e. there must be the same number of rows). In the image below, one column has been left out of the selection (the selected area is shown in blue).

office2013_excel_graph1

When you have selected the area in the spreadsheet, select the graph type you want from the drop-down menu in the Charts group on the Insert tab. You can also select one of Excel’s recommendations by clicking Recommended Charts. The example below shows a three-dimensional column chart.

excel2016_graph2

After you have selected the necessary data and chart type, the program will bring up a chart with default settings that you can edit. A new menu, Chart Tools, will also appear in the ribbon menu. It contains tabs with various tools for editing your chart according to your needs (see image below). The chart-tool menu is visible whenever the chart is active. This means that if you click on the document outside the chart, the menu will disappear, and if you click on the chart again, it will reappear.

excel2016_graph3

Editing charts

When you have created a chart you can edit it to your taste. You can change headers and captions by clicking on them and writing the new entry instead. You can move mobile elements (such as headers and captions) manually by presing down the primary mouse button and dragging them to another place in the chart. You can also change the font, point size, and other text features by selecting the chart or the part of it that you want to alter and make the changes with the tools on the Home tab. To change the layout and data presentation format of the chart itself, use the tools in the Chart Tools menu.

Changing the data-presentation format

You can use the Change Chart Type tool in the Type group on the Design tab to alter the type of chart if you think e.g. a pie chart would illustrate your data better. With the Switch Row/Column tool in the Data group, you can switch the data between the x and y axes (see example below).

You can change headers and captions in the chart through the menus in the Labels group on the Layout tab. The Axes group allows you to set the x and y axes as well as gridlines to make your chart more readable.

You can also change the headings and legends displayed in your chart: when you click the worksheet to activate it, buttons such as those found below appear next to it. Clicking the plus icon opens the Chart elements menu for manually changing the presentation method.

excel2016_graph4

The Axes toolbar group allows you to specify the desired x and y axes and gridlines to clarify your chart. You can also delete them completely from these menus. You can also add lines and bars to make it easier to analyse the data in your chart.

Edit chart layout

More editing alternatives available are available on the Design tab. Select the part of the chart that you want to edit and click on the Format Selection button in the Current Selection group to open a window where you can  make detailed changes to the formatting of the selected section. Use the menus in the Insert shapes group to add pictures, shapes or text boxes to your chart. Use the tools in the Background group to edit the background settings of the chart, e.g. the use of fillers and 3D effects.

The last tab of the chart tools, Format, contains tools for formatting the chart. The menus in the Shape Styles group allow you to change the colour of the chart sides or background, or select one of the ready-made themes. With the tools in the WordArt Styles group, you can add shading, colours and other effects to the captions, or choose among the ready alternatives.

To save time on formatting the layout of the chart you can use one of the ready layout formats in Excel. You can change the colour theme of your chart by choosing one of the alternatives in the Chart Styles menu on the Design tab. Use the arrow buttons to the right of the list to scroll down the list, or open the whole list by clicking on the bottom button (see image below).

excel2016_graph5

Moving a chart

If you created a graph on an existing spreadsheet, it may have been placed on top of the spreadsheet data. In such cases, you need to move the graph. You can move a chart inside a program window in Excel and other programs in the following way: activate the chart by clicking on it once. A black border will appear around the chart, with modifying handles at each corner and on each side, as a sign that the chart has been selected. Take hold of the chart with the mouse and drag it to your preferred position.

You can use the tools in the Arrange group on the Format tab to adjust the alignment of the chart and e.g. the order of elements that overlap.

You can move the chart to another document or onto its own worksheet by clicking on the Move Chart button on the Design tab. Select New shhet if you want to move the chart into another spreadsheet (you can name the new sheet in the box beside the selection), and As object in if you want to embed is as an object in an existing spreadsheet. Select the spreadsheet you want to embed it in. Finally, click on OK. Excel will move your chart according to your choise, either to another worksheet or as an image into another spreadsheet.

Resizing

The size of a chart is not always the right one for presenting the data. This means that you will often need to resize the chart after creating it.

To resize a chart in Excel and other programs, move the cursor to a modifying handle of the chart. When the cursor changes into a two-headed arrow, press down the mouse button and drag the chart in the direction you want to resize it.