Downloading Collected Data and Analyzing It Using the Excel Spreadsheet

If you do not have it already, download Acrobat Reader in order to easily print out this page. This file can be read and printed on virtually any platform -- Macintosh, Windows, or Unix. You can download the Acrobat player for free from Adobe. For Netscape users, you'll want to use the Acrobat plug-in that makes viewing Acrobat files a snap!

If you have not done so, follow your browser's directions to allow it to recognize and open Adobe Acrobat files. If you need help, you'll find that in the Adobe site as well.

IMPORTANT NOTE: You can only create a table in Excel if the data you plan to include is a number and only a number. If there are any letters with the numbers (ex. 10 ml.) you must remove them before trying to create a table. We are sorry for the inconvenience and we plan to correct this in the future.

• Downloading Collected Data and Analyzing It Using the Excel Spreadsheet

Downloading and Saving Files:

At the COIILS and IBSP web pages, or at any of the STAR/COIILS investigation pages, click on the button called Collected Data Pages/Downloads. When the Data Table Downloads page appears, choose the investigation from which you want to download your collected data and then click the "Create Data" button.

A new page will appear asking you to "Click to download current data from … followed by the name of the investigation.

The Save as… window will now appear and you must identify where you want the file saved. By clicking on the drop-down arrow, in the Save in… box, you will be able to save the file wherever you choose - usually under C: in My Documents, or to another drive (A: floppy, D: CD, E: Removable Disk). Once you have chosen the location, you can save the file (It will save with the ending .csv). NOTE: When doing this, a “Big Time Error” message may appear if too many students are trying to download the file at the same time.

Click on the Windows "Start" button on your bottom toolbar. In Programs, double-click on the Microsoft Excel icon to open it. Once Excel is up, go to File on the toolbar and choose Open in the drop-down menu.  When the Open… window appears make sure All Files (*,*) is chosen from the drop-down menu in the Files of Type: box at the bottom of the window. In the Look in: box at the top of the Open… window, locate the file you wish to open in My Documents or on another Drive.

This file will usually appear with part of the investigation name and end in .csv. Double-click on the file and it should open in Excel. To save this file as an Excel document, go to File on the toolbar and choose Save As… in the drop-down menu. When the Save As window appears go to the bottom box and choose Microsoft Excel Workbook (*xls)… from the list. If you want to change the name of the file you can do that by typing it into the File Name: box. Click the Save button and the file will now be saved as an Excel (xls) file.

Manipulating Downloaded Data in Excel:

We are going demonstrate the process using some sample data from the Veering investigation as a fairly simple example. NOTE: Some data will be easier to manipulate than others, but as your students begin to experiment with Excel, they should discover a variety of ways to manipulate even the most complicated information.

We will begin by opening the veerexp.xls file. This file will probably extend beyond the width of the screen and you will have to scroll to the right using the scroll bar at the bottom of the screen. The columns will also be squeezed so that it may not be possible to completely read the headings. You can widen the columns by moving your cursor into the gray areas at the top of the spreadsheet that contain the column letters - A,B,C… and placing it on one of the column lines so that it becomes a bold cross with arrows at each end of the cross line. Double-click and the column will expand to the width of the column heading.

NOTE: You may actually want to make the column narrower by holding the left-click down after the cross appears and then moving the column line one direction or the other. The names of the column titles can also be changed by clicking on the name to create a bold lined box around it and then typing in the new name.

Creating a Chart:

To analyze certain pieces of data in the Excel file, hold down the Control (Ctrl) button on your keyboard and then, holding the left-click button in on the mouse, move the cursor across the area you want in your chart (graph). As long as you keep the Ctrl key down, you can let up on the left-click, move the cursor and then highlight another piece of data or column headings. In the example used below, we highlighted one whole section of the spreadsheet, but we could have chosen to just highlight column I and column M, and then compare their data in a chart.

NOTE: You may discover from time-to-time that you have highlighted more information than can be clearly displayed on a chart (graph). You may also find that you need to create additional columns or rows where you can total and/or average data, and sometimes it is even necessary to relocate the data to produce the chart (graph) you are wanting (See directions at the end of this document). NOTE: Don't forget to "Save" your file as you go.

After you have highlighted the components of the Excel spreadsheet that you want in your chart (graph), go to Insert on the top toolbar and click Chart… located in the drop-down list.

 The Chart Wizard Window should appear showing Step 1 of 4 - Chart Type. A Chart type can be chosen in the left-hand list and then a Chart sub-type can be chosen on the right-hand list. In this example, we are going to choose "column" and the first sub-type.

Click the "Next" button. A new Chart Wizard window will appear showing Step 2 of 4 - Chart Source Data. Where it says Series in: make sure "rows" is chosen (you can also choose columns and see what that chart will look like.). Click on the Series tab and make certain Series 1 is highlighted in the left-hand column under Series. Next to Name:, you can type in the name of the team - Team 1 (from column D in this case). Click on the Add button under the Series box and Team 1 should be added to the top of the list. Highlight Series 2 and now type in Team 2 in the Name: box and add it. When all of the groups have been added in this order, highlight each of the remaining Series# and click the Remove button until they are all removed and only the Teams remain. NOTE: Be careful to follow this process or you may lose some graph data and need to return to this point to redo the information.

Additional Instructions for Veering in an Excel Spreadsheet: Begin by choosing a "line" graph for your veering chart. To get the veering graph to begin at the zero point on the "Y" axis you must place your pointer on the on the "X" axis (the line running midway on the graph with the distance marks labeled) and it should say "Category Axis". Right-click and it should say "Format Axis". Uncheck all three boxes at the bottom of the "Scale" tab. 

 

Click the Next button and Chart Wizard - Step 3 of 4 - Chart Options should now appear. Make sure the Titles tab is chosen. You can now type in the name of the chart and also identify the X and the Y axis'. We will title this chart "Veering Paths of Right and Left-Handed People" and type it in under Chart Title. If you wait just a second or two, the title will appear above the chart. We will identify the X axis as "Test Groups" and the Y axis as "Number of People". NOTE: The exact descriptions will vary, but it is important to challenge your students to provide descriptions that will best identify what the chart is trying to depict. The rest of the tabs under Chart Options are there for you and your students to manipulate in any way that you feel will best present the chart.

Click the Next button and Chart Wizard - Step 4 of 4 - Chart Location should now appear. If you choose "As new sheet:" a separate new sheet, containing the chart, will be created in the Excel file and the tab will now appear at the bottom of the Excel spreadsheet page. If you choose "As object in:" the chart will be inserted below the existing data. I usually choose "As new sheet". This allows you to create several different charts, all on different sheets in the same file. NOTE: If you right-click on the sheet tabs in the Excel spreadsheet, you can rename the tabs to better identify each chart (graph) produced.

Now that the chart (graph) is created, you and your students can manipulate it even more. You can change the colors of the graph bars by double-clicking on them. A window should appear with color options and other tabs to manipulate. You can change the width and height of your chart and legend by single-clicking on them and then placing your cursor in any of the small black boxes around the edge. Double arrows will appear, allowing you to widen or narrow the box by holding your left-click down and moving the cursor. By double-clicking on the chart or legend, you can change the fill color. By right-clicking inside the chart, you can return to any Chart Wizard window to make changes. NOTE: At any time you decide you want to undo something, go to Edit in the toolbar and choose "Undo" from the drop-down list. With this information, you can begin the process of manipulating data in Excel to create a variety of charts (graphs).

NOTE: The following information will help you manipulate and expand the existing data so that it can be graphed in other ways.

Inserting a Row or Column:

To insert a row, place your cursor on the row number on the left-hand side just below where you want the row inserted and left-click. This should highlight the entire row. Go to Insert on the toolbar and click on "Rows" in the drop-down list. This will insert a row. To insert a column, place your cursor on the column letter at the top of the table just to the right of where you want the column inserted. Go to Insert on the toolbar and click on "Columns" in the drop-down list.

Totaling a Column:

To total a column, highlight the empty box beneath the columns you want to total. Go to Insert on the toolbar and choose Function… in the drop-down list. A Paste Function window will appear. Choose "All" in the left-hand list under Function category. Scroll down the right-hand (Function Name:) list until you find "SUM". Highlight "SUM" and then click the OK button. A SUM window will appear with the range of column boxes it assumes you want to sum. NOTE: You can change the range by highlighting the number you wish to change and then typing in the correct one. Click OK and the box should now contain the total for that column. If you highlight that box, the formula will appear in the Formula Bar above the spreadsheet. If you want to sum other columns to the right or left, click on the box with the sum in it. Move your cursor to the bottom right-hand corner where it should change from a thick plus to a narrow plus. Left-click and hold while you move your cursor left or right through the next column(s). When you let up you should see that the columns have been totaled. NOTE: The same process applies to totaling rows.

Averaging a Column or Row:

The same process applies as above, only choose "AVERAGE" from the right-hand (Function Name:) list in the Paste Function window.

Converting Your Excel Graphs and Charts to a Web Page:

To convert your Excel chart and graph to a web page, go to “File” on the menu bar and choose “Save as Web Page”. When the “Save as” window appears, choose where you want to save your file by clicking on the arrow in the “Save in” box and locating your folder.  (NOTE: For an easy way to make sure everything gets moved to the web server, create and name a folder on your desktop in advance and then save it there.) Once you have located your folder, click “Save” in the bottom right-hand corner.  Your Excel data table, chart/graph(s) have now been converted to a web page that can be linked to your own schoolyard home page on your web server.  (NOTE: Be sure to give your webmaster both the html file and the folder with the same name and “_file” as the extension.)