Developer's Guide to Excelets:
Dynamic and Interactive Visualization with "Javaless" Applets
or Interactive Excel
|Examples of Excelets||Instructions||Mathematical Modeling||Assessment||Chemical
(for general chemistry)
(for materials science)
(for intro astronomy)
Excelets are interactive Excel spreadsheets or simulations of mathematical models. The user changes a variable and the spreadsheet changes in numerical, graphical, and/or even symbolic form (equations). Through the use of numerical experimentation and "what if" scenarios, we have a powerful learning tool for students using readily available off-the-shelf software. All of this is done computationally with no use of programming, no macros or Visual Basics for Applications, VBA. The use of VBA adds another level of sophistication via programming and also possible security concerns. An Excelet with or without VBA resembles a "Javaless" applet. It is assumed that users of this guide are experienced with the use of Excel. If you need to gain this experience, see Using Excel for Handling, Graphing, and Analyzing Scientific Data: A Resource for Science and Mathematics Students (2003) to get started. If you are just converting over to Excel 2007, here is a quick guide to get you started. What features NOT supported in Excel 2007, click here! See Dealing with Data in Excel 2010 for using Excel 2010.
Here are some examples of Excelets in chemistry and mathematics (For best results - right click and then select Save Target As... to download, then open directly in Excel) :
|interactive features tour||fractions||derivatives|
(using drag-and-drop of data points)
("just add data", How does your bag compare?)
("just add data", visual display of variation)
(list box and lookup table, "just add data")
(deriving conversions and inverse functions)
|Eo vs. EN
(data culling using autofilters)
(using drag-and-drop of data points)
interactive periodic trends
(lots of features for changing graphs)
(coin equivalency, summation, and making change)
(comparison for a square and rectangle)
|The Acceleration Due to Gravity|
do you square a binomial?
(visualizing by graphing a square and determining area)
(two visual geometric proofs)
the Genetic Odds!
(random numbers, sample size, and error)
(concentric circles and setting areas)
(circular and elliptical motion and trig functions)
(linear and logarithmic scales)
|Exploring the Family of Quadratic Functions||
Rattle, and Roll…
(earthquakes, p & s waves with distance-time & locating epicenters)
(a 3-D surface plot)
|ideal gas law (activity)||radioactive decay (activity)|
(examines adding/subtracting positive/negative integers)
(a vertical shadow stick and many measurements)
(scaling and habitable zone)
(residence time and pumping)
(examine the behavior of water vapor in the atmosphere)
(the Hertzsprung-Russell or H-R Diagram)
(Doppler Effect and measuring velocity in astronomy)
|How small an impactor can reach the surface of a planet? (parameters that influence)||Will the peg fit in the opening?|
For more Excelets in general chemistry, see the Chemical Excelets page and for materials science, see the MatSci Excelets page.
How do I get started developing Excelets?
The basics of developing interactive computational Excel spreadsheets are described in An Interactive Higher-Order Thinking Tool, which includes an Excel tutorial to get you started. For step-by-step instructions to get started with two Excelets, see Interactive Excel Spreadsheets: Constructing Visualization Tools to Enhance Your Learner-centered Math and Science Classroom. Here is an excellent website for Excelets in mathematics:
Interactive MS Excel Workbooks and
instructions (See ICTCM 2008 for starters)
Listed below are a series of links to pdf files that describe a variety of useful things needed to develop Excelets. Numerous screen shots are included with the instructions. The forms toolbar tools are used because they operate on both PC and Mac platforms. Will Excelets function in Open Office Calc? - click here
To get the Forms Toolbar
For Excel 2007 - click here
|Loading the Analysis ToolPak||Using the Insert Function capability|
|To insert a comment box||How to add the interactive point for interpolation||Setting the scale on a graph|
|To use the data validation capability||Using the RANDBETWEEN function||How to add a tangent line on a function|
|To set the Conditional Formatting||Using the Protect Sheet capability||Turning a set of data on-off on a graph|
|Drag-and-drop of data points||Using Error Bars in a Creative Fashion||Data Culling with Autofilters|
|Check Box or Option Button from the Forms Toolbar||Using the List Box from the Forms Toolbar||Using the Vertical Lookup Table Function|
|Analysis ToolPak Troubleshooting Guide||Decimals and Negative Values for Spinners and Scroll Bars||Making Decisions using the Logical Functions|
a Systems Model Excelet
(Pan Water Cycle includes naming variables)
Square and Zoom Out (Excel)
(automatic square grid for data and a manual zoom out method for graphs)
|Formula Auditing Tools as an Instructional Tool in Building Excelets||To get the Developer Tab in Excel 2010|
Viewing spreadsheets containing macros requires a change in security - click here for instructions.
A major use of Excelets is to support mathematical modeling in the sciences.
|Mathematical Modeling of Data - Support materials|
|Getting started with your first model from experimental data:||Investigating the Height of a Stack of Cookies* ("Just add data" Excel)||Investigating the Height of a Stack of Cookies Activity** (pdf )|
|Measuring the Stack Height of Nested Styrofoam Cups ("Just add data" Excel)||Measuring the Stack Height of Nested Styrofoam Cups Activity (pdf)||Stacking Cups II ("Just add data" Excel)|
|Patterns in Data: Ideal and Real-world Examples (Excel)||
Rate of Change Explorer (Excel)
||Correlation: Are two variables related? (Excel)|
|Scatter of Data (Excel)||Best-fit Straight Line*** (Excel)||Types of Error (Excel)|
|Scatter of Data Activity (pdf)||Interpolation and Extrapolation**** (Excel)||Types of Error Activity (pdf)|
|Comparing Mathematical Models and Experimental Error (Excel)||Comparison of Limited and Unlimited Population Growth (Excel)||Developing Systems Models in an Excel Spreadsheet (Excel)|
|Exploring Measurement Error with Cookies (Excel)||Exploring Cyclic Data in Nature (Excel)||Atmospheric Carbon Dioxide over Time (Excel)|
|Using Excel’s Solver to minimize the SSE for a mathematical model (pdf)||Non-linear Regressions by Using the Solver ("Just Add Data" Excel)||Paradise Lost: Chesapeake Bay and Sea Level Change (Excel) ( pdf activity)|
|Numerical Methods (Excel)||Intercepts, both x and y (Excel)||The Mathematics of Straight Lines (Excel)|
|Best-fit Straight Line II: Examining Error in Linear Regression (Excel)||Kinetics of Cancer Cell Growth (Excel)||Stacking Nested Cubes: A Scientific Investigation - coming soon!!|
*http://mathforum.org/mathtools/tool/25383 **http://mathforum.org/mathtools/activity/25384 ***http://mathforum.org/mathtools/tool/20618 ****http://mathforum.org/mathtools/tool/22405
High school teacher-developed Excelets as part of Computation and Science for Teachers (CAST) Workshop held at the Pittsburgh Supercomputing Center, in Pittsburgh, PA and in partnership with the Maryland Virtual High School:
|Computational Resources||Overview of CAST|
|Computational Spreadsheets||CAST Professional Development Modules|
More high school teacher-developed Excelets from the Hampton Roads Modeling and Simulation Initiative.
An excellent source of information on spreadsheets in all disciplines is Spreadsheets in Education (eJSiE).
Excel resources for mathematics and the sciences - click here for a pdf file with hot links (2/11 version).
Please e-mail any corrections, modifications, suggestions, or questions.
Scott A. Sinex Prince George’s Community College 7/2013