Making Graphs Using Excel

To get the best out of this guide, open MSExcel and practise using the functions as you go. The notes are written with respect to Macintosh. There may be slight variation in functions if you are using a PC.

All data that you wish to include in your graphs (except for axis titles) must be entered from left to right onto an Excel worksheet (Fig. 1). Each data point should be entered into its own cell. (If you are drawing a histogram, you will also have to enter values for the x-axis onto the worksheet.)

Figure 1.This worksheet has the mean values of a variable entered for females on the left and males on the right. The words female and male are entered under the corresponding mean value so they can be used as X-axis labels. Note that the Chart Wizard icon is indicated by a popup label.

Using the cursor, select (highlight) the cells that contain the data you are graphing.

Choose to graph your data by selecting the Chart Wizard icon in the tool bar or click on Insert and then Chart….

MSExcel will now produce a window (Chart Type) allowing you to select the type of graph. Column graphs and line graphs are the types of graphs most commonly used in scientific papers. Select Column and click Next > at the bottom of the window (Fig. 2).

Figure 2. Chart Types Window.

^Top

A new window appears consisting of two levels: Data Range and Series. Select Data Range and highlight the cells on your worksheet containing the data you wish to plot (Fig. 3). If you want to change the labels on the x-axis select the Series level, choose series in rows, then click on the Category (X) axis labels: data box and highlight the cells on your worksheet containing the labels for the x-axis (Fig. 4).


Figure 3. The two levels of the Source Data window.

Figure 4. Selecting the X-axis labels.

^Top

Click on Next>. A new window appears containing several levels. Select the Titles level to enter axis labels (e.g. x and y-axis) (Fig. 5) and a chart title. However, a chart title is not normally entered (as this should be in the legend below your figure when you place it into a document).

Figure 5. Entering the axis titles

^Top

If you do not want a legend on your graph you can select the legend level and then use the cursor to deselect legend (Fig. 6). Click on Next >.

Figure 6. Show legend is unselected.

The new window allows you to select whether the graph will be placed in either a new sheet or an object in the worksheet (Fig. 7). Either option is fine. Click Finish to create your graph.

Figure 7. Select to place your chart on a new sheet or as an object in the current sheet.

^Top

Adding Y Error or range bars

The data used to draw error/range bars must be entered onto the worksheet. The simplest way of doing this is by entering error/range bar data below the corresponding data used to construct the graph (e.g. mean value). In order to make range bars, you do not need to enter the numbers of the range that the bars are to go between (however, the range is shown in figure 8. below to help you understand the calculations). Instead enter the amount that the range bars will deviate from the mean point plotted on the graph. For example, a student wishes to make a column graph, where the column represents the mean and the bars indicate the range. The mean is 11 and the range is from 7-12. In a cell on the worksheet the student enters the mean value of 11. The amount that the range bar deviates positively from the mean (i.e. 1 because 12-11 = 1) is entered in the cell below the mean cell. The amount that the range bar deviates negatively from the mean (i.e. 4 because 11-7 = 4) is entered in the cell below the previous one (Fig. 8).

Figure 8. Enter the amount the sample deviates positively and negatively from the means

To add Y-error/range bars:

Double click on the columns or data points on the graph. A window appears with several levels including Y-error bars.

Figure 9. The Y Error Bar level of the window has been selected.

Select the Y Error Bar level (Fig. 9).
Click on Custom and then click on the + box next to custom.
Using the cursor select the data on the worksheet describing how much the error/range bars deviate, in a positive direction, from the data plotted on the graph (e.g. how much the range positively deviates from the mean) (Fig. 10).

Figure 10. Selecting the values of the positive bars.

Click on the - box (under the + box) and then select the data on the worksheet describing how much the error/range bars deviate, in a negative direction, from the data plotted on the graph (Fig. 11).

Figure 11. Selecting the values of the negative bars.

Click on OK at the bottom of the window. Now your graph has error/range bars.
You may now copy and paste your graph into a word document if required. Add figure number and legend under the finished graph.

^Top

The steps involved in making a line graph are basically the same as making a column graph. Select line graph instead of column graph on the first window after clicking on Chart Wizard (Fig. 12).

Figure 12. A worksheet and its corresponding line graph. Note that the data have been entered onto the work sheet in the same manner as was done for the column graph.

Once your graph is plotted, experiment with editing the chart features by double-clicking on axes, axis labels, background, etc. or selecting Chart, Chart Options. Remember that less is more: don’t get carried away with fancy backgrounds, patterns, data points and fonts, as these only obscure your data. The default background is grey and this is best removed, by highlighting the plot area and selecting None in Format Plot Area. Ditto gridlines.

^Top

Appendix: creating a frequency histogram of continuously variable data

Once you have opened or created a file of data (e.g. a list of scores in a class test; heights of individuals in a population), you need to create a list of values that you want plotted on the x axis of your graph. This is called the Bin range. e.g. for height values ranging from 120 cm to 200 cm you might choose x axis values of 120, 125, 130, 135, 140, 145, 150, 155, 160, 165, 170, 175, 180, 185, 190, 195, 200. You need to create this list in another column on the worksheet containing your data.

(For a long list you can do this quickly using a calculator that adds a set value to the value above, then copying and pasting this function down as many rows as you need.)

Select Tools, Data Analysis. (You may have to install the data analysis tools from your original Excel software installation disk.) Scroll down the list (Fig. 13), select Histogram and click OK.

Figure 13: Data Analysis window (left) and Histogram window (right) (with ranges entered).

In the Histogram window you need to enter the following information:

Input range: click in this box then use the mouse to highlight the data you wish to plot. (Use click and drag, or click on the first value in the list, hold down the mouse button while you press Shift then click on the last value in the list.) The cell range will automatically appear in the box.

Bin range: click in this box then highlight the bin range values you entered on your worksheet.

Tick the labels box ONLY if you have included the column label for your data in the input range.

Click on New worksheet ply, enter a name for the new sheet if you wish, click on Chart output then OK. (Alternatively, you can choose an output area in the original worksheet by dragging the mouse over the desired area.)

The histogram will be plotted in a new worksheet on the screen. The Bin values and their frequencies will be displayed in a table at the left hand side of the graph. (To return to your data, click on the appropriate tab at the bottom of the workbook.)

Refer to Help, Contents and Index, Analysing Statistical Data, Histogram analysis tool for further details.

^Top