Arguably, one of the most powerful features in Excel is the ability to create dynamic ranges, which expand or contract as you add or delete data. You can create a dynamic range that consists of a single row by choosing Insert | Name | Define, then enter MYRANGE in the Names In Workbook field, then enter the following formula

=OFFSET(Sheet1!$B$5,0,0,1,COUNTA(Sheet1!$5:$5))

in the Refers To field, then finally click Add to save the reference. in addition, it's even possible to take this method a step further and make both the rows AND columns dynamic, with the following syntax

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B),COUNTA(Sheet1!$5:$5))

where the range counts how many cell entries are in column B, and how many cell entries are in row 5, then expands or contracts the range automatically. (Major thanks on this one, Stuart!)

Chip Pearson, an Excel MVP, has written a helpful explanation of array formulas in Excel. His site also contains other helpful Excel tips, links, and info.

To make your formulas completely invisible to a user, select the cells, choose Format | Cells, click the Protection Tab, then select the Hidden checkbox and click OK. Next, apply the same color to the text within the cells as your background, i.e. white letters on a white background are invisible. Finally, choose Tools | Protection | Protect Sheet to enable protection, which will prevent the formulas from appearing on the formula bar.

When entering a formula in Excel 97 or 2000, press Ctrl+Shift+A to view the arguments for the function. For instance, typing =DSUM into a cell, then pressing Ctrl+Shift+A will return =dsum(database,field,criteria). In Excel 2002/XP, a screen tip appears automatically once you type the function name.

If you purposely need to use a circular reference in a worksheet, make sure that you enable the Iteration option by choosing Tools | Options, click the Calculation tab, then toggle the Iteration checkbox. Otherwise, you'll repeatedly get error messages stating that Excel can't evaluate the formula.

There's much more to Excel's SUBTOTAL function than meets the eye, such as the ability to only sum visible cells. Refer to Excel's online help for more information. (Thanks to Shane Devenshire for this one!)

Buy a copy of John Walkenbach's Excel 2002 Formulas today from Amazon.com. Another book that we highly recommend is Microsoft Excel Version 2002 Inside Out by Craig Stinson and Mark Dodge.

By default, the File menu in Excel shows the last 4 files that you saved. Get more out of this list by choosing Tools, Options, and then selecting the General tab. Set the Recently Used File List choice to the maximum setting of 9.

To move a worksheet row, select the desired row(s) and then while holding the Shift key down, use the left mouse button to grab the edge of the selection. You can now drag and drop the row where you wish. This technique also works with columns, or even a group of cells. (Thanks to Jeff Jurs, who got paid for this tip!)

Case Training, Inc. has recently launched an Excel CD-based training session featuring 49 of the best timesaving tips presented in the form of short videos. Try a sample video clip or purchase on our web site.