The detailed information is contained within cards and this information is easily extracted into the Excel worksheet.
At present, there are two rich data types available. Stocks and Geography. These can be located on the Data ribbon under the grouping Data Types.
Using AI, Excel will recognize different location types such as Countries, Cities and even counties under the Geography data type. With the Stock data type, Excel will recognize stocks on major exchanges, some currency pairs, and even some cryptocurrency pairs.
You will recognize a rich data type by the icon beside the text. For stock and currency related data you will see columnar building icon on the left side of the cell. For Geography data you will see a flag icon on the left side of the cell.
If Excel is unable to find a rich data type to match the cell contents, a question mark will be shown on the left of the cell. By clicking the question mark a data selector pane will open. From here you can select between different data sources for your search.
Once you have converted data to a rich data type, you can view the information from the card by hovering over the icon and left clicking on the mouse. You can also use the keyboard shortcut Ctrl + Shift + F5 to show the card.
You can also quickly add data from the card to your spreadsheet. This is achieved by clicking on the icon or the insert data field. All fields contained within this card can be extracted to your worksheet.
For Example, selecting the 52 week high and the 52 week low will add the data to the next cells in the worksheet.
When you select a cell that contains a field from a rich data type, you will note from the formula bar, the data is extracted using a formula.
To add a field from a rich data type using a formula, select the sell you want to see the data in. Enter = and the cell reference that contains the rich data type. A drop down will appear showing you all the fields available.
Select the required field, press tab to enter the field to the formula and press enter.
You will note that the fields are show in square brackets.
This way of extracting fields from a rich data type cell is known as the Dot formula type.
There is also a new function, FIELDVALUE that will allow you extract card data from a rich data type cell.
The syntax for FIELDVALUE is
=FIELDVALUE(value, field_name)
Where the value is the rich data type cell, and the field name is the field you want to extract.
When using FIELDVALUE, you must place the field name in quotes. For example
=FIELDVALUE(A2, “52 week high”)
By using either the DOT formula type or the FIELDVALUE function, you can use field data for calculations. For example, if you wanted to calculate the difference between the 52 week high and the 52 week low you could use either of the following
=FIELDVALUE(A2,"52 week high") - FIELDVALUE(A2, "52 week low")
Or
=A2.[52 week high]-A2.[52 week low]
Where A2 contains the rich data type which is of type Stock.
In the examples we have looked at so far, our data has not been in table format. When we extract card information while not in table format, no headers appear telling us what data field we have extracted. However, when we use a table format, the headers do appear.
To convert data to table format, the keyboard shortcut is CTRL + T. Once is table format, when fields are extracted from the Rich data type cell, headers appear in the table allowing you quickly see what each data point relates to.
However, if you enter a country, fields will include Largest City, Households and much more.
It is also possible to extract further rich data type fields from an existing rich data type.
For example, if we enter London and convert this to a rich data type and select Admin Division (state/province), a new rich data type cell will be populated. In this case, England. You can then extract further information about the secondary rich data type.
Open a blank Excel workbook and enter the county you live into a blank cell. Select the data type Geography and then extract the Area and Population. What values do you get?
Now using a formula from the Rich data type cell, calculate the Population per Sq KM. (area). What answers did you get? Which formula do you prefer to use, the Dot formula or the function FieldValue?
If you had a large workbook and you wanted to locate rich data types, how would you go about this?
Before you watch the video below showing you how to do this, give it a try and leave a comment below.
Now there is value in Learning with The Excel Club and our Learn and Earn activities.
We are the first Excel, PowerBI and DAX blog in the world where you can Earn tokens while you Learn.
