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:

• Alt-VU (View / Full screen) maximizes the Excel window and gets rid of various borders, toolbars, formula bars, etc. (This is a toggle command.)
• If you want to hide even more, use Alt-TO (Tools / Options...), select the View tab, and uncheck the following: Row & column headers, Horizontal scroll bar, Vertical scroll bar, Sheet tabs.
• Hide the paper clip ("office assistant"): right-click on it and select Hide.
• Tell the Windows taskbar to get out of the way when the mouse is not near it. Right click on the taskbar, choose Properties, and check Auto Hide.
• If the Excel window contains multiple subwindows for some reason, maximize one of them.

Setting up multiple views ahead of time:

• This is the simplest and most effective technique that I have. You can create multiple windows on the worksheet in advance, then easily switch among them during class. Each window can be viewing a different part of the worksheet, and can have its own zoom level and split/freeze setup. Make sure your display resolution when you're setting up the windows is the same resolution you'll be using in class.

• To switch among windows, use Alt-W1 (Window / 1) through Alt-W9 (Window / 9). You can have up to 9 windows on tap like this.

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:

• Sometimes I use the mouse (or a physical pointer) to point to things while I talk.
• Often I like to highlight a region by clicking and dragging. (You may want to start at a cell just outside the region of interest, since the cell where you start will not be highlighted itself.)

Commands for moving around quickly:

• A complete summary of such commands is available via online help. Press F1, type "shortcut," click on "keyboard shortcuts," and click on "Keys for moving and scrolling in a worksheet."

  • The arrow keys and PgUp/PgDn are the most useful.
  • Alt-PgUp/Alt-PgDn are horizontal versions of PgUp/PgDn.
  • Ctrl-Home returns to the upper left of the worksheet, and Ctrl-End to the lower right.
  • Ctrl-arrow moves "to the edge of the current region." (Roughly speaking, until it hits a blank cell.)

• Your keyboard has a Scroll Lock or ScrLk toggle key. When it is toggled on, the movement keys scroll the window rather than moving the cursor. To return to where the cursor is, type Ctrl-Backspace.

  (If you don't turn Scroll Lock on, you can still scroll by moving the cursor to the edge of the window and beyond. But that may take longer, and it is harder to recover if you scroll too far.)

• I haven't used this handy feature. But F5 lets you jump to a cell by name. You can use a row/column name like "BB6," or select or type a meaningful name like "mygraph" that you have assigned to a block of cells. (Naming cells is very easy - press F1 and type "name cell" to get help on how.)

Commands for zooming:

• Alt-VZC (View / Zoom / Custom) lets you choose how much to zoom, e.g., 150%.
• Select a range and use Alt-VZF (View / Zoom / Fit selection) to make it as big as possible.
• Alt-VZ2 (View / Zoom / 25%) zooms out to see the high-level structure of the spreadsheet.
• Alt-VZ1 (View / Zoom / 100%) restores the default size.
• As the students arrive at class, point to the small text on the screen and invite them to sit closer. Optical zoom beats digital zoom every time. :-)

Other commands you will need:

• Ctrl-Z or Alt-Backspace or Alt-EU (Edit / Undo) is the Undo command. Useful if you type something you didn't mean to. Also useful if you experiment with some of the red parameters and want to change them back quickly.
• To copy the final probabilities back over the initial ones as described in footnote 29, you must copy their values, not their formulas. Use Alt-EC (Edit / Copy) followed by Alt-ESV (Edit / Paste special / Values).

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:

• Alt-WS (Window / Split) will split the screen horizontally and vertically into four panes. (This is a toggle command.) The split bars will fall just above and left of the current selection (but you can adjust them by dragging, or remove them by dragging them off the screen). The different panes can be scrolled independently, except that panes in the same column will stay aligned horizontally and panes in the same row will stay aligned vertically. You can move from pane to pane by clicking (or F6).
• Alt-WF (Window / Freeze) is like Alt-WS, except that only the pane in the lower right can scroll freely. The other panes are intended to hold fixed row and column headers. You can move the cursor from pane to pane across the dividing bars.
• I don't use this feature. But you can hide arbitrary rows or columns using Alt-ORH (Format / Row / Hide) and Alt-OCH (Format / Column / Hide).
• I don't use this feature. But multiple windows that show different worksheets, or different parts of the same worksheet, can be shown side-by-side using Alt-WAT (Window / Arrange / Tiled). This command will simultaneously display all the windows that are neither minimized (Alt-dash N) nor hidden (Alt-WH).

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:

• Note: First, make sure to set your screen resolution to match the projector's resolution. (Right click on the desktop, then select Properties / Settings / Screen area.) Also follow the instructions earlier on this page for using as much of the screen as possible.

• Alt-WN (Window / New) creates a new window and switches to it. Now you can scroll, zoom, and split/freeze to get the look you want. To destroy the current window, if necessary, use Ctrl-W.

• Note: When setting up these windows, experiment with the Scroll Lock key and the F5 key. They may make your life a little easier.

• Note: The suggestions below are for an projector with resolution of 1280 x 1024. If your projector has a lower resolution, like 1024 x 768, or 800 x 600, you won't have to zoom as much. You may have to tinker with the suggestions - just do what looks good.

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:

   • alpha₃(H) = total probability of paths from START to H/3 = ?
   • beta₂(H) = total probability of paths from H/2 to STOP = ?
   • Total probability of all paths from START to STOP via H/2 = ?
   • p(day 2 weather = H | ice cream = <2, 3, 3, ...>) = ?

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.