Graphing Functions in Excel Excel Help & Tips
Excel 97, 2000, XP, 2003
This activity will show you how to draw graphs of algebraic functions in Excel.
• Open a new Excel workbook and look for the Standard Toolbar.
• If it is not there, left click on View
then Toolbars, then Standard
to make it appear.
• Now look for the Chart Toolbar.
• If it is not there, left click on View then Toolbars, then Chart to make it appear.
www.mathsroom.co.uk
To draw the graph of y = 3 x − 2 in Excel
• First you need to draw up a table of values for x and y.
Left click on cell A1 and type x – the heading for column A.
Left click on cell B1 and type y – the heading for column B.
x values Cell A1
• To obtain whole numbers from 0 to 10 in column A:
Left click on cell A2 and enter the value 0
Left click on cell A3 and enter the Excel formula =A2+1
Excel will put the value 1 in cell A3 as shown.
Now look for the little black ‘fill down’ square at the
bottom of the right-hand side of cell A3.
‘fill down’ square
• Move the mouse until the cursor (cross) is on this square then left click and at the
same time drag the mouse so that the cursor moves down column A.
Release the mouse button when you reach cell A12.
You should find that Excel enters values from 2 to 10 in cells A4 to A12.
Fill down copies the formula from A3 into the other cells, in each case increasing the cell
reference by 1, so that each value entered is one more than the last.
This tells Excel to multiply the value in
y values cell A2 by 3 then subtract 2
• Now enter the Excel formula =3*A2-2 in cell B2.
Excel will work out the value of 3 × 0 − 2 and put the result (i.e. – 2) in cell B2.
• Use fill down to copy the formula into cells B3 to B12.
The values and formulae
in the cells will now be as
shown here.
You can check the formulae
if you wish by left clicking
on each cell – the formula
will appear in the box
above the column headings.
Values Formulae
www.mathsroom.co.uk
Now draw the graph as follows:
• Select the values in columns A and B by left
clicking on cell A1 and then dragging the mouse
until the cells are highlighted as shown.
• Now left click on the Chart Wizard:
The options for Step 1 of Chart Wizard will appear.
• Left click on
XY(Scatter) and
then the last
graph to give a
scatter graph
‘with data points
connected by
lines without
markers’.
• Then left click
Next.
This will take you to
Step 2.
This shows the Data
range i.e. the
references of the
cells that are being
used and that the
series is arranged in
columns.
• Left click Next to
take you to Step
3.
www.mathsroom.co.uk
• Enter the title and labels for
your graph as shown.
• Then left click on Gridlines
at the top.
• Left click on any of the gridline
boxes that are not already
ticked. Ticks will appear and
gridlines will be added to the
graph as shown.
• Then left click on Legend.
A legend (or key) is useful when a
graph has more than one line, but in
this case it is not needed.
• Left click on the Show legend box
and the tick and the legend will
disappear.
• Then left click on Next.
In Step 4 you are given the choice
of having the graph drawn as an
object on the same sheet as the
table or on a new sheet by itself.
• Choose the second option as
shown, then left click Finish.
www.mathsroom.co.uk
Your graph will appear, but its appearance may leave a lot to be desired!
• Before starting to improve it, Save your spreadsheet, giving it an appropriate name.
Remember to save the spreadsheet periodically whilst you work on it.
To alter the size and
shape of the graph, use
the handles.
• Use the corner
handles to enlarge
the graph.
• Use the side handles
to change its shape.
Many other features of the graph can also be improved.
There is more than one way of obtaining the menus for doing this.
One way is simply to double left click
on the feature you wish to change.
• Left double click on the Chart
Area – this is the white part of the
graph.
This menu will appear. It allows you to
change the appearance of the border
and background of the graph as well as
the font (i.e the text) and other
properties.
• Experiment with these if you wish,
then left click OK.
If you don’t like the changes you have made, left click on the Undo
button.
www.mathsroom.co.uk
Another way to obtain the Format menu for a feature is to right
click on the feature. This method lets you format the feature and
do other things as well.
• Right click on the Chart Area of the chart.
This menu will appear. Selecting Format Chart Area would give the
menu for formatting the chart area again. Selecting Chart Type,
Source Data, Chart Options or Location takes you back to one of
the Chart Wizard Steps you followed when setting up the graph.
• Experiment with these now if you wish.
Now try this third way of changing things:
Left click the arrow next to ‘Chart Area’ on the Chart toolbar. Format button
This gives a list of things
you can change.
• Left click on Plot
Area and then on the
Format button.
The menu shown below
should appear.
• Left click on both None boxes and then
OK.
This will remove the grey colour from the
plot area on the graph.
You can alter the scale and appearance of the axes and gridlines in a number of ways.
www.mathsroom.co.uk
• On the Chart toolbar select Value (X) Axis
Minor Gridlines then left click the Format
button.
• In the Patterns menu set Colour to a mid-
shade of grey, rather than Automatic (black).
This will make the minor gridlines less obvious
than the major gridlines.
(Another way of doing this is use different
Weights for the major and minor gridlines.)
• In the Scale menu enter the following
values:
Minimum 0 N.B. If a value is
Maximum 10 already there, just
Major Unit 1 click the Auto box to
Minor Unit 0.2remove the tick.
When you enter values the ticks in the Auto
boxes will disappear. This means Excel will
keep the scale you want and not change the
scale automatically if you change the size of
the graph.
• Left click OK.
• Now select Value (Y) Axis Minor
Gridlines on the Chart toolbar and left
click the Format button.
• In the patterns menu set the Colour to the
same shade of grey that you used earlier.
• In the Scale menu enter the values
shown:
Minimum –5 This will give a y-axis
Maximum 30 from – 5 to 30 with major
Major Unit 5 gridlines at intervals of 5
Minor Unit 1 units and minor gridlines
at intervals of 1 unit.
UsingIf a value is already
• Then left click OK. there, just click the Auto
www.mathsroom.co.uk
Your graph should now look like this.
The y axis minor gridlines are selected
because they have just been changed.
Note that the label on the y axis looks
odd.
To change the orientation:
• Left double click on the y axis title to
obtain the Format Axis Title menu.
• Then left click on Alignment.
• Use the mouse to move the red
marker at the end of the Text
alignment line until it lies in a
horizontal position.
(Alternatively you could type 0 in
the Degrees box.)
• Left click OK.
The y should now be the right way up.
• Use the mouse to move the labels to
the ends of the axes if you wish, as
shown here.
To make the letters in the titles and labels
italic:
• Highlight a letter, then left click the
Italic button on the Formatting Toolbar
(if this is in view)
or right click on the letter, then
select Format Chart Title and choose
the Bold Italic option from the menu.
• Experiment with ways of changing the appearance of your graph.
• Draw graphs of other functions. Some suggestions are given belo
www.mathsroom.co.uk
Use Excel to draw graphs of the following functions.
In the first three use a scatter graph ‘with data points connected by lines without
markers’
– the last Scatter Graph option.
In the others choose a scatter graph ‘with data points connected by smoothed lines
without markers’ – the 3rd Scatter Graph option.
Value in Excel formulae for:
Function x values Fill down to
A2 A3 B2
y = 5x 0 to 10 0 =A2+1 =5*A2 A12 and
B12
y = 12 x + 3 0 to 10 0 =A2+1 =A2/2+3 A12 and
B12
y = 5 − 2x 0 to 5 0 =A2+1 =5-2*A2 A7 and B7
y=x 2 – 4 to 4 –4 =A2+0.5 =A2^2 A18 and
B18
y = 3x 2 – 4 to 4 –4 =A2+0.5 =3*A2^2 A18 and
B18
y = x2 + 3 – 4 to 4 –4 =A2+0.5 =A2^2+3 A18 and
B18
1 A26 and
y= 0.2 to 5 0.2 =A2+0.2 =1/A2
x B26
2 A51 and
y= 0.2 to 10 0.2 =A2+0.2 =2/A2
x B51
Experiment with other functions if you have time.
www.mathsroom.co.uk
