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.)
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. To learn more about basic charting concepts in Excel, tune into Wayne Winston’s upcoming webcast entitled Excel: Charting Part 1.
Dr. Wayne Winston is a Visiting Professor at Wake Forest University and Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University. He holds a B.S. in mathematics from MIT and a Ph.D. in operations research from Yale. He won over 40 teaching awards at Indiana University. He has written over a dozen books including Marketing Analytics, Data Analysis and Decision Making, Operations Research, Practical Management Science, Excel 2016 Data Analysis and Business Modeling, and Mathletics. Dr. Winston has taught classes and consulted for many leading global organizations. He is also a two-time Jeopardy! Champion and has consulted for the NBA’s Dallas Mavericks and New York Knicks.