Geography Data Type

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 2 - 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+F2, click that, and you get some information from various places about that city: Population, Area, Latitude, Longitude, Time zone, 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 []. Alright, so no square brackets needed if it's a single word, no spaces, no punctuation, but- so =A2.Population works, otherwise you have to use that longer value. Alright, =A2. let's go time zone(s), 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, comma, see 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 FIELDVALUE is not working. Alright now, in addition to having that new FIELDVALUE, we have a new error to go along with that. So if I would ask for =FIELDVALUE(A2,”Something”) and it doesn't know what something is, and we get a #FIELD! 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 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 that "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, of course these don't have to match, Time zone, 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 dropdown, 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 countries, 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, % 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 recalc, it 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. Or a great new formula construct, =A2.Population, remember to wrap Population in [] if the value you want, the name of the value has spaces or any kind of punctuation. There's a new FIELDVALUE 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, to learn about more new features in Excel, please subscribe and click that bell!

Well I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!