MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Geography Data Types in Excel


November 05, 2018 - by Bill Jelen

Geography Data Types in Excel

Office 365 customers should have a new gallery on the Data tab of the Ribbon with icons for Stocks and Geography. These are new data types in Excel and are hopefully the first of many such data types.

Info

In the past, the only real data typing in Excel was when you formatted a cell as Date or Text. These new rich data types are an advancement beyond those.

To start, type some cities or states or countries in Excel. Select the range. In the figure below, the cities were suggested in response to this Tweet - you will notice that the format is varied, sometimes including a state or a state abbreviation or no state at all.

Select some cells containing cities
Select some cells containing cities

On the Data tab, choose Geography. The data is sent to a geography server to figure out the location of each city.

Declare the cells as Geography.
Declare the cells as Geography.

The cell is successful if you see this map icon. Note that the state information is removed - leaving only the official name of the city.


A map icon indicates success
A map icon indicates success

Click on any map icon (or select the cell and press Ctrl + Shift + F5) to display a card with information about the city.

A floating card appears
A floating card appears

But the card is not really that useful.

The useful feature is the new formula nomenclature. With Little Rock in A2, go to cell B2 and type =A2 followed by a period. A tool tip appears with all of the information known about Little Rock. Start to type a property and the tooltip narrows. In this image, the items starting with L are Latitude, Longitude and Leader(s).

Press tab to insert the latitude in B2.
Press tab to insert the latitude in B2.

The image below shows several columns returning Latitude, Longitude, Population, and Leaders. Once you have the first row of data, double-click the fill handle to copy the formula down to all rows.

All of this data is returned from the data type
All of this data is returned from the data type

The video for today shows extra features:

  • How to search for a city when Excel can't automatically figure out the city
  • How to sort a table by a field that is not being shown

Watch Video

Download Excel File

To download the excel file: geography-data-types-in-excel.xlsx

The new data types in Excel have been a popular feature in my live Power Excel seminars.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Whoever came up with custom number formatting was a real $#@_*;"

Title Photo: Noah Mayer 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.