Short Course Listings
Short Course

Course Information
Course Title: Examples of Analytical Data Treatment Using Microsoft® Excel™: Part 1 – Some Basics
Categories: 1 - Chemometrics
2 - Data Analysis
3 - Statistics
4 - Teaching Analytical Chemistry
Instructor(s): Mark Stauffer Course Number: 96
Affiliation: University of Pittsburgh - Greensburg
Course Date: 03/07/2016 - Monday Course Length: 1/2 Day Course
Start Time: 01:00 PM End Time: 05:00 PM
Fee: $300 ($400 after 2/12/16) Textbook Fee:

Course Description
This course deals with the use of Microsoft® Excel™ in such areas as descriptive statistics for data and results, significance testing, detection limits, titration curves, basic regression, graphing, and other techniques and tools. Topics are chosen at the instructor’s discretion; suggestions from participants are welcome. A basic operational knowledge of Excel™ is assumed. Participants will use Excel™’s math, statistical, and graphing functions to generate results, and will receive relevant course materials. Purchase of “Excel for Chemists: A Comprehensive Guide”, Third Edition, by E. Joseph Billo (Wiley-VCH, New York, NY, 2011) (ISBN 978-0-470-38123-6) is OPTIONAL.

Target Audience
Professionals in industry, academia, government, and health-related areas who want a refresher on using spreadsheets for their projects, undergraduate chemistry and related science majors, graduate students in the sciences – in other words, anyone who wants some experience in manipulation of experimental data and results with Microsoft® Excel™.

Course Outline
There are a plethora of topics that a course of this type can cover. Being able to address them all in a half-day course is impossible. Thus, I present the following list of potential topics that can be covered and from which the instructor and participants can select (NOTE that the bulleted items are NOT listed in any particular sequence, and that not all of them can and will be covered):

• Univariate linear regression (e.g., for use in calibration): using Excel’s™ functions for regression, such as slope, y-intercept, R-square, and standard error of the estimate; using Excel’s™ LINEST function; using the trendline option; using the Regression tool in Excel™’s Analysis ToolPak
• Using Excel’s™ mathematical functions
• Graphing with Excel™: Excel’s™ many chart options
• Using error bars in graphs
• Introduction to Excel™’s matrix functions for vector and matrix manipulations
• Numerical differentiation (e.g., for first-derivative plots of pH titration curves) and integration
• How to “jazz up” the contents of your spreadsheet cells (e.g., using subscript, superscript, boldface, italics, other cell-formatting options)
• Basic statistics using Excel™’s statistical functions and the Analysis ToolPak
• Significance testing: Using Excel’s™ statistical functions for performing significance tests, e.g., the t test and F test; using Excel’s™ Analysis ToolPak tools for significance testing
• Fitting data to a power series: Using LINEST to fit data to power series
• Using the Solver (e.g., for spectrophotometric analysis of mixtures in which the spectra of the components overlap severely, for optimization, for nonlinear least-squares curve fitting, and for solving 2nd-order and higher-order polynomials)
• Basic spreadsheet operations and formatting
• Creating and using worksheet formulas

The aforementioned list of topics is only a small portion of the possible topics related to using Microsoft® Excel™. There are topics not listed here that participants may want to learn. Thus, I encourage participants to contact me directly (, prior to the short course, about topics they would like to be covered in this short course, whether or not they are given in the aforementioned list.

I intend for this short course to be interactive, in that the instructor and the participants may share and discuss, in an informal and relaxed manner, the many uses of Excel™ in analytical chemistry. The instructor may know things that the participants may not, and the participants may have ideas for using Excel™ and its many functions and tools that the instructor does not know. Thus, participants will be asked to share their expertise with the group, rather than the instructor talking at the participants for the entire short course. Additionally, and most important, I intend for this short course to be a hands-on course in which the participants get to work with various exercises that incorporate the topics that are covered. The instructor will develop exercises for the participants to hone their skills with Excel™ and its myriad capabilities. These exercises will be part of the package that the participants receive as part of their course registration.

Course Instructor's Biography
Dr. Mark T. Stauffer is Associate Professor of Chemistry, and the immediate former chair of the Division of Natural Sciences, Mathematics, and Engineering, at the University of Pittsburgh at Greensburg. He received his B.S. (1979) and Ph.D. (1998) degrees in Chemistry from the University of Pittsburgh, and was employed by the Ethyl Corporation during the 1980s as an analytical chemist in Ethyl’s Research and Development Department. Dr. Stauffer has held teaching positions at the University of Wisconsin-Madison, Shippensburg University, and Carnegie Mellon University. He joined the Pitt-Greensburg faculty in 2001 as an Assistant Professor of Chemistry, and was promoted to Associate Professor with tenure in 2007. Since 2002, Dr. Stauffer has managed a successful undergraduate research effort at Pitt-Greensburg that has, to date, involved over 80 students and produced nearly 50 oral papers and posters delivered at various technical conferences, three publications in Spectroscopy Letters (2007, 40(3), 429-437; 2007, 40(3), 439-452; and 2010, 43(7), 597-601), a printed paper (2003, 80, 65-67) and an online paper (April 2008 issue) in the Journal of Chemical Education, and a chapter titled “Limiting Reactants in Chemical Analysis: Influences of Metals and Ligands on Calibration Curves and Formation Constants for Selected Iron-Ligand Chelates”, in Stoichiometry and Research – the Importance of Quantity in Biomedicine (A. Innocenti, Ed.; InTech Publishing, 2012; ISBN 978-953-51-0198-7). He was Guest Editor for the Special Issue, “Selective Chelating Agents”, of the peer-reviewed online journal Sensors. He has presented this short course in various formats since Pittcon 2009, and has presented many workshops on graphing and data/results manipulation using Excel™ to undergraduate science majors and faculty colleagues at Pitt-Greensburg since 2001. Dr. Stauffer has presented (with colleague Dr. Christine McCreary) a short course on Excel™ in May 2002, sponsored by the Spectroscopy Society of Pittsburgh. His most recent short course was presented to the Global Analytical Group of the Avery Dennison Corporation on April 9, 2014. His research interests involve profiling of metals and anions in abandoned mine drainage, other natural waters, and soils, determination of metals in foods, beverages, and animal and human hair, phytoremediation of metals in waters and soils, chelation of metal ions by components of melanin in cat, dog, and human hair as well as by humic, tannic, and fulvic acids, antioxidant activity and polyphenol reactions with metal ions, studies of protonation constants for ligands and stability constants for metal-ion chelates, UV-visible and atomic absorption spectrophotometry, electrochemistry, analytical method development, calibration, and validation, field analytical methodology, and chemometrics. He is a member of the American Chemical Society, the Society for Analytical Chemists of Pittsburgh (SACP), the Spectroscopy Society of Pittsburgh (SSP), and the OnSiteSM Scientific Board. He is a former President of the Pitt-Greensburg Faculty Senate and served as Chair of the Division of Natural Sciences, Mathematics, and Engineering at Pitt-Greensburg from July 2008 through June 2014, and is again a regular full-time faculty member – with more time to devote to this short course.