Be sure to enter formulas in Excel in all lowercase letters. When you press enter, Excel converts all valid portions of the formula to UPPER CASE. For instance, in the case of =sum(jj1:j10), pressing enter causes Excel to return the #NAME error, with =SUM(jj1:J10). Notice that the incorrect JJ1 is still in lower case, while the rest of the formula was capitalized. (Our thanks go to Aamir Ijaz Khan, who earned $5.00 for submitting this tip!)

Enter =CELL("filename") in a worksheet cell to display the name and path of your spreadsheet. If you save the file in a new location, the formula will update automatically. (Our thanks go to Manprit S.) http://www.acctadv.com/go.mv?id=tip

You can copy a formula or text to adjacent cells by clicking on the fill handle (the notch in the right-hand corner of a worksheet cell) with your left-mouse button and dragging either down or across. (Our thanks go to Kedar Desai, who got paid for submitting this tip!) For even more options, hold down the right-mouse button while you click and drag...when you release the mouse button, a shortcut menu will appear.

The =EOMONTH function is a handy way to determine the last day of a given month. However, that function requires that you have the Analysis ToolPak installed and enabled (users often don't). Instead, use the =DATE function. For instance, =DATE(2000,1,0) will return 12/31/99. You can also use this technique in your VBA macros and functions. (Thanks to Stephen Bullen for this clever tip!)

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.