Using Excel for Data Analysis in CHM 161

1. Using Excel for Data Analysis in CHM 161 Monique Wilhelm

2. What is Excel? • Computer program by Microsoft • Spreadsheet program used to present data and produce graphs • Can also be used to perform calculations using function “programming” • Available on all campus computers

3. Why use Excel? • Makes for better presentation of results • Cleaner, more accurate graphing • Ability to calculate more Accurate values from graphs

4. Lesson 1 Making Tables • X values (independent variables) to the left of Y values (dependent variables) • Use top row to indicate which values you will have in which columns • Be sure to add units to labels • Click on cell (rectangle) and type (word or value)

5. Lesson 2: Performing Calculations • Click on cell that you would like to have the value for the calculation placed • Type “=“ to let program know that you want it to perform calculation • Select “Insert” on toolbar • Scroll down to “Function” and click

6. Lesson 2: Performing Calculations cont’d • Click on categories to find function desired • For this class, most common will be SUM and AVERAGE • Double click on function to select • Click on red-white-blue box to select values that you want program to use for calculation • Use cursor to highlight boxes with desired values • Click red-white-blue box to accept values • Click OK to complete

7. Lesson 3: Making Graphs • Input data as previously described • Use cursor to highlight values to be plotted • Select “Insert” on toolbar • Scroll down to “Chart” and click • Click on type desired (best for this class is “XY Scatter”) • Click on picture to right to choose desired style (Use points without lines) • Click “Next”

8. Lesson 3: Making Graphs Cont’d • Look to be sure that data is lined up on the right axes • If not, Select “rows” or use “series” tab to rearrange data • Click “Next” • Fill in Title (Descriptive enough to tell what info can be obtained from graph, not x vs. y, etc.) • Example: “Standard Curve for the Determination of Cobalt Concentration”

9. Lesson 3: Making Graphs Cont’d • Click on tabs to change/remove legend/gridlines, etc. • Click “Next” • Select “As New Sheet” and click “Finish” • Right click on desired area if changes are necessary • Click on grey area and then the bucket and select “No Fill” to remove grey and save printer ink

10. Lesson 4: Best Fit Lines • Used for all standard curve graphs made in this class • Make graph as previously described • Click on data point • Select “Chart” on toolbar • Scroll down to “Add Trendline” and click • Select box of desired curve (ie “linear”)

11. Lesson 4: Best Fit Lines cont’d • Click on “Options” tab • Click “Display Equation on Chart”, “Display R-squared value”, etc. to turn these options on • R-squared tells how well your data fits the line generated • Can be used as indicator of accuracy for data • Click on “OK” • Click on Equation, etc. displayed on chart and drag to desired (visible) location

12. Adding to Notebook • After you have completed your editing, select “File” on toolbar • Scroll down to “Save” and click and save as you would any other file • Select “File” on toolbar • Scroll down to “Page Setup” and click • Click on “Chart” tab • Select “Custom”

13. Adding to Notebook • Click on “Options” • Scroll down to “2” in “Pages per Sheet” • Will print 2 graphs per sheet of paper to fit in notebook • Click “OK” until back to graph • Select “File” on toolbar • Scroll down to “Print” and click • Follow usual printing procedures

14. Adding to Papers • Click on outer edges to select whole graph • Select “Edit” on toolbar • Scroll down to “Copy” and click • Open program that you desire to use to write paper (ie MS Word) • Click area of report where you wish to insert graph • Select “Edit” on toolbar • Scroll down to “Paste” and click • Click corner and drag to scale to appropriate size