Examples of Analytical Data Treatment Using Microsoft Excel: Part 1 – Outliers, Significance Testing, & Analysis of Variance

Examples of Analytical Data Treatment Using Microsoft® Excel™: Part 1 – Outliers, Significance Testing, & Analysis of Variance

Mark Stauffer
University of Pittsburgh – Greensburg

Course: sc034

1/2 Day Course

Beginner Level

Saturday, Feb. 24

Chemometrics, Data Analysis, Data Management, Statistics, Teaching Analytical Chemistry, Validation

$325 ($425 after Feb. 5)

More Course Info


Be sure to check out Mark’s other Short Course:

Examples of Analytical Data Treatment Using Microsoft® Excel™: Part 2 – Calibration & Regression (course #sc097)

Course Description

This course deals with the use of Microsoft® Excel™ for treatment of outlier data and results, significance testing involving two samples, and analysis of variance to test statistical significance of multiple samples affected by one or more factors. Participants are welcome to bring data related to their own work for practice purposes; suggestions from participants for this course are welcome as well. A basic operational knowledge of Excel™ is assumed. Participants will use Excel™’s math and statistical functions and analysis tools 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

In this version of “Examples of Analytical Data Treatment Using Microsoft® Excel™: Part 1”, the focus will be on outlier data treatment, using two-sample significance testing, and analysis of variance (ANOVA) in the Excel™ worksheet environment. The proposed outline for this course is as follows:

    1. Introduction, review of agenda, preliminary administrative items (approx. 15 min.)
    2. Treatment of outlier data/results in the Excel™ worksheet environment (approx. 60 min.)
    a. Dixon Q test
    b. Grubbs (G) test
    c. Modified Thompson tau test
    3. Two-sample significance testing in the Excel™ worksheet environment (approx. 75 min.)
    a. t test: comparison of data set mean to a known (“population”) mean
    b. t test: comparison of two means
    c. t test: comparison of “paired” data for individual samples
    d. F test: comparison of two variances
    4. Analysis of variance (ANOVA) in the Excel™ worksheet environment (approx. 80-85 min.)
    a. Some background on ANOVA
    b. ANOVA involving one factor: One-way ANOVA
    c. ANOVA involving two factors: Two-way ANOVA
    i. Two-way ANOVA with replication
    ii. Two-way ANOVA without replication
    5. Wrap-up (approx. 5 min.)

Also anticipated is integration of some of the following topics into the short course. Participants are welcome to make suggestions for any aspects of this course.

  • Using Excel’s™ mathematical functions
  • Graphing with Excel™: Excel’s™ many chart options
  • Using error bars in graphs
  • 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
  • Basic spreadsheet operations and formatting
  • Creating and using worksheet formulas

I encourage participants to bring any data/results from their work, etc. with them (within any proprietary constraints and other possible limitations, of course), to gain experience with the material learned in this course. I also encourage participants to contact me directly (mtschem1@pitt.edu), prior to the short course, about ideas they have to supplement the topics covered in this short course, and even other topics not they are given in the aforementioned outline.

I want this short course to be interactive, so the instructor and the participants may share and discuss, in an informal and relaxed manner, the many uses of Excel™ in analytical chemistry, and in this case, in working with outlier data/results and significance testing involving two or more samples. The instructor and the participants can benefit from their mutual experiences with using Excel™ and its many functions and tools. 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 will make this short course as hands-on as possible to allow the participants to work with various exercises that incorporate the topics 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.

Instructor’s Biography

Dr. Mark T. Stauffer is Associate Professor of Chemistry, and currently chair of the Division of Natural Sciences, Mathematics, and Engineering (NSME), at the University of Pittsburgh at Greensburg. The St. Marys, Pennsylvania native 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 nearly 90 students and produced over 80 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 has edited a book on applications of molecular spectroscopy (Applications of Molecular Spectroscopy to Current Research in the Chemical and Biological Sciences; M. T. Stauffer, Ed.; InTech Open Access Publishing, 2016; ISBN 978-953-51-2680-5 (print), 978-953-51-2681-2 (online)). He was Guest Editor for the Special Issue, “Selective Chelating Agents”, of the peer-reviewed online journal Sensors. Dr. Stauffer has presented short courses on analytical data treatment 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 chemistry colleague Dr. Christine McCreary) a short course on Excel™ in May 2002, sponsored by the Spectroscopy Society of Pittsburgh. A short course on analytical data treatment 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. Dr. Stauffer is a member of the American Chemical Society, the Society for Analytical Chemists of Pittsburgh (SACP), and the Spectroscopy Society of Pittsburgh (SSP). He is the recipient of an Honorable Mention for the 2016 Carnegie Science Awards in the University/Post-Secondary Educator award category. He is the Immediate Former Executive President of the Gamma Sigma Epsilon national chemistry honor society. He is a former President of the Pitt-Greensburg Faculty Senate and is the recipient of the Pitt-Greensburg Faculty Awards for Service (2007) and Professional Development (2016). Dr. Stauffer served as Chair of the Division of Natural Sciences, Mathematics, and Engineering at Pitt-Greensburg from July 2008 through June 2014, and was briefly a regular full-time faculty member until January 2017, when he returned to the NSME Division helm as its Chair.