Contents
With the Summer 2018 Updates, Excel 2016 received a revolutionary new ability to add a new type of data to cells − shares (Stocks) и Map (Geography). The corresponding icons appeared on the tab Data (Date) in group Data Types (Data types):
What is it and what is it eaten with? How can this be used at work? What part of this functionality is applicable to our reality? Let’s figure it out.
Entering a new data type
For clarity, let’s start with geodata and take the following table “for experiments”:
First, select it and turn it into a “smart” keyboard shortcut Ctrl+T or using the button Format as a table tab Home (Home — Format as Table). Then select all the city names and select the data type Geography tab Data (Date):
A map icon will appear to the left of the names, indicating that Excel has recognized the text in the cell as the geographic name of a country, city, or region. Clicking on this icon will open a beautiful window with details on this object:
What was not recognized automatically will be marked with a question mark, when clicked, a panel will appear on the right, where you can refine the request or enter additional data:
Some names can have a dual meaning, for example Novgorod can be both Nizhny Novgorod and Veliky Novgorod. If Excel does not recognize it as it should, then you can right-click on the cell and select the command Data Type – Change (Data Type — Edit), and then choose the correct option from those offered in the panel on the right:
Adding Detail Columns
You can easily add additional columns with details for each object to the created table. For example, for cities, you can add columns with the name of the region or region (admin division), area (area), country (country / region), date founded (date founded), population (population), latitude and longitude (latitude, longitude) and even the name of the mayor (leader).
To do this, you can either click on the pop-up icon in the upper right corner of the table:
… or use a formula that will refer to an adjacent cell and add a dot to it, and then select the desired option from the drop-down list of hints:
… or just create another column, naming it with the appropriate name (Population, Agents etc.) from the drop-down list with hints:
If you try all this on a column not with cities, but with countries, you can see even more fields:
Here are economic indicators (income per capita, unemployment rate, taxes), and human (fertility, mortality), and geographical (forest area, CO2 emissions) and much more – almost 50 parameters in total.
The source of all this information is the Internet, the search engine Bing and Wikipedia, which does not pass without a trace – this thing does not know many things for Our Country or gives out in a distorted form. For example, among the mayors, only Sobyanin and Poltavchenko give out, and he considers the largest city in Our Country … you will never guess which one! (not Moscow).
At the same time, for the States (according to my observations), the system works much more reliably, which is not surprising. Also for USA, in addition to the names of settlements, you can use a ZIP code (something like our postal code), which quite unambiguously identifies settlements and even districts.
Filtering by implicit parameters
As a nice side effect, converting cells to new data types makes it possible to filter such columns later on implicit parameters from the details. So, for example, if the data in the column is recognized as Geography, then you can filter the list of cities by country, even if there is clearly no column with the name of the country:
Display on the map
If you use in the table recognized geographical names not of cities, but of countries, regions, districts, provinces or states, then this makes it possible to subsequently build a visual map using such a table using a new type of charts Cartogram tab Insert – Maps (Insert — Maps):
For example, for regions, territories and republics, this looks very nice:
Of course, it is not necessary to visualize only the data from the proposed list of details. Instead of the population, you can display any parameters and KPIs in this way – sales, number of customers, etc.
Stock data type
The second data type, Stocks, works in exactly the same way, but is tailored for recognizing stock indices:
… and the names of companies and their abbreviated names (tickers) on the exchange:
Please note that the market value (market cap) is given for some reason in different monetary units, well, this thing does not know Gref and Miller, obviously 🙂
I want to warn you right away that using all this for trading will not work very well, because. the data is updated only once a day, which, in most cases, is too slow for trading. For more frequent updates and up-to-date information, it is better to use macros or queries to exchanges via the Internet using Power Query.
The future of new data types
Undoubtedly, this is only the beginning, and Microsoft will most likely expand the set of such new data types. Perhaps, over time, you and I will even have the opportunity to create our own types, sharpened for specific work tasks. Imagine a type, for example, for displaying data about an employee or client, containing his personal data and even a photo:
HR managers would like such a thing, what do you think?
Or imagine a data type that stores the details (size, weight, color, price) of each item or service on a price list. Or a type that contains all the game statistics of a certain football team. Or historical weather data? Why not?
I’m sure we have a lot of interesting things ahead 🙂
- Import bitcoin rate from online exchange to Excel using Power Query
- Visualization of geodata on a map in Excel
- Converting values with the CONVERT function