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).

                                              iii.      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.  Don’t forget to include units in parentheses if appropriate.

                                              iii.      Value Y axis – enter the Y axis label here.  Don’t 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.

f.        Hit “Next” 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.