Excel 2020: Geography & Stock Data Types in Excel

September 07, 2020 - by Bill Jelen

Geography & Stock Data Types in Excel. Photo Credit: Benjamin Blättler at Unsplash.com

In the past, Excel did not really handle data types. Yes, you could format some cells as Date or Text, but the new data types provide a whole new entry point for new data types now and in the future.

Start with a column of City names. For large cities like Madison Wisconsin, you can just put Madison. For smaller towns, you might enter Madison, FL.

A list of cities in A2:A8. Houston, El Paso, and Madison stand on their own. But smaller cities need the state: Paris, KY.

From the Data tab, select Geography.

The new Data Types gallery on the Data tab offers Stocks and Geography. Choose Geography.

Excel searches the Internet and finds a city for each cell. A folded map appears next to each cell. Notice that you lose the state that was in the original cell.

Each value in A2:A8 now has a map icon to the left of the text. One annoyance, Paris, KY now just says Paris.

Click on the Map icon and a data card appears with information about the city.

Click any map icon and a box pops up with information such as Population, County, Mayor.

The best part: for any data in the card, you can use a formula to pull that data into a cell. Enter =A2. Population in cell B2 and Excel returns the population of El Paso. Double click the Fill Handle in B2 and Excel returns the population for each city.

If El Paso is in A2, type =A2.Population in B2 to get the population of 683,080. Copy the formula down and you see the population for each city.

Caution

These new formulas might return a #FIELD! error. This means, Excel, or more correctly Bing, does not know the answer to this yet, but it may do so at some time in the future. It is not an error with the formula or the table, just a lack of knowledge currently.

The Geography and Stock data types have extra features if you format as a table using Ctrl+T.

A new Add Data icon appears to the right of the heading. Use this drop-down menu to add fields without having to type the formulas. Clicking the icon will enter the formula for you.

The Add Data icon appears near the top of the table, just outside the right edge.

You can also sort the data by any field, even if it is not in the Excel grid. Open the drop-down menu for the City column. Use the new Display Field drop-down to choose Longitude.

Another trick: open the filter drop-down for City and you can choose to sort City by Longitude, even though Longitude is not in the table.

With Longitude selected, choose sort Smallest to Largest.

After choosing Longitude in the City drop-down, choose Sort Smallest to Largest. This will sort cities West-to-East.

The result: data is sorted west to east.

Cities are sorted west to east. Column B has the population. Column C has the state. Column D shows the county.

Title Photo: Benjamin Blättler at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.