MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Geography Data Type


August 07, 2018 - by Bill Jelen

Geography Data Type

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.

Select the city names and choose Data, Geography.
Select the city names 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.

Click this symbol to see a Data Card.
Click this symbol to see a Data Card.

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.


Scroll through the data card to learn about the city.
Scroll through the data card to learn about the city.

Even better: To pull data about the city into Excel, use =A2.Population. Copy that formula down to get population for each city.

Retrieve any of these fields with the dot notation.
Retrieve any of these fields with the dot notation.

Note that =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:

Because of the parentheses in Leader(s), you need square brackets: =A2.[Leader(s)].
Because of the parentheses in Leader(s), you need square brackets: =A2.[Leader(s)].

With the new Linked Data Type comes a new #FIELD! error. This means that you have asked for a field that does not exist.

If there are Martians in Miami, the Men in Black aren't documenting it in Wikipedia.
If there are Martians in Miami, the Men in Black aren't documenting it in Wikipedia.

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).

This function seems completely redundant since =A2.Population is easier.
This function seems completely redundant since =A2.Population is easier.

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.

Sort the data east to west.
Sort the data east to west.

The result: the data is sorted east to west even though longitude is not a field in the worksheet.

Sorted by Longitude
Sorted by Longitude

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.

Watch Video

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


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.