Making Scientific Graphs with Microsoft Excel

Understanding the layout of an Excel page:

1. The "workbook" (spreadsheet) is divided into columns, labeled with letters, and rows, labeled with numbers.
2. The columns and rows divide the page into "cells". Each cell can be identified by its row number and column letter. Example: we will not enter data into cell 1-A.
3. Entering data into these cells.
   1. Data for the independent variable goes vertically down column A, starting with row 2.
   2. The description of the dependent variable goes on row 1, starting with column B. Use additional columns (C, D, etc.) if there is more than one variable.
   3. Data for each dependent variable goes vertically down the column under its description.
   Adding cell information:
4. Move the cursor to a cell and click. A box forms around the cell and you can now type either letters or numbers in the cell.
5. Move the cursor to the next cell and click. The first cell accepts data and the new cell is ready for data.
6. Continue until all data is added to the workbook page.
7. When finished, click on a cell outside the range of your data. This will enter the data in the last cell.

   Making the "chart":

8. Move the cursor to cell 1-A.
9. Click and hold the mouse while dragging to highlight the cells to be used to make the graph.
10. Click the chart wizard button on the tool bar:
    1. Step #1: Pick the appropriate chart type. A single click on a type choice will give you a description of the type. Unless otherwise noted, your graphs will be "XY (Scatter)" plot. Click "next".
    2. Step #2: The source data should be correct. (Sometimes, when your graph does not look right, it's because the "Series in:" radio button is on "Rows" when it should be "Columns".) Click "next".
    3. Step #3: There are several operations (tabs) available in this step on the wizard.
       1. Titles - type a chart title in the space provided.
       2. Axes - this should be correct.
       3. Gridlines - for most line graphs, you will want to show the major gridlines for both X and Y-axis.
       4. Legend - your choice here depends on the data. Check the Excel graph evaluation rubric for more details.
       5. Data labels - show data labels only if they will not clutter the graph.
       6. Data table - show the data table if there is room. Do not show the data table if it makes your print of the graph take more than one page.
       7. You can make changes back and forth in the chart wizard without a problem. When you are satisfied with your graph to this point, continue.
    4. Step #4: You can place the chart in the spreadsheet ("As object in:") or on a new sheet ("As new sheet:"). Either is ok, as long as the graph is at least half of the page! Click "Finish" to draw the graph.
11. Changes can be made at any time by activating the graph (click just inside the graph page margin), then clicking on the chart wizard button on the toolbar.

    Adding a Trend-line ('best-fit' line):

12. To create a trend-line, go to the "Chart" pull down menu and select "Add Trendline…"
13. Choose the equation type on the "Type" tab that you want (usually "Linear" or ask the teacher if you're not sure) and repeat this for each series of data by changing the "Based on series:"
14. If you need to know the equation of your data, double-click the trend-line, go to the "Options" tab and check the "Display equation on chart". Don't leave the equation on your chart.

    Prepare the graph for printing:

15. Click just inside the graph page margin to activate the graph.
16. Click and hold on one of the corner boxes, drag to enlarge the chart.
17. Double click inside the colored graph area.
    1. All printed graphs in this class must have a white background.
       1. Click "Fill Effects".
       2. Click "One Color". Make the color white in the drop-down menu ("Color 1").
       3. Adjust the slide bar all the way to "Light" so there is no shade.
       4. Click "Ok" and "Ok".
    2. The default data lines can be changed.
       1. Double click with the cursor exactly on a data line.
       2. Make the line weight appropriate.
       3. Make the line color appropriate.
       4. If appropriate, click "smoothed line".
       5. "Marker" adds a dot at each data point.
       6. "Data labels": show "Value" if it doesn't clutter the graph.
    3. Resize the graph plot area by clicking inside the plot area, then click, hold, and drag the plot area to cover most of the page.
18. Click on the graph title to activate it.
    1. Make the font appropriately large and move the title to an open area inside the plot area. Remember, this is a "scientific" graph. Only standard font types are appropriate.
19. Check the graph legend. Be sure it is an appropriate position that does not clutter the graph. If needed, click and hold on the legend and move it.
20. Add your personal information to the graph.
    1. Use the "text box" to place the following personal information on the page.
       1. Your name
       2. Assignment number/name.
    Printing your graph:
21. Click just inside the page margin to activate it.
22. Go to "File", then "Print preview".
23. Look carefully at your graph. Be sure everything is the way it should be.
24. If you see a problem, click "Close" print preview and fix the problem.
25. If everything is ok, click "Print". Save the file in your directory with the appropriate file name.