Chemical Spreadsheet Investigations: Empowering Student Learning of
Concepts via Camouflaged Mathematical Exploration
Prince George’s Community College
A series of interactive Excel spreadsheets or Excelets for discovery learning in general chemistry has been produced. Students are engaged in numerous higher-order thinking and science process skills as they work through the Excelets and accompanying activities. All of this is done computationally in Excel using formulas.
Printable version (pdf) with hot links
Introduction [top of page]
Welcome to “CSI: Chemistry” where conceptual learning is done via dynamic exploration through interactive Excel spreadsheet investigations. Warning to seasoned chemical investigators: These interactive Excel spreadsheets or Excelets illustrate simple concepts for novice learners and conceal the mathematics to allow chemical concept exploration. The mathematics will surface on further investigation and the experts may notice that the science process is backwards (simulation à mathematical model). This is done using pedagogical license for general chemistry students! Now, it is time for students to “click-and-think.”
In the simplest of ways, the series of Chemical Excelets (Sinex, 2007a)
enhances the dynamic and interactive visualization of a vast number of chemical
concepts found in the general chemistry curriculum (Sinex
and Gage, 2003). Recently, Lim (2006)
summarized the use of spreadsheet simulations and their advantages. The static factual nature of topics
introduced in textbooks and a large number of college lectures is brought alive
in the easy-to-use, discovery format of Excel spreadsheets. What happens if we start a topic by plotting
data or use some sort of graphical approach, such as a bar or scatter
graph? Let’s look at the active
pedagogical learner-centered environment, as recommended by Siebert and
McIntosh (2001) and
Let’s consider the introduction of weak acid behavior as part of a lecture-discussion in second semester after acid-base definitions and pH have been covered. We are going to cover the classical calculations using the acid dissociation constant, but first let’s explore how the magnitude of Ka and initial concentration influences dissociation using an Excelet, as illustrated in Figure 1. Here we can change the starting weak acid and its initial concentration (done using the menus on the spreadsheet) and see visually how the dissociation is influenced as well as the calculated percent dissociation. Upon discovering the behavior we can then introduce the classical calculations via the initial-change-equilibrium or ICE chart.
Figure 1 – The Dissociation of Weak Acids (Link to Excelet, select “dissociation” tab)
After this, the effects of various stresses (common ion effect, salt effect, temperature) to the equilibria based on Le Chatelier’s Principle can be introduced in a discovery fashion. Figure 2 illustrates a means to discovering the common ion effect. Here the option button selects the common ion and the spinner controls the concentration by clicking on either the up or down arrows. Some of the questions on this worksheet have the answers available in comment boxes. This is helpful when students use the Excelets for reviewing out of class.
Figure 2 – Common Ion Effect (Link to Excelet, select “effect of common ions” tab)
The behavior of buffers can be discovered by comparing pure water, a weak acid solution, a weak acid and its salt mixture, and just a salt of a weak acid with the addition of a small amount of strong acid or base. Then buffer behavior via the salt/acid ratio can be explored, along with buffer capacity and acid distribution diagrams. All-the-while the classical calculations are introduced as needed to explain the discovered behavior from the numerical experimentation with the Excelets. The classroom experience is now discussion-based and not just straight lecture. Plus, all the Excelets used in class are available to students on the course webpage.
Now let’s put the Excelet in the hands of the students to investigate periodic trends (prior use of Excelets is assumed or students would be given a brief introduction). This requires a written activity where the instructions drive the student to discover the trends by exploring the plotted data as shown in Figure 3. Before exploring the periodic trends, students complete a set of pre-activity questions to be sure that they can do electron configurations for atoms and ions. Students, in small groups, use a laboratory period (2.5 – 3 hours) to go through this activity and the instructor will circulate and address questions. Occasionally, some whole class discussion may occur during the activity especially in discovering the cause of the trends. Some follow-up discussion will occur at the completion of this activity as well.
Figure 3 – Periodic Trends (Link to Excelet)
Students get to explore atomic radii, ionization energies (first and second), electron affinities, and electronegativities for the first 38 elements. Elements are identified by atomic number and via a tracer point, and the autofilters feature in Excel allow trends to be viewed by group or period. Ionic radii, multiple ionization energies, and some Group B element properties can also be explored.
The ultimate power of Excelets is to extend discovery-based laboratories where data are collected and analyzed. We have done this for the behavior of gases in first semester and the discovery of Beer’s Law in second semester. These provide the opportunity to explore multiple variables and address why we hold certain variables constant during experiments. Table 1 gives two examples of discovery-based laboratories that are enhanced with Excelets that include examining random and/or systematic errors. The use of the ideal gas law Excelet combined with the laboratory activity allows for a very discussion-based development of the ideal gas law and, at a later point in the semester, after an introduction to liquids, real gas behavior (Sinex and Gage, 2005).
Table 1 – Discovery-based Laboratories
Excelets and accompanying handouts
Many of the Excelets and associated activities function as out-of-class projects. A number of them build on handling and graphing data with Excel. Temperature scales, Raoult’s law, and radioactive decay have assessment data to analyze after exploring these topics with an Excelet. The temperature scales Excelet and activity are an excellent way to get students to deal with data in Excel and derive the temperature conversion relationships in an active discovery learning environment. Some results for the radioactive decay Excelet are discussed in Sinex (2005a). Excelets that introduce students to mathematical modeling of data, linear regression, and goodness-of-fit are available.
By constructing a spreadsheet with a number of manipulable variables using a variety of features available in Excel, an interactive environment with dynamic and animated graphs can be produced. This allows a multi-variable approach to investigating a large number of topics in general chemistry. With the mode of questioning designed into an activity to accompany an Excelet, a guided-inquiry based or discovery approach can be achieved. Students will uncover correct mathematical relationships using ideal data and then random and systematic error can be incorporated into the data to see the effects and get the data to be of a real-world nature. The idea of introducing students to random and systematic error utilizing a simple measurement was explored in Sinex (2005b) and is included in a number of Excelets such as radioactive decay and the ideal gas law. For further discussion on evolving your pedagogy to “click-and-think,” see Sinex (2007b).
Through “what if” scenarios and numerical experimentation, students investigate concepts or topics in a real-world, scientific manner. They make predictions, examine the influence of a number of variables, and draw conclusions either individually, in small groups, or even in a large classroom setting. Many higher-order thinking skills are also employed in these investigations. The basic information for producing Excelets can be found at the Developer’s Guide to Excelets website (Sinex, 2007c) which includes an Excel tutorial, illustrated instructions with numerous screenshots, and many more examples. All of this is done computationally in Excel (no programming required or use of Macros, just the use of formulas). For faculty wanting to expand their computational thinking, see the National Computational Science Institute website.
In a recent survey of 29 students in general chemistry II, 76% preferred the dynamic graphs in Excel over the static graphs in their textbook. The interactive nature of Excelets was favored because it allowed students to play and experiment (34%), discover on their own (14%), think (7%) or all three of these (41%). Table 2 gives the results for five of the questions in the survey. In general, the visual aspects are appreciated and students felt it enhanced their understanding of concepts. Their use of these Excelets for studying out-of-class could see some improvement.
Table 2 – Student Survey Results given as percentage (number of students)
Using Excelets does not require that you be familiar with Excel.
most definitely I think so just barely not at all don’t know
7% (2) 38% (11) 34% (10) 14% (4) 7% (2)
Excelets offer a more visual experience with graphs instead of using just the mathematical equations.
most definitely I think so just barely not at all don’t know
66% (19) 24% (7) 10% (3)
Excelets make it easier to grasp or learn a concept.
most definitely I think so just barely not at all don’t know
55% (16) 31% (9) 14% (4)
The instructions for using any interactive Excel spreadsheet are usually
too few need a little more just right too complicated don’t know
14% (4) 45% (13) 41% (12)
After the instructor uses an Excelet in class that is available on the web, do you go and use it to review and help enhance your understanding?
never once in a while usually always don’t know
3% (1) 59% (17) 34% (10) 3% (1)
Provided below are some selected comments from students (Table 3). In general, comments have been on the positive side and students have pointed out some concerns that are easy to address.
Table 3 – Comments from Students
On the positive side
- All of the excelets were very helpful
- Helps me interact and understand and to grasp info more
- It is visual and helps me to see and understand what can affect and cause a change
- Helpful in understanding concepts
- Great learning tool, visual
On the constructive side
- Some Excelets busy
- Add more questions for student to test themselves
- Never saw comment boxes
- More group work in class
- Larger display in class
Students were asked to rank how Excelets were used in the class. Results are shown in Figure 4 with lower scores indicating the more favorable responses.
Figure 4 - Rank of how Excelets are used (error bar is plus/minus one standard deviation)
As is typical, students opt for the use of Excelets in the more passive mode of a lecture; however, students are engaged and involved when this format is used. Even in a lecture/discussion format, they make predictions and they must conclude by observation what has happened when variables are changed.
Excelets provide a “click-and-think” mode of learning for students in a large variety of topics in general chemistry with an off-the-shelf software package. Even in a lecture environment, the “chalk-and-talk” mode can be minimized by using Excelets. Your classical lecture can now evolve into a more discussion-based format with students discovering concepts through predict-test-observe using technology as well. Student engagement via out-of-class projects or follow-up activities in the laboratory is increased considerably. Excelets also provide another great way to add interactivity to instruction in online classes. We can explore some mathematically more advanced topics from a conceptual approach, such as competing and consecutive reactions, that are a great introduction for organic chemistry.
Excelets address the visual (various types of graphs, use of conditional formatting) and kinesthetic (interactive features) learning styles. With the mode of questioning, they drive students toward deeper learning using an engaging pedagogy. Starting to develop a mind set for numerical experimentation in freshmen chemistry students will also help support the reform efforts suggested for physical chemistry (Zielinski and Schwenz, 2004), as we all work to strengthen students’ conceptual understanding. Combining the reflective formative assessment spreadsheets proposed by Wagner (2007) with simple Excelets as part of the question has potential for the future.
The author wishes to thank Barbara Gage of
Lim, K.F. (2006) Use of Spreadsheet Simulations in University Chemistry Education, Journal of Computer Chemistry, Japan, 5 (3), 139-146.
Sherman, T. and Kurshan, B. (2004). Teaching for Understanding. Learning and Leading with Technology 32 (4): 6-11.
Siebert, E.D. and McIntosh, W.J. Eds. (2001) College Pathways to the Science Education
Standards; NSTA Press:
Sinex, S.A. (2007b) Evolving Your Pedagogy from “Chalk-and-Talk” to “Click-and-Think” with Interactive Excel Spreadsheets, Proceedings of the AFACCT 2007 Conference - Critical Thinking, Critical Teaching (forthcoming). (accessed September 2007).
Sinex, S.A. and Gage, B.A. (2003) Discovery Learning in General Chemistry Enhanced by Dynamic and Interactive Computer Visualization, Chem. Educator 8 (4), 266-270.
Sinex, S.A. and Gage, B.A. (2005) Scaffolding a Conceptual Understanding of the Gas Phase in General Chemistry Enriched by Technology at 230th American Chemical Society National Meeting in Washington, DC. (accessed September 2007).
Zielinski, T.J. and Schwenz, R.W. (2004) Physical Chemistry: A Curriculum for 2004 and Beyond, Chem. Educator 9 (2), 108-121.
Wagner, J. (2007) Using Spreadsheets to Assess Learning, The Physics Teacher 45 (1), 34-37.