- Understanding Formulas
- Creating a Simple Formula
- Creating a Formula Using Formula AutoComplete
- Editing a Formula
- Understanding Cell Referencing
- Using Absolute Cell References
- Using Mixed Cell References
- Using 3-D Cell References
- Naming Cells and Ranges
- Entering Named Cells and Ranges
- Managing Names
- Simplifying a Formula with Ranges
- Displaying Calculations with the Status Bar
- Calculating Totals with AutoSum
- Performing One Time Calculations
- Converting Formulas and Values
- Correcting Calculation Errors
- Correcting Formulas
- Auditing a Worksheet
- Locating Circular References
- Performing Calculations Using Functions
- Creating Functions
- Creating Functions Using the Library
- Calculating Multiple Results
- Using Nested Functions
- Using Constants and Functions in Names
Creating a Formula Using Formula AutoComplete
To minimize typing and syntax errors, you can create and edit formulas with Formula AutoComplete. After you type an = (equal sign) and begin typing to start a formula, Excel displays a dynamic drop-down list of valid functions, arguments, defined names, table names, special item specifiers—including [ (open bracket), , (comma), : (colon)—and text string that match the letters you type. An argument is the cell references or values in a formula that contribute to the result. Each function uses function-specific arguments, which may include numeric values, text values, cell references, ranges of cells, and so on.
Enter Items in a Formula Using Formula AutoComplete
- Click the cell where you want to enter a formula.
- Type = (an equal sign), and beginning letters or a display trigger to start Formula AutoComplete.
For example, type su to display all value items, such as SUBTOTAL and SUM.
The text before the insertion point is used to display the values in the drop-down list.
- As you type, a drop-down scrollable list of valid items is displayed.
Icons represent the type of entry, such as a function or table reference, and a ScreenTip appears next to a selected item.
- To insert the selected item in the drop-down list into the formula, press Tab or double-click the item.
Use the Keyboard to Navigate
Using the keyboard, you can navigate the Formula AutoComplete drop-down list to quickly find the entry you want.
- Refer to the table for keyboard shortcuts for navigating the Formula AutoComplete drop-down list.
Turn on Formula AutoComplete
- Click the File tab, and then click Options.
- In the left pane, click Formulas.
- Select the Formula AutoComplete check box.
- Click OK.