# Using Excel for Data Analysis in CHM 161

Document Sample

Using Excel for Data
Analysis in CHM 161
Monique Wilhelm
What is Excel?

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

• Makes for better presentation of
results
• Cleaner, more accurate graphing
• Ability to calculate more Accurate
values from graphs
Lesson 1 Making Tables

• X values (independent variables) in
columns 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)
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
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
Lesson 3: Making Graphs

• Input data as previously described
• Use cursor to highlight values to be plotted
• Select “Insert” tab
• Click on “scatter” and select the one with
dots that are NOT connected
• The “Chart Tools” menu should appear
• In the “design” tab, go to the “chart
layouts” box and select the style that you
prefer(for this class, you should choose the
one that has a title and labeled axes)
• In the “layout” tab, select “legend” and
scroll down to “None” if the legend is not
needed (only one line)
Lesson 3: Making Graphs Cont’d

• Move the chart to its own location by
selecting the “design” tab then selecting
“move chart” (far right). Click the bubble
for “new sheet”. Click OK.
• Look to be sure that data is lined up on
the right axes
• If not, right-click on a data point, select
“select data”, and click “edit”. A box
should appear. Select the red/white/blue
box under “series X values” then highlight
the values you want for the X axis. Click
on the red/white/blue box to accept
those values. Repeat for y values. Click
OK. Click OK again.
Lesson 3: Making Graphs Cont’d

• Click on “Chart Title” on the graph and 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”
• Do the same for the Axes labels. Don’t forget
to put units. (Absorbance does not have units)
Lesson 4: Best Fit Lines

• Used for all standard curve graphs
• Make graph as previously
described
• In the “layout” tab, select
“trendline” and scroll down to
“More Trendline Options…”.
• Click the bubble for “linear”
Lesson 4: Best Fit Lines cont’d

• Click “Display Equation on Chart”
and “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 Equation, etc. displayed
on chart and drag to desired
(visible) location

• After you have completed your editing,
save the files
• Select the “page Layout” tab
• Click on “size”.
• Select “more paper sizes”. A window will
appear
• Click “options”. A new window will
appear.
• 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

• 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

• Right-click on outer edges to select whole
graph
• 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

DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 0 posted: 9/15/2012 language: English pages: 14