You see a #FIELD! error in your Excel worksheet where you expected to see data. This error occurs when a formula references a specific field from a linked data type, but that field cannot be found. Linked data types connect your spreadsheet to an online source, and changes to that source can break your references. This article explains how to identify the broken link and restore your data.
Key Takeaways: Fixing the #FIELD! Error
- Field Chooser icon: Use this button next to the cell to select a valid field from the updated data type card.
- Data > Refresh All: Updates all linked data types in your workbook, which can resolve errors from stale connections.
- Data Types > Change: Converts the data type to text, permanently removing the link but preserving the last retrieved value.
Why the #FIELD! Error Appears with Linked Data Types
Excel’s linked data types, like Stocks or Geography, pull information from the cloud. When you use a formula to extract a specific property, such as =A2.Price, Excel looks for the “Price” field in the online data card linked to cell A2. The #FIELD! error means this lookup failed. The most common reason is that the data provider has updated or restructured their information, and the field name your formula uses no longer exists in the current version of the data card. Other causes include a lost internet connection during a refresh or the original cell losing its data type connection entirely.
Steps to Resolve the #FIELD! Error
Follow these methods to fix the broken reference and get your data back.
Method 1: Use the Field Chooser to Select a New Field
- Select the cell with the #FIELD! error
Click on the cell containing the error. This is usually a cell with a formula referencing another cell that has a linked data type. - Click the Field Chooser icon
A small icon resembling a spreadsheet with a lightning bolt will appear to the left of the cell. Click this icon. - Choose a new field from the menu
A menu will list all available fields from the updated data card. Select the field that contains the data you need, such as “Last Price” instead of “Price”. Excel will update your formula automatically.
Method 2: Refresh the Linked Data Connection
- Go to the Data tab
Click the Data tab on the Excel ribbon. - Click the Refresh All button
In the Queries & Connections group, click Refresh All. This forces Excel to update all linked data types in the workbook with the latest information from the online source. - Check the cell again
After the refresh completes, see if the #FIELD! error is replaced with data. If the error persists, the specific field name is likely still unavailable, and you should use Method 1.
Method 3: Convert the Data Type to Text
- Select the source data type cell
Click on the cell that contains the original linked data type, not the cell with the formula error. - Go to Data > Data Types
On the ribbon, click the Data tab, then find the Data Types group. - Click Change and select Convert to Text
Click the Change button and choose “Convert to Text” from the menu. This action breaks the live link and converts the current values into static text. - Manually re-enter your formulas
Formulas referencing the now-plain text cell may still show #FIELD!. You will need to edit these formulas to reference the static text value directly.
If the #FIELD! Error Keeps Returning
All Cells Show #FIELD! After Refresh
If every linked data type shows this error after a refresh, you may have lost your internet connection. Check your network status. Excel also requires a valid Microsoft 365 subscription for most linked data types to function. Ensure your subscription is active.
Error Appears in Some Cells But Not Others
This indicates a partial update from the data provider. Some entities in your list may have a new data structure. You must use the Field Chooser on each error cell individually to map to the new, correct field name for that specific stock or geography item.
Data Type Menu is Grayed Out
If you cannot access the Data Types group on the Data tab, your workbook may be saved in an older file format like .xls. Linked data types only work in the modern .xlsx format. Save a copy of your workbook as an Excel Workbook (*.xlsx) and try again.
Resolving #FIELD! vs. Other Common Errors
| Item | #FIELD! Error | #REF! Error | #VALUE! Error |
|---|---|---|---|
| Description | Linked data type field is missing | Cell reference is invalid or deleted | Wrong data type used in a formula argument |
| Common Cause | Online data source updated its field names | A column, row, or sheet referenced by a formula was deleted | Using text in a math operation or vice versa |
| Primary Fix | Use the Field Chooser to select a valid field | Restore deleted content or update the formula reference | Check formula arguments for consistent data types |
| Data Type Link | Always involves a linked data type (Stocks, Geography) | Can occur with any standard cell reference | Can occur with any standard formula |
You can now clear the #FIELD! error by updating your field references with the Field Chooser. For persistent issues, use Data > Refresh All to update all connections. A useful advanced tip is to use the Data > Queries & Connections pane to monitor the status of all your workbook’s external data links and refresh them selectively.