'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix XLOOKUP Formula Showing as Text in Excel
Question details
The user is attempting to use an XLOOKUP function, but the formula remains visible in the cell as raw text instead of calculating and returning the expected result.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Entering an XLOOKUP formula into a cell to retrieve data from a separate worksheet or table.
- Observed behavior
- The formula itself is displayed in the cell rather than the calculated output. Changing worksheets or the number of rows does not seem to affect the calculation failure in the original sheet.
Ensure that your workbook calculation mode is set to Automatic and that you haven't accidentally typed a space or an apostrophe (') before the equals sign in your formula.
Disable Show Formulas and Change Cell Formatting
The most common reasons for a formula displaying as text are the 'Show Formulas' feature being enabled or the cell being pre-formatted as Text before the formula was entered.
When a cell is formatted as Text, Excel treats anything entered into it as a literal string, ignoring mathematical operations or functions. Alternatively, the global 'Show Formulas' setting will force all cells to display their underlying formulas.
Go to the 'Formulas' tab on the ribbon and look for the 'Show Formulas' button in the Formula Auditing group. Click it to toggle it off if it is highlighted.
Select the cell or column containing the XLOOKUP formula. Navigate to the 'Home' tab, locate the Number Format drop-down menu, and change it from 'Text' to 'General'.
Double-click the problematic cell to enter edit mode, or click inside the Formula Bar at the top, and press the Enter key. This forces Excel to recalculate the cell as a standard formula.
Verify Table Names and Structured References
If you are using structured references (e.g., Table2[@Email]), invalid names or syntax can cause the formula to fail or behave unexpectedly.
Easily Troubleshoot and Calculate XLOOKUP with WPS Office
WPS Spreadsheet fully supports advanced functions like XLOOKUP, VLOOKUP, and structured table references. You can seamlessly open your Excel files, easily adjust cell formatting, and instantly calculate complex formulas on large datasets without hassle.
- 1. Download and Install: Visit the official WPS website to download WPS Office for free and install it on your computer.
- 2. Open your spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbook that contains the problematic XLOOKUP formulas.
- 3. Format and calculate: Select the uncalculated cells, change their format to 'General' from the Home tab, and double-click to recalculate the results instantly.
Frequently Asked Questions
Why is my Excel formula showing text instead of the result?
This commonly occurs if the cell was formatted as 'Text' before you typed the formula, or if you have the 'Show Formulas' option enabled in the Formulas tab. Additionally, check if there is a space or apostrophe before the equals sign.
Does the number of rows affect XLOOKUP calculation?
No, the number of rows (even thousands, such as 5,000+ rows) will not cause an XLOOKUP formula to display as raw text. While massive datasets might increase processing time, they do not change a formula into a text string.
Why does my XLOOKUP formula work on one sheet but not another?
This typically indicates sheet-specific formatting issues. The failing sheet likely has its columns formatted as Text, or has 'Show Formulas' enabled exclusively for that tab. Copying the data to a new sheet often resolves background corruption or formatting glitches.
