If you are searching for how to Map Excel data by ZIP code, then you have come to the right place. Today, in this article I will explain how to map Excel data by ZIP code. Actually, Excel has a wonderful feature named Data Type. With geographic data type, you can easily find the location of any valid ZIP code.
How to Map Excel Data by ZIP Code: 2 Easy Methods
Here, I will describe 2 methods to Map data by ZIP code in Excel. In addition, for your better understanding, I’m going to use a sample dataset. Which contains 2 columns. Those are the Customer Name and ZIP Code.
1. Using Geographic Data Type to Map Data by ZIP Code
Here, I will use the Geography tab to find the Region and Country for the given ZIP codes. Basically, for the mapping, I will include geographical data in Excel. Now, let’s talk about how you can insert geographical data for different ZIP codes in Excel.
- Firstly, select the ZIP codes.
- Secondly, from the Data tab >> click on the Geography feature, which is under Data Types. Furthermore, you might use the drop-down arrow to find the Geography Data Type.
As a result, you will see the following output.
- Now, click on the symbol beside the ZIP codes.
So, you will get all the geographic information including the detailed location related to that ZIP code.
- After that, select the C5 cell.
- Then, click on the Field icon.
- Then, choose City.
As a result, you will see the city name holding that ZIP code in a cell next to C5 means in the D5 cell.
- After that, select the D5 cell.
- Then, click on the Field icon.
- Then, choose a Country/region.
So, you will see the country name holding that ZIP code in a cell next to D5 means in the E5 cell.
Here, you can follow another way to insert the geographical location for that particular ZIP code.
- First, in the D5 cell write down (=C5.) Then you will be able to choose any type of available information from a list.
As I want to insert the city name. I have chosen City. Thus, the formula becomes.
=C5.City
- Subsequently, press ENTER.
- Then, to get the other geographical locations select D5 & E5 cells and then use the Fill Handle icon.
As a result, you will get all the regions and countries for the given ZIP codes.
Read More: How to Create Zip Code with Excel Formula
2. Use of Filled Map Chart for ZIP Code in Excel
You can use the Filled Map chart to get the location by a given ZIP code. The steps are given below.
Steps:
- Firstly, by following the steps of method-1 you should insert the city and country name.
- Secondly, select the D5:D13 cells and by clicking the Feild icon, you may insert Population.
So, you will get all the populations of those countries.
- Now, select the Country, and Population columns.
- Then, from the Insert tab >> go to Maps which is under the Charts group section >> choose Filled Map chart.
Finally, you will see the Map chart.
- Then, double-click on the Chart field.
As a result, a new window named Format Data Series will open at the rightmost corner of the Excel sheet.
- After that, select Series Options.
- Then, you may choose Diverging (3-color) from the Series Color.
- Furthermore, you may change the colors too.
Lastly, you will get the following Map chart.
Read More: How to Lookup ZIP Code in Excel
How to Plot ZIP Code on Map in Excel
In this section, I will plot the ZIP code on the Map chart in Excel. So, you should follow the methods first to get the geographic location of the given ZIP codes. Here, I have taken the city name and city’s population for your better understanding.
- Now, select the data.
- Then, from the Insert tab >> choose Table.
At this time, a dialog box named Create Table will appear.
- Now, make sure that you have selected the data range in the Where is the data for your table? box. Here, if you select the data range before then this box will auto-fill up.
- Then, check the My table has headers option.
- Finally, press OK.
- Now, select the created Table.
- Then, from the Insert tab >> go to Maps which is under the Charts group section >> choose Filled Map chart.
As a result, you will get the following map.
At this time, for my ZIP codes, I will insert a Slicer.
- Firstly, select any cell within the table.
- Secondly, from the Insert tab >> go to the Filters option.
- Thirdly, choose Slicer.
Subsequently, a new dialog box named Insert Slicers will appear.
- Now, check the ZIP Code.
- Then, press OK.
Finally, you will see the special filter column named ZIP Code on the Excel sheet.
Here, if you choose any of the ZIP Codes, the map will show you only that region.
Read More: How to Lookup County from Zip Code in Excel
💬 Things to Remember
- Here, you can refresh your Excel sheet and you will get up-to-date values by using geographic data type.
- Furthermore, if you had big data then your Excel file for mapping charts will take up so much space.
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 2 suitable methods of How to Map Excel Data By Zip Code. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Convert Zip Code to State with VLOOKUP in Excel
- Excel Formula to Change Zip Code to State
- How to Auto Populate Zip Codes in Excel
<< Go Back to Zip Code in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!