Using Computers in Chemical Education Newsletter - Fall 2007

Chemical Spreadsheet Investigations:  Empowering Student Learning of

Concepts via Camouflaged Mathematical Exploration


Scott A. Sinex

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.



Excel spreadsheet                     

discovery learning                    

          “click-and-think” pedagogy




Weak Acid Equilibra - A Discovery-based Lecture-Discussion

Interactive Periodic Trends – A Student-centered Activity

Using Excelets to Extend Discovery-based Laboratory Activities

Out-of-Class Projects

Tapping the Pedagogical Power of Excelets

Feedback from Students

Some Final Thoughts

References and Notes

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 Sherman and Kurshan (2004), which Excelets can create along with some questions for students to ponder.  Excelets can be placed in the hands of students for activities in and out of the classroom or can be used to introduce topics in a discovery-based fashion.  Excel is used as a data handling, analyzing, and graphing tool in our general chemistry program (Sinex and Gage, 2006) since a data-modeling approach is used to introduce many topics.


Weak Acid Equilibria – A Discovery-based Lecture-Discussion                        [top of page]

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.


Interactive Periodic Trends – A Student-centered Activity                                      [top of page]

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.


Using Excelets to Extend Discovery-based Laboratory Activities                    [top of page]

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


Out-of-Class Projects                                                                                   [top of page]

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.


Tapping the Pedagogical Power of Excelets                                                     [top of page]

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.


Feedback from Students                                                                               [top of page]

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.


Some Final Thoughts                                                                                     [top of page]

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 Prince George’s Community College and Susan Ragan of the Maryland Virtual High School.


References and Notes                                                                                   [top of page]

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: Arlington, VA.


Sinex, S.A. (2005a) Exploring Radioactive Decay in Excel:  An Interactive Visual Thinking Tool, Using Computers in Chemical Education Newsletter, Fall 2005. (accessed September 2007).


Sinex, S.A. (2005b) Investigating Types of Errors, Spreadsheets in Education 2 (1) 115-124.


Sinex, S.A. (2007a) Chemical Excelets:  Interactive Excel Spreadsheets for General Chemistry, (accessed September 2007).


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. (2007c) Developer's Guide to Excelets: Dynamic and Interactive Visualization with "Javaless" Applets or Interactive Excel Spreadsheets, (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).


Sinex, S.A. and Gage, B.A. (2006) Using Excel for Handling, Graphing, and Analyzing Scientific Data:  A Resource for Science and Mathematics Students. (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.


Return to Using Computers in Chemical Education Newsletter - Fall 2007


[top of page]