Mark a cell as a geography data type in Excel and you can use formulas to return fields such as: State, County, Population, Latitude, Longitude, or Mayor. This feature is new in Office 365.
The article for today's video is here: Geography Data Types in Excel
Specific Sections:
(0:00) Intro to Geography Data Types in Excel
(0:22) Picture Transparency in Excel
(0:55) List of Twitter handles of who helped
(1:54) Declaring data as geography
(2:05) Searching for unknown data
(2:28) Ambiguous city names - Sutter Illinois
(2:50) Displaying a Data Card with more info
(3:11) New =A2.Population formula nomenclature
(3:45) Get County or mayor for each city
(4:15) Using IFERROR around the formula
(4:30) Format as Table for extra fields
(4:40) Return time zone for each city
(4:50) Sort west to east using longitude in the filter drop-down
(5:40) Fields returned from Country - area, forested percent, and more
(6:10) Needs Office 365 - not Excel 2019 or Excel 2016
The article for today's video is here: Geography Data Types in Excel
Specific Sections:
(0:00) Intro to Geography Data Types in Excel
(0:22) Picture Transparency in Excel
(0:55) List of Twitter handles of who helped
(1:54) Declaring data as geography
(2:05) Searching for unknown data
(2:28) Ambiguous city names - Sutter Illinois
(2:50) Displaying a Data Card with more info
(3:11) New =A2.Population formula nomenclature
(3:45) Get County or mayor for each city
(4:15) Using IFERROR around the formula
(4:30) Format as Table for extra fields
(4:40) Return time zone for each city
(4:50) Sort west to east using longitude in the filter drop-down
(5:40) Fields returned from Country - area, forested percent, and more
(6:10) Needs Office 365 - not Excel 2019 or Excel 2016
Transcript of the video:
Learn Excel from MrExcel podcast episode 2249. Geography Data Types in Excel.
Hey, welcome back to MrExcel netcast. I'm Bill Jelen.
Last week I was in Albuquerque, New Mexico, which is just a beautiful, beautiful place to do.
I am doing a Power Excel Seminar there and I've been doing this new section called new features announced at Ignite in Orlando.
And I show off things like the picture transparency, which is great, you know, because it can reveal the things behind the picture.
And, of course, talk a lot about Dynamic arrays but the thing as I reflect on this, that really is getting the most ohs and ahhs, is the new geography data type, which I have not covered here in the podcast.
So when I'm doing the session live, I type in the city where I'm at and then ask people to give me other cities both large and small, and then use this.
But, you know, how do we do that here in the podcast?
So, I'm really thankful I sent a tweet out yesterday, I'm recording this on Saturday, November 2nd.
I say I'm creating a new Excel video, give me a quick reply with your home city, and wow a 140 replies. So, thanks to everyone who replied.
Even the people who replied after I'm doing this video. Sorry, you didn't make it into just a shout-out to...
Here's everyone's Twitter thumbnails who replied.
All right, so here's the data we have and there were some duplicates in there and so if it's the same city.
I remove them of the three, but other than that I just left it exactly as it came in.
All right so places like Plymouth, you know, not fully qualified and we're going to see how well this feature works. So, the new feature is called a geography data type.
I select all of my cells.
Come here to the data tab and you see we have two new things, if you're in Office 365.
This is not an Excel 2019 or Excel 2016 or 13 or anything like that. We have Stocks and Geography.
Now I have no idea why these are in Spanish and everything else is in English.
My Excel crashed earlier today and when it fired back up, I'm in Spanish, but in your case, its gonna say Stocks and Geography. I'll report this to the Excel team and I'm sure it'll get fixed.
All right, so we choose our data like this and then choose Geography.
And what's gonna happen is we're gonna send all these values out to the internet.
And if it's successful, we get the little map icon, but then we have some here that it couldn't figure out. All right, so they give you a little search here.
All right, how about just this and there we are.
And it's funny they have the accent above the e, I have no idea why they couldn't figure that out. Choose select.
West Collingswood Heights. Now, this is in New Jersey.
And with all due respect to the person in West Collingswood Heights as I've tried to figure out how it could figure this out, I had a search for just Collingswood.
And I'm sure that West Collingswood Heights is so much better than Collingswood, but unfortunately, we're gonna have to go with a new one. And then Sutter. Sutter. This is really crazy.
Sutter, there are multiple Sutters in Illinois, so when I asked for Sutter, Illinois, they want to know, is it the one in Hancock County or the one in Sutter County and I actually went out...
I know the person now, I went and looked your Sutter, you were nice enough to give me the zip code, is in Hancock County.
So I fixed that one. Okay, so three how do we fix? Now, here's the cool thing.
A little map icon.
You can hover over that map icon, show card, CTRL+shift+F5 or just click on the map icon. And it brings up pictures of the city; how cool is that.
And tells you a whole bunch of information about that city but this is not useful because it's just in a little floating box, you know.
What good is that! But check this out! Come over here, now type population and then a brand new formula type, =A2.Population and BAM, they give me the population for that city.
And then check this out! Double click and shoot that down and we get the population for all of those. All right.
So, here we have a city that Wikipedia must not know the answer to, so that new #FIELD! error. All right, what else can we do here.
We can come out and ask for, basically, the state.
And, for the United States, this works out fine that's =A2.Administrative Division 1.
Double clicking, copy that down or I can ask for the county, and this doesn't make as much sense in other parts of the world. So, Administration Division 2.
Double click and copy that down, or I can even ask for the Mayor =A2.Leaders. Double click and copy that down.
Now, take a look at this formula up here in the formula bar, because there's X parentheses in there they wrapped that in square brackets.
I didn't have to wrap it in square brackets, they just wrapped it in square brackets.
And it looks like a lot of these places Wikipedia doesn't know who the mayor is, you can always wrap that, in IFERROR like this with quote quote and at least you'll get blanks in those cases.
Now, this is one case certainly where it makes a lot of sense to format as a table, so CTRL+T, the advantages of formatting as table is you get this awesome little symbol out here.
You can come out to this little symbol and ask for something else, like maybe the time zone, =A2.timezone, just off the screen there and it returns what time zone they're in, if they know.
The other awesome, awesome thing about this, is they know latitude and longitude but I'm not even going to bring those in to the table.
I'm just gonna come back here to city and open this up and say that I want to display this, or sort this by Longitude and sort smallest to largest and it goes West to East.
So of the Twitter followers who answered me, Portland, Oregon is the farthest West and then you see that we go throughout the United States here, and then over through Europe and to Australia and New Zealand.
Really awesome, the ability to be able to sort the city by something that's not even in the data.
And, of course, we could have added Latitude and Longitude and sorted that way, but it's nice to be able to sort by something that you don't really want to display at all. So, really really cool. All right, here's another one.
So we're gonna start with country, go to the data tab, choose Geography and then let's see what we can get for country.
We can get the abbreviation, we can get the area and then all kinds of things, like what percentage of the country is forested.
Sort that Z to A. All right, Sweden, Zambia, Brazil, lots of force there.
All kinds of interesting information for each of those countries.
Again, this is office 365 only, it'll never be an Excel 2019 because they're going out to the Internet. This is a service, right?
In order to have a service, you have to have the subscription, so if you have the perpetual versions of Excel, you shouldn't have the perpetual versions of Excel.
Awesome, awesome new thing the geography data types and again thanks to everyone who played along and answered my tweet. I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel netcast. I'm Bill Jelen.
Last week I was in Albuquerque, New Mexico, which is just a beautiful, beautiful place to do.
I am doing a Power Excel Seminar there and I've been doing this new section called new features announced at Ignite in Orlando.
And I show off things like the picture transparency, which is great, you know, because it can reveal the things behind the picture.
And, of course, talk a lot about Dynamic arrays but the thing as I reflect on this, that really is getting the most ohs and ahhs, is the new geography data type, which I have not covered here in the podcast.
So when I'm doing the session live, I type in the city where I'm at and then ask people to give me other cities both large and small, and then use this.
But, you know, how do we do that here in the podcast?
So, I'm really thankful I sent a tweet out yesterday, I'm recording this on Saturday, November 2nd.
I say I'm creating a new Excel video, give me a quick reply with your home city, and wow a 140 replies. So, thanks to everyone who replied.
Even the people who replied after I'm doing this video. Sorry, you didn't make it into just a shout-out to...
Here's everyone's Twitter thumbnails who replied.
All right, so here's the data we have and there were some duplicates in there and so if it's the same city.
I remove them of the three, but other than that I just left it exactly as it came in.
All right so places like Plymouth, you know, not fully qualified and we're going to see how well this feature works. So, the new feature is called a geography data type.
I select all of my cells.
Come here to the data tab and you see we have two new things, if you're in Office 365.
This is not an Excel 2019 or Excel 2016 or 13 or anything like that. We have Stocks and Geography.
Now I have no idea why these are in Spanish and everything else is in English.
My Excel crashed earlier today and when it fired back up, I'm in Spanish, but in your case, its gonna say Stocks and Geography. I'll report this to the Excel team and I'm sure it'll get fixed.
All right, so we choose our data like this and then choose Geography.
And what's gonna happen is we're gonna send all these values out to the internet.
And if it's successful, we get the little map icon, but then we have some here that it couldn't figure out. All right, so they give you a little search here.
All right, how about just this and there we are.
And it's funny they have the accent above the e, I have no idea why they couldn't figure that out. Choose select.
West Collingswood Heights. Now, this is in New Jersey.
And with all due respect to the person in West Collingswood Heights as I've tried to figure out how it could figure this out, I had a search for just Collingswood.
And I'm sure that West Collingswood Heights is so much better than Collingswood, but unfortunately, we're gonna have to go with a new one. And then Sutter. Sutter. This is really crazy.
Sutter, there are multiple Sutters in Illinois, so when I asked for Sutter, Illinois, they want to know, is it the one in Hancock County or the one in Sutter County and I actually went out...
I know the person now, I went and looked your Sutter, you were nice enough to give me the zip code, is in Hancock County.
So I fixed that one. Okay, so three how do we fix? Now, here's the cool thing.
A little map icon.
You can hover over that map icon, show card, CTRL+shift+F5 or just click on the map icon. And it brings up pictures of the city; how cool is that.
And tells you a whole bunch of information about that city but this is not useful because it's just in a little floating box, you know.
What good is that! But check this out! Come over here, now type population and then a brand new formula type, =A2.Population and BAM, they give me the population for that city.
And then check this out! Double click and shoot that down and we get the population for all of those. All right.
So, here we have a city that Wikipedia must not know the answer to, so that new #FIELD! error. All right, what else can we do here.
We can come out and ask for, basically, the state.
And, for the United States, this works out fine that's =A2.Administrative Division 1.
Double clicking, copy that down or I can ask for the county, and this doesn't make as much sense in other parts of the world. So, Administration Division 2.
Double click and copy that down, or I can even ask for the Mayor =A2.Leaders. Double click and copy that down.
Now, take a look at this formula up here in the formula bar, because there's X parentheses in there they wrapped that in square brackets.
I didn't have to wrap it in square brackets, they just wrapped it in square brackets.
And it looks like a lot of these places Wikipedia doesn't know who the mayor is, you can always wrap that, in IFERROR like this with quote quote and at least you'll get blanks in those cases.
Now, this is one case certainly where it makes a lot of sense to format as a table, so CTRL+T, the advantages of formatting as table is you get this awesome little symbol out here.
You can come out to this little symbol and ask for something else, like maybe the time zone, =A2.timezone, just off the screen there and it returns what time zone they're in, if they know.
The other awesome, awesome thing about this, is they know latitude and longitude but I'm not even going to bring those in to the table.
I'm just gonna come back here to city and open this up and say that I want to display this, or sort this by Longitude and sort smallest to largest and it goes West to East.
So of the Twitter followers who answered me, Portland, Oregon is the farthest West and then you see that we go throughout the United States here, and then over through Europe and to Australia and New Zealand.
Really awesome, the ability to be able to sort the city by something that's not even in the data.
And, of course, we could have added Latitude and Longitude and sorted that way, but it's nice to be able to sort by something that you don't really want to display at all. So, really really cool. All right, here's another one.
So we're gonna start with country, go to the data tab, choose Geography and then let's see what we can get for country.
We can get the abbreviation, we can get the area and then all kinds of things, like what percentage of the country is forested.
Sort that Z to A. All right, Sweden, Zambia, Brazil, lots of force there.
All kinds of interesting information for each of those countries.
Again, this is office 365 only, it'll never be an Excel 2019 because they're going out to the Internet. This is a service, right?
In order to have a service, you have to have the subscription, so if you have the perpetual versions of Excel, you shouldn't have the perpetual versions of Excel.
Awesome, awesome new thing the geography data types and again thanks to everyone who played along and answered my tweet. I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
