Excel’s New Geographic Data Types

10 min read

Office 365’s new geographic data types allow you to type a location in Excel and write formulas that extract information about the location to a different cell. For example, if you enter “Paris” in cell E4, entering the formula “E4.Population” in a different cell will return the population of Paris.

To illustrate the use of the new geographic data types we entered 5 state names in cells E3:E7 of the worksheet States of the file Beckernewdatatypes.xlsx (see Figure 1.)


Figure 1: Using Geographic Data Types to Extract State Information

After selecting the range E3:E7, choose Geography from the Data Types group on the data tab (we will discuss the Stocks data types next month!) You will see the card icons shown in Figure 1. With your cursor in cell E3, clicking on the card icon displays the information (obtained from Wikipedia) that you can extract to different cells. The card for Indiana is shown in Figure 2.

Figure 2: Card for Indiana

Next, in F3 we type “E3”. Then, Excel presents you with all types of available information. After clicking on Population the formula “E3.Population” returns Indiana’s population. Copying this formula to E4:E7 returns the population of the other states.

Next, we copied from G3 to G4:G7 the formula “=@FIELDVALUE(E3,$G$2)”. In cells G3:G7 the new FIELDVALUE function pulls the data field specified in cell G2 (Area). Note that if you change cell G2 to “Capital”, then G3:G7 automatically update to show each state’s capital. Since data such as population changes over time, you might want to periodically refresh your data by selecting B3:B7 and after right-clicking choose Data Types followed by Refresh.

In Figure 3 (see worksheet Cities) we extracted each city’s State and County.

Figure 3: Extracting the State and County

In Figure 4 (see worksheet Countries) we extracted each country’s Calling Code and Life Expectancy.

Figure 4: Country Code and Life Expectancy

If Excel has doubts about the location you have in mind, you will see a question mark. After clicking on the question mark, you may choose a specific location. For example, as shown in the worksheet Questions and Figure 5, we entered four instances of Bloomington and then selected the states of Indiana, Minnesota, Illinois and California. Then we extracted each city’s state and population.

Figure 5: Extracting Data for 4 Different Cities Named Bloomington

Stay tuned for next month’s Excel update, where we’ll explore Office 365’s new Stock data types. 

Now Leaving Becker.com

You are leaving the Becker.com website. Once you click “continue,” you will be brought to a third-party website. Please be aware, the privacy policy may differ on the third-party website. Adtalem Global Education is not responsible for the security, contents and accuracy of any information provided on the third-party website. Note that the website may still be a third-party website even the format is similar to the Becker.com website.