If Excel formula autocomplete is not working, first check that Formula AutoComplete is enabled under File > Options > Formulas, then type an equals sign and part of a function name in an editable cell. In Microsoft 365 for Windows, that setting controls suggestions for functions and other formula entries; it is different from autocomplete for ordinary text in cells.
What Excel formula autocomplete does
Formula AutoComplete is the suggestion list that appears while you build a formula. For example, typing =SU may bring up matching function names such as SUM. Excel can also suggest defined names, table names, and other formula elements as you enter a formula. The list helps you choose a valid item, but it does not write the whole calculation or decide which ranges you intend to use.
When the list appears, select the intended suggestion with the arrow keys and press Tab, or click the suggestion. Excel inserts it into the formula. You can then enter the rest of the formula, including its arguments. Pressing Esc dismisses the list; if it disappears, continue typing or remove the incomplete text and enter the prefix again. The list may take a moment to appear, and it will not necessarily show useful matches when the text you have typed does not match an available name.
Autocomplete is not the same as the function-argument tooltip that can appear after a function name and an opening parenthesis. Nor is it the same as Excel completing a repeated text entry in a worksheet column. Identifying which behavior is missing is often the fastest way to find the right setting.
Turn Formula AutoComplete on in Windows
In Excel for Microsoft 365 on Windows, use this path:
- Select File, then Options.
- Choose Formulas in the Excel Options window.
- Under Working with formulas, select the Formula AutoComplete checkbox.
- Select OK, return to a worksheet, and test a formula in a cell you can edit.
Type an equals sign followed by a recognizable prefix, such as =SU. If the setting was already selected, turn it off, select OK, reopen Options, turn it back on, and test again. This can help if the preference appears enabled but suggestions still do not show.
Do not confuse this control with Enable AutoComplete for cell values, found under File > Options > Advanced > Editing options. That second setting applies to repeated text entries in worksheet cells, not to function suggestions inside formulas.
Check how you are entering the formula
Excel’s formula suggestions are context-sensitive. Click a normal worksheet cell, type =, and begin a function name. If you are editing an existing formula, click within the formula in the cell or formula bar and enter the prefix where a formula element is expected. A cell formatted as Text, a leading apostrophe, or an entry that does not begin with an equals sign can make Excel treat what you type as text rather than as a formula.
For a simple check, use this illustrative layout: put 12, 18, 20, and 10 in cells B2:B5. In another editable cell, enter =SUM(B2:B5). The result should be 60. If you can enter and calculate that formula but do not see suggestions while typing, the calculation engine is working and the problem is more likely the suggestion setting, the timing of the list, or the editing context.
Also check whether you are in a mode that prevents normal editing. A read-only workbook, Protected View, or a protected sheet may limit changes. Those conditions do not necessarily disable suggestions everywhere, so test in a new blank workbook rather than assuming protection is the cause.
Why the suggestion list may not appear
Autocomplete can appear absent even when it is enabled. Try these checks before changing more settings:
- Type a longer prefix. A single equals sign may not make the list easy to notice. Add the first few letters of a known function, such as SUM.
- Look for a dismissed or obscured list. Pressing Esc closes suggestions. A small window, screen scaling, or a list opening near the edge of the screen can make the dropdown easy to miss.
- Check the exact spelling. Excel can only suggest a matching available function or name. A typo, or a function name unavailable in that Excel version, may produce no useful match.
- Test outside the current workbook. A blank workbook helps separate an application setting from workbook-specific names, protection, or unusual editing conditions.
- Close and reopen Excel. Save your work first. Restarting can clear a temporary interface problem; it will not repair a disabled setting, but it is a simple diagnostic step.
Excel’s formula list is a convenience, not a validator for every part of a calculation. A suggestion can insert a function name while the formula still has an incorrect range or missing argument. Conversely, a valid formula can often be typed even if the dropdown is not appearing.
Check Excel version, updates, and add-ins
Some functions are only available in particular releases. For instance, XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but an older release will not gain that function merely because autocomplete is enabled. If suggestions work for SUM but not for a newer function, check the Excel version and function availability before troubleshooting the whole feature.
To check for updates in Microsoft 365 for Windows, select File > Account > Update Options > Update Now, if that option is available. Work or school administrators may control updates, and the available commands can differ with the installation. Save open files before installing an update.
If suggestions fail in multiple workbooks, test whether an add-in is interfering with Excel’s interface. On Windows, you can start Excel in Safe Mode by pressing Windows+R, entering excel /safe, and pressing Enter. If autocomplete works in Safe Mode, an add-in or customization may be involved. Close Safe Mode, then review File > Options > Add-ins. At the bottom, choose COM Add-ins next to Manage and select Go to review enabled items. Change add-ins cautiously, especially on a managed work computer.
Mac and Excel for the web
On a Mac, the Windows File > Options route does not apply. In Excel, open Excel > Settings, or Excel > Preferences in older releases, and look for the AutoComplete settings. The exact organization and labels can vary by Excel build. If you cannot find the formula suggestion control, update Excel and consult the help for that installed version rather than following Windows-only menu directions.
Excel for the web can offer formula suggestions while you type, but it does not have the same full desktop Options dialog. If the list is missing in a browser, click an editable cell, confirm you are entering a formula, and try a new workbook. Refresh the page after saving your work, or open the workbook in the desktop app to compare behavior. If suggestions work in the desktop app but not in the browser, the issue may be limited to the web session or browser environment.
When you mean autocomplete for cell values
If you are expecting Excel to complete a person’s name, product, or other text while you type in a cell, you may be looking for cell-value AutoComplete, not Formula AutoComplete. In Windows, enable it at File > Options > Advanced > Editing options > Enable AutoComplete for cell values. Excel uses existing entries in the same column as the basis for suggestions, so it is most useful when you are adding another item to a list of similar text.
This feature is not a general search box and does not predict new text. If the matching value is in another column, or the column does not contain a suitable earlier entry, there may be nothing to complete. It also does not automatically choose an item from every data-validation dropdown. A validation list is a separate restriction or selection control; whether you can type to find an entry depends on the version and the particular list behavior.
Alternatives when suggestions are unavailable
You can still build formulas without the dropdown. Type the function name and its arguments directly, use the Insert Function button beside the formula bar to search for a function, or use Excel’s help and function reference to confirm the arguments. When a formula already exists, the Formula bar can be easier to inspect than a narrow cell.
For example, to total values in a known range, use the SUM formula shown earlier rather than waiting for a suggestion to select it. If you are unsure of a function’s arguments, use the Insert Function dialog or begin typing the name and inspect any available argument guidance. These methods do not repair autocomplete, but they let you keep working while you diagnose it.
Frequently Asked Questions
How do I turn on formula autocomplete in Excel?
In Microsoft 365 for Windows, select File > Options > Formulas and check Formula AutoComplete under Working with formulas. Type an equals sign and a function prefix in an editable cell to check that suggestions appear.
Why does Excel autocomplete text but not formulas?
Text completion and formula suggestions use different controls. Cell-value completion is under File > Options > Advanced, while formula suggestions are controlled under File > Options > Formulas.
Why is Excel not suggesting a function I know?
Check the spelling and whether that function is supported by your Excel version. Newer functions may be unavailable in older perpetual releases. Also test a common function such as SUM to see whether suggestions work generally.
Can I use formulas if autocomplete is off?
Yes. Type a valid formula directly into a cell or the formula bar. Autocomplete assists with entry; it is not required for Excel to calculate a correctly entered formula.
Does Excel for Mac have the same autocomplete setting?
Excel for Mac has autocomplete preferences, but its menus differ from Windows. Look under Excel > Settings or Excel > Preferences, depending on the release, and check the help for your installed build if the option is not where expected.
Related
- Excel EIN Number Format: Keep All 9 Digits Visible
- Excel Dependent Drop-Down Lists: Setup and Fixes
- Excel DOS Keyboard Shortcuts: What Actually Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Column and Row Labels Missing? Restore Them FastIf Excel’s column letters and row numbers are missing, turn on Headings: on Windows, open the View tab…
- Excel Formula Not Calculating? Fix Zero ResultsIf an Excel formula shows zero, first check Formulas > Calculation Options > Automatic, then confirm the formula…
- Excel SUM Formula Not Working? Fix It Step by StepIf an Excel SUM formula shows zero, leaves out values, or does not update, first check that the…
- Excel Keeps Not Responding? Causes and Fixes to TryIf Excel keeps not responding, first give it a minute if it may be calculating or loading data;…
- Excel #VALUE! Error: Why It Happens and How to Fix ItYou keep getting a #VALUE! error in Excel because a formula is receiving a value or argument it…
- VLOOKUP Table Array Not Working? Fix the RangeIf your VLOOKUP table array is not working, check that the lookup value is in the first column…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.