How to make graphs in Excel
Dr. Gaines Tyler
The Williams School
This document has the information you will need to make high quality graphs using excel. I have written this using
a PC runninig Microsoft Office 2003 on Windows XP Pro. If you are running a Mac, you may find that some of the
dialog boxes look different, but they should be similar enough to allow you to figure it out anyway. Using these
instructions, you should be able to quickly make high quality graphs of various types to make your Word or
PowerPoint documents look great!
Please note: I have shown how to make good black and white graphs for printing. These may be too boring
to use in a PowerPoint presentation. For that application, please consider leaving all of the default colors in
the graph.
I. XY Scatter plot with regression line (line of best fit).
1. Launch Excel.
Set the view to “normal” so your window looks like this:
2. Enter your data.
The independent variable (X-axis) data should be entered into the first column, and the independent variable(s) data
in the columns to the right. Once entered, your data should look like this:
NOTE: Always leave the first cell (A1) empty.
How to make graphs in Excel
Dr. Gaines Tyler
3. Select the data.
Left click and hold on the first cell (A1) and drag to highlight all of your data.
4. Click on the chart wizard icon.
or
(Mac)
5. In the dialog box that opens, select the graph type (XY scatter plot).
The following graph will be generated:
The Williams School
How to make graphs in Excel
Dr. Gaines Tyler
The Williams School
6. Click “next” to finish the graph by adding a title and axis labels.
If you only have one line of data to plot, click on the “Legend” tab and deselect “show legend.”
Click “next” to move to the next step, and choose whether to place the graph on the same sheet or on a new sheet. If
you want to print the graph out as a full page, select “new sheet.” If you want to place it into a Word or PowerPoint
document, select “as object in” and click “finish.” Your graph should look something like this, and be on the same
page as your data. I am only showing you the graph in the following images.
Volume of Sap Generated by Four Hardwood Species
Volume (L)
20
15
Maple
Oak
Willow
Cherry
10
5
0
0
1
2
3
4
5
6
7
8
9
Month
7. Finish the graph by adding the regression lines and editing it for best appearance.
a) Click on one corner of the graph and drag it to make it a good size.
b) Click on the title, and then select a font size and style that is appropriate for the graph. The default font size is
often ridiculously large, making the title as big or bigger than the plot. The legend is usually oversized too, click on
the legend and select a smaller font size.
c) The grey background in the plot area wastes ink and toner, and makes it hard to see the plot. Double click in the
grey area and in the section marked “area,” select “none.”
How to make graphs in Excel
Dr. Gaines Tyler
The Williams School
Your graph should now look like this:
Volume of Sap Generated by Four Hardwood Species
Volume (L)
20
15
Maple
Oak
10
Willow
Cherry
5
0
0
1
2
3
4
5
6
7
8
9
Month
d) If you are using a black and white printer, the colors are difficult to see. Change them to black using one of the
following methods:
i. Right click on one of the points to open a dialog box. Select “format data series.” Set both foreground
and background to black. You may also change the size and shape of the marker if you desire.
ii) Left click on one of the points to highlight the series. Then go to Format > Selected Data Series to open
the dialog box shown above. Follow the instructions above.
e) Add a trend line for each series of data using one of the following methods.
i) Right click one of the points, and in the dialog box, select “add trend line.” If you want the equation, select the
options tab, and click on “display equation on chart.”
ii) Click once on one of the points to highlight the series.
Got to Chart > Add Trendline
Select “linear.” If you want to add the equation of the line, click on the options tab and select “display
equation on chart.”
Click on the equation to select a smaller font, and then drag the equation to a more appropriate part of the graph.
Click the legend box, then one of the legends that says “linear.” Delete it. Repeat this for all of the “linear” legends.
How to make graphs in Excel
Dr. Gaines Tyler
The Williams School
The graph should now look like this:
Volume of Sap Generated by Four Hardwood Species
20
y =2.4976x - 3.5393
Volume (L)
15
Maple
10
Oak
y =1.0231x - 1.0864
5
Willow
y =0.5576x - 0.6118
Cherry
y =0.2994x - 0.3811
0
0
1
2
3
4
5
6
7
8
9
-5
Month
Notice that the X axis labels are numbers, not the month names as in the data sheet. This seems to be a bug in
Excel, where text labels are not permitted on a multiple series graph. Numerical labels are fine. This can be
corrected by the following method.
Click on the graph, and then on the chart wizard icon. Click “next” until you get to the page where you can enter
titles, etc. Select the Axes tap, and click on the X-axis value box to turn off those labels.
Next, click on the text box icon at the bottom of the Excel window.
Click in the area where you want to place the labels, and the click and drag the text box to the size appropriate to the
labels. Type in the labels using the space bar to evenly space the labels.
Congratulations! Your finished graph should look something like this.
Volume of Sap Generated by Four Hardwood Species
20
y =2.4976x - 3.5393
Volume (L)
15
Maple
10
Oak
y =1.0231x - 1.0864
5
y =0.5576x - 0.6118
y =0.2994x - 0.3811
0
-5
March
April
May
June
July
Month
August
Sept
Oct
Willow
Cherry
How to make graphs in Excel
Dr. Gaines Tyler
The Williams School
II. Bar (column) graphs.
1. Launch Excel, and set view to “normal.”
2. Enter the independent variable (the columns, or x values) in the first column, and the dependent variables
(the value, or height for each column) in the second column.
3. Left click then drag the cursor to highlight the data (see figure in step 3) above.
4. Click on the chart wizard icon.
or
(Mac)
5. In the dialog box that opens, select column as the chart type.
You should get a graph looking something like this:
How to make graphs in Excel
Dr. Gaines Tyler
The Williams School
6. Click “next” to add titles and axis labels.
i) Under the Titles tab, add the title, and labels for both axes.
ii) If you only have one set of data (as with this graph), under the legends tab deselect “show legend.”
iii) Under the Data labels tab, you may want to select “value.”
7. Click “next” to finalize the graph.
If you want to place the graph into a Word or PowerPoint document, click “as object in.”
If you want to print out a full page graph, select “as new sheet.”
Click “finish.”
Your graph should now look like this:
Sap Volume for Four Species (March - October)
Volume (liters)
20
17.5
15
10
7.04
3.96
5
2.11
0
Maple
Oak
Willow
Cherry
Species
7. Finish the graph by editing it for best appearance.
a) Click on one corner of the graph and drag it to make it a good size.
b) Click on the title, and then select a font size and style that is appropriate for the graph. The default font size is
often ridiculously large, making the title as big or bigger than the plot
c) The grey background in the plot area wastes ink and toner, and makes it hard to see the plot. Double click in the
grey area and in the section marked “area,” select “none.”
d) You may want to click on the plot frame and resize it because with the legend removed, it looks lopsided.
How to make graphs in Excel
Dr. Gaines Tyler
The Williams School
e) If you are printing in black and white, the colors of the bars can be difficult to see. Change the color using one of
the following methods:
i) Right click one of the bars, and in the open dialog box, in the area section, select black as the color.
ii) Click on one of the bars, and go to Format > Selected Data Series. In the open dialog box (see above), in the
area section, select black as the color.
To save toner, and improve the appearance, click the “fill effects” button, then go to the “patterns” tab. Select white
as the background color, and select a pattern.
Click OK in both dialog boxes.
Congratulations! Your finished graph should look like this:
Volume (liters)
Sap Volume for Four Species (March - October)
20
18
16
14
12
10
8
6
4
2
0
17.5
7.04
3.96
2.11
Maple
Oak
Willow
Species
Cherry