Fix an Excel Worksheet Link That Displays as Text Instead of a Value
Question details
The user needs an Excel worksheet-reference formula to return its referenced value instead of appearing as text in the cell.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user enters a worksheet link formula, such as =Westgate!A16, to display a value from another worksheet.
- Observed behavior
- The formula itself is displayed in the cell rather than being calculated.
Click the affected cell and check whether the formula begins with an equal sign (=). If only one formula is displayed as text, check that cell's number format before changing workbook-wide formula display settings.
Change the formula cell from Text to General and recalculate it
Use this when a single worksheet-link formula is shown as text instead of returning a value.
Excel treats entries in cells formatted as Text as literal text. After changing the format, you must re-enter or edit the formula so Excel evaluates it.
Click the cell that displays the worksheet link, such as =Westgate!A16.
On the Home tab, open the Number Format drop-down list and choose General instead of Text.
With the cell still selected, press F2 to edit it, then press Enter to recalculate the formula.
Turn off Show Formulas when every formula is visible
Use this when formulas are displayed throughout the worksheet, not only in one cell.
Use single quotes for worksheet names that contain spaces
Use this when the source worksheet name includes spaces or special characters.
Calculate Worksheet Reference Formulas in WPS Spreadsheets
WPS Spreadsheets lets you format cells as General, enter cross-sheet references, and calculate linked values with familiar spreadsheet controls. Open your workbook in WPS Office to correct formulas that are being treated as text and continue working with compatible Excel files.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the affected .xlsx or .xls workbook.
- 2. Select the formula cell: Click the cell showing the worksheet link as text.
- 3. Set the cell format to General: On the Home tab, open the number format list and select General instead of Text.
- 4. Re-enter the formula: Press F2, confirm the formula begins with =, and press Enter to calculate the referenced value.
Frequently Asked Questions
Why does =Westgate!A16 show in the cell instead of the value?
The cell is commonly formatted as Text, causing Excel to treat the formula as characters. Change the format from Home > Number Format > Text to General, then press F2 and Enter.
How do I reference a worksheet name that has spaces in Excel?
Put single quotes around the worksheet name. For example, use ='West Gate'!A16 rather than =West Gate!A16.
What does Show Formulas do in Excel?
Show Formulas displays formulas instead of calculated results across the worksheet. Turn it off from Formulas > Show Formulas, or press Ctrl+`.
Why does changing the cell format not immediately calculate my formula?
Changing Text to General does not automatically reprocess existing text. Select the cell, press F2, and then press Enter to submit the formula again.
