Use Formula AutoComplete

To make it easier to create and edit formulas and limit typing and syntax errors, you can use Formula AutoComplete. After you type an = (equal sign) and the beginning letter or letters of a function in the formula bar, you can see a list of functions, names, and text strings. There's also text that helps you choose the right function.

Here are some things you can do using the Formula AutoComplete.

Display items in the Formula AutoComplete list

The following table shows how to control the display of items in the Formula AutoComplete list.

To display	Type this
Excel Online 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.

Go through the Formula AutoComplete list using the keyboard

The following table shows the keys you can use to use the Formula AutoComplete 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)

Insert an item from the list

Important: As you're typing a formula, 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.