To complete this chapter, you need a few more tips and tricks for entering functions.
If you forgot to enter the arguments for a function, you can view them in the edit box. For example, to use the VLOOKUP() function, enter the equal sign, VLOOKUP, and an opening parenthesis. Press the Ctrl+Shift+A key combination to enter the arguments for the function in the formula bar.
To use the Function Wizard instead, press the Ctrl+A key combination. The Function Wizard starts up for the defined function.
Note
In Excel 2002, Excel 2003, Excel 2007, and Excel 2010, the arguments for a function are shown in a ScreenTip (see Figure 4-6 earlier in this chapter). For this to work, you have to select the Show Function ScreenTips check box in the Excel Options dialog box.
If you are constantly using complex functions, you can save the formulas by using the AutoCorrect option. The AutoCorrect feature is mostly used to correct common typing errors. You probably know this feature from Microsoft Word. Use the AutoCorrect feature to replace a string with a formula.
For example, assume that you often use the same table structure, and you want to calculate the sum for the 500 cells below the current cell without having to consider the reference. The following formula calculates the sum of the 500 cells below the active cell:
=SUM(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4,TRUE),TRUE):INDIRECT(ADDRESS(ROW()+501, COLUMN(),4,TRUE),TRUE))
To make this formula globally available, perform the following steps:
In Excel 2003, select the Tools/AutoCorrect menu option. In Excel 2007 or Excel 2010, select the Proofing category in the Excel Options dialog box and click AutoCorrect Options. Enter a string in the Replace box on the AutoCorrect tab.
Enter the formula shown just before these steps in the With box. Remember that you have to enter an equal sign in front of the string (see Figure 4-19).
Click Add and then OK.
Now you can just use the string _sum500 to reproduce the formula.
To analyze complex formulas or to search for errors, you can calculate parts of the formula, such as function arguments, in the form of references or nested function calls. The reference or the function call is replaced by a value. Perform the following steps:
Select the cell containing the formula, and press the F2 key or double-click the cell.
Select the part of the formula you want to calculate.
Press the F9 key. Excel replaces the selected part of the formula with the result.
This way you can solve a formula step by step and analyze the individual parts of the function.