Preview of Stocks and Geography, New Data Types in Excel

Hundreds of millions of customers have created truly amazing solutions in Excel – and they’ve done all this while working with cells that most often contain (or evaluate to) just text and numbers. What becomes possible if Excel evolved to a world where cells weren’t limited to just a single, flat, piece of text, but could instead hold a far richer concept? Today we are announcing a preview of new data types that will, over time, fundamentally transform what’s possible in Excel. We’re starting this journey with Stocks and Geography as the first two AI-powered data types, which will help users quickly turn complex data into action.

 

To see new data types in action, we’ll start with some text in cells – in this case the text represents countries from different places in the world (please forgive the typos – we’ll come back to those in just a moment…) -

 

This is just text in cells at this point… 

 

Just click the data type we want to convert the text to (Geography in this case)…

 

You can find this feature on the Data tab…

 

 

  …and the cell is converted! It now holds a new data type – representing countries in this case. This content is rich – the cell isn’t holding just a single piece of text anymore – it’s been transformed into a new kind of value which has lots of information. Notice the icon next to the name of the country, signifying that this cell holds a data type – clicking that icon will display a card showing all the data in that cell:

 

Cards are the way to view the full contents of the cell

Excel uses Microsoft Knowledge Graph, the same intelligent service that powers Bing, to provide the data. It recognizes, in context, what is meant by your text and converts that to the right type of data. It even fixed the typos and capitalization mistakes in the country names!

 

 

It’s easy to work with this data in the grid – if you happen to have this data in a table, you may notice a widget that lets you grab fields and pull them into a column of their own. 

It’s easy to pull fields out to a column of a Table – Excel writes the formula for you

And Excel didn’t just copy that data out of the cell – it dereferenced it by writing a formula for you! You can use them in any function as well – just use the dot “.” operator to get a list of fields – as shown below, this time shown using City data. These are full, calculation enabled, first class data types in cells!

 

 

The dot operator and autocomplete are how you use fields in a formula

 

These work with other features in Excel as well – for instance, here’s an example with all the U.S. states – with some U.S. Census data showing the % of population change pulled out into a column. You can create a Map Chart in a single click.

 

A map of the U.S. showing % population change

If you want to filter that column of States by time, you can tell the Filter to operate on Time Zone by changing the selection from the drop down at the top

 

 

The Select field dropdown lets you work with a field from column of data types

…and just like that, the table and map update.

The filter is applied, and the map updates, without needing to pull fields into the grid

It’s not just States or Countries either – we support things like Zip Codes, Cities, and other types like Stocks, Index Funds, and other financial data (to use these, just type in some ticker symbols, fund names, or company names, and hit the Stock button). This data is refreshable as well – for example, many of the Stocks will fetch up to date prices when the market is open and you Refresh.

 

As you try this feature, you may notice the intelligent conversions sometimes aren’t sure what to convert to. In those cases, Excel will ask you to specify which data type should be returned from the service. For example, the city Portland works fine when it’s a list of other cities that are nearby, but when it’s in a blank grid, with no other textual context, then Excel will ask you which Portland you meant. You can always change the data type via the right click menu as well. Read more how to use data types in this article.

 

Ambiguous cases are detected

With the introduction of these two data types, Excel moves beyond just Text and Numbers -into much richer data types that don’t always have to be single, flat, value. For now, we’re starting with these two domains of data, but we’ll be adding more over time, including those, based on data unique to your organization. Also, data types is only one of a new wave of intelligent features coming to Excel - Insights was another that we launched recently. Read about Insights and other intelligent features in Excel.

 

 

To stay connected to Excel and its community, read Excel blog posts, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.

           - John Campbell, PM Manager, Excel

 

The new data types is starting to roll out in preview to Office 365 subscribers enrolled in the Office Insiders program, in the English language only, this month.