Create Graphs and Lines

Introduction
This is a brief outline of how to produce acceptable graphs using Microsoft Excel. The version used here is Excel 97. If you have an earlier version you may have less features, a newer version may have more features. This is not intended as a step by step tutorial for this reason. It is a complicated package and unless you are confident and have several hours to spare you may not achieve very good results.

Data Table

1. General appearance

There are many options for changing the appearance of the content of cells. The content can be aligned in different ways (e.g. centred) both horizontally and vertically. The font type, size and colour can also be changed. Different borders can be put around the cells. Don't waste much time with this unless you are intending to print out the table for your final report.

Column headings: titles and units

• Set the column headings, titles and units, to plain text:
  Format→Cell→Number→Category=Text

• For long titles set the text to wrap around inside the cell, rather than across the next cell.
  Format→Cell→Alignment→Text control=Wrap text

2. Data

• Set the data cells to be numbers.
  Format→Cell→Number→Category=Number

• Set the correct number of decimal places
  Format→Cell→Number→Decimal places=1
  (or however many places you want)

In this example the contents of each cell have been centred. For the middle column headings the cells in columns B, C and D have been merged (Format→Cell→Alignment→Text control=Merge cells) to make one cell. This just makes things a bit neater to look at.

3. Calculating averages

The average function can be used to calculate averages automatically.

In the example above the following text, not the number 1.13, has been typed into cell E3:

=AVERAGE(B3:D3)

This means calculate the average of cells B3, C3 and D3, and display the answer. This doesn't have to be typed in for every cell. By dragging the small square at the bottom right of selected cell E3 down the column the formula is copied into all the other cells. Saves quite a bit of time!

An alternative way to write the same formula would be:

=(B3+C3+D3)/3

Using this simple method can be quicker than trying to find if a special function exists, and then working out the correct syntax for it.

Other examples;

• =POWER(E4:3) and =E4*E4*E4 both mean E4 cubed.
• =SQRT(E4) and =POWER(E4:0.5) both calculate the square root of E4

You should be able to calculate most things using these basic maths symbols:

symbol	use
( )	brackets to group parts of a calculation together
+	plus
-	minus
*	multiply
/	divide
SQRT(A1)	square root

Line Graph

1. Creating the chart

You've got all the data into the table, now you have to draw the chart. In the above example select cells A3 to A12 with the mouse, then E3 to E12. You will have to hold down the Ctrl key as you drag the mouse to select both columns at once.

With the two columns highlighted create a new chart:

Insert→Chart→Chart type=XY(Scatter)

Make sure you select the XY(Scatter) NOT Chart type=Line

If you select all the default options your chart will look something like this:

This won't get you many marks, you will have to change just about everything on the graph to bring it up to an acceptable standard.

Below is an improved version of the graph that would be suitable if you have a colour printer. For a black and white printer get rid of the blue squares completely and make the points and line black as well.

2. Chart options

Chart → Options will allow you to set up the basic appearance, which includes the following:

• Titles: for the chart and axes
• Axes: x and y axes and numbers shown or not
• Gridlines: on or off (These are the blue background lines below)
• Data Labels/Legends: these are not normally needed.

3. Finished Graph

There are many other options that can be set up. Double clicking on part of the chart, such as the x axis, will bring up a menu allowing that part to be configured.

chart area/plot area	Keep these simple, it is best to use a plain white background.
axis scale (e.g. for x axis)	minimum=0 ; start value maximum=120 ; end value major unit=10 ; goes up in tens minor unit=1 ; not used in this example
data series (i.e. the points)	line = none ; this is a dot to line, so switch it off! marker ; set up to produce a cross "+" using, background = no colour ; style = + ; size=10
grid lines	If you use these set them to a pale colour to look like graph paper.

Finally, make sure the chart is adjusted so that it is big when printed out. Postage stamp sized graphs will not gain many marks. The graphs shown on these web pages are smaller than they would be if printed to make them easier to view on a monitor.

4. Adding mathematical symbols to Excel charts

With Excel 97 and Excel 2000 it is not obvious how to add a title such as "√length" to a graph axis. Excel allows you to change the font, but doesn't let you insert symbols as you would with Microsoft Word. One way to get round this is to format the text including symbols as you want it to look in Word. Then cut and paste the text into the correct location in Excel.

Line of Best Fit

1. Trying out a trendline

Selecting the Add trendline option will produce a menu with various options similar to the example below. Each type will try to a fit a line according to a certain mathematical function. Some of these you may not understand, but that doesn't matter, concentrate on those which make sense. Try them out and see if there is a good match. Note that some of the trends can't be calculated if you have a data point at (0,0). If this is the case remove the value from your selected data and try again.

4.2 Trend types

linear	y=mx+c ; e.g. y=2x, y=-3x+12
power	y=xn ; e.g. y=x2, y=x3, y=x½ (square root), etc
polynomial	y=a+bx+cx2+dx3+... The order gives the highest power of x
exponential	e.g. the type of curve you get for radioactive decay
logarithmic	You will either have studied logs in maths or not!

3. Options

To extend the trend line beyond the first and last points of the graph you must enter values in the Forecast Forward and Backward boxes. In the example graph above it has been extended forward by 5 (x-axis) units and backwards by 9.9. (Remember that some trend lines don't like to go back to x=0. If this is the case extend back to just greater than zero. Hence a value of 9.9 rather 10.)

The style and colour of the line may also be easily changed. You want to keep it as a solid narrow line in a colour that shows up well.

4. Display equation on chart

For the above graph with a trendline of the power type the equation produced is;

y = 0.359 x ^0.497

If we round up the numbers we get;

y = 0.4 x ^0.5, or y = 0.4 √x

Thus the data shows a convincing trend that y is proportional to the square root of x. To prove this you could plot a second graph of y against the square root of x. This will produce a straight line.

5. Words of Warning

The computer may not fit a good trendline to your graph. If you think you can draw a better trendline by hand then do so. Your brain can intuitively take account of points that may be slightly wrong, or may have larger errors than others. The computer software may not. Remember, if you make a poor job of adding a line of best fit you will not get 8 marks!