Microsoft Office 365 has once again upped the ante for Excel users with the introduction of its revolutionary Geographic Data Types feature. This game-changing functionality allows users to input a location in Excel and then craft formulas that seamlessly extract relevant information about that location. From population figures to area details, the possibilities are vast. In this exploration, we'll unravel the potential of this new tool, demonstrating its applications through various scenarios.
A Glimpse of Geographic Data Types
Let's kick off our journey by delving into the practical application of Excel's Geographic Data Types. Imagine you have a list of state names entered in cells E3:E7 of your worksheet. These could be states from any country, and for our illustration, let's consider the United States. The file we're working with is aptly named "Vishalnewdatatypes.xlsx."
Figure 1: Using Geographic Data Types to Extract State Information
Once you've selected the range E3:E7, navigate to the Data tab and choose Geography from the Data Types group. This will unleash the power of the Geographic Data Types, represented by card icons, as depicted in Figure 1.
With your cursor in a specific cell, let's say E3, clicking on the card icon reveals an array of information sourced from Wikipedia. This information can be effortlessly extracted to different cells, paving the way for dynamic data utilization. The card for Indiana, for instance, is displayed in Figure 2.
Figure 2: Card for Indiana
Extracting Information with Formulas
Now, let's bring formulas into play. In cell F3, type "E3." Excel, in its intuitive manner, displays all available information. By clicking on Population, the formula "E3.Population" becomes your gateway to retrieving Indiana's population. A simple copy of this formula to cells E4:E7 ensures the population figures for the other states are seamlessly extracted.
To add another layer of versatility, we venture into column G. In cells G3:G7, we input the formula "=@FIELDVALUE(E3,$G$2)." The new FIELDVALUE function plays a crucial role here, pulling the data field specified in cell G2, which, for our illustration, is "Area." Notably, if you decide to shift your focus to a different data field like "Capital," a mere update in cell G2 dynamically reflects this change in G3:G7.
Since certain data, such as population figures, undergo changes over time, it's prudent to periodically refresh your data. This can be accomplished by selecting the relevant range and opting for Data Types, followed by Refresh.
Expanding Horizons: Beyond States to Cities and Countries
Geographic Data Types extend far beyond the realm of states. In Figure 3, the worksheet "Cities" showcases our ability to extract each city's associated state and county.
Figure 3: Extracting the State and County
Figure 4, housed in the "Countries" worksheet, takes our exploration to a global scale. Here, we extract each country's Calling Code and Life Expectancy with the same finesse.
Figure 4: Country Code and Life Expectancy
Addressing Uncertainties: The Power of Flexibility
In instances where Excel encounters doubts about the location you have in mind, a question mark symbolizes this uncertainty. By clicking on the question mark, you gain the flexibility to choose a specific location. As exemplified in the "Questions" worksheet and Figure 5, instances of Bloomington in four different statesâIndiana, Minnesota, Illinois, and Californiaâare addressed. Here, we extract each city's state and population, showcasing the adaptability of Geographic Data Types.
Figure 5: Extracting Data for 4 Different Cities Named Bloomington
Looking Ahead: A Teaser for Stock Data Types
As we conclude this deep dive into Excel's Geographic Data Types, a tantalizing prospect awaits in our next Excel update. Office 365 introduces Stock Data Types, offering a new dimension to data manipulation within Excel. Stay tuned for our exploration of this exciting addition, where we'll unravel the potential of Stock Data Types and their diverse applications.
In the dynamic landscape of Excel, the introduction of Geographic Data Types exemplifies Microsoft's commitment to empowering users with cutting-edge tools. As professionals and learners alike harness these capabilities, the bridge between innovation and functionality becomes ever more seamless. Excel's evolution continues, and embracing these advancements ensures a future-ready skill set for every user.