Graphing With Excel 2007

  1. Enter data in Excel Worksheet.
  2. Without selecting the data, pick Insert → Scatter (no lines)

  1. Click on Chart to select.
  2. Move Chart away from data table so it will not interfere with the following steps.
  3. Go to Chart Tools
  4. Select the Design Tab
  1. Click the Select Data Button

  1. Click Add – you will see the Edit Series Box

  1. Click on the little box that looks like a spreadsheet with a little red arrow beside Series name
  1. The edit series box will minimize like below.
  1. Go to the spreadsheet and select the series name – in this case, I selected Pre-1982
  2. Click on the little box that looks like a spreadsheet with a little red arrow to return to the full box
  3. Now click the box for the Series X values
  4. The box will again minimize
  5. Put your cursor over the first cell you want to include for X values.
  6. Right click, hold and move the mouse down to select the entire range for X
  7. Click on the little box that looks like a spreadsheet with a little red arrow to return to the full box
  8. Repeat for the Series Y Values
  9. You should see Data Points on your Chart.
  10. Click OK to return to the Select Data Source Box
  11. Repeat steps ii to vii for the Post 1983 Pennies
  12. Select the layout tab
  13. Use Chart Title to insert a title
  14. Use Axis Titles to label axes (include units)
  15. Use Gridlines to get lines on graph as desired.
  16. For a “Line of Best Fit” Go to Layout → Trendlines select Linear Trendline
  17. To change the color of the lines go to Format
  18. Make sure the current selection is Series “Pre-1982” Trendline
  19. Use the Shape Styles box to select color and thickness of line.
  20. To Find Slope – Select Formulas → More Functions → Statistics → Slope – The Function Arguments Box will appear.

  1. Click on the little box that looks like a spreadsheet with a little red arrow beside Series name
  2. The box will again minimize
  3. Put your cursor over the first cell you want to include for Y values.
  4. Right click, hold and move the mouse down to select the entire range for Y
  5. Click on the little box that looks like a spreadsheet with a little red arrow to return to the full box
  6. Repeat for the Series X Values
  7. Click OK – Excel will calculate your slope.
  8. Can adjust to correct significant figures with the Number Command on the Home Tab
  9. For a “Smooth Curve” Go to Layout → Trendlines select Two Period Moving Average
  10. To set Graph to start at Origin – Select Layout → Axes → More Axes Options
  11. Set Minimum to Fixed, 0
  12. Printing
  13. Select the Chart and print, as you would any other item

  1. If you wish to print the data table, select the Cells you want to print.
  2. Click the printer icon

  1. Under print what, pick selection
  2. Click Preview
  3. Use Page Settings button to make the page appear, as you wish.
  1. One of my favorites is to center the table, using margins, center horizontally.
  1. Once you have the format to your satisfaction, select the print button.

Graphing With Excel

  1. Enter data in Excel Worksheet.
  2. Select Data
  3. Pick Insert → Chart
  4. You will get a Chart Wizard
  5. Choose XY Scatter Plot
  6. Select Data Range
  7. Enter Graph Titles and Axes Labels
  8. Choose the “As an Object In” selection
  9. Note – Must have chart selected to get Chart Menu
  10. Go to Chart ￫ Add Trendline

Best Fit Line

  1. Select Linear Trendline

Smooth Curve

  1. Select Moving Average Period = 2.
  2. Go to Chart ￫ Source Data
  3. Name Series
  4. Add/Change Data

c.  To make any modifications to data range, etc.

d.  To add an additional line – Add – specify data range for each axis

e.  Edit to change data information or legend.

  1. To Find Slope – Select Insert → Function
  2. Type in slope to select
  3. Select the ranges for the x and y values for the slope calculation
  4. Can adjust to correct significant figures with the Number Command on the Home Tab
  5. Printing – Select graph and print.

Page 9 of 9

Graphing With Excel 2007 - Rev 1.docx

February 19, 2010