Version 1.3
Graphs and charts in Microsoft Excel – an overview by Mike Griffiths
Contents
1Notes on Style
2Overview of Excel
3Graphs in Excel – the basics
4Scatterplots
5Introduction to formatting, and other changes to graphs
6Copying graphs to Word documents
7Changing the data source
8More than one data series: line graphs
9More than one data series/independent variable: bar charts
10Combination graphs
11Two value axes
12Error bars
13Histograms
14Survey results
1Notes on Style
This document is intended as a guide to techniques in Excel, rather than as a style guide. Therefore, the illustrations do not necessarily show graphs that are finished ready for publication.
All publishers, and markers of student work, will have certain expectations of a finished graph, e.g.
- All axes should be clearly labelled
- Units should be shown.
For historical reasons, tables and figures are usually considered to be two separate series within a document.
- Tables are made up of words and numbers, which a typesetter would have been able to set up. They are labelled Table xxx, starting at 1 with the first table and going up in order. After the table number there should be a caption, explaining what the table shows. Table numbers and captions go above the table they refer to.
- Figures are graphs or other illustrations, which would have had to be given to the type setter on a metal plate to be inserted into the document. They are labelled Figure xxx, starting at 1 with the first figure and going up in order. After the figure number there should be a caption, explaining what the figure shows. Figure numbers and captions go below the figure they refer to. Therefore, it is usually easier to add them in your word processor, rather than in Excel.
For more details on the style that is needed for your graphs (or the rest of your document) refer to the style guide issued by your publisher, or in the case of students any instructions given by your instructor. (For psychologists, the ultimate authority is likely to be the latest version of the Publication Manual of the American Psychological Association).
2Overview of Excel
Excel is a versatile tool for calculation and data manipulation. However, in this document I will be focussing on another of its strengths – graphs. It is very versatile in this area too, featuring a wide range of graph types whose formats can be changed in all sorts of ways. Note that Excel calls graphs ‘charts’, so I will use the terms interchangeably.
But first, I should say something about Excel itself. Excel is rather like a blank sheet of paper – how the data are laid out is up to you. So if, for example, you want titles, it is up to you to put them in, wherever you want them. The versatility of Excel also means it is easy to make mistakes. So if a value is described on the spreadsheet as being the mean, that is because you have marked it as being the mean – Excel will have no idea whether you made a mistake in entering it (or in entering the figures you used to calculate it).
A useful feature of Excel is that (by and large) it is dynamic. That is to say, if you change a number on the sheet, Excel will automatically change anything that is calculated from that number. That includes graphs.
Like most programmes, Excel has an ‘undo’ facility. Go to Edit – Undo, or click on the button. Note the little arrow at the right of the ‘Undo’ icon – if you click on this you can go back and undo several commands at once.
Finally, remember that a good way to learn about this sort of programme is to experiment! If you are doing so with important data, do remember to keep a backup copy of your file under a different name…
3Graphs in Excel – the basics
Suppose that we want to print a graph of rainfall data over several months. Open Excel and enter the data, as in figure 1.
Highlight the data (e.g. by clicking on the top left hand cell, holding down the left mouse button, moving to the bottom right cell and releasing). Click on the ‘Chart wizard’ icon (). You can just click ‘finish’ and you get a reasonable chart (as in Figure 2) from the default settings – or click ‘next’ repeatedly to go through the Chart Wizard and see some of the things you can change. Notice that the graph in figure 2 is called a ‘column’ chart in Excel, which reserves the name ‘bar chart’ for one in which the bars are horizontal.
Figure 1: sample data for basic graphs
You can repeat this, but choosing other graph types in step 1 of the Chart Wizard. (Or if you click on the Chart Wizard icon with the existing graph selected, you can change that graph to a different type.) Some of the other basic graphs you can create are shown in figure 3.
Figure 2: default bar chart for the data in Figure 1
(a) line chart / (b) pie chartFigure 3: other default charts from the data in Figure 1
4Scatterplots
Mike GriffithsPage 1 of 16
Another popular kind of graph is a scatterplot. Suppose you had collected the data in figure 4, and you wanted to plot one variable against the other. Select the data on the Excel spreadsheet, click on the Chart Wizard, and in Step 1 select ‘XY (Scatter)’. The default output is shown in figure 5. It shows each of the cases in the data: for example the extreme left point shows that the person who studied for 0 hours scored 5 on the test.
It is simple to add a trend line. Click once on the chart, and on the drop-down menu at the top of the screen go to Chart – Add Trendline… Click on ‘OK’ and you get the chart shown in figure 6. The trend line can be edited (e.g. made less bold) by double-clicking on it to bring up the Format Trendline dialogue box.
Figure 4: data for scatterplot
Mike GriffithsPage 1 of 16
Figure 5: default output for data in figure 4.
Figure 6: the chart from figure 5 after adding a default trend line
5Introduction to formatting, and other changes to graphs
You can move the graph around the spreadsheet. Left-click with the mouse on the white area, then move the mouse with the button held down (i.e. ‘drag’ the graph). You can resize the graph by clicking once on the white area, then dragging one of the little black boxes which appear on the edges.
You can change some aspects of the graph just by changing the original data in the spreadsheet. For example, on the graphs in section 2 you can change ‘inches’ to ‘feet’, ‘June’ to ‘Jul’, or the numbers.
Most other formatting is done by one of the following means. (Often, there is more than one way of doing it).
- Select the graph (by left-clicking on the white area) and click on the drop-down ‘Chart’ menu at the top of the screen.
- Hover the mouse over a part of the graph so that the screen tip (the words that appear in a yellow box) refer to the thing you want to change. Double click, or right click. Usually, double-clicking will bring up a dialogue box relevant to that feature of the graph, and right-clicking will bring up a drop-down menu which includes a link to the same dialogue box.
Some titles can be changed simply by clicking on them, then typing in the changed wording that you want. However, the most flexible way of changing the wording of titles and axis labels on the graph is to select the graph, and then click on the ‘Chart’ drop-down menu at the top of the screen, then ‘chart options’, then choose the ‘Titles’ tab. Make your changes, and click on ‘OK’. To change the typeface or font size of any of the wording, right-click on it, click on ‘Format Chart title’ [or whatever the item is] and then on the ‘Font’ tab. Make your changes and click on ‘OK’.
You can remove the grey background (or change it to a brighter colour, e.g. for a poster). Hover the mouse over it so that the screen tip reads ‘plot area’. Right click, and on the pop-up menu click on ‘Format plot area’. Under ‘area’ click on ‘none’ (for a white background), or the colour you want. Click on ‘OK’.
You might also want to remove the horizontal lines. Hover the mouse so that the screen tip reads ‘Value Axis Major Gridlines’, right click, and choose ‘clear’.
To change bars to a suitable format for a black-and-white report, double-click on one of them. In the dialogue box that comes up, choose the ‘Patterns’ tab. Click on ‘Fill effects…’. The ‘Fill effects’ dialogue box comes up. Click on the ‘Pattern’ tab. On the drop-down boxes at the bottom, choose black for the foreground and white for the background. Then click on the pattern that you want, and click on ‘OK’.
In a line graph, we can change the format (e.g. colour) of the line(s) and data points in just the same way. Double-click on the appropriate line to bring up a ‘Format Data point’ dialogue box. Despite its name, this has options to re-format the line as well as the data markers.
On a simple graph such as the one in figure 2, we do not need the right-hand box that says ‘inches’. This is called a ‘legend’. Right-click on it, and you can remove it by selecting ‘clear’. If we had chosen ‘Format legend’ and the ‘Placement’ tab, we could have moved it elsewhere on the graph, such as to the bottom.
Various changes can be made to the axes of the graph. For example, to change the vertical axis, hover the mouse so that the screen tip reads ‘Value Axis’. Double-click. Under the ‘Scale’ tab, you can change the minimum and maximum values, and change the intervals of the figures on the graph. For example, to show only whole inches, change ‘major unit’ to 1.
You may find that not all of the description you need (e.g. names of the months in this example) is shown on the axes, especially if you have reduced the size of the graph. This can usually be cured by reducing the font size.
Some of the above changes are illustrated in figure 7.
Figure 7: Figure 2 after editing.
6Copying graphs to Word documents
Copying a graph into a Word document is straightforward.
- Right click on the Excel graph and choose ‘Copy’
- Open Word, and from the drop-down menu select ‘Edit’ and ‘Paste’.
However, there are a couple of tips worth noting, whether you are pasting graphs or any other kind of objects into Word.
Keeping the object from moving around. As soon as you have pasted the object, right-click on it and choose ‘Format Object’ (or Format Picture, as appropriate). Go to the Layout tab, and under ‘Wrapping style’ choose ‘In line with text’ (unless you know what you are doing and really want one of the other options). This ensures that the position of the object remains fixed in relation to your text.
Layout. If you have layout problems in Word, the answer is usually to use a table. Suppose you want to insert two graphs side by side, as in figure 3 above.
- On the drop-down menus select Table, Insert, Table. The ‘Insert Table’ dialogue box appears. Choose 2 rows and 2 columns. Click on ‘OK’.
- Keep the insertion point (cursor) inside the table. On the drop-down menus, select Format, Borders and Shading. When the dialogue box appears, check that the Borders tab is selected, and that the ‘Apply to’ drop-down box at bottom right reads ‘Table’. Click on ‘None’ at top left, and ‘OK’. The black border disappears from the table, leaving just a light grey border to help you lay out your material. This will not be printed.
- Select all the cells in the table and centre the text (via the Format drop-down menu or using the ‘Center’ format box, )
- Paste a graph into each of the top cells. Do not forget to change the Wrapping style (see previous paragraph). Re-size each graph as required by clicking on it and dragging the little black squares at the corners.
- Appropriate headings (or subheadings) can be put into the bottom two cells of the table.
7Changing the data source
Sometimes you might want to change the data source. Examples include:
- If you had created the graph in section 7 below, but had entered years (e.g. ‘2005’ and ‘2006’) in place of ‘Yr 1’ and ‘Yr 2’, Excel would have assumed that 2005 and 2006 were data to be plotted on the graph. You would need to correct this.
- If you have created a graph that you like, you might want to copy it and use exactly the same format for a different set of data.
Select the graph (e.g. by clicking on the white area) and click on the drop-down ‘Chart’ menu. Click on ‘Source data’. The ‘Data range’ tab gives you the option to change the data (for example if you have copied the graph and want to use different source data). (Notice the icon. If you click on this, the sheet re-appears, so you can select cells using the mouse or keyboard).
The ‘Data range’ tab also allows you to swap round the rows and columns, if Excel has wrongly guessed which way round you want the graph.
The ‘Series’ tab is more complicated, but more flexible. It allows you to specify exactly which parts of the spreadsheet represent the names to be used in the spreadsheet, and which represent the x and y values of each data series.
8More than one data series: line graphs
Creating a line graph with more than one data series is much the same as when there is only one. If you enter and select the data in figure 8, click on the Chart Wizard icon and select a line graph, the default output is shown in figure 9.
Figure 8: data for figure 9.
Figure 9: default line chart for the data in figure 8.
9More than one data series/independent variable: bar charts
If you want a bar chart for the kind of data shown in figure 10, again you follow a similar process to that for a simple bar chart. If you simply select cells A1:C4 and click on all the defaults in the Chart Wizard, you will get the chart shown in figure 11(a).
Sometimes you want the chart the other way round, as in figure 11(b). If you are creating the chart from scratch, this is done in step 2 of the Chart Wizard. Under ‘Series in’, swap from ‘Rows’ to ‘Columns’ or vice versa. Alternatively, if you want to modify a chart you have already created, right-click it and select ‘Source Data’. Again, swap between Rows and Columns. In either case you get a preview to show what it will be like.
Figure 10: data for the charts in figure 11.
Pages of two newspapers devoted to various kinds of material.
(a) default / (b) transposedFigure 11: two way bar charts
10Combination graphs
Suppose you wanted to present the data in figure 12, where the two data series represent different variables. You might want to emphasise this by showing them in different ways, as in figure 13. In step 1 of the Chart Wizard, select Custom Types. Scroll down to find ‘Line - Column’. Select this, and the default result is shown in Figure 13.
Figure 12: data for combination chart (figure 13)
Figure 13: combination chart.
11Two value axes
If you are presenting two sets of data on one graph, they might have quite different sizes, as in figure 14. You then might want a graph showing two scales, as in Figure 15.
Figure 14: data for combination chart (figure 15)
To create this from scratch, follow section 9 above, but choose ‘Line - Column on 2 Axes’ in step 1 of the Chart Wizard. Figure 15 shows the default chart.
It is also possible to edit an existing graph. Double-click on one of the data series on the graph, and the ‘Format Data Series’ dialogue box opens. Click on the ‘Axis’ tab and under ‘Plot series on’ at the top, select ‘Secondary axis’.
Figure 15: Combination chart with two axes
In either case you might want to add the words “(left hand scale)” after “Hrs sun” and “(right hand scale)” after “Ice cream sales”. Simply add these to the descriptions in the data on the spreadsheet
12Error bars
The easiest way to create a graph with error bars is to put the values into the spreadsheet as shown in figure 16[1].