Interactive Excel Spreadsheets: Constructing Visualization Tools to Enhance Your Learner-centered Math and Science Classroom Scott A. Sinex Department of Physical Sciences and Engineering Prince George’s Community College http://academic.pgcc.edu/~ssinex Workshop presented at Powering Up with Technology Conference Prince George’s County Public Schools Northwestern High School 18 November 2006 Interactive Excel Spreadsheets: Constructing Visualization Tools… 1 Interactive Excel Spreadsheets: Constructing Visualization Tools to Enhance Your Learner-centered Math and Science Classroom This is a quick guide or introduction to developing interactive Excel spreadsheets or Excelets (aka – simulations). It assumes that you have a basic knowledge of working with Excel. The objectives of this guide are four-fold: 1. to introduce you to the Forms Toolbar and a number of the tools available; 2. to create a “just add data” interactive Excel spreadsheet; 3. to create a dynamic graph with manipulable variables interactive Excel spreadsheet, and; 4. to resize the Excelets to fit for full screen viewing on your computer. Interactive Excel Spreadsheets: Constructing Visualization Tools… 2 Getting the Forms Toolbar Let’s get the forms toolbar. Go to View on the menu bar and select Toolbars and then select Forms (a check mark will appear when you click on it). The toolbar will then appear as shown below. You may have to drag it to “dock” it with the toolbars at the top of the screen. In Excel, if you move the cursor over each tool it will show you its name. Constructing a simple “just add data” spreadsheet – The M&M Color Comparison Open up a blank Excel worksheet. Set up two columns, one with the M&M colors and the second with the number of each color (you can make these up here). Label the columns – color and number. Leave row 1 empty for a title. Click on the cell A2 (“color”) and highlight all the data as shown here. Interactive Excel Spreadsheets: Constructing Visualization Tools… 3 Now click on the chart wizard icon or go to Insert on the menu bar and select Chart… When the Chart Wizard menu pops up, select the Chart Type: as Column and use the clustered column that is highlighted black already (default). Click the Finish button and the chart should appear as illustrated below. If you click on one of the columns or bars on the graph it will highlight them all and then click again and it should just highlight the one you are clicking on. Now double click and you can set the colors to match the M&M’s. Interactive Excel Spreadsheets: Constructing Visualization Tools… 4 Now let’s add a total to the number column, so we can make the comparison to the official color percentages from: http://ww2.mms.com/us/about/products/milkchocolate/ Click on the cell B9 and then click on the AutoSum icon on the toolbar. You should get the screen shot to the right. If you hit the Enter key, you will get the total number of M&M’s in your bag. Go to the M&M’s website given above and get the percentage of each color in a bag of milk chocolate M&M’s. Place them in column D. In column C, we will calculate the number of each color that in theory should have been in your bag. Here is a screen shot to the left with the official values and the set up for the calculation using the percentage from the M&M website. The number of each color in theory is calculated by taking the actual total number of M&M’s in your bag ($B$9) and then multiplying by the official percent of that color (D3) divided by 100 to get a fraction. The total is from the SUM calculation. Once you have the formula in the cell, click the mouse and a number will appear. Drag this formula down to complete the column. Interactive Excel Spreadsheets: Constructing Visualization Tools… 5 Now we need to add the new column of data to the bar graph. Click on the bar graph to highlight it. You should see that the data plotted is also highlighted. Place the cursor on the lower right corner of the blue box as shown on the screen shot to the left. The box will darken and the style of the cursor changes as shown. Drag the box to include the data in column C. This will now add a new set of bars on the bar graph. This is the comparison of your data to the official M&M color percentages. You will need to change the colors as before. The right bar of each pair on the graph will be the value based on the M&M website. You may want to change the pattern to help distinguish them from your actual data or wait to see what is next. If you clear the number column (C3-C8) but leave the rest of the worksheet intact, data can be added by your students to get an instant comparison. I would suggest moving the graph to cover columns C and D to hide them from view. Interactive Excel Spreadsheets: Constructing Visualization Tools… 6 Would you like to hold off the comparison until all the data for each color was added and viewed on the graph? We can do this using a Check Box from the Forms Toolbar in combination with logical statements. Click the Check Box icon and then go to F16 on the worksheet and click. This will deliver a Check Box. Right click on the Check Box, select Edit Text to label the check box. Right chick on the Check Box, select Format Control… to get the Format Control pop-up menu, and then select the Control tab. Click in the box of the Cell Link: and then click on F16. Click the OK button. Now if you click on the check box it will return “TRUE” when checked and “FALSE” when unchecked in cell F16. Now go to cell C3 and change the formula by adding the yellow highlighted part shown below: =IF($F$16=TRUE,$B$9*D3/100,0) Drag this formula down the column to C8. Now when you click on the checkbox it will use the original formula if true or return a zero if false. The zero in this column will drop the bars so that they are not viewable on the bar graph. This will allow students to turn the comparison values on and off and remind them that the right bar in each pair is the M&M website data. You still need to clean up things and label, but I will let you do that later. To remove the gridlines on the worksheet, click on the Toggle Grid icon on the Forms Toolbar. Use this spreadsheet to test the following hypotheses: h How do different bags of the same size of M&M Milk Chocolate candies compare? Interactive Excel Spreadsheets: Constructing Visualization Tools… 7 h Do the bags come close to the official numbers from the M&M website? h Do you see a difference between small bags and large bags of M&M’s? Constructing a dynamic graph with manipulable variables – The Equation of a Straight Line Now let’s create an interactive Excel spreadsheet that graphs a straight line where we can adjust the slope and y-intercept and see how the graph responds. The slope and y-intercept will be our manipulable variables. We will also display the y = x line for comparison. Open a new worksheet and add the items shown on the screen shot below. Drag the formula in cell B4 down the column. Now if you go to cell F2 you can change the slope or to cell I2 to change the intercept. The data using the formula in column B will respond. Now highlight the data in columns A and b and then go to the Chart Wizard and set up a xy scatter plot using the “connect with lines” sub-type. Press the Finish button. Now you have a graph that will respond to changes in the slope (m) and y-intercept (b). Since Interactive Excel Spreadsheets: Constructing Visualization Tools… 8 the autoscale feature on the axes is the default, the graph rescales automatically, so the line may not appear to change. This can be disabled by clicking on each axis and deselecting autoscale. Or we can add the y = x line for comparison. Add the data in column C and then click on the graph and drag the blue box as before to get the y = x line to appear on the graph. Now when you change the slope and intercept the line changes with respect to the y = x line and effects of the change are easier to visualize on the graph. Let’s put sliders, or scroll bars as they are called in Excel, on the slope and intercept variables. Go to the Forms Toolbar and click on the Scroll Bar icon. Click on an empty area of the worksheet to insert or drop the scroll bar. You will get the big vertical scroll bar as shown in the screen shot to the right. Put your cursor on the lower right corner (its style will change as shown) and click and drag upward until it changes to horizontal; resize it to look good. Right click the horizontal one and copy and then paste to get another one. Move them to a position two cells below the slope and intercept cells (see the next screen shot). Interactive Excel Spreadsheets: Constructing Visualization Tools… 9 Right click on the scroll bar for the slope and select Format Control… and the Control tab. Link the scroll to cell F3 and leave the settings the same. Repeat for the intercept linking to cell I3. You can then move the sliders up to cover the cells where they are linked (but not yet). A disadvantage of the scroll bar (and the spinner too) is that it can only be changed by positive whole numbers, so we need to use formulas to generate decimals and negative values. In the following cells, place the formulas as shown: In cell F2 In cell I2 =F3/10-5 =I3/10-5 What the formula above accomplishes: Slope: -5 to +5 by 0.1 Intercept: -5 to +5 by 0.1 Interactive Excel Spreadsheets: Constructing Visualization Tools… 10 If you want to turn the y = x line off and on, get a check box as before and link it to cell F24. Change the formula in cell C4 to =IF($F$24=TRUE,A4,0) and drag it down the column. To hide the line (actually just lay it on the x-axis): click on the pink line or the pink markers on the graph to change its color to black and the marker to none. Again there is some clean up left to you, such as add a title and label the axes on the graph. Write some “what if” questions to get your students exploring with this Excelet. h If the m = 2 and b = 0, how does the line change compared to y = x line? h If the m = 1 and b = 2, how does the line change compared to y = x line? h What are the values of m and b for the y = x line? h What if m = - 1 and b = 0, how does the line compare to the y = x line? h How do you generate a line parallel to the y = x line? Students can actually discover slope and y-intercept. Excelets have the potential to evolve your classroom from “chalk-and-talk” to “click-and-think.” Sizing your Excelets to fit the computer screen If you go to View on the menu bar and select Zoom… Then the Zoom menu will pop- up and you can size things so that all areas are in the view of your students. Interactive Excel Spreadsheets: Constructing Visualization Tools… 11 For more interactivity with “how to” instructions (as pdf files) and many pre-built examples of interactive Excel spreadsheets or Excelets to download, see the: Developer’s Guide to Excelets at http://academic.pgcc.edu/~ssinex/excelets Take the interactive features tour to see the possibilities that Excel has to offer. All these Excelets are done by computational methods (formulas in cells) with no programming required. Many of the Excelets have guided-inquiry based activities with them. Please feel free to contact me with any questions, comments, or problems you may encounter. e-mail: ssinex@pgcc.edu The author wishes to thank Barbara Gage of Prince George’s Community College and Susan Ragan of the Maryland Virtual High School.