In my review of Numbers ’09, I included a tip about entering formulas using the keyboard. In the event that not all of you choose to read the Numbers review from top to bottom, here’s the tip, along with additional details on exactly how to use it.
As a long-time Excel user, one of the things that frustrated me about Numbers ’08 was its heavy reliance on the mouse. It seemed that any time I wanted to do anything with the program, I was reaching for the mouse. This was especially annoying when entering formulas. If I was entering a formula in cell AB275, for instance, that referred to cell AB276, I’d have to grab the mouse or hand-type the cell’s address, even though the two cells are side-by-side.
In Numbers ’09, it appeared that the same limitation existed. Then I discovered the (mostly hidden) power of the Option key—start typing a formula (=sum(), then press and hold the Option key, then use the arrow keys to move around the worksheet. As you do, each cell you pass through will appear in your formula; release the Option key, and the currently-selected cell reference will be inserted in your formula.
After I figured this out, I couldn’t believe it wasn’t documented…and it turns out, it is documented, but not in the program’s help. Instead, you’ll find it referenced in both the Keyboard Shortcuts page (accessible via the Help menu), as well as in the iWork as in the iWork Formula and Functions Reference Guide, which you can get to via the Help menu within Numbers, or by directly visiting the iWork ’09 Manuals page. Even if you don’t care about keyboard entry of formulas, this is an excellent document to download—it’s roughly 300 pages of detailed information on all of Numbers’ functions, and other tips for working with formulas.
In addition to the basic use of Option and the arrow keys to select one cell, there are other keyboard tricks you can use when working with formulas. To select a range of cells, for instance, first use Option and the arrow keys to select the first cell, then (while still holding Option), press and hold Shift and move the active cell with the arrow keys. As you move, you’ll see the selection expand; when the proper range is selected, just release Command and Option.
If you need to select cells that are on another table (in the current sheet, or another sheet), press Command and Page Down (or Up) while still holding Option to select the other table, then release Command and select the cells using the arrow keys. You can add the Shift key to select a range of cells in another table.
Finally, you can toggle a cell reference through the various options—absolute, row or column absolute, and relative—by pressing Command-K with the cell reference selected in the formula editing window.
This is a relatively minor new feature in Numbers ’09, but it’s one that’s most welcome to those of us who have been using Excel for many years.
Udpated 4:57pm to correct inaccurate statement about this feature not being documented in the Keyboard Shortcuts documentation.