Geography and Stocks are the first types of Linked Data Types in Excel
This feature is exclusive to Office 365 since it requires downloading online data
Type some cities, select the cities, mark as Geography
Hover over icon to see data card
New =A2.Population syntax or wrap in square brackets: =A2.[National anthem]
New FIELDVALUE function
New #FIELD! error
Cool trick: Sort table by a column that is not there
Field error during refresh
How to Recalc using Data, Refresh
This feature is exclusive to Office 365 since it requires downloading online data
Type some cities, select the cities, mark as Geography
Hover over icon to see data card
New =A2.Population syntax or wrap in square brackets: =A2.[National anthem]
New FIELDVALUE function
New #FIELD! error
Cool trick: Sort table by a column that is not there
Field error during refresh
How to Recalc using Data, Refresh
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2227: Geography Data Types.
If you like what you see in this video, please subscribe and ring that bell.
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
Another great, new feature that appeared in Office 365.
Now, this feature will not ever be in Excel 2019.
It is Office 365-Exclusive, you have to be a subscriber for this new data types feature.
Right now they introduced two-- Stocks and Geography-- but my sense is there's going to be a lot more of these as time goes by.
Alright, so here we have a bunch of cities in the United States, I'm going to choose those and choose Geography, and they're going to go out to the internet and try and figure out what I mean by those cities.
And we get this great, little map icon on each one, which means that they were successful, alright?
Now here's some great new things we can do with these linked data types: The first thing-- and by all means this is not my favorite-- hover over the icon-- it's a show card, which is Ctrl+Shift+F to click that-- and you get some information from various places about that city-- Population, Area, Latitude, Longitude, Timezone, Description-- and then down here tells you where the data came from.
Alright, so, you know, that's great in case you don't have Google or Bing and you can't search something.
But here's what's really cool, is we have this new ability to pull data from Cell A2 using Dot Constructs.
So, =A2. and then I can choose.
So, you know, if I would choose, for example, A2.Population, it will return the population from the internet, double-click to copy that down and I get populations for all those cities.
=A2.Population.
Isn't that a wild formula?
Something unlike anything we've ever seen.
Now, if you happen to choose something that has spaces in it, like Administration Division 1, if I choose that then you have to wrap it in square brackets.
Alright, so no square brackets needed if it's a single word, no spaces, no punctuation.
But so equal to population works.
Otherwise, you have to use that longer value.
Alright, =A2. let's go time zones-- Eastern time zone, double click and copy down to see the time zone.
Alright.
Now this is the fast and easy way to do this.
They also provided a function for this and I don't know anyone who would ever use this function, it's called: =FIELDVALUE.
So, A2, -- see the the tooltips there are not working, so let's just choose one of these from the list-- = A2 Area.
But that's not the right way to do this-- you have to do =A2, Area-- there's still a bug there.
Hopefully, that'll get fixed over time.
Use quotes-- yeah, so that autocomplete for field value is not working.
Alright, now in addition to having that new field value, we have a new error to go home with that.
So if I would ask for equal field value of A2 comma something and it doesn't know what something is, and we get a #FIELD exclamation error.
That shows you that the other place where this error will appear temporarily is if you change a value.
So let's change from San Diego to Los Angeles, I'm going to press Enter, and watch these five-- these four-- cells right here.
They're temporarily going to change to Field errors, but then pop in with the right value.
So that case means, "So, hey, we're going out to the Internet to try and figure out what data to use." Alright?
Now, here's some really cool things: So Population, State-- and, of course, these don't have to match-- Timezone, get rid of this one here, and I will add a filter to this.
Alright, now what I'd like to do is I'd like to see these things arranged in order from East to West.
Alright, so I'm going to open the drop-down and we have this new thing, "Select Field", and I can sort by something that's not even in the spreadsheet.
For example, Longitude, sort Largest to Smallest, and we go Miami, Cleveland, Atlanta, Chicago-- that seems to be right, going from the East coast to the West coast.
Right?
So that's a cool trick.
Now this is working with cities, it also works with countries.
Here, I have =A2.[ National Anthem ], I get a different list for Country, probably a lot more information available for Countries or States or Provinces and so on.
Like that.
You know, another thing that can go wrong is if you put in a country that does not exist.
Alright, so put in the lost Country of Atlantis, then you get that.
What?
What are you talking about that little icon there-- We need help with this text-- and then a data selector pops up where you can search, and that's not what I want so you can put in, you know, another Country, another fictitious place, and then finally I meant Italy and I choose that.
Now, the first thing is, I get the card and then I have to type Select to actually have that changed to Italy, and then copy down to get the National anthem.
Alright?
So, these are cool, these are great.
It's a brand new thing for Excel.
I've been hearing about this for a couple of years-- this project-- and it's finally arrived with both Geography and Stocks.
Alright, so you can put in some companies here.
Let's put in KO for Coke.
Alright, they were able to resolve that to Coca-Cola Company.
=A2. got all kinds of stock information-- the CEO, the Change, Percentage Change, and so on.
The thing that's interesting here, is that they're going to give us the current price, but there's no way to get, like, a history of prices, when I imagine all kinds of people need a way to go back and, like, store the historical prices, it'll be all kinds of macros that'll pop up for that.
And so I'm also pressing F9 to recount-- does not recalculate.
Does this because it's expensive to go out to the internet and get all of these.
So, back here on the Data tab, Refresh All will force these to be refreshed.
Alright, so it's not going to recalculate all the time, which is actually, you know, a good thing.
Alright, so this is one of those new features discussed in my book, Microsoft Excel 2019 Inside Out.
You can pre-order that book now-- click the "I" on the top right hand corner.
Linked data types-- first two are Geography and Stocks.
Its exclusive to Office 365 since it requires downloading data; type some Cities, select the Cities, mark as Geography there on the Data tab, hover over the icon to see the card; a great new form of the construct, =A2.Population, remember to wrap Population in square brackets if the value you want for the name of the value has spaces or any kind of punctuation; there's a new field value function, it's not working well when you use the autocomplete; New #FIELD! error, and then a cool trick-- Sort by a column-- it is not there if you use the filters, the field error appears during refresh, that just means it's going out-- I guess if you're offline that field error would appear up here forever; and then data refresh in order to force a recalc.
To download the workbook from today's video, visit the URL in the YouTube description.
Learn about more new features in Excel, please subscribe and click that bell.
Well, I thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like what you see in this video, please subscribe and ring that bell.
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
Another great, new feature that appeared in Office 365.
Now, this feature will not ever be in Excel 2019.
It is Office 365-Exclusive, you have to be a subscriber for this new data types feature.
Right now they introduced two-- Stocks and Geography-- but my sense is there's going to be a lot more of these as time goes by.
Alright, so here we have a bunch of cities in the United States, I'm going to choose those and choose Geography, and they're going to go out to the internet and try and figure out what I mean by those cities.
And we get this great, little map icon on each one, which means that they were successful, alright?
Now here's some great new things we can do with these linked data types: The first thing-- and by all means this is not my favorite-- hover over the icon-- it's a show card, which is Ctrl+Shift+F to click that-- and you get some information from various places about that city-- Population, Area, Latitude, Longitude, Timezone, Description-- and then down here tells you where the data came from.
Alright, so, you know, that's great in case you don't have Google or Bing and you can't search something.
But here's what's really cool, is we have this new ability to pull data from Cell A2 using Dot Constructs.
So, =A2. and then I can choose.
So, you know, if I would choose, for example, A2.Population, it will return the population from the internet, double-click to copy that down and I get populations for all those cities.
=A2.Population.
Isn't that a wild formula?
Something unlike anything we've ever seen.
Now, if you happen to choose something that has spaces in it, like Administration Division 1, if I choose that then you have to wrap it in square brackets.
Alright, so no square brackets needed if it's a single word, no spaces, no punctuation.
But so equal to population works.
Otherwise, you have to use that longer value.
Alright, =A2. let's go time zones-- Eastern time zone, double click and copy down to see the time zone.
Alright.
Now this is the fast and easy way to do this.
They also provided a function for this and I don't know anyone who would ever use this function, it's called: =FIELDVALUE.
So, A2, -- see the the tooltips there are not working, so let's just choose one of these from the list-- = A2 Area.
But that's not the right way to do this-- you have to do =A2, Area-- there's still a bug there.
Hopefully, that'll get fixed over time.
Use quotes-- yeah, so that autocomplete for field value is not working.
Alright, now in addition to having that new field value, we have a new error to go home with that.
So if I would ask for equal field value of A2 comma something and it doesn't know what something is, and we get a #FIELD exclamation error.
That shows you that the other place where this error will appear temporarily is if you change a value.
So let's change from San Diego to Los Angeles, I'm going to press Enter, and watch these five-- these four-- cells right here.
They're temporarily going to change to Field errors, but then pop in with the right value.
So that case means, "So, hey, we're going out to the Internet to try and figure out what data to use." Alright?
Now, here's some really cool things: So Population, State-- and, of course, these don't have to match-- Timezone, get rid of this one here, and I will add a filter to this.
Alright, now what I'd like to do is I'd like to see these things arranged in order from East to West.
Alright, so I'm going to open the drop-down and we have this new thing, "Select Field", and I can sort by something that's not even in the spreadsheet.
For example, Longitude, sort Largest to Smallest, and we go Miami, Cleveland, Atlanta, Chicago-- that seems to be right, going from the East coast to the West coast.
Right?
So that's a cool trick.
Now this is working with cities, it also works with countries.
Here, I have =A2.[ National Anthem ], I get a different list for Country, probably a lot more information available for Countries or States or Provinces and so on.
Like that.
You know, another thing that can go wrong is if you put in a country that does not exist.
Alright, so put in the lost Country of Atlantis, then you get that.
What?
What are you talking about that little icon there-- We need help with this text-- and then a data selector pops up where you can search, and that's not what I want so you can put in, you know, another Country, another fictitious place, and then finally I meant Italy and I choose that.
Now, the first thing is, I get the card and then I have to type Select to actually have that changed to Italy, and then copy down to get the National anthem.
Alright?
So, these are cool, these are great.
It's a brand new thing for Excel.
I've been hearing about this for a couple of years-- this project-- and it's finally arrived with both Geography and Stocks.
Alright, so you can put in some companies here.
Let's put in KO for Coke.
Alright, they were able to resolve that to Coca-Cola Company.
=A2. got all kinds of stock information-- the CEO, the Change, Percentage Change, and so on.
The thing that's interesting here, is that they're going to give us the current price, but there's no way to get, like, a history of prices, when I imagine all kinds of people need a way to go back and, like, store the historical prices, it'll be all kinds of macros that'll pop up for that.
And so I'm also pressing F9 to recount-- does not recalculate.
Does this because it's expensive to go out to the internet and get all of these.
So, back here on the Data tab, Refresh All will force these to be refreshed.
Alright, so it's not going to recalculate all the time, which is actually, you know, a good thing.
Alright, so this is one of those new features discussed in my book, Microsoft Excel 2019 Inside Out.
You can pre-order that book now-- click the "I" on the top right hand corner.
Linked data types-- first two are Geography and Stocks.
Its exclusive to Office 365 since it requires downloading data; type some Cities, select the Cities, mark as Geography there on the Data tab, hover over the icon to see the card; a great new form of the construct, =A2.Population, remember to wrap Population in square brackets if the value you want for the name of the value has spaces or any kind of punctuation; there's a new field value function, it's not working well when you use the autocomplete; New #FIELD! error, and then a cool trick-- Sort by a column-- it is not there if you use the filters, the field error appears during refresh, that just means it's going out-- I guess if you're offline that field error would appear up here forever; and then data refresh in order to force a recalc.
To download the workbook from today's video, visit the URL in the YouTube description.
Learn about more new features in Excel, please subscribe and click that bell.
Well, I thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
