_xlfn. in excel is a prefix that appears, when a function is not recognised by your Excel version. This is usually happens when you enter a formula with a function which has been introduced in the later or newer versions of Excel, but the file is opened in an older version that does not support that function.
To understand this easily, I’m going to use the function TEXTJOIN which is introduced in the 2019 version but in the Excel 2013 which causes _xlfn. in the current version.
Example of _xlfn. in Excel:
If a formula contains the TEXTJOIN function in the formula and it will display the _xlfn. in the formula bar.
=TEXTJOIN(", ", TRUE, B4:B7)The above formula will be displayed normally in the newer version of the excel but in the older version the same formula will display _xlfn. with it.
=_xlfn.TEXTJOIN(", ", TRUE, B4:B7)This means that Excel does not recognize TEXTJOIN in your version.
Other Function That may show XLFN in Older Version of Excel:
| Function Name | Function Usage | Function Introduced in: |
|---|---|---|
TEXTJOIN | Merges text strings with a delimiter | Excel 2016 |
IFS | Formula to have multiple conditions without nested functions | Excel 2016 |
CONCAT | Function used to replace CONCATENATE function for merging texts | Excel 2016 |
XLOOKUP | A powerful alternative Function to VLOOKUP and INDEX-MATCH | Excel 2019 / 365 |
FILTER | Function to filter an array based on the given criteria | Excel 2019 / 365 |
UNIQUE | Function to give the unique values in the range of area | Excel 2019 / 365 |
Fix for _xlfn. error while using TEXTJOIN Function:
- You need to upgrade you excel to the latest version. You can follow the instructions provided in the official documentation from microsoft.
- You can enable the compatibility mode in your older version of Excel. Check if the file is in Compatibility Mode (.xls format) and convert it to .xlsx to overcome this issue.
- If the above two didn’t help you, then you need to do the text joining using manual formula.
=B4 & ", " & B5 & ", " & B6 & ", " & B7The above manual formula will joins all the text with “,” comma delimiter without any function to do it.
Conclusion:
_xlfn. error will appear when your excel was not able to understand or recognize the function name and its usage. In simple words, this error indicates missing compatibility and the best way to fix this is to upgrade your excel to the latest version. Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage.
That’s it.
References:
- Referred an community tutorial named Issue with excel formula starting _xlfn. from Techcommunity Microsoft and retrieved on 14/3/25.
- Referred an community tutorial named Issue _xlfn.Single and @ being added to formulas – unsupported functions – NOT THE REASON from Techcommunity Microsoft and retrieved on 14/3/25.
- Referred an community tutorial named What does ‘_xlfn’ mean in an Excel formula? from Answers Community Microsoft and retrieved on 14/3/25.
- Referred an community tutorial named Issue: An _xlfn. prefix is displayed in front of a formula from Support Microsoft and retrieved on 14/3/25.
