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.
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.
In Figure 4 (see worksheet Countries) we extracted each country’s Calling 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.
Stay tuned for next month’s Excel update, where we’ll explore Office 365’s new Stock data types.
