You have a bunch of zip codes (postal codes) in Excel. What can you do with those? Thanks to the new Office 365 Geography Data type, you can easily get the city, county, state or province from Excel. Then, I show you how to use a State slicer to create a zip code map for any state showing where your customers are located.
Transcript of the video:
Learn Excel from MrExcel podcast Episode 2285. The new zip code data type in Excel.
How many times do you go to the store.
And at the cashiers there say "Hey what's your zip code"? What do they do with that information.
If you are the store and so now we have some new cool tools in Excel.
You know I get to do seminars I ask people what their zip code is.
I have a whole bunch of zip codes here and I'm gonna try and collapse us down to get a summary by zip code. So: Insert, Pivot Table.
I'll put the Pivot Table in an existing worksheet right here click OK. Drag zip code down along the rows area.
And then sum the numbers. Cool. All right that's great.
I don't need a grand total here at the bottom so right click and remove grand total and actually I don't want this to be a pivot table at all so I'm gonna copy it Ctrl C and paste those values with Alt ESV and now I just have a regular old thing - a table.
Alright instead of Sum of Number let's just do Total on there.
Alright, I'll make this into a table ctrl T and this is the new part right here I'm gonna choose all of those zip codes and on the Data tab declare them to be Geography.
Now with over a thousand zip codes this is gonna take some time so I'll pause the video until it finishes ok wait that took about a minute.
Not forever but longer than you would want to watch in the video and now I see that there's 16 zip codes that are just flat-out wrong.
The person gave me the wrong zip code or maybe it was just a typo or maybe they didn't want me to know where they live who knows and I'm gonna get rid of these.
But I'm not gonna get rid of these right now because it would be super annoying to go through one at a time delete the rows.
Alright we'll just we'll get rid of here in a bit all right but now that we have the zip codes check this out this is a table so Ctrl T and then I can add here the City. Alright so now I can see the City if I needed it add the County.
I'm not gonna do that today and then the State or Province like that all right Now, to get all of the error cells - the ones that it couldn't find there's no way to sort by that icon but I can sort by state and that will force all of the field errors down here to the bottom.
Right so they don't know the city or the state. Now these could be valid zip codes.
It just spans two cities maybe but I know where I'm headed with this so I'm gonna take these few records down here and say that I want to delete those from the table.
So Home, Delete, Delete Table Rows and we end up with just the items that worked.
I mean that was what 30 items and I have 1,800 that worked so that is really cool.
So first off, converting Zip codes into a City and State and County as well all right so that's a cool thing.
All right, now the second thing I want to do is I want to add a map.
I want a map so I take the zip codes and the totals and we're going to insert a brand new map type that was introduced in Office 365 somewhere around 2017. So not a 3D map but a filled map chart.
And this is going to take a long time because there's 1800 in our little zip code boundaries that it has to has to draw and they warned me here that there's several of these that they couldn't plot right. And that could be because it's a P.O.
Box and with a P.O.Box there's no shape.
If it's a Point zip code it's not going to get it so that 87% you know it seems high all right so here's call this MrExcel Seminar Attendees. Cool.
All right and you can just barely see the items there so there's this data is just too large too large of an area to plot but what I can do is I can come down here into my table and because it's a table and I'm in Excel 2013 or newer I can insert a slicer based on state.
Okay, I'll bring this up here so it's next to our chart. Make it wider.
Number of columns maybe three or four? There we are.
All right so now what I can do is I can click on any state and it will update to show me just the counties in that state.
Right-click, Format Data Series and Map Area: Only Regions with Data. I'm not sure like that. Automatic. Right.
Oh there we are that's good so we got the shape of Wisconsin and we can see the counties where I've been. Let's do Texas. All right you can see I was out in El Paso, Dallas.
Tennessee. I'm sure a lot of Nashville area but it was also out in Knoxville.
North Carolina?
South Carolina - I remember I was in Columbia South Carolina so this is cool. This is cool.
It shows you for each place a little map of where your customers are coming from.
Florida my home state now a lot of different areas here along the east coast and down in Fort Myers and even Tampa.
Alabama I did a seminar in Mobile and Huntsville and so on. All right.
so this is all very cool being able to take zip codes and get the city state and county out of it and then once you have the zip code as a data type creating these zip code maps.
Very cool bit of functionality here.
Hey for those of you who've seen me in one of my seminars I appreciate you coming out maybe giving me your zip code even if it was a fake zip code I still appreciate having you there.
I am retiring from traveling to these seminars 35 seminars a year means 70 days on the road it's just not fun to do the travel anymore. I still love to do the seminars.
I'll still be doing the seminars in Florida where I can drive but otherwise there are five dates left: Tulsa, Springfield, Chicago, Appleton and Chattanooga.
If you've ever had a chance to see me I would love to meet you have you come to one of my seminars so click the link down there in the YouTube description for a list of how to register for these five last upcoming seminars.
Data types are discussed in my new book MrExcel LX the Holy Grail of Excel Tips.
Click that "i" in the top right hand corner.
If you like what you see here please Subscribe and ring that bell. Hey we'll see you next time for another netcast from MrExcel.
Excel
How many times do you go to the store.
And at the cashiers there say "Hey what's your zip code"? What do they do with that information.
If you are the store and so now we have some new cool tools in Excel.
You know I get to do seminars I ask people what their zip code is.
I have a whole bunch of zip codes here and I'm gonna try and collapse us down to get a summary by zip code. So: Insert, Pivot Table.
I'll put the Pivot Table in an existing worksheet right here click OK. Drag zip code down along the rows area.
And then sum the numbers. Cool. All right that's great.
I don't need a grand total here at the bottom so right click and remove grand total and actually I don't want this to be a pivot table at all so I'm gonna copy it Ctrl C and paste those values with Alt ESV and now I just have a regular old thing - a table.
Alright instead of Sum of Number let's just do Total on there.
Alright, I'll make this into a table ctrl T and this is the new part right here I'm gonna choose all of those zip codes and on the Data tab declare them to be Geography.
Now with over a thousand zip codes this is gonna take some time so I'll pause the video until it finishes ok wait that took about a minute.
Not forever but longer than you would want to watch in the video and now I see that there's 16 zip codes that are just flat-out wrong.
The person gave me the wrong zip code or maybe it was just a typo or maybe they didn't want me to know where they live who knows and I'm gonna get rid of these.
But I'm not gonna get rid of these right now because it would be super annoying to go through one at a time delete the rows.
Alright we'll just we'll get rid of here in a bit all right but now that we have the zip codes check this out this is a table so Ctrl T and then I can add here the City. Alright so now I can see the City if I needed it add the County.
I'm not gonna do that today and then the State or Province like that all right Now, to get all of the error cells - the ones that it couldn't find there's no way to sort by that icon but I can sort by state and that will force all of the field errors down here to the bottom.
Right so they don't know the city or the state. Now these could be valid zip codes.
It just spans two cities maybe but I know where I'm headed with this so I'm gonna take these few records down here and say that I want to delete those from the table.
So Home, Delete, Delete Table Rows and we end up with just the items that worked.
I mean that was what 30 items and I have 1,800 that worked so that is really cool.
So first off, converting Zip codes into a City and State and County as well all right so that's a cool thing.
All right, now the second thing I want to do is I want to add a map.
I want a map so I take the zip codes and the totals and we're going to insert a brand new map type that was introduced in Office 365 somewhere around 2017. So not a 3D map but a filled map chart.
And this is going to take a long time because there's 1800 in our little zip code boundaries that it has to has to draw and they warned me here that there's several of these that they couldn't plot right. And that could be because it's a P.O.
Box and with a P.O.Box there's no shape.
If it's a Point zip code it's not going to get it so that 87% you know it seems high all right so here's call this MrExcel Seminar Attendees. Cool.
All right and you can just barely see the items there so there's this data is just too large too large of an area to plot but what I can do is I can come down here into my table and because it's a table and I'm in Excel 2013 or newer I can insert a slicer based on state.
Okay, I'll bring this up here so it's next to our chart. Make it wider.
Number of columns maybe three or four? There we are.
All right so now what I can do is I can click on any state and it will update to show me just the counties in that state.
Right-click, Format Data Series and Map Area: Only Regions with Data. I'm not sure like that. Automatic. Right.
Oh there we are that's good so we got the shape of Wisconsin and we can see the counties where I've been. Let's do Texas. All right you can see I was out in El Paso, Dallas.
Tennessee. I'm sure a lot of Nashville area but it was also out in Knoxville.
North Carolina?
South Carolina - I remember I was in Columbia South Carolina so this is cool. This is cool.
It shows you for each place a little map of where your customers are coming from.
Florida my home state now a lot of different areas here along the east coast and down in Fort Myers and even Tampa.
Alabama I did a seminar in Mobile and Huntsville and so on. All right.
so this is all very cool being able to take zip codes and get the city state and county out of it and then once you have the zip code as a data type creating these zip code maps.
Very cool bit of functionality here.
Hey for those of you who've seen me in one of my seminars I appreciate you coming out maybe giving me your zip code even if it was a fake zip code I still appreciate having you there.
I am retiring from traveling to these seminars 35 seminars a year means 70 days on the road it's just not fun to do the travel anymore. I still love to do the seminars.
I'll still be doing the seminars in Florida where I can drive but otherwise there are five dates left: Tulsa, Springfield, Chicago, Appleton and Chattanooga.
If you've ever had a chance to see me I would love to meet you have you come to one of my seminars so click the link down there in the YouTube description for a list of how to register for these five last upcoming seminars.
Data types are discussed in my new book MrExcel LX the Holy Grail of Excel Tips.
Click that "i" in the top right hand corner.
If you like what you see here please Subscribe and ring that bell. Hey we'll see you next time for another netcast from MrExcel.
Excel
