There’s some exciting news for those using Excel 2016 on an Office 365 basis (ie, not the “perpetual licence”). You might not have this initially, but don’t fret. According to Microsoft, this feature is being made available to users on a “gradual basis” over several days or weeks. It will first be available to Office Insider participants and later to Office 365 subscribers. If you are an Office 365 subscriber and you want it is as soon as it becomes available, be sure to have the latest version of Office at all times. In other words, keep those updates updated!
So what’s the big deal?
For a start, you can now get stock and geographic data in Excel. All you have to do is type text into a cell and convert it into the “Stocks” or “Geography” data type. These two data types are new, and they are considered linked data types because they have a connection to an online data source. That connection allows you to bring back rich information that you can work with and refresh — something many users have wanted to do seamlessly for years.
Here’s a peek at the two new data types:
Data type 1: Stocks
In this graphic, the cells with company names in column A contain the “Stocks” data type. You may tell this because they have the building icon. The “Stocks” data type is connected to an online source that contains more information. Columns B and C are extracting that information. Specifically, the values for price and change in price are getting extracted from the “Stocks” data type in column A.
Data type 2: Geography
In this example, column A contains cells that have the “Geography” data type. This time, the map icon indicates this. This data type is connected to an online source that contains more information. Columns B and C are again extracting that information. Specifically, the values for population and gasoline price are getting extracted from the “Geography” data type in column A.
You can see how these data types might be useful. So how do you set it up? Assuming you have had your version of Excel 2016 updated, it’s as easy as, er, 1, 2, 3, 4, 5, 6 …
Step 1: Type some text
That’s right: just type some text in cells — in this example, country names. However, you also could type province names, territories, states, cities, etc. If you want stock information, similarly type in company names, fund names, ticker symbols, and so on.
Step 2: Create a table
Although it’s not required, it is recommended you create an Excel Table. This is so that ranges may be extended readily and easily later, should you wish. Select any cell in your data and go to Insert > Table or use the keyboard shortcut Ctrl+T. This will make extracting online information easier later.
Step 3: Select some cells
Next, select the cells that you want to convert to a data type.
Step 4: Pick a data type
On the Data tab, click either “Stocks” or “Geography”.
Step 5: Icons appear
If Excel finds a match between the text in the cells and the online sources, it will convert your text to either the “Stocks” data type or “Geography” data type. You will know immediately if they have been converted since they will have the building icon for stocks and the map icon for geography.
Step 6: Add a column
Click the “Add Column” button, and then click a field name to extract more information, such as “Population”.
If you see the question mark in a circle symbol instead of an icon, then Excel is having difficulty matching your text with data in Microsoft’s online sources. Go back and review your data. Correct any spelling mistakes and when you press ENTER, Excel will do its best to find matching information. If this does not work, click question mark in a circle symbol and a “Selector” pane will appear. Search for data using a keyword or two, choose the data you want and then click “Select”. That’s it!
How to write formulae that reference data types
You can use formulae that reference linked data types. This allows you to retrieve and expose more information about a specific linked data type. For example, consider the linked data type, Stocks, used in cells A2:A11 below. In columns B and C, there are formulae that extract more information from the “Stocks” data type in column A.
In this example, cell B2 contains the formula =A2.Price, and cell C2 contains the formula =A2.Change. When the records are in a table, you can use the column names in the formula instead. In this case, cell B2 would contain the formula =[@Company].Price and cell C2 would contain =[@Company].Change. The additional benefit is that these formulae would automatically copy down, too.
Some tips for when you start playing with these two new data types:
- As soon as you type the dot operator (.) after a cell or column reference, Excel will present you with a formula AutoComplete list of fields that you can reference for that data type. Select the field you want from the list or type it if you know it.
- Data type field references are not case sensitive, so you can enter =A2.Price, or =A2.price.
- If you select a field that has spaces in the name, Excel will automatically add brackets ([ ]) around the field name, eg, =A2.[52 Week High].
- The FIELDVALUE function can also be used, but it is recommended only for creating conditional calculations based on linked data types.
This last point is a nice lead-in to the other new item.
The FIELDVALUE function
You can use the FIELDVALUE function to retrieve field data from linked data types like the “Stocks” or “Geography” data types. There are easier methods for writing formulae that reference data types (see above), so the FIELDVALUE function should be used mainly for creating conditional calculations based on linked data types.
Similar to the new data types, this brand-new function is being made available to customers on a gradual basis over several days or weeks. It will first be available to Office Insider participants and later to Office 365 subscribers. If you are an Office 365 subscriber, make sure you have the latest version of Office or you may not get the update when it’s your turn.
The FIELDVALUE function syntax has the following arguments:
- value: this is the cell address, table column, or named range that contains a linked data type
- field_name: this is the name or names of the fields you would like to extract from the linked data type.
The FIELDVALUE function returns all matching fields(s) from the linked data type specified in the value argument. This function belongs to the Lookup & Reference family of functions.
In the following basic example, the formula, =FIELDVALUE(A2,"Price") extracts the Price field from the “Stock” data type for the JM Smucker Co.
The next example is a more typical example for the FIELDVALUE function. Here, the IFERROR function is used to check for errors. If there isn’t a company name in cell A2, the FIELDVALUE formula returns an error, and in that case, displays nothing (""). However, if there is a company name, then the formula will retrieve Price from the data type in A2 with =IFERROR(FIELDVALUE($A2,B$1),"").
Note that the FIELDVALUE function allows you to reference worksheet cells for the field_name argument, so the above formula references cell B1 for Price instead of manually entering “Price” in the formula.
If you try to retrieve data from a nonexistent data type field, the FIELDVALUE function will return the new #FIELD! error. For instance, you might have entered “Prices”, when the actual data type field is named “Price”. Double-check your formula to make sure you’ve used a valid field name. If you want to display a list of field names for a record, select the cell for the record and press Ctrl+Shift+F2.
— Liam Bastick is director of SumProduct, a global consultancy specialising in Excel training. Send ideas for future Excel-related articles to him at firstname.lastname@example.org. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.