Office
Sign in

#FIELD! error

The #FIELD! error will appear when:

Note: You can insert linked data types from the Data tab. You can also write formulas that reference them.

#FIELD! error when inserting a table column

When a new column tries to reference a field from a data type record, but the record doesn't have any data for that particular field, the #FIELD! error will occur. For example, you added a column to show stock expense ratios, but the stock record on that row doesn't have an expense ratio in the first place, so the result is #FIELD!.

Solutions:

  • If Error Checking is enabled, click the Exclamation point icon to the left of the cell, and choose the Ignore Error option.

    Ignore a #FIELD! error

  • Remove the column, and replace it with a new column that references a field that does have data.

#FIELD! error when typing a formula

If you type a formula like =A1.City, or =FIELDVALUE(A1,"City"), and cell A1 doesn't use a Geography data type, the #FIELD! error will occur. Similarly, if you create a formula like =A1.Price, or =FIELDVALUE(A1,"Price"), and cell A1 doesn't use the Stocks data type, then the error will occur.

This can also happen when a formula references a field from a data type, but the data type doesn't have any data for that particular field. For example, if you entered a formula like =A1.ExpenseRatio, or =FIELDVALUE(A1,"ExpenseRatio"), but the that particular stock doesn't have an expense ratio in the first place, the result is #FIELD!.

Solutions:

  • If Error Checking is enabled, click the Exclamation point icon to the left of the cell, and choose the Ignore Error option.

  • Delete the formula.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Introduction to data types in Excel

FIELDVALUE function

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×