Geography Data Types in Excel
November 05, 2018 - by Bill Jelen
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.
On the Data tab, choose Geography. The data is sent to a geography server to figure out the location of each city.
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.
Click on any map icon (or select the cell and press Ctrl + Shift + F5) to display a card with information about the city.
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).
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.
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
