How To Use The FIELDVALUE Function In Microsoft Excel
In Microsoft Excel, the FIELDVALUE function retrieves data from the internet and adds it to your table. This can be really useful for adding data that is not easily accessible or is difficult to gather manually. For example, you could use the FIELDVALUE function to add data about geographic regions such as countries, cities, or states. In this blog post, we will show you how to use the FIELDVALUE function in Microsoft Excel. We will also provide some tips on how to prepare your data so that the FIELDVALUE function works correctly. Let’s get started!
What Does FIELDVALUE Do?
The FIELDVALUE function in Microsoft Excel allows you to add data from the internet to your table. This can be really useful for adding data that is not easily accessible or is difficult to gather manually. The FIELDVALUE function only works with company names or geographic region names, so you have to prepare your data in a certain way.
Where To Find It?
Option 1:
The FIELDVALUE function can be found in the Formulas tab of Excel under Lookup & Reference
Option 2:
You can use Formula Builder to walk you through the formula step by step.
Option 3:
You can manually type the formula into any cell.
The Formula
=FIELDVALUE(value, field_name)
- Value – The company name or geographical place that you want to look up (i.e. Walmart or the United States)
- Field_name – The name or names of the type of data that you want to look up (i.e. stock price or population)
Setting Up Your Data
Because it only works with company names or geographic region names, you have to select a cell containing a company name or country name and define it for Excel. The following steps will set up your data:
- Type a company or country/region name into an Excel cell, for example “Walmart” or “United States”
- Go to the “Data” ribbon and click on “Stocks” or “Geography” or in the “Data Types” section.
- If the company or geographic region is found, a small map symbol is shown in the cell
Available Data Types
Both company and geography have a specific set of data types (field_name) that you can look up. Check out our Excel workbook for the full list.
Let’s Take a Look at Some Real-World Examples
Excel Workbook
Example 1 – Stocks
For our first example, we will look at pulling information on a company’s stock.
First, we need to set up the data. In order to pull a value, you need to connect it to Excel’s live data. Enter a company name or multiple companies, one per cell. Highlight the cells and go to the Data tab on the ribbon. In the middle of the page you will see connections for stocks and geography. Select stocks. If Excel is able to find the company, the little bank icon will appear.
Next, enter the data types that you want to look up. Then enter the formula referencing company (which is the value argument) and data type (which is the field_name argument). Excel will query the data tables and return the information requested.
You can directly enter the value and field_name into the formula, but since syntax is so important I recommend referencing a table.
Your turn: Use the workbook and FIELDVALUE to follow the examples in blue and finish the practice formulas highlighted in green
Example 2 – Geography
For our first example, we will look at pulling information on different countries.
First, we need to set up the data. In order to pull a value, you need to connect it to Excel’s live data. Enter a county name or multiple countriess, one per cell. Highlight the cells and go to the Data tab on the ribbon. In the middle of the page you will see connections for stocks and geography. Select Geography. If Excel is able to find the country, the little map icon will appear.
Next, enter the data types that you want to look up. Then enter the formula referencing country (which is the value argument) and data type (which is the field_name argument). Excel will query the data tables and return the information requested.
You can directly enter the value and field_name into the formula, but since syntax is so important I recommend referencing a table.
In the example for France, the output of the formula is a #FIELD error. In this case, the syntax was correct but the data sets are not complete. This is indicating that the information requested could not be found. See below for more information on #FIELD errors.
Your turn: Use the workbook and FIELDVALUE to follow the examples in blue and finish the practice formulas highlighted in green
Tips and Tricks
The Function Returned A #FIELD Error
If you receive a #FIELD error, this means that the FIELDVALUE function could not find the data you were looking for. This is usually caused by one of three things:
– The data type you are trying to lookup does not exist in the linked record
– The company or geography cannot be found in the linked record
– The field_name was not entered correctly and the syntax needs to be updated to match an available field
The Limit Does Exist
There are limited data types. The available data is quite limited. For example, you might need stock quotes. But I need more information, for example, the last years’ revenues, costs, profit margin and so on to evaluate companies.
In addition, data options are limited. The first limit is that you don’t have many options for your data. For example, you can only choose from a list of stock quotes. You can’t select the date and time you want to receive the stock quote.
Can’t find FIELDVALUE in Excel
Not all versions of Microsoft Excel have this function. FIELDVALUE was only introduced in Office 365.
Missing Data
Some major data is missing from the fields. For example, the “year founded” for Apple and Amazon. Or the “Official Name” of the USA. As more users switch to Office 365, the data will continue to improve.
Let’s Recap
The FIELDVALUE Function is a tool that makes it easy to add data from the internet (from so-called linked records) and adds them to your table. We showed you how to use this function in Microsoft Excel. We also provided some tips on how to prepare your data so that the FIELDVALUE function works correctly. Finally, we covered some examples of how to use both the company and the geography data sets.
Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.