Geography Data Type
August 07, 2018 - by Bill Jelen
Office 365 subscribers will soon start seeing a new feature on the Data tab: Linked Data Types for geography or stocks.
Start by typing some city names or country names in Excel. Select those cells and choose Data, Geography.
For each city that is recognized, a map icon will appear in the cell. Click that icon or press Ctrl + Shift + F2 to get more information about the city.
The data card contains fields about the city. At the bottom of the data card are links to the source web pages where the data came from.
Even better: To pull data about the city into Excel, use
=A2.Population. Copy that formula down to get population for each city.
=A2.Population works when the field name has no spaces or punctuation. If the name of the field contains spaces or population, wrap it in square brackets:
With the new Linked Data Type comes a new
#FIELD! error. This means that you have asked for a field that does not exist.
There is also a new function:
=FIELDVALUE(A2,"Population"). Note that the formula autocomplete for this function is not working. (See the video below for an example).
Here is a fun trick... add Filters to the data. You can now choose to sort City by Longitude, even though Longitude is not shown in the worksheet.
The result: the data is sorted east to west even though longitude is not a field in the worksheet.
Note that F9 will not cause these fields to update. You have to use Data, Refresh or Data, Refresh All to update the fields.
I predict that Geography and Stocks are the first two of many data types that will be introduced. Note that you must have Office 365 to have this feature. It will not work in the perpetual versions of Office 2019 or Office 2016. In the video below, you can see other examples using Country or Stock symbols.
Download Excel File
To download the excel file: geography-data-type.xlsx
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"A watched macro never finishes (set that ScreenUpdating to false)"
Title Photo: Kyle Glenn on Unsplash