These tutorial screencasts should be viewed as shorter, bite-sized versions of the Excel lessons that are offered through the Chem 300 course. Although they can be viewed out of order, some knowledge of previous tutorials is sometimes assumed for full comprehension.
These screencasts come in two forms: shorter, more focused tutorials and complete lessons (with handouts). The shorter lessons can be viewed out of order, though they sometimes assume some knowledge from earlier tutorials; for example, the linest() function is explained in more detail in the first calibration curve tutorial than in later tutorials. The full lessons are more sequential and should be viewed in order if possible. The full lessons also include a link to a PDF file, which you should read before playing the screencast; eventually, you should be able to do all the steps in the lessons on your own, without the aid of the screencast.
Eventually each tutorial/lesson will be available for both Mac and Windows platforms, but the Excel principles are the same for both (the menus and shortcuts will change) so don't feel like you will get little from the "wrong" platform if it isn't available yet.
The screencast links are to the files on my Dropbox account. You can play the screencast in your browser and if your internet connection is fast enough that might be sufficient, but you will get better resolution if you download the clip to your hard drive and then play it. The longer screencasts also include "chapter" markers (eg, at each of the steps in a lesson); depending on your video player, you may be able to jump directly to key points in the video (the QuickTime player can do this, for example).
If any of the links don't work, please let me know.
| Topic | Screencasts |
|---|---|
| Excel Tutorials | |
| Setting up and using keyboard shortcuts; useful shortcuts for navigation, selection, and editing. Table of useful shortcuts: Mac & Win |
Mac (24:53) Win (22:21) |
| Relative and absolute addresses in Excel formulas; named cells and named ranges in Excel formulas; intro to array formulas | Mac (18:32) Win (22:25) |
| Array formulas (application: pooling standard deviations) | Mac (10:04) Win (13:02) |
| Hypothesis testing and confidence intervals | Mac (16:52) Win (18:38) |
| Linear calibration curves | Mac (13:36) Win (15:58) |
| Quadratic calibration curves (with an explanation of how to choose between linear vs quadratic fitting) | Mac (21:40) Win (19:21) |
| Internal standards method (using a calibration curve) | Mac (11:27) Win (13:50) |
| Standard addition method | Mac (12:53) Win (19:39) |
| Finding roots graphically and with the Solver (application: complicated equilibrium calculations) | Mac (20:58) Win (21:24) |
| Nonlinear regression and robust regression with the Solver | forthcoming |
| Full Excel Lessons | |
| Introduction to Data Analysis and Manipulation in Excel Application: calculating/plotting an acid-base titration curve. Excel concepts: basics of navigation, formula creation and editing, and plotting data. |
Mac (38:34) Win (54:22) |
| Functions in Excel Application: generate random numbers from two distinct probability distributions and use Excel functions to calculate some useful sample statistics. Excel Concepts: functions, array functions/formulas, mixed absolute/relative addresses. |
Mac (53:19 + 13:46 optional) |
| Plotting in Excel Application: plot a titration curve and its 1st & 2nd derivatives. Excel Concepts: plotting, adding data, secondary plots, adding plots as inserts, labeling individual data points. |
Mac (22:06) |