Collecting Data with Excel

This tutorial provides examples of how Excel can be used to create data collection templates that might be used in a classroom laboratory.

Those who are unfamiliar with Excel may wish to first read The Basics of Using Excel.

The fundamentals of graphing data with Excel are described in Graphing with Excel.

Those interested in another application of Excel in the curriculum should read Grading with Blackboard and Excel.

Preformatted templates can be useful in lower-level lab courses to hide the complexities of Excel.

A Data Collection Template

Template Formatting Options

A Data Collection Template

Excel has a number of features that make it useful for designing data collection templates.

• An introductory astronomy lab uses a planetarium program to calculate positions of planets and compare them with some famous predictions by Kepler:
  
  
   
• The data that the students collect are to be entered into the white cells; the others contain formulae that automatically calculate the desired results.
   
  To indicate their special status, they are selected and then changed to a different background using the Format Cells dialog:
   
  

  	Menu:	Format Cells...
  	Click:	Format Cells Patterns
  	Click:	Format Cells Patterns Cell shading Color: [gray]

   
  

• This, of course, will not prevent them from being messed up, so we can also lock them:
   
  

  	Menu:	Format Cells...
  	Click:	Format Cells Protection
  	Click:	Format Cells Protection Locked

  
   
  

  Note that you can also hide formulas if you choose (but not their results).

• Once the template has been set up and the calculation verified, a duplicate can be made where the input data is cleared and the protection is turned on:
   
  

  	Menu:	Tools Protection Protect Sheet...
  	Type:	Protect Sheet Password (optional):

  
   
  

  Clearing the data doesn't affect the structure of the formulae or the graph; they remain ready to calculate or display the data.

Template Formatting Options

Excel provides a lot of flexibility for setting up templates.

• The planetarium program provides the data in days for the first four planets (because they are relatively short), and in years for the last five planets:
   
  
   
  However, for our calculations we need the period to be consistently expressed in years.
   
  So, the template can be organized to convert days to years for just these four planets but not the others (note the formula for cell C2).

• One thing that students always want to do is write down too many digits of information.
   
  The cells can therefore be formatted to display only the appropriate number of digits for the quantity in question (see the picture above)
   
  Any extra digits "disappear" in the display, reinenforcing the notion that they are meaningless.
   
• Kepler's Third Law equates the square of a planet's orbital period P to the cube of its semimajor axis a (its average distance from the Sun).
   
  It is therefore useful to graph P and a on a log-log plot, as they should then fall along a straight line with a 2/3 slope:
   
  
   
  Excel allows you to easily set a logarithmic scale:

	Double-click:	[chart] [axis]
	Click:	Format Axis Scale
	Click:	Format Axis Scale Logarithmic Scale

 
Note that the minimum and maximum values must be positive for this type of scale.

• This brings up an interesting dilemma when data hasn't been entered yet.
   
  The input to the graph will be zero, and Excel complains vociferously.
   
  To get around this, the calculation of the semimajor axis a can have a small number added to it, as in cell F2 below:
   
  
   
  Because the representation of the number has been limited to a few digits, the calculation shows up as 0.000 when there is no input data.
   
• This approach doesn't work for the period P because its data is partially entered directly, which would be deleted when saving it without input data.
   
  As an alternative, you may set up a masked calculation field, used only by the graph, by setting its font to white on a white background, as in cell H2 below:
   
  
   
  If the cell is hidden as well as locked, then its formula also won't show up when the template is protected.
   
• You can download this template by clicking here.