Use Formula AutoComplete

Applies To
Excel for Microsoft 365 Excel 2024 Excel 2021

To make it easier to create and edit formulas and to minimize typing and syntax errors, use Formula AutoComplete. After you type an = (equal sign) and beginning letters or a display trigger, Microsoft Excel displays a dynamic drop-down list of valid functions, names, and text strings that match the letters or trigger. You can insert an item from the drop-down list into the formula by using an insert trigger.

Screenshot that shows Formula Autocomplete.

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

2⁠. As you type, Excel displays a scrollable list of valid items with the closest match highlighted.

3⁠. Icons represent the type of entry, such as a function or table reference.

4⁠. Detailed ScreenTips help you make the best choice.

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","{
    • 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.

Note

  • 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 don't display in the AutoComplete drop-down list, but you can still type them.

Use keys to navigate the Formula AutoComplete list

The following table summarizes the keys that you can use to navigate 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 list. ESCAPE (or click another cell)
Turn on or off Formula AutoComplete. ALT+DOWN ARROW

Enter an item from the list by using an insert trigger

Important

As you type a formula, even after using an insert trigger, remember 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.

Turn Formula AutoComplete on or off

Note

This section doesn't apply to Excel for the web.

  1. Select File > Options > Formulas.
  2. Under Working with formulas, select or clear Formula AutoComplete.

Tip

You can also press ALT+DOWN ARROW.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.