Use of Spreadsheet Simulations in University Chemistry Education

Kieran F. LIM


1 Introduction

The use of computers and related technologies offers many new opportunities and challenges for chemical education. These technologies can be used for lecture presentations, multimedia simulations, visualisation, access to information on the WWW, discussion and communication, and chemical information and informatics. Specialist software is available for computational chemistry, symbolic mathematics, statistics, computer-based learning (CBL), and videoconferencing. Technology, which has not been designed for use in science or education, can nevertheless be used in teaching and learning: such "worldware" [1] include web browsers, word processors, spreadsheets [2] and email/communication software, even mobile phones [3].
One opportunity offered by the use of computers is the ability to conduct simulations, which have become a powerful tool in university chemistry education. Many publishers now offer "Living graphs" or other simulations as part of the textbook package [4]. Other recent examples include [5 - 9]. Part of the appeal of such simulations is well expressed in the following quotation from Haile [10]:
"... simulation can produce revelation. By this I mean that simulation has the potential to yield the unexpected. Most often the unexpected is readily understood: 'Oh yes, of course! Why didn't I think of that before?' ... I think this revelatory aspect of simulation is a major reason why it has become so popular and enthralling. People enjoy pleasant surprises, especially if they are also educational." [10]

2 Examples

This section presents examples from the author's teaching of the use of spreadsheet simulations in university chemical education. None of these examples use MACROs, but still demonstrate that spreadsheets are capable of a level of sophistication that is greater than commonly expected.

2. 1 One-Dimensional Schrodinger equation

In 1949, Kuhn introduced a quantum mechanical "particle-in-a-box" model of an electron in an extended p system [11]:

a is the box length. The shapes of some of these wavefunctions are shown in Figure 1.
The harmonic model for molecular vibrations has Gaussian-type wavefunctions:

Figure 1. The shapes of the four lowest-energy unnormalised wavefunctions for the particle-in-a-box model [11] (Equation 1) (left) and the harmonic oscillator (Equation 2) (right). The shaded zones are the classically forbidden regions.

The shapes of some of these wavefunctions are shown in Figure 1.
The use of analytical functions for these wavefunctions (Equation 1 and Equation 2) does not yield any insight. Furthermore, they obscure the similarities: the lowest-energy wavefunctions each have no nodes and one lobe, and are zero at the boundaries of the domain; as energy increases, the wavefunctions decrease in wavelength and increase in the number of lobes and nodes: see Figure 1. Use of a spreadsheet simulation quantum_well.xls (Figure 2) allows students to continuously vary the energy of a trial wavefunction to see how the energy affects the wavelength and overall shape of the wavefunction [12 - 14]. By easily switching between different potential-energy curves, the students can see the similarities in the shapes of the wavefunctions, even though their analytical forms (Equation 1 and Equation 2) may be different, or even non-existent in the case of the triangular (Figure 3) and other potential types.

Figure 2. A screen capture of the spreadsheet document quantum_well.xls for numerical simulation of solutions to the Schrodinger equation. Users are able to choose among a number of different potential energy functions (cell C11) and adjust the energy (cell C5) of the trial wavefunction. The shaded zones are the classically forbidden regions.

Figure 3. The spreadsheet document quantum_well.xls is able to find solutions to the Schrodinger equation for potential energy functions, which do not have an analytical form for the wavefunctions. The shaded zones are the classically forbidden regions.

2. 2 The harmonic oscillator approximation

The potential energy curve for a molecular vibration can be described by an anharmonic Morse oscillator (Figure 4):

However, Equation 3 is often approximated by a harmonic oscillator (Figure 2 and Figure 4):

Again, the mathematical forms (Equation 3 and Equation 4) appear very different and offer no insight: how can Equation 4 approximate Equation 3? Use of a spreadsheet simulation (Figure 4) allows students to continuously and smoothly change the Morse oscillator (Equation 3) into its harmonic approximation (Equation 4), by adjusting an anharmonicity parameter, related to De and b in Equation 3 [6]. The reverse procedure, smoothly changing from an harmonic to a Morse oscillator is a demonstration of the principle "delocalisation lowers energy", which is often cited in organic chemistry texts: here the principle is shown to be quite general, not just applying to electrons, but also applying to vibrations.

Figure 4. A screen capture of the spreadsheet document anharmonicity.xls, which shows the relationship between the harmonic oscillator and the anharmonic Morse oscillator. Users are able to adjust the harmonicity (cell C9) to continuous and smoothly change the Morse oscillator (Equation 3) into its harmonic approximation (Equation 4).

2. 3 Rotational structure in vibrational spectra

Linear molecules have vibrational spectra (Figure 5) with a number of almost-evenly-spaced peaks arising from rotational changes associated with the vibrational transition: (v+1, J')¬(v, J"). The energies of these rotational-vibrational transitions are given by:

where changes in the bond length on vibrational excitation alter the rotational constant, B. (Planck's constant, h, is a conversion factor for changing from frequency units to energy units.)

Figure 5. Part of the infrared spectrum for carbon dioxide. The main peaks are due to (12C)(16O)2. The smaller peaks are due to other isotopomers.

Use of a spreadsheet simulation (Figure 6) allows students to see how the spectrum depends on initial-state and final-state bond lengths, temperature and other parameters [5]. For example, Figure 7 shows that as temperature is varied, the relative intensities of the individual rotation-vibrational spectral lines change, but the transition energies (Equation 5) do not.

Figure 6. A screen capture of the spreadsheet document Vibrot.xls, which simulates the rotational structure in a vibrational or vibronic spectrum. Users are able to continuously and smoothly adjust the initial-state and final-state bond lengths (cells C9 and G9) and other parameters.

Figure 7. Simulation of part of an infrared spectrum showing how the peaks change intensity, but not position, as temperature is varied.

3 Discussion

Why are computer simulations so successful? Traditional approaches to teaching and learning chemistry have been limited by the use of real molecules. For example, if students are learning how the boiling points of substances increase with increasing mass, they may look at a series of substances such as helium, neon, argon, krypton and xenon: see Figure 8. However, the simple trend of boiling point increasing with mass has been complicated by other factors for the series H2O (water), H2S (hydrogen sulfide), H2Se (hydrogen selenide), and H2Te (hydrogen telluride).

Figure 8. Variation of boiling points with mass for two series of chemical substances. The simple trend of boiling point increases with mass has been obscured by other factors in the case of H2O.

The use of models has several advantages: models are simpler than the systems they mimic, thus removing the unwanted complicating factors. However, to achieve physically meaningful results, the parameters for most models are tied to reality. For example, while we may use spheres to model atoms, these atoms have masses that correspond to real atoms: 4 g mol-1 for helium, 20 g mol-1 for neon, et cetera. A more convincing argument would be to show how a property (eg, boiling point) changes as some variable (eg, mass) is continuously varied. This is possible using a simulation, which can use values that do not correspond to any physical system: a simulation is more complicated than the system being simulated [10]. For example, in a simulation, we could explore the effect of varying mass, by simulating an "atom", with the size and other characteristics of argon, but with the mass of helium, one order of magnitude smaller than the true mass of argon [15, 16].
It has often been claimed, "chemistry is an experimental science". However, in the mathematical approach to chemistry, either there are solutions (eg, Equation 1, Equation 2, and Equation 5) to mathematical-chemical equations (eg, the Schrodinger equation) or there are no solutions: where is there the possibility of "experimentation"? Simulations enable the student (or scientist) to play "what if" scenarios: what would happen to "property X" if the masses of the atoms were increased smoothly and continuously by an order of magnitude [15, 16]? Exploration is an important part of discovery and learning [17]. Henon and Heiles, in their landmark 1964 paper on chaos theory, refer to simulations as numerical experimentation [18]. Simulations ensure that the sub-disciplines of physical, theoretical, and computational chemistry are truly experimental science.
The success of simulations is also well supported by models of learning. Gardner refers to "multiple intelligences", one of which is the logical-mathematical intelligence [19]. Other authors have referred to learning styles [20, 21]. Different students will have different preferred approaches to learning. Some may favour the symbolism of mathematical equations; others will not. Simulations enable those students who are weak in the logical-mathematical intelligence, or who do not favour abstract thinking to gain an appreciation of the significance and meaning of mathematical equations similar to those shown above. Table 1 lists some free-text responses from students on the use of the spreadsheet quantum_well.xls discussed in Section 2.1. Simulations quickly demonstrate qualitative trends, by generating a large number of solutions, but without the tedium of working the mechanics of the mathematics [22, 23]. Learning is enhanced when students can engage with material on more than one level: simulations help to engage students on both visual and tactile-kinesthetic levels, through visualisation [24] and "doing".

Table 1. Some free-text responses from Deakin University students on the use of the spreadsheet quantum_well.xls discussed in Section 2.1. Students' grammatical and spelling mistakes have not been corrected.
"... you could see how the graph changed when you changed the energy"
"... easy to understand computer program"
"By reading about the Schrodinger Eqn I gained a better understanding of it, and by seeing the solns [solutions] on the spreadsheet also helped."
"It helped me learn how to use Excel better."
"It was interesting in terms of the varying potential models and their corresponding wavefunctions"
"... it was useful to visually see how the Schrodinger [sic] equation could be solved"
"Helped me to understand how energy levels are quantised ..."

There is no doubt that specialist mathematical software can prepare more sophisticated simulations for teaching and learning than spreadsheets [5 - 9]. Ehrmann has introduced the term "worldware" to describe software that was not designed for instruction [1]. Spreadsheets, like other worldware, are commonly available, inexpensive, and relatively easy to learn, because of the large user base. Most students have learnt how to use worldware before entering university: see Table 2. Since spreadsheets are commonly used in the commercial workplace, the use of spreadsheets provides another link between the world of chemistry education and the common world. Students like using spreadsheets: see Table 3. Furthermore, the use of worldware facilitates learning. Galbraith and Pemberton have shown that the use of specialist mathematical software (eg. MAPLE) can create additional barriers to learning [25]. (Of course, there will always be a need to use more specialised software in some upper-level classes.) Software that isn't designed for instruction can be good for learning [1]. Despite their shortcomings, the advantages of worldware [1] have resulted in increasing usage of spreadsheets in university chemical education [26, 27].

Table 2. Percentage of students reporting that they have the skill to use a type of software.(a)
Software type2000200120022003
Word processing999810097
Library catalogue44767670
(a) The 2000-2002 results are based on limited surveys of the author's first year undergraduate chemistry class [28 - 30]. The 2003 results are from a survey of first year classes from every faculty in the author's university [31].

The destinations of most chemistry graduates are in non-research environments, most of which use spreadsheets [see, for example, Table I in Ref. [32]]. The examples in this paper illustrate that, in many cases, university educators can both use simulations and align their own usage of software with that of their graduates' workplaces, without compromising any scientific or educational attributes of their visual aids. Thus university educators can be in a better position to prepare their students for future employment. This paper advocates that, where practical, university teachers should use spreadsheets to prepare simulation teaching aids. Spreadsheet simulations and other resources can be found in Spreadsheets in Education <>, the Journal of Chemical Education, the Journal of Chemical Education's JCE Webware collection <>, and the (United States) National Science Digital Library (NSDL) <>.

4 Summary

Worldware is generalist software, not designed for instructional purposes, but can often be used with great effectiveness in teaching and learning. Worldware has the advantages of being cheap, and readily available and most university students know how to use them before entering university. Spreadsheets are designed for numerical calculations. They can be used for modelling, i.e. for representations of the real world, and for simulations, i.e. for "what if" numerical experiments. Spreadsheet simulations are effective for student learning because they engage the students at both visual and tactile-kinesthetic levels and are extremely beneficial for those students who are weak in logical-mathematical "intelligence". The examples from the author's teaching, which have been presented here, demonstrate that even without the use of MACROs, spreadsheets can be used to build sophisticated simulations.
"Technology can enable important changes in curriculum, even when it has no curricular content itself. What matters most are educational strategies for using technology." [1]

Table 3. Some free-text responses from Deakin University students to the question "What did you like about this unit [subject]?"
"Use of computers especially Excel ..."
"I found the calculations a little difficult, but the use of Excel helped a lot"
"Use of Excel to perform multiple simple calculations"
"... sessions in computer room"

The spreadsheets, quantum_well.xls, anharmonicity.xls, and Vibrot.xls can be obtained from the Journal's website, <>, or from the author, Associate Professor Kieran F. LIM, e-mail:

This paper is the full version of a lecture [33] presented at the 2005 Pacifichem Conference. K.F.L. thanks: Ms Jeanne LEE () for collaborations on some aspects of this work, and encouraging and helpful discussions on the other aspects; Dr Amanda Kendle (University of Western Australia) and Professor William F. Coleman (Wellesley College, USA) for collaborations on some aspects of this work; Professor Masato M. ITO and the other members of the organising committee for an invitation to present this work to the 2005 Pacifichem Conference. The work presented in this paper has received ethics clearances (EC 264-2001, EC 29-2002, EC 239-2002) from the Deakin University Ethics Committee.


[ 1] S. C. Ehrmann, Asking the right question: What does research tell us about technology and higher learning?, Change: The Magazine of Higher Learning, 27, 20 (1995).
[ 2] N. Yoshimura, Simulation of acid-base titration curve by using table-function in Microsoft Excel, J. Computer Chem., Jpn, 5, 29 (2006).
[ 3] T. Yoshimura, Y. Nakayama and A. Uejima, Development and testing of chemistry education resources for a mobile phone, J. Comput. Chem., Jpn, 3, 35 (2004).
[ 4] P.W. Atkins and J. de Paula, Atkins' Physical Chemistry, 7th Edn, Oxford University Press, Oxford (2001).
[ 5] K. F. Lim, Bond length dependence on quantum states as shown by spectroscopy, J. Chem. Educ., 82, 145 (2005).
[ 6] K. F. Lim and W. F. Coleman, The effect of anharmonicity on diatomic vibration: A spreadsheet simulation, J. Chem. Educ., 82, 1263 (2005).
[ 7] T. J. Zielinski, Symbolic mathmatics docments large and small, J. Chem. Educ., 82, 1099 (2005).
[ 8] B. F. Woodfield, M. B. Andrus, et al, The Virtual ChemLab Project: A realistic and sophisticated simulation of organic synthesis and organic qualitative analysis, J. Chem. Educ., 82, 1728 (2005).
[ 9] D. Hanson, T. J. Zielinski, E. Harvey and R. Sweeney, Quantum states of atoms and molecules, J. Chem. Educ., 82, 1880 (2005).
[10] J. M. Haile, Molecular Dynamics Simulation: Elementary Methods, John Wiley and Sons, New York (1992).
[11] H. Kuhn, A quantum-mechanics theory of light absorption of organic dyes and similar compounds, J. Chem. Phys., 17, 1198 (1949).
[12] K. F. Lim, Using spreadsheets in chemical education to avoid symbolic mathematics, CCCE Newsletter: Using Computers in Chemical Education, 2003 (Spring), paper 5 (2003).
[13] K. F. Lim, Using spreadsheets to teach quantum theory to students with weak calculus backgrounds, Maths for Engineering and Science, ed. by C. Hirst, LTSN MathsTEAM, Edgbaston (UK) (2003), p. 24.
[14] K. F. Lim, Using spreadsheets to teach quantum theory, New Directions in the Teaching of Physical Sciences, 1, 16 (2003).
[15] K .F. Lim, Quasiclassical trajectory study of collisional energy transfer in toluene systems. I. Argon bath gas: energy dependence and isotope effects, J. Chem. Phys., 100, 7385 (1994).
[16] K. F. Lim, Quasiclassical trajectory study of collisional energy transfer in toluene systems. II. Helium bath gas: energy and temperature dependences, and angular momentum transfer, J. Chem. Phys., 101, 8756 (1994).
[17] G. Polya, How to Solve It; A new aspect of mathematical method, 2nd Edn, Princeton University Press, Princeton (1971).
[18] M. Henon and C. Heiles, The applicability of the third integral of motion: some numerical experiments, Astron. J., 69, 73 (1964).
[19] H. Gardner, Frames of Mind: The Theory of Multiple Intelligences, 2nd Edn, Fontana, London (1993).
[20] R. M. Felder, Reaching the second tier: Learning and teaching styles in college science education, J. Coll. Sci. Teach., 23, 286 (1993).
[21] R. Dunn, S. Griggs, J. Olsen, M. Beasley and B. Gorman, A meta-analytic validation of the Dunn and Dunn model of learning style preferences, Journal of Educational Research, 88, 353 (1995).
[22] A.E. Solow (ed.), Preparing for a New Calculus, Mathematical Association of America, Washington (DC) (1994), MAA Notes, vol. 36.
[23] R. Day, The computer simulation in the education process or computer simulations go to school, Australian Educational Computing, 1, 40 (1986).
[24] P. W. Atkins, The challenge of visualization (Plenary lecture), paper presented at Conference on Physical Chemistry, Hobart (Tas), 2004.
[25] P. Galbraith and M. Pemberton, Convergence or divergence? Students, Maple, and mathematics learning, Mathematics Education in the South Pacific, ed. by B. Barton, K. C. Irwin, M. Pfannkuch and M. O. J. Thomas, Mathematics Education Research Group of Australasia, Pymble (NSW) (2002), vol. 1, p. 285.
[26] S. Abramovich, Spreadsheet-enhanced problem solving in context as modeling, Spreadsheets Educ., 1, 1 (2003).
[27] J. E. Baker and S. Sugden, Spreadsheets in education: The first 25 years, Spreadsheets Educ., 1, 18 (2003).
[28] K. F. Lim and J. Lee, IT skills of students enrolled in a first-year undergraduate unit, Aust. J. Educ. Tech., 16, 215 (2000).
[29] K. F. Lim and A. Kendle, Computer and IT skills of Australian first-year university undergraduate students, Chem. Educ. J., 5, Paper 5-24 (2002).
[30] K. F. Lim, The ability of beginning university chemistry students to use ICT (information and communication technology) in their learning in 2002, Aust. J. Educ. Chem., 62, 27 (2003).
[31] K. F. Lim, A survey of 1st-year university students' ability to use spreadsheets at the start of 2003, Spreadsheets Educ., 1, 52 (2004).
[32] K. F. Lim, Some unusual applications of the "error-bar" feature in EXCEL spreadsheets, J. Chem. Educ., 79, 135 (2002).
[33] K. F. Lim, Use of spreadsheet simulations in university chemistry education (Invited lecture), paper presented at Pacifichem 2005, Honolulu, USA, 2005.