Working with new data types
Liam Bastick shares some tips for when you start working with the two new data types in Excel.
This series has introduced you to the two new data types that allow Excel to do even more. The last article revealed a simple six-stage process you can use to create Stocks or Geography data types. Now you know how to create a data type, this next episode will explain how you can work with them.
We’ll start by showing how you can use formulae that reference these linked data types.
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. When the records are in a Table, you can also use the column names in the formula instead. In this case, cell B2 would then contain the formula =[@Company].Price instead. The additional benefit is that these formulae would automatically copy down too.
Tips and more tips
Some tips for when you start working 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, such as =A2.[52 Week High].
The associated new FIELDVALUE function can also be used 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, so the FIELDVALUE function should be used mainly for creating conditional calculations based on linked data types.
The syntax for FIELDVALUE is as follows:
It 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.
For example, the formula
extracts the Price field from the Stock data type:
The next example is a more typical example of the FIELDVALUE function. Here, we’ve used the IFERROR function 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 we want the formula to retrieve the 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. The advantage of this calculation is easy to see: copying it across into cell C2 provides the employee total immediately.
If you try to retrieve data from a non-existent 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.
You might also be interested in
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...