How to Make a Line or Scatter Graph in Excel with Error Bars for Each Data Point & Multiple Lines on the Same Graph
(NOTE: there are several different ways to draw this type of graph; here is one option.)
1. Open your spreadsheet containing the data you wish to
graph. Make sure that your X values are
in one column and that Y values (calculated means) are in a second column with
each row containing the matching X, Y values.
Have the associated error values (standard error of the mean) for each Y
value in the same row in a third column.
Make sure that the data are sorted with the X values ranging from lowest
to highest value (you can use the Data Sort function to do this if needed).
2. Now click on the picture of a graph in the toolbar at
the top. (Alternatively, you may go to
Insert Chart from the drop-down menus.)
3. Step 1 of the Chart Wizard will appear. Pick the picture & description that best
matches the type of graph you are trying to draw. Choose the XY (Scatter) category. You can additionally pick Scatter
with data points connect by smoothed lines or Scatter with data
points connected by lines to connect your points with a smoothed line
or with straight lines. Click Next.
4. Step 2 of the Chart Wizard, Chart Source Data, will appear. There are 2 tabs as follows.
a. Data Range skip this tab and go straight to the Series tab to select your data.
b. Series contains the following fields. Fill in the fields as listed below for each line that you wish to appear on the graph. Click the Add button in the lower left to add additional series (each series will be one line on the graph).
i. Name: - fill in the words you wish to appear in the legend for the first set of XY pairs that you wish to graph
ii. X Values: - Click on the picture of the spreadsheet at the right of the blank; this allows you to go to the spreadsheet and highlight your X data (just the numbers, not the column heading).
Y Values: - Click on the spreadsheet, and highlight your Y
data (just the numbers, not the column heading).
Click Next when you are done picking XY values for all the series (lines) that you wish to appear on the graph.
5. Step 3 of the Chart Wizard has several different tabs. You will want to go through most of them as you make the graph look the way you want.
a. Titles tab
i. Chart Title This is the graph title that will appear above the graph by default. There may already be a title if you entered one under step 2.
ii. Value X axis enter the X axis label here. Dont forget to include units in parentheses if appropriate.
iii. Value Y axis enter the Y axis label here. Dont forget to include units in parentheses if appropriate.
b. Axes tab You can alter the appearance of the X or Y-axis. Generally, you will not need to change the default settings on this.
c. Gridlines tab You can check the boxes on this tab to make gridlines appear or disappear. You may alter the appearance of your graphs as you wish. Do avoid making the graphs too cluttered.
d. Legend tab You can check to whether or not you wish to have a key (series legend) appear; you can also check boxes to indicate where on the page you would like the key to appear. Note that as you check or uncheck boxes, you can see what the result would be in the graph picture. (NOTE: you can also highlight and drag the key to other locations on the graph later.)
e. Data Labels tab You can check boxes to show specific data labels and values. Play with the options. Remember, less clutter is usually better.
when you are done making your choices on step 3.
6. Step 4 of the chart wizard simply asks where you
would wish for the graph to appear. You
can check for the graph to appear by itself on a separate sheet in the
workbook, or you can check for the graph to appear as an object embedded in the
current sheet of the notebook, next to the data. I am comfortable with either choice; go wild.
1. Add the error bars to your graph as follows.
a. Place the cursor on a data point in the first series or line, and right-click the mouse. One of the choices should, Format Data Series; select that. One of the tabs in those formatting menus reads Y error bars; click on that tab to bring it to the front.
b. Choose the Both option.
c. Choose Custom and use the spreadsheet picture to highlight the standard errors that you calculated on the spreadsheet. You will have to select the standard errors twice; once for the + and once for the - options. (NOTE: The standard error option shown on this tab calculates standard error of the population, not standard error of the mean. It is a much larger number than standard error of the mean and is not the number I would like for you to graph.) Click OK when you are finished.
d. Repeat this procedure for each line or series of data within your graph.
e. To change the color or appearance of the error bars,
place the cursor on one of the error bars, and right-click the mouse. Choose Format Error Bars. The Patterns tab will let you
change the appearance and color of the error bars for that series or line. You may have to click away from plot before choosing
to format error bars in a second line or series.
7. In order to change the scaling on the axes, move the
cursor over the axis labels (a highlighted box saying, value X/Y axis will
appear), and double-click to open the Format Axis menu; go to the Scale
tab, and set the indicated numbers to ones that seem like reasonable minimum,
maximum, major and minor unit numbers.
Click OK to see what it looks like.
8. There are many formatting menus that you can reach to make your graph look exactly as you would like. Additionally, you can go back and change any of the selected options, data, etc., without starting over on the graph. As a general rule of thumb, place the cursor on the item you would like to change, and double-click the mouse to immediately open a menu, or right-click the mouse for a set of menu options that you can choose from. Make your graph as individual as you wish, however, remember that it needs to communicate effectively, e.g. I need to be able to see lines and symbols clearly. Please use the Excel Help function or just play to find all the options available.