ImagineIT overview
Exploring data
Modelling
Advanced modelling
List of toolbar buttons
Handy hints for Excel
Try the toolbar
Buy the toolbar
Maths in Action book

1 Tell the toolbar where the table of data is

Do one of the following:

Either

• Select the cell which is to be in the top left hand corner of your table (containing the number 1).

• Click .

• Enter the numbers of rows and columns and then click OK (for the BodySizes example this would be 19 rows and 15 columns).

Or

• Select the whole table, excluding column headings. (Hint: you can drag across the table with the mouse to select it, or you can click in the top left hand cell, then hold down shift while you click in the bottom right hand cell.)

• (If you did this with the BodySizes example you would have to select a block of cells containing 19 rows and 15 columns with the cell containing the number 1 in the top left hand corner.)

• Click

The table is automatically highlighted with a yellow background so you can easily see where it is:

2 Tell the toolbar which rows to use for the graph titles, axis labels and plot labels

Now you have told the toolbar where your table of data is, you next need to tell it where to find the rows that it needs to use for graph titles, axis labels, and plot labels when one column is plotted against another on a graph. (Plot labels are labels that appear on a graph to show which plots are which. Excel calls these 'series labels' or 'legends'.)

• With any cell in the table selected, click . (Note it is important to select a cell in the table.)

• You will see a box on screen with spaces where you can specify the rows you want to use for graph titles, axis labels, plot labels (and also column names - but we will not be using these here). You say where each row is by clicking on any cell in the row. (You can leave any spaces blank that you don't need. In the present case the names space will be left blank.)
  

  

In the case of the BodySizes example, prompts have been added in the first column to indicate which rows to use. (However, you do not have to keep to this arrangement when writing your own spreadsheet. You can use any rows you choose whether or not they are adjacent, provided they do not overlap the table. You can also use the same row for more than one purpose.)

Once again, the toolbar adds colours to help you remember which rows have been used.

3 Plotting a graph

Decide on the two columns that you want to plot one against the other and the type of graph you want. In the BodySizes example you could start by plotting armspan against height to see if these are correlated.

• Click on any cell in the column that you want to use for the y-values (usually the vertical axis) - this would be the armspan column in this case. (Note that you must select a cell within the yellow table area, not the name row or one of the label rows above it)

• Click the appropriate graph button , , , , , , (the last of these is not an x-y graph in the usual sense, but what Excel confusingly calls a 'line chart' - actually just another way of displaying a bar chart).
  

In the present case a scattergraph would be most appropriate.

[Note that Excel uses the term 'chart' to refer to all types of charts and graphs.]

• Choose either a Normal Excel graph or a Super Graph. For data exploration either can be used, but a Super Graph does have the advantage that graph titles, axis labels and plot labels can be changed on the spreadsheet and graphs will then be automatically re-labelled.

(For models, on the other hand, a Super Graph is almost always the best choice. This is because it allows you to 'freeze copies of plots'. This means that if the spreadsheet data changes, plots of the new and old data both appear on the graph so that you can make comparisons. This enables you to understand how a model behaves in different scenarios.)

• Say which column you want for the x-values (usually the horizontal axis) by clicking in the x-axis space in the box that appears, and then clicking on any cell in the x-value column. (In the present case, this would be the Height column.)

• Click the Plot all rows button

  (TIP If you only want to plot certain rows, select the relevant rows in either the x-value or y-value columns by clicking on the first value to be plotted, then holding down Shift while you click on the last value to be plotted - then choose Plot rows shown )

The resulting graph will look like this:

Notice that the graph opens in a new window. You can resize this and the spreadsheet window to give the best view of both the data and the graph.

Notice, too, that the axes have been labelled correctly and the plot label included (showing plot labels is an option you can select).

The graph has been given the title Human body measurements, and you can see that this heading comes from cell E3 (above the y-axis column). If you type this text into the same cell on your spreadsheet the graph title will appear automatically.

Whenever you change what is plotted on the axes (see below) the various graph labels change automatically if you are using a Super Graph.

4 Exploring the data graphically

(a) Changing the x or y values plotted

The toolbar makes it very easy to explore data graphically in order to look for relationships because you can change what is plotted on either axis with just two clicks of the mouse.

Click on the graph window and then click on the plot you want to change

Click on the appropriate buttons - to change just the x-values , just the y-values , or both x and y-values, and possibly which rows are plotted as well

Experiment with plotting different body dimensions against each other and see what correlations you can find.

(b) Adding a best fit line

Excel has a very useful feature that enables you to plot a best fit (or 'regression') line (of y against x).and to read off the square of the correlation coefficient.

A best fit line only has meaning if the quantities on both axes are numerical values that can take a range of possible decimal values. In other words, best fit lines are only appropriate to the three types of x-y plots: , , , not to bar charts or pie charts.

• Right-click on the data plot and choose Add Trendline . . Leave the first thumbnail (the straight line option) selected.

• If you wish, you can select the Options tab, and click any of the three check boxes at the bottom - for Set intercept = 0 (forces the best fit line to go through the origin), Show equation on chart, and Display R-squared value on chart).· Click OK.

• If you change what is plotted on either axis as in (a), the best fit line will automatically change too.

If you show the equation and/or the R squared value, these can be moved around the graph window to a convenient place (as in the illustration).

To remove a best fit line, simply click on it so it becomes highlighted and press Delete.

(c) Changing the type of graph

• Click on the graph you want to change

• Either click on one of the graph buttons , , , , , , , or select Chart type from the Excel Chart menu. (Remember that is not an x-y graph in the usual sense, but what Excel confusingly calls a 'line chart' - another way of displaying a bar chart).

  Hints on choosing a graph type

  If the y-values and x-values are numerical quantities which can take a range of possible decimal values (like armspan and height), use one of the following three types of x-y plot: , , . These three are identical except for the way the data points are shown: points and joins, joins only, or points only.

  If the y-values are numerical quantities which take a range of possible decimal values (like height), but the x-values have distinct values which are not related to each other (such as names, eye colours or shoe sizes), use one of the variations on a bar chart: , , .

You can also use a pie chart for this type of data.

Following is an example of a bar chart showing different people's heights: To get the title Individual heights, this text was typed into the Graph titles row above the y-values (Height) column.

(d) Sorting the data

Excel provides an easy way of sorting data. The data must be in a continuous block and if column headings are used, these must be in the row immediately above the data. In the BodySizes example, this row also happens to be the row for labels for data plots (but this does not have to be the case).

Notice that a blank row has been left above these headings. If this row was not blank, Excel would think that whatever information it contained was part of the table to be sorted.

Take the preceding bar chart as an example. It would be more informative if the data was sorted in order of increasing height:

First select cells A7 to A9 and press Delete to clear the text in these cells. (This needs to be done otherwise Excel would think it was part of the table to be sorted.) The data to be sorted, including headings, is now completely surrounded by blank cells.

Click on any cell anywhere in the Height column. (This cell must be one of the Height values or the heading, not a cell above or below the table.)

Go the Data menu and select Sort . .

Make sure that Sort by is set to Height, and that the Header row button at the bottom is selected.

Click OK.

The data is sorted and the bar chart now looks like this:

(e) Adding extra plots to a graph

As a (somewhat bizarre) example, follow these instructions to plot a bar chart showing both ear length and nose length.

Draw a bar chart of ear length against name (as explained previously).

• Click on the spreadsheet window and then on any cell in the new y-value column (nose length).

• Click on the Add plot button .

• You will be shown one of the graphs that you have drawn and asked to select either Next graph or Choose this graph. Keep selecting Next graph until you see the graph which you want to add the new plot to, then select Choose this graph.

• A box will appear giving you the opportunity to change the column to be used for the x-values. Check that the column shown is the one you want, or, if not, click in the x-axis space in the box, and then on any cell in the correct x-values column.
  

ou will now have a bar chart showing both ear length and nose length and will be able to see that people's ears are generally longer than their noses, but that there is considerable variability!

(f) Deleting plots on a graph

• Click on the plot you want to delete

• On a Super Graph, now click on the Delete plot button , (or, for a normal Excel graph, you can simply press the Delete button on your keyboard)
  

Richard Beare
14th February, 2006