Home
Microsoft

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

 

Image1.pngThis is just text in cells at this point… 

 

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

 

new data types.jpgYou 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:

 

Image3.pngCards 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. 

Image4.pngIt’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!

 

 

Image5.pngThe 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.

 

Image6.pngA 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

 

 

Image7.pngThe Select field dropdown lets you work with a field from column of data types

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

Image8.pngThe 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.

 

Image10.pngAmbiguous 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.

14 Comments
Occasional Visitor

I'm an Office 365 subscriber, Pro Plus I think.  How do I get the new Stocks and Geography data?

 

Thanks,

David  

@DAVID BAHR

 

You can follow the instructions in the below article.  Be sure to pay close attention to the portion about editing the XML configuration in Notepad:

 

http://www.knowledgewave.com/blog/office-365-tips-how-to-become-a-microsoft-office-insider

Microsoft

@DAVID BAHR@Matt Mickle has got it right - to try it now, you need to be a subscriber who is getting the fast version of the Office insider builds. We're steadily rolling it out over time to more and more insider customers - and making improvements based on customer usage and input along the way. Eventually it will be deployed to all subscribers.

New Contributor
Would be useful to know the version number that gets this feature. I'm on Excel build 9126.2116 (click to run, monthly channel targeted) and don't see this.
Senior Member
Wow! This is really something! Great job Excel team!
Microsoft

@Calum Steen this is for folks who are on the "Insider" builds (formerly known as Insiders Fast). It will come to the monthly targeted channel too, but is currently only in the Insiders ring. See What is Office Insider for more information. One final thing to keep in mind - we deploy it to each channel over a period of time until eventually all users have the feature turned on - so some people may currently be on the Insider channel but may not see it just yet - they should soon as it gets turned on for more and more Insiders over the coming days.  

Contributor

As I'm just on a monthly channel.

What is the version number to get this update. Actually I'm on the 1803 version (built 9126.2116)

Microsoft

@Mehdi HAMMADI you can't use it currently on the monthly channel, it's not just a matter of build number. See my comment above - you'd need to switch from the monthly channel to the Insiders channel. On the insider channel, I believe the more current builds that have it are based off of 9226. That said, it's still rolling out. What I mean by that is that even if a customer has the correct build, the feature may be turned off - we turn it on for a wider and wider set of customers, monitoring for quality issues as we go, in an effort to reduce bugs/issues. So there may be some customers who are using Insiders builds that don't see it yet - in that case, please be patient a little longer as the feature will light up for 100% of Insiders soon, and then will begin rolling out to the monthly channel after that. 

Contributor

Thank you for the reply and I will waiting for It.

New Contributor

 I'm now on Excel Version 1805 (Build 9327.2006 click-to-run) but this feature remain elusive...

This is absolutely great functionality and I really love how it's implemented.

HOWEVER, Excel currently crashes every time the Stocks data is refreshed... Initially, the stock data is picked up without issues but, on the attempt to refresh, Excel crashes and restarts. I have tried under variety of scenarios but always the same.

 

Another user has reported this issue at:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_365hp/refreshrefresh...

The above issue has now been fixed (requires updating to the very latest version) - thanks a lot to MSFT team.

Microsoft

@Leonid Klimov - thanks again for bein an Excel customer and for taking the time to report the issue - as of the roll out from this morning, everything should be fixed now, glad to see that you got the change. @Calum Steen, you should be seeing the feature if you are on the "insiders" build as noted in the replies above - please let me know if you still aren't seeing it - all Insiders should have had access since earlier this month. 

Occasional Visitor

This is a great addtion by Microsoft developers...wish I could take advantage of it. Followed the instructions for becoming an "Insider" and got an error when attempting to run the Development Tools setup program, with the correct configuration.xml file:

https://www.reddit.com/r/Windows10/comments/8szehd/office_365_insider/?utm_content=full_comments&utm...