Using Excel for IB Science Courses

This is a very introductory guide to using Microsoft Excel (based on Excel 2000). It should give you all the skills necessary to complete assignments in General Chemistry lecture and laboratory. Excel is an extremely powerful and sophisticated program with capabilities far beyond those required for this course. If you haven't used Excel before, consider this a good first step.

Entering Data

The most common way to organize data in Excel is by vertical column with a label in the first row.

Example:  Solutions of varying concentration are analyzed by an instrument which generates a signal based on the solution concentration.

In this case the independent quantity is Concentration and the dependent variable is Signal (we know the concentration, but we have to measure the signal that it causes).  This means that Concentration should be on the X-axis and Signal should be on the Y-axis.  In Excel, the X-axis values need to be to the left of the Y-axis values.

Making a Graph

For starters, Excel refers to graphs as "charts".  By graphing these data, we can see the relationship between signal and concentration.

1.  Select the data to be graphed by clicking and dragging the cursor over the range of cells containing your data (including labels).

2.  Press the "Chart Wizard" button.   

        Chart Wizard Step 1:  Select Chart Type "XY Scatter".

                                                       Select the first Chart Subtype (points with no lines).

Chart Wizard Step 2:  This shows you a preview of your graph and confirms that your data are in columns.

Chart Wizard Step 3:  Add a graph title and labels for the X and Y axes.  Axis labels should include the quantity being measured (data type) and the units (IMPORTANT!).

Chart Wizard Step 4:  This asks you where the finished graph should be located.  The default value (object in sheet) is fine.  Press "Finish".  You should now have a graph next to your numerical data. 

Best Fit Line, Slope and Intercept

Often, we need to determine the mathematical relationship between the points on a graph.  In the graph above, you can see that the points form a straight line.  A best fit line can be added to the graph by the following:

1.  Click once on the chart to that it is selected.

2.  From the toolbar, select "Chart" > "Add Trendline".

3.  The default type of line is "Linear" and that is what we will most often use.

The equation of that line will follow the form:

        y = mx + b        where m is the slope and b is the y-intercept.  

To determine the value of m and b:

1.  Return to the "Add Trendline" dialog box.  Select the "Options" tab (at the top of the box).

2.  Check the box for "Display Equation on Chart"

3.  For some graphs you may also want to check the box for "Display R-squared Value on Chart"

4.  When you return to your graph, a new text box will have the equation of the best fit line, along with the R-squared value.  Often the text box is in the middle of the graph, so move it to one side.

                               

The R-squared statistic measures how well the data fit the model of a straight line.  The closer this value is to 1.0, the better the fit of the data to the linear model.  In this graph, the points are all very close to the best fit line, so the R-squared value is high.

Printing and Fine-Tuning

When printing, you must make sure the graph is selected.  There are several ways to tell this.  The graph above has a black border with little boxes at the corners and sides.  This shows that it is selected for editing.  Also, when a graph is selected, the menu on the top toolbar contains a heading called "Chart".  When the graph is not selected this reverts to "Data".

1.  Changing shading  The graph above has the default Excel formatting.  The graph area is shaded gray, and while this doesn't take away from the graph, it will eat up a lot of ink or toner.  To change this to white, double click in the shaded area.  A "Format Plot Area" box appears.  Under "Area", select "None" for shading.

2.  Changing numbering on an axis  If you need to change the scale on an axis, double click on that axis and a "Format Axis" box appears.  You can also right click on the axis and select "Format Axis".  Under the "Scale" tab, you can change the high and low values used on that axis.

3.  Printing  Make sure the graph is selected.  Select "Print Preview" to make sure you are only printing the graph.  Here you can also switch between landscape or portrait printing styles using the "Setup" button.  After double-checking the content of the graph, print away!

Checklist for a Presentable Graph

The following items are required for a good graph (usually, content may vary).

1.  Useful title

- More than just "Y vs. X"

- Not just "Concentration", but "Concentration of NaCl in..."

2.  X and Y axis labels (with units in parentheses)

3.  Appropriate numerical scale on x and y axes

- Conventional increments count by 1, 5, 10, 50 or 100, etc... (or 0.001, 0.005, 0.0025, etc...)

- Make sure all data are on the graph

- If a point has a y-value of 102 and the y axis only goes to 100, the point will not be visible

- Do not include extra decimals in the axis labels

- If the range is 0 to 200, the numbers should be in the format 0, 100, 200 and not in the format 100.000, 200.000, etc...

4.  Format the chart area to remove the default gray coloring

5.  When printing, choose landscape or portrait depending on what looks best for the graph

Citation: http://pages.towson.edu/racasey/tools/GenChemExcel.htm

Reference: For more help/clarification on graphing with Excel. http://www.ncsu.edu/labwrite/res/gt/gt-menu.html