Teaching Effectively from a Live Spreadsheet

An Interactive Spreadsheet for Teaching the Forward-Backward Algorithm gives a lesson plan and materials for teaching from a live spreadsheet.

If you want the class to run smoothly, it helps to know the shortcut keys and tricks that will help you navigate quickly around the spreadsheet. This page lists relevant tricks in Microsoft Excel. You certainly don't need to know or use all of these tricks.

As the paper says:

It is possible to teach from a live spreadsheet by using an RGB projector. The spreadsheet's zoom feature can compensate for small type, although undergraduate eyes prove sharp enough that it may be unnecessary. (Invite the students to sit near the front.)
Of course, interesting spreadsheets are much too big to fit on the screen, even with a ``View / Full Screen'' command. But scrolling is easy to follow if it is not too fast and if the class has previously been given a tour of the overall spreadsheet layout (by scrolling and/or zooming out). Split-screen features such as hide rows/columns, split panes, and freeze panes can be moderately helpful; so can commands to jump around the spreadsheet, or switch between two windows that display different areas. It is a good idea to memorize key sequences for such commands rather than struggle with mouse menus or dialog boxes during class.

To use as much of the screen as possible, apply these commands ahead of time:

Setting up multiple views ahead of time:

As distributed, the Forward-Backward spreadsheet already has 9 useful windows already set up for you (with zoom levels set for a standard 800x600 LCD projector):

  1. Initial probability table.
  2. Split screen between the initial probability table and the reconstructed weather graph. During lecture, you can scroll this view horizontally (Alt-PgUp/PgDn) to see the second-order graph of iteration 0, as well as the tables and graphs for subsequent iterations.
  3. Trellis diagram.
  4. Table of calculations. You will want to scroll around in the columns of this graph. I also like to jump back and forth between this and the previous view.
  5. Second-order probability graph. During lecture, you can scroll the right half of this view horizontally, as before. This shows how the second-order probabilities are plugged back into the next iteration. (To see more of the initial weather construction in the left half of the view, scroll that half, or drag the vertical divider back and forth.)
  6. Bird's-eye view of the whole spreadsheet. This lets the students see how the iterative computation is laid out.
  7. Result of 10 iterations.
  8. Final parameters, and other parameters to copy back over the initial parameters if desired. Switch back to window 1 to paste.
  9. Perplexity over time.
  10. Excel's default view. This is not very useful when teaching, and there's not a key to switch quickly to window #10. However, it is the initial view when you open the spreadsheet. As the students arrive at class, point to the small text on the screen and invite them to sit closer.

Pointing to things on the screen:

Commands for moving around quickly:

Commands for zooming:

Other commands you will need:


Advanced Maneuvers

As distributed, the Forward-Backward spreadsheet already has these features turned on in various windows. But if you want to set things up yourself, here's how.

Commands for viewing multiple parts of the worksheet at once:

You might want to set up the windows a little differently than in the distributed spreadsheet, for example because you have different screen resolution, or because you want to teach differently. Here's how you could have set up the default ones:

  1. Initial probability table. Zoom: 250% (use Alt-VZC). Cell B10 in the upper left corner.

  2. Split screen between the initial probability table and the reconstructed weather graph. Zoom: 175%. Put cell B10 in the upper left corner. Go to cell B20 and use Alt-WS (Window / Split). Now scroll the lower pane to put cell B67 in the upper left corner.

  3. Trellis diagram. Select the region underneath the diagram (e.g., press F5 and type "J1:U15"), then zoom in on it with Alt-VZF.

    I use this graph to make the students derive the recursive alpha and beta formulas. Before class, I write something like this on the board, to be filled in during class:

  4. Table of calculations. Zoom: 195%. Put cell A25 in the upper left corner. Now go to cell C27 and use Alt-WF (Window / Freeze) to freeze the row and column headers.

  5. Second-order probability graph. Zoom level: 150%. Put cell B10 in the upper left corner. Go to cell F17 and use Alt-WS (Window / Split) to split the screen 4 ways. Now scroll the lower right pane to put cell K67 in the upper left corner.

  6. Bird's-eye view of the whole spreadsheet. Zoom: 25% or 50%. Put cell A1 in the upper left corner.

  7. Result of 10 iterations. I like to show the 3-D graph at the same time. Zoom level: 150%. Ctrl-End to see the 3-D graph. Scale the 3D graph to the same height as the iteration-10 graphs, and put it directly to their right (obscuring some blue text). Now put cell GU10 in the upper left corner. Go to cell HC18 and use Alt-WS to split the screen 4 ways. Now scroll the lower right pane to put cell HO66 or so in the upper left corner.

  8. Final parameters, and other parameters to copy back over the initial parameters if desired. Zoom: 250% (or whatever you used for the initial probability table, since you'll be switching back and forth between them). Put cell HN10 in the upper left corner.

  9. Perplexity over time. Zoom: 160%. Put cell GX1 in the upper left corner.

  10. Excel's default view. Just create a new window.


Jason Eisner - jason@cs.jhu.edu - Last Modification $Date: 2003/11/12 16:28:55 $ (GMT)