MrExcel.com - Your One Stop for Excel Tips & Solutions

Geography, Stock, and Exchange Rate Data Types

July 28, 2022 - by

Geography, Stock, and Exchange Rate Data Types

Office 365 subscribers will see a new Data Types gallery appear on the Data tab of the ribbon starting in late 2018 or early 2019. Initially, the feature is supporting geography and stock data types, but there are hopes that they will expand to other data types, including the ability for a company to define their own data types.

Gotcha: the new data types work better when you format the data as a Ctrl+T table. Although I generally try to avoid tables, I will use them in this example.

Start with a column of locations. This can be countries, states, counties, or cities. Select the table and press Ctrl+T to define it as a table.

A heading of City followed by entries like Lexington, KY and Dallas, TX
Figure 591. Start with a list of cities.

On the Data tab, select Geography.

There are two data types in a gallery on the Data tab. Choose Geography from the gallery.
Figure 592. Declare the cells as a geography data type.

If there are no misspellings, a map icon will appear for each cell. Notice that the original text is replaced with new text. Since there are many different places named Springfield, I am not sure that I like that Excel removed Missouri from Springfield.

The text in the cells changes to Lexington and Dallas. But a map icon appears to the left of the entry.
Figure 593. The symbol in column I is used to add data columns.

Click the map icon for any cell and a floating data card appears with all of the information that Excel knows about that city. My initial joke was that the card was useless, as it just hovered there. But now, you can actually add new fields from the card, so it is better. Click on Population and Excel will add a new column with the population for each city.

Click on the map icon, and a floating card appears with facts: Lexington is in Fayette County, Kentuck. Linda Gorton is the mayor. The population in 2017 is 321,959. The city encompasses 739.40 square miles.
Figure 594. You can add a field to the table using the card.

You can also add fields to the table using the icon that appears to the right of the table.

A formula of =H2.Population puts the population in cell I2. Or, if you have formatted the data as a table, the formula might be =[@City].Population.
Figure 595. A formula of =H2.Population returns the population from that city.

Data types are an improvement in Excel. One cell can contain essentially an entire row in a lookup table and you can extract that data to the grid using a formula such as =H2.Population. If you go to the cell for Dallas and type Houston instead, the population in column I would update.

Gotcha: Because this data relies on the Internet, the feature is exclusive to Office 365. It will never be in Excel 2016 or Excel 2019.

Additional Details: When you click on Stocks or Geography, Excel looks at the neighboring cells to try to ascertain what you mean by the value in a cell. For example, consider a cell with the word Georgia. If the surrounding cells are countries from the former Russia Republic, then Excel will assume you mean the country of Georgia. If the surrounding cells are states in the southeastern United States, then Excel will assume you mean the state of Georgia.

The Data Types look at surrounding cells for context. Georgia is in A5, C5, and E5. Column A contains other former Soviet Repulbics. Column C contains states. Column E is blank except for Georgia.
Figure 596. Excel looks at other cells in the column to figure out meaning.

In the figure below, you've added Capital to each location. Excel automatically assigned the correct Georgia in both lists. However - for the lone Georgia in the last column, the map icon is replaced by a question mark, indicating the Excel is not sure what you mean.

The Geography Data Type conversion works correctly, providing the country of Georgia in column A, the state in column C. But for the lone entry of Georgia in column E, you get a circled question mark icon instead of the map icon.
Figure 597. The question mark means Excel is not sure what you mean.

Choose the cell with the question mark and a Data Selector appears. You can choose between various matches for Georgia, such as Georgia in the United States, Georgia the country, or Georgia Town in Franklin County, Vermont.

What if you wanted to have the state of Florida and the country of Georgia? Excel won't initially get it correct, but you can force the data selector to appear. Right-click a single cell and choose Data Type, Change.

Right-click any cell with a dara type. The context menu offers Data Type flyout menu with Show Card, Refresh, Change..., and Convert to Text.
Figure 598. Right-click a data type cell to get to the Change option.

Before moving from Geography to Stocks, here is a benefit of making your data types be a table. When you open the filter drop-down menu for City, a new box appears where you can choose any field. To sort the cities from west to east, select longitude in that box and then Sort Smallest to Largest.

If the data types are in a Table, the Filter drop-down offers a new technique. Even though the column says City, you can open the Select Field drop-down menu and choose Longitude or any other field. When you Sort Smallest to Largest, the cities are sorted west to east.
Figure 599. Sort city by Longitude, even if it is not displayed.

The result is that the cities are organized west to east:

The cities are now sorted west to east: San Antonio, Dallas, Springfield, Chicago, Huntsville, Chattanooga, Lexington, Fort Myers, Jacksonville.
Figure 600. The data is sorted based on information in the card.

This article is an excerpt from Power Excel With MrExcel

Title photo by GeoJango Maps on Unsplash