How to Fix Excel Displaying VLOOKUP/XLOOKUP Formula Instead of Result
Question details
The user needs to resolve an issue where Excel displays the raw formula text rather than calculating the expected result.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Entering a VLOOKUP or XLOOKUP formula into a spreadsheet to retrieve data.
- Observed behavior
- The cell displays the formula text exactly as typed instead of executing the function and showing the calculated value.
Ensure that you have not accidentally typed a space or an apostrophe before the equals sign in your formula, as this forces Excel to treat the entry as plain text regardless of your settings.
Change Cell Formatting from Text to General
The most common reason formulas display as text is because the cell format is set to 'Text'. Changing it to 'General' resolves the issue.
When a cell is pre-formatted as Text, Excel literally interprets anything you type into it—including formulas—as plain text characters. You must change the format and then force Excel to re-evaluate the cell content.
Click on the cell or highlight the range of cells displaying the formula text instead of the result.
Go to the 'Home' tab on the Excel ribbon. In the 'Number' group, click the format dropdown menu (which likely says 'Text') and select 'General'.
Double-click the cell containing the formula, or press 'F2' on your keyboard to enter edit mode, and then hit 'Enter'. Excel will now calculate and display the result.
Disable the 'Show Formulas' Setting
Excel has a built-in feature to display all formulas in a worksheet for auditing purposes, which might have been accidentally turned on.
Set Workbook Calculation to Automatic
If workbook calculation is set to manual, formulas will not update or calculate until you manually trigger them.
Easily Manage Formulas and Calculations with WPS Spreadsheet
WPS Spreadsheet offers an intuitive interface for data analysis and formula auditing. If you frequently encounter formatting or calculation issues, WPS provides easy-to-use tools to quickly identify and format cells correctly.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your existing workbook.
- 2. Check cell formatting: Select the cell showing the formula text, navigate to the 'Home' tab, and ensure the format dropdown is set to 'General'.
- 3. Verify formula auditing: Go to the 'Formulas' tab and ensure the 'Show Formulas' button is toggled off.
- 4. Recalculate the cell: Press 'F2' and then 'Enter' to instantly calculate the VLOOKUP or XLOOKUP formula and display your result.
Frequently Asked Questions
Why does my VLOOKUP formula have an apostrophe in front of it?
An apostrophe at the beginning of a cell entry tells the spreadsheet software to treat the contents strictly as text. To fix this, simply delete the apostrophe, ensure the cell is formatted as General, and press Enter to allow the formula to calculate.
Can I apply the General format to an entire column of broken formulas at once?
Yes. Click the column letter to select the entire column, go to the Home tab, and select General from the Number format dropdown. Because Excel requires the cells to be refreshed, you can then use the 'Text to Columns' feature under the Data tab and immediately click 'Finish' to force-update all cells in the column without doing it one by one.
Does XLOOKUP behave differently than VLOOKUP regarding text formatting?
No, both VLOOKUP and XLOOKUP are standard spreadsheet functions. If the cell format is set to Text, if there is a space before the equals sign, or if 'Show Formulas' is enabled, the software will display the raw formula text regardless of which lookup function you are using.
What if my formula calculates but shows a #N/A error after fixing the text issue?
If the formula calculates but returns an error like #N/A, it means the text formatting issue is resolved, but the lookup value was not found in your source data. Verify that your lookup value exactly matches the data in your lookup array, keeping an eye out for hidden spaces or mismatched data types.
