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:
- [ (opening bracket) immediately after the table name.
Example: AnnualSummary[
- , (comma) immediately after a special item.
Example: =AnnualSummary[#All],
- : (colon) immediately after a column name.
Example: AnnualSummary[Sales:
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
