How to Fix Excel Functions Missing from the Function List
Question details
Users are unable to find certain functions like DATEDIF or XLOOKUP in the Excel Insert Function dialog or formula autocomplete suggestions.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to insert or type specific formulas using the Insert Function tool or relying on formula autocomplete while working in a spreadsheet.
- Observed behavior
- Certain functions (like DATEDIF) are completely absent from the autocomplete list and offer no syntax help, while others (like XLOOKUP) unexpectedly disappear depending on the file format or compatibility settings.
Verify your current version of Office, as newer functions like XLOOKUP are only available in Microsoft 365 or Office 2021 and later.
Update File Format and Enable Autocomplete Settings
Resolve missing modern functions like XLOOKUP by exiting Compatibility Mode and ensuring that formula screen tips are turned on.
Modern functions like XLOOKUP cannot be used or displayed if your workbook is saved in an older format (.xls) or running in Compatibility Mode.
Look at the title bar of your Excel window. If it says '[Compatibility Mode]' or the file extension is '.xls', modern functions will be disabled.
Click on 'File' > 'Save As', and choose 'Excel Workbook (*.xlsx)' from the format dropdown menu. Close the file and reopen the new .xlsx version.
Navigate to 'File' > 'Options' > 'Formulas'. Under the 'Working with formulas' section, check the boxes for 'Formula AutoComplete' and 'Function screen tips', then click 'OK'.
Open a completely new, blank workbook. Type '=XLOOKUP' into a cell to verify if the function now correctly appears in the autocomplete list.
Manually Type Legacy Functions Like DATEDIF
Understand that certain legacy functions are intentionally hidden from the function list but can still be used.
Use WPS Spreadsheet for Complete Function Support
WPS Office provides a highly compatible spreadsheet application where modern functions like XLOOKUP and legacy functions like DATEDIF are fully supported, easy to use, and clearly documented.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook directly from the home screen.
- 2. Use the Insert Function tool: Click on the 'Formulas' tab and select 'Insert Function' to easily search for required formulas from a comprehensive list.
- 3. Type your formula: Simply type '=' followed by your function name (e.g., =XLOOKUP) in any cell to see instant autocomplete suggestions and detailed syntax guides.
Frequently Asked Questions
Why does the DATEDIF function not show syntax help?
DATEDIF is considered a legacy function originally provided for compatibility with older spreadsheet programs. Microsoft intentionally hides it from the function list and autocomplete features, but it still works perfectly when you type the full formula manually.
Is XLOOKUP available in all versions of Excel?
No, XLOOKUP is a newer function and is only available in Microsoft 365, Excel 2021, and newer versions. If you are using Excel 2019 or earlier, the function will not be available in your application.
Why is my Formula AutoComplete not working at all?
Formula AutoComplete might be disabled in your application settings. You can quickly re-enable it by navigating to File > Options > Formulas and ensuring the 'Formula AutoComplete' box is checked.
