Appendix A/S12

APPENDIX A: Generating Graphs and Error Bars with Microsoft Excel 2008

1. To launch Excel, click on the icon in the dock at the bottom of the screen. An Excel Workbook will open. If Microsoft Excel is already open, select New Workbook from the File menu to display a new spreadsheet.
2. To determine the mean and standard deviation, write Average in cell A2 and Standard Deviation in cell A2. List variable 1 in cell B1, variable 2 in cell B2… Now enter the number of filled vacuoles per cell for variable 1 in cells B4, B5, B6... Enter the number of filled vacuoles per cell for variable 2 in cells C4, C5, C6…
3. Be sure that the formula builder bar under view is selected.
4. To determine the average value for variable 1 (10 mM inhibitor), click on cell B2, press the = symbol, click on the upper left pull down menu and select AVERAGE, highlight cells B4-Bxx and press return. The average value should now appear in cell B2.
5. To determine the standard deviation for variable 1, click on cell B3, press the = symbol, click on the upper left pull down menu and select STDEV, highlight cells B4-Bxx and press return. The standard deviation value should now appear in cell B2.
6. To determine average and standard deviation values for variables 2 and 3 (1 and 0.1 mM inhibitor), highlight cells B2 and B3, select the lower right square on the highlighted box with the black + symbol, drag to the right until you reach cells D2 and D3, and then release the mouse. The average and standard deviation values for variables 2 and 3 should now appear in cells C2, D2, C3 and D3.
7. To make your graph, first open a new sheet by selecting Insert on the menu bar and then selecting sheet and blank sheet. Assign a title to column A (the x-axis) of spreadsheet and enter variable titles values in ascending order. Assign a title to column B (the y-axis) and record the average vacuole values that correspond to each concentration.
8. To select data to be plotted, highlight both columns, including headers.
9. Click on the Charts tab below the toolbar. A gallery of chart types will appear below. Press the Column button on the far right to display the appropriate charts in this so-called Elements Gallery.
10. To select the proper chart type from the Elements Gallery, press the “Clustered Column” thumbnail (all the way to the left). A chart will appear on the worksheet.
11. Open the Formatting Palette by pressing on the Toolbox icon. Under Chart Options you can label the axes and provide a title.
12. To add error bars, double click on the columns on your graph. The format data series box will appear. Select Error Bars on the left. Under display, select Both. Under error amount, select Custom and Specify Value. The Custom Error Bars chart will appear. Clear any values in the Positive Error Value box and then highlight the standard deviation values that you calculated previously. Do the same with the Negative Error Values. Click OK twice.
13. The chart may be created as an object in the worksheet, or it may be displayed as a new sheet. Under the Chart menu select Move Chart... to choose between these two options.
14. Double-clicking on either axis will allow you to change the scale, font and other features of the axis.
15. When you are ready to print, from the File menu select Page Setup... Choose the Orientation of the printout (portrait or landscape). Adjust the Scaling to a figure other than 100%, if desired. A 50% scaling works well for inserting graphs into your lab notebook.
16. If you are in lab, the printer is called "Hallway HP4200" and is located outside the lab (L-310). Students in room L-304 will use another printer closer to their lab. Ask your instructor about the location of other available printers.

Addendum: Adding Error Bars in older versions of Excel (pre-2007)

Error bars are lines that extend beyond a plotted value in the x and/or y direction. The size of the error bar signifies the magnitude of the variability of the data around the mean. The error bar can be a constant value, a percentage of the plotted data, the standard deviation (SD) or the standard error of the mean (SE or SEM). The direction here are for plotting the standard deviations; therefore, there will be a different value for each data point.

The values for the error bars must be in the same order as the order of the mean values on the data spreadsheet. The row or column of data containing the standard deviation or SEM must be in the numeric format. These values will be used to extend a line representing the standard deviation above and below the bar or data point.

Double click on one of the bars on a column graph(or data points in a line graph) to bring up a menu page.

Important: Select “Y error bars” tab

• Do not be fooled by the options of standard deviation and standard error!
• Click on 'Both' under "Display". Click the 'custom' button.
• Click on the radio box next to '+'. This collapses the window.
• Find the tab for the worksheet with the table of mean and sd values you prepared. Select the cells with the sd’s that correspond to the plotted means. Highlight all the sd’s just as you highlighted all the means.

• Click the radio box to return to the prior screen
• Check that correct selections are still highlighted:

Repeat all the above steps for the ‘–‘ radio box:

Click OK

Formatting GRAPHS:

Click on the figure
Go to CHART: CHART OPTIONS
Use the TABS in Chart options to fix the following
If you want to remove the grid lines (this is a personal preference decision and NOT a suggestion or requirement),
Gridlines: DESELECT “major gridlines” under “Value (Y) axis”
Legend: DESELECT “show legend” if you’ve only got one set of data
Optional: Change Gap width: To remove the gaps between the columns of the histogram, double-click on one of the columns, choose the “options” tab, and then reduce the “gap width” to zero

Click OK
Remove the default background shading
Double click on the background of the plot you’ve created
For “Border” select custom and “Area”, select “NONE”. (Why a dark grey background is the default is one of Excel’s many great mysteries).

Copy the Figure to a WORD DOC:
Double click on any word or axis to delete or change
Remove the original excel title

ADD: Figure #, caption/legend: Add a text box (below the figure and above a table). Give the Figure a number, title, and add some text that briefly explains how these data were collected. Can you look at the figure and read the text and fully understand the figure?

Other possible modifications:
Changing text. Two slow clicks will allow you to change the text itself. A fast double click on any text (including axis labels) will produce a menu with tabs for ‘Font’, ‘Alignment’ and ‘Patterns (color)’ changes. Changing Axis scale ,etc. Double clicking on the axis or on the numerical axis labels will provide a menu for making multiple changes on the axis, such as changing the scale.
Double click on the Y axis (IMPORTANT!)
Specify the max and min values, and set the major unit (gridlines)
i. This is important! Changing the scaling can have a profound effect on the visual impact of your data (play with it if you don’t think so)
ii. The minimum value does NOT need to be zero!
iii. Be thoughtful about the scale. If you are comparing 2 graphs you would want the data to be plotted on identical scales. You also don’t want the scale to make a tiny change look large.

Notice that you can set where the X axis crosses the Y … Use this when your X axis winds up floating in space, or at the top, or somewhere else unseemly
You can also specify fonts and other axis details here.

REMEMBER: If you are stuck and can’t figure out how to modify something … double click it! Whether an axis, a legend, a label, or the data itself.