New data types in Excel 2016

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):

New data types in Excel 2016

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”:

New data types in Excel 2016

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):

New data types in Excel 2016

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:

New data types in Excel 2016

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:

New data types in Excel 2016

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:

New data types in Excel 2016

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:

New data types in Excel 2016

… 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:

New data types in Excel 2016

… or just create another column, naming it with the appropriate name (Population, Agents etc.) from the drop-down list with hints:

New data types in Excel 2016

If you try all this on a column not with cities, but with countries, you can see even more fields:

New data types in Excel 2016

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:

New data types in Excel 2016

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):

New data types in Excel 2016

For example, for regions, territories and republics, this looks very nice:

New data types in Excel 2016

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:

New data types in Excel 2016

… and the names of companies and their abbreviated names (tickers) on the exchange:

New data types in Excel 2016

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:

New data types in Excel 2016

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

Leave a Reply