| | Product Information Help and How-to Training Templates Related Products and Technologies Support and Feedback Technical Resources Additional Resources | Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.
To make it easier to create and edit formulas and minimize typing and syntax errors, use Formula AutoComplete. After you type an = (equal sign) and beginning letters or a display trigger, Microsoft Office Excel displays below the cell a dynamic drop-down list of valid functions, names, and text strings that match the letters or trigger. You can then insert an item in the drop-down list into the formula by using an insert trigger.
 Type the = (equal sign) and beginning letters or a display trigger to start Formula AutoComplete.  As you type, a scrollable list of valid items is displayed with the closest match highlighted.  Icons represent the type of entry, such as a function or table reference.  Detailed ScreenTips help you make the best choice.
What do you want to do?
Control the drop-down list by using display triggers
The following table summarizes how to dynamically control the display of items in the Formula AutoComplete drop-down list.
| To display |
Type this |
| Excel and user-defined function names |
A letter or beginning letters anywhere a function can be entered. Example: Su
|
| Function arguments |
(No display trigger). Type the argument, such as a number or cell reference, or use a display trigger, such as beginning letters or an [ (opening bracket).
Example: SUM(5, A2, [
For each subsequent argument, type a comma and then the argument or another display trigger.
Note The following functions have arguments with enumerated constants that automatically display in the drop-down list: CELL, FV, HLOOKUP, MATCH, PMT, PV, RANK, SUBTOTAL, and VLOOKUP.
|
| Defined names and table names |
A letter or beginning letters where that name can be entered. Example: Ann
|
| Table column specifiers and special item
specifiers ([#All], [#Data], [#Headers], [#Totals], [#ThisRow]) |
One or more of the following:
Note If the cell is in a table, the table name is optional. For example, the following formulas would be the same:
=[Sales]/[Costs]
=AnnualSummary[Sales]/AnnualSummary[Costs]
|
| Connection names in Cube functions |
" (opening quotation mark) immediately after the opening parenthesis of a Cube function name. Example: CUBEMEMBER("
Note Only OLAP connections stored in the current workbook are listed.
|
| Multidimensional expressions (MDX) text strings in Cube functions |
One or more of the following:
- " (opening quotation mark) immediately after the comma for an argument.
Example: CUBEMEMBER("SalesCubeData","
- . (period) immediately after a closing bracket.
Example: CUBEMEMBER("SalesCubeData","[Customers].
Example: CUBEMEMBER("SalesCubeData","[Customers].[Mexico].
- ( (opening parenthesis) immediately after an opening quotation mark for an MDX text string to indicate the beginning of a tuple.
Example: CUBEVALUE("SalesCubeData","(
- , (comma) immediately after a closing bracket in an MDX text string to indicate the second part of a tuple.
Example: CUBEVALUE("SalesCubeData","([Customers].[Mexico],
- { (opening brace) immediately after an opening quotation mark for an MDX text string to indicate the beginning of a set expression.
Example: CUBEVALUE("SalesCubeData","{
Notes
- You must be connected to an OLAP data source to enter an MDX text string using Formula AutoComplete.
- If a caption is defined, it is displayed in a ScreenTip to help confirm the choice.
- If an MDX text string is ambiguous, then a unique member name is still entered but you must decide if the correct one was entered. For example, if there are two values for the following MDX text string:
CUBEMEMBER("SalesCubeData","[Customers].[Mexico].[Hidalgo].[Dora N. Boots]
One of the following values would be entered:
[Customers].[Name].&[54342]
[Customers].[Name].&[34297]
If the one entered is not what you want, you would delete it, and then select the other one.
- Microsoft SQL Server Analysis Services function names, such as "Children", "Parent", or "Crossjoin" are not displayed in the drop-down list, but you can still type them.
|
Notes
- At any time that you are using Formula AutoComplete, you can type what you want to finish the formula.
- You can use Formula AutoComplete in the middle of an existing nested function or formula. The text immediately before the insertion point is used to display values in the drop-down list, and all of the text after the insertion point remains unchanged.
- Defined names that you create for enumerated constants, such as the ones used in the SUBTOTAL function, and Cube function connections do not display in the AutoComplete drop-down list, but you can still type them.
 Top of Page
Navigate the Formula AutoComplete drop-down list by using keys
The following table summarizes the keys that you can use to navigate the Formula AutoComplete drop-down list.
| To |
Press |
| Move the insertion point one character to the left. |
LEFT ARROW |
| Move the insertion point one character to the right. |
RIGHT ARROW |
| Move the selection up one item. |
UP ARROW |
| Move the selection down one item. |
DOWN ARROW |
| Select the last item. |
END |
| Select the first item. |
HOME |
| Move down one page and select a new item. |
PAGE DOWN |
| Move up one page and select a new item. |
PAGE UP |
| Close the drop-down list. |
ESCAPE (or click another cell) |
| Turn on or off Formula AutoComplete. |
ALT+DOWN ARROW |
Top of Page
Enter an item from the drop-down list by using an insert trigger
Important As you are typing a formula, even after using an insert trigger, don't forget to type the closing parenthesis for a function, closing bracket for a table reference, or closing quotation mark for an MDX text string.
- To insert the selected item into the formula and put the insertion point directly after it, press TAB, or double-click the item.
Top of Page
Turn Formula AutoComplete on or off
- Click the Microsoft Office Button
 , click Excel Options, and then click the Formulas category.
- Under
Working with formulas, select or clear Formula AutoComplete.
Tip You can also press ALT+DOWN ARROW.
Top of Page
|
United States
Email this link
Printer-Friendly Version
Share
