Excel: New Geography Data Type

The first linked data types are Stocks and Geography, but Microsoft hopes to add more categories in the future. Because these data types are retrieving data from the internet, Microsoft made the decision to only support them for people using Office 365. If you buy a perpetual license to Office 2019, you won’t have the features described in this article.

IDENTIFYING LOCATIONS

To use the Geography data type, start by entering a list of countries, states, provinces, or cities in some cells. It’s strange how the algorithm for identifying the location works. If you simply type “Madison,” Excel will assume you mean the city of Madison that’s located in Wisconsin. If you wanted a different (smaller) Madison, such as one located in Alabama, Arkansas, Florida, or any of the other 10 U.S. states that have a city named Madison, you need to specify the state as well—e.g., “Madison, AL,” “Madison Ark.,” or “Madison, Florida”—before asking Excel to assign the cell to the Geography data type. The feature works with cities, states, territories, regions, or provinces around the world. If you enter “Paris” instead of “Paris, Ky.,” for example, you’ll get the Paris in France.

Select the cells containing the place names. On the Data tab, choose Geography. There’ll be a slight pause as Excel searches online to find places to match to each cell. Once a cell is successfully matched to a particular location, an icon that looks like an unfolded map will appear to the left of the place name.

If there are any places that Excel couldn’t resolve, an icon showing a circled question mark will appear in the cell and a Data Selector panel will appear on the right side of the screen. The original cell value will appear with a note saying that the place couldn’t be found.

As you use the search box in the Data Selector panel, it’s better to type fewer words. For example, “Sutter, IL” will produce no results, but typing “Sutter” will return several results, including two different places in Illinois called Sutter. You can select whether you meant the one in Tazewell County or the one in Hancock County. The search will also return Sutter, Calif., as well as Sutterville, Sutter Creek, and Sutter Hill.

DISPLAYING THE LINKED DATA

Click on the Map icon in any cell to display a data card. The data card will show some statistics about the location. There’s often a photo (which is scrolled out of view in the image below) and a one-paragraph description. While the card is interesting, it’s more useful to have Excel return specific fields from the card to display in the worksheet.

The Linked Data Types support a new type of formula. If cell A2 contains a link to Madison, Wis., then enter =A2. into cell B2. An autocomplete box will appear with all of the available fields from the data card. You can choose from State, County, Area, Country, Latitude, Leader, Longitude, Name, Population, or Time Zone.

If you choose Population, Excel will enter =A2.Population in the cell. For Madison, Wis., the result will be 252,551. Copy that formula into all rows, and you’ll get the population of each city in your list.

To learn the source of this information, display the data card and scroll down to the Powered By section. This will show you where Excel found the data. In this case, it came from the U.S. Census Bureau.

You might encounter a new error type called #FIELD! when using these formulas. For example, you might ask for the mayor of a city using =A2.[Leader(s)]. This works for Chicago, Ill., and Madison, Wis., but it might fail for some unincorporated places such as Greentown, Ohio. If the data isn’t available for a certain place, you’ll see the #FIELD! error.

If your data is a list of countries, you’ll have a broader choice of fields from the data card, including Calling Code, Birth Rate, Currency Code, Total Tax Rate, and more.

For states, the available fields include Capital, Governor, Largest City, Population Change, Number of Households, Median Household Income, Building Permits Issued, Population Over 65, and Time Zone.

If you format your data as a table using Ctrl+T (or Home, Format as Table), you can filter or sort by fields that aren’t displayed in the Excel grid. Open the Filter dropdown menu for a Country field, for example, and you’ll see a new Select Field menu at the top of the panel. Choose Official Language (shown below), and the Filter check boxes at the bottom will allow you to filter results to view countries where English is one of the official languages. As another example, you can sort cities by descending Longitude and arrange the data in an east-to-west fashion.

SHARING DATA WITH OTHER EXCEL VERSIONS

Since the data type formulas only work in Office 365, you’ll need to convert the information to text if you have to share the data with people using another version of Excel. Select the data, right click to open the quick menu, then choose Date Type, Convert to Text. This only works if the top-left cell of your selection contains a linked data type, so be careful to select starting from the first place name and not from the header row.

The new Geography data type is excellent, and the concept of having linked data opens up many new possibilities for future software updates. Imagine being able to type a number from a catalog and easily access the product’s description, price, category, and more.

SF SAYS

Filtering to all cities with a population greater than 100,000 is now easy in Excel.