Use Formula AutoComplete

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 (the equal sign, plus the first letter of a function, for example.) Microsoft Excel displays below the cell a dynamic drop-down list of valid functions, names, and text strings that match the letters or display trigger. You can then insert an item in the drop-down list into the formula by using an insert trigger.

1. Type the = (equal sign) and beginning letters or a display trigger to start Formula AutoComplete.

2. As you type, a scrollable list of valid items is displayed with the closest match highlighted.

3. Icons represent the type of entry, such as a table entry (SalesSummary), defined name (SalesTax) or function (SEARCH, SECOND…SLOPE, and so on).

4. Detailed ScreenTips help you make the best choice.

What do you want to do?

Control the drop-down list by using display triggers

Navigate the Formula AutoComplete drop-down list by using keys

Enter an item from the drop-down list by using an insert trigger

Turn Formula AutoComplete on or off

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	Type ( -- an opening parenthesis Type the argument, such as a number or cell reference, or use a display trigger, such as beginning letters, a comma, an opening parenthesis, 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.AVG, RANK.EQ, SUBTOTAL, and VLOOKUP.
Defined names and table names	A letter or beginning letters where that name can be entered. Example: YearlySales
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 (a string containing comma separated values). 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].[Gutierrez].[Paulo Neves] 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.	Esc (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

1. Click the File tab, click Options, and then click the Formulas category.

2. Under Working with formulas, select or clear Formula AutoComplete.

Tip: You can also press Alt+Down Arrow while you are typing a formula to toggle Formula AutoComplete.

Top of Page