Look in any science or economics textbook and you will find graphs. The graphs tell a story which we want our students to understand. Typically, the graphs are mathematical models of systems that are dynamic and influenced by other variables not plotted. So, here come mathematical equations with four or five variables and we expect understanding. For beginning students, presenting these equations is not always the best way to provide an introduction to the investigation of a system. How about offering them a dynamic, graphical visualization of the model and hold off on the mathematical computations at this point?
Well, if you have a computer, you probably have Excel, and hence, the ability to produce an interactive graph of your model or a simulation of the system described by a target equation. Now you can pose questions and ask what is going to happen. Which variable has the greatest effect on the system? How does the graph change if we increase the value of a variable? An active learning environment has just been created in your classroom. Additionally, out-of-class investigative projects with further simulations can be assigned. This article provides the basis for an explanation of the development of interactive Excel spreadsheets. A basic knowledge of Excel to handle data and produce graphs is assumed. See Fryer (2002) or Sinex and Gage (2001) for help. No programming is required!
Constructing a Simple Interactive Excel Spreadsheet
Since we cannot just put an equation or function into Excel and plot it, we need to get the function in through calculation of data, and then plot the data. If the data is modified, the plot will adjust. Let's use as an example the standard form of the quadratic equation: y = ax2 + bx + c. How is the shape and position of the curve influenced by changing the coefficients a, b, and c? The coefficients are the variables we want to be able to adjust in value. Here is a diagram of what we are going to develop.
This is simply an adjustable computational spreadsheet, where the graph responds to changes in a, b or c. Our formula is the quadratic equation given above. In Excel, to lock-in the use of a single cell so it will not change when you drag a formula, you add the $ (dollar sign) to the cell notation (ex. $A$1). The graphs must be done as xy scatter plots, as this is the only plot where the x-axis is a variable. The curves on these plots should be the smoothed ones, as Excel uses a cubic spline to fit the data. A regression line (trendline) can be displayed too, as it will adjust to changes in the data as the regression equation is recomputed. This allows monitoring (display in a cell) of parameters, such as slope. Keep in mind that graphs will rescale automatically to the range of the data. If you want a particular x-y scale, deselect auto-scale and set it. A tutorial in Excel on how to build this spreadsheet and add further interactivity is included (Excel tutorial steps 1-4, click on the tabs near the bottom of your screen).
To prevent students from "fouling up" the spreadsheets, I recommend turning on the protection feature. Go to Tools from the menu, select Protection, and then select Protect Sheet. Use of a password is optional. Unprotect is accomplished the same way. However, before this is done, go to each cell where data is going to change and deselect the protection for the cell (deselect "locked"). This is done by right clicking on the cell, select Format Cells… and select the Protection tab. If you want to hide your formulas from view, then select hidden. See the blue box in step 4 of the Excel tutorial.
Do you want the students to see the data as it changes? If not, you can click on the column and select hide. It will not be viewable or printed. You can also place the graph on top of the columns too.
Fostering Higher-Order Thinking
Once you have the interactive Excel spreadsheet, a series of questions needs to be designed that explore the effects of changing the variables. It's important to ask students to predict what will happen if a specific change is made. Then let them investigate it. College students are often afraid to make mistakes; however, predictions are just educated guesses. As students start predicting they elicit prior knowledge and set up the opportunity to enhance conceptual understanding. Use the interactive spreadsheet to explore and discover relationships and not as a confirmation tool. Bring the mathematics in later or, better yet, derive the equation from graphical exploration and analysis. The effects of multiple variables can easily be investigated. In science, developing a simple model by experimental data collection is easily followed and extended by examining the effect of other variables. Simulations can show the sensitivity of a model to certain variables. Models can range from simple to very complex mathematical ones as discussed in Liengme (2002).
The use of a reference line, a set model with specific known conditions, is a good tool to evaluate comparative graphs (Excel tutorial steps 5-6). The analysis and interpretation follows as variables are adjusted and the graphs of the model at different conditions appear for comparison to the set conditions. The use of such an interactive simulation allows topics that are difficult to illustrate with static graphs to be more understandable when viewed in a dynamic fashion.
Build an activity around the simulation that guides your students to discover behavior and relationships initially. To help guide students to use a simulation wisely, it is advisable to suggest that only one variable at a time be explored and that the full range of a variable be studied in an organized fashion – low to mid to high range. Here are a set of questions to investigate the behavior of the quadratic equation that can be used with the included tutorial.
- How does changing the "a" coefficient influence the shape of the curve?
- What is the difference between two curves for a > 0 and a < 0?
- What does changing the "b" coefficient do to the curve?
- What does changing the "c" coefficient do to the curve?
- If "a" approaches zero, what happens to the curve?
The table below provides several examples using interactive Excel spreadsheets in three disciplines. Second-semester general chemistry students use the Beer's Law Simulator after a laboratory where they have collected data to derive Beer's Law. The simulator allows them to develop a through understanding of variables that influence a Beer's Law measurement, a very powerful analytical tool in chemistry and biology. Understanding when the model may breakdown and not hold true is an important aspect to consider also. Assessment questions on possible errors during colorimetric analysis are now easy to compose and use.
DisciplineSimulationMathematicsInteractive Curves - Exploring functions in college algebraChemistryBeer's Law Simulator (pdf activity)EconomicsTrade
Adding "Bells and Whistles" to the Simulation
Excel has the ability to add more interactivity. Two very useful items for adjusting variables are the scroll bar and spinner, which are available from the forms toolbar, not the ones from the control toolbar (Bradley, 2002). The scroll bar and spinner, which can be linked to a cell, allow values to be changed by clicking and, on the scroll bar, dragging. They have some limitations, such as only positive values and whole numbers can be entered; however, this can be overcome by writing formulas (see Excel tutorial steps 6-7).
The comment box, which can be added to any cell, is a great way to add hints or short explanations of calculations or data. Just right click on any cell, and select Insert Comment. A cell with a comment box is recognized by a little red triangle in the upper right corner (see Excel tutorial step 6). When the cursor is placed on this cell or you mouse over, the comment box appears.
Using Excel 2002 (part of Office XP), it is possible to get a movable data point on a graph. To do this you set up a computation and fit the data points with a regression line. The regression line responds to input variable changes. Next add a new data point in the columns, where an x-value is supplied by a separate cell input and the y-value is computed using the regression results. This is done as a new series and categories (x-values) in first column option and you can add marker lines (Liengme, 2002). This point will then respond to changes in x, calculate y, and replot on the graph. This is a great way to introduce the idea of interpolation and explore behavior especially of non-linear models (see Excel tutorial steps 8-9).
The data validation option, (under Data on the menu bar and then select Validation…), allows you to set limits on input parameter boxes, show a limit message, and/or a warning message as well. On selecting the cell, the limits appear as a message similar to the comment boxes. This is very useful for novices exploring models for the first time (see Excel tutorial step 10).
Some Final Thoughts
Exploring and discovering with interactive Excel spreadsheets, aka simulations, is a great way to get students involved in active learning. All of this is accomplished from a purely computational venue with no macros or use of Visual Basic for Applications (VBA). Adding macros/VBA will allow more advanced simulations. For examples in economics, see Between the Sheets and in physical sciences, see Interactive Excel.
My freshman chemistry students are constantly involved using simple computational simulations. More advanced students might get involved developing or "dissecting" to understand the basis of the computation. Excel offers educators at all levels an inexpensive and powerful capability for dynamic graphical visualization to enhance learner-centered instruction.
Email: Scott Sinex
References
Helen Bradley (July, 2002) Excel's Best-Kept Secret: What? You're Not Using the Forms Toolbar?, WinPlanet, (accessed June 2003).
Fryer, W. (2002) Excel Spreadsheets: An Excel Shortcourse for Teachers, Technology and Learning Magazine, (accessed June 2003).
S.A. Sinex and B.A. Gage (2001) Using Excel for Handling, Graphing, and Analyzing Scientific Data: A Resource for Science and Mathematics Students, (accessed June 2003).
B.V. Liengme (2002) A Guide to Microsoft Excel 2002 for Scientists and Engineers, 3rd edition (opens in new tab), Butterworth-Heinemann, Oxford, England.