
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 (WileyVCH, New York, NY, 2011) (ISBN 9780470381236) is OPTIONAL. 


Target Audience
Professionals in industry, academia, government, and healthrelated 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 halfday 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, yintercept, Rsquare, 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 firstderivative 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 cellformatting 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 leastsquares curve fitting, and for solving 2ndorder and higherorder 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 (mtschem1@pitt.edu), 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 handson 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 WisconsinMadison, Shippensburg University, and Carnegie Mellon University. He joined the PittGreensburg 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 PittGreensburg 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), 429437; 2007, 40(3), 439452; and 2010, 43(7), 597601), a printed paper (2003, 80, 6567) 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 IronLigand Chelates”, in Stoichiometry and Research – the Importance of Quantity in Biomedicine (A. Innocenti, Ed.; InTech Publishing, 2012; ISBN 9789535101987). He was Guest Editor for the Special Issue, “Selective Chelating Agents”, of the peerreviewed 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 PittGreensburg 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 metalion chelates, UVvisible 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 PittGreensburg Faculty Senate and served as Chair of the Division of Natural Sciences, Mathematics, and Engineering at PittGreensburg from July 2008 through June 2014, and is again a regular fulltime faculty member – with more time to devote to this short course. 


 
