Mapping group of zip codes

Occasional Visitor

I have accounts with sales territories based on zip codes. I already have all the zip codes in their range for each account. Is there a way to map out each account's territory? More specifically, is there a way to see what territories overlap?

2 Replies

@crimsafemarketing 

I would suggest you to provide some sample of your data.

Because depending how it is organised, you can use just a MATCH() formula to get the groups.

1. Set the data

Open your Excel. Enter your data into the spreadsheet with ZIP codes and values; write them or copy from somewhere and paste.

If you prefer to do that with geography data type; input your data which includes geographical values, then go to Data > Data Types > Geography.

 

JulianoPetrukio_0-1632489389766.png

Your data will be converted into a geography data type. You can then add columns to the data, like population or tax rate.

 

2. Create the map chart

Once you finish arranging your data, select the cells you want to turn into the map.

Then on the Insert tab, go to Charts > Maps > Filled Map.

JulianoPetrukio_1-1632489417803.png

 

Here Excel reads the ZIP codes you entered and creates a map chart based on them. Relatively low values are represented by light colors and higher values are shown with darker colors.

 

JulianoPetrukio_2-1632489444989.png

 

 

 

@Juliano-Petrukio 

I'm having the same issue as @crimsafemarketing 

 

I want to give a value (1 to 7) to every group of zip codes that start with the same two digits. I need to do this for about 15 different countries in Europe.

 

What I want to do is make a table with clustered zip codes, for example in Belgium it would be:
All zip codes starting with 10 - 12 > Brussels
All zip codes starting with 13 -14 > Walloon Brabant
All zip codes starting with 15 - 19 > Flemish Brabant

All the way up to zip codes starting with 90 - 99

 

This way people can instantly see in the list what value is attached to their zip code.

 

But we also want to add a map of the country, which shows every region that has the same two digits zip code. Like this: https://en.wikipedia.org/wiki/Postal_codes_in_Belgium#/media/File:2_digit_postcode_belgique.png

 

I can use the value per 2 digit zip code to give every region a color, ranging from dark for low values to a light color for higher values. This gives people the chance to view the value of their region without searching for it in a long list but instantly recognize their region and the corresponding value by looking at the map.

 

Only problem is that I can't seem to organize zip codes based on their first two digits in Excel and make a map with only those regions. I did manage to make a map with all zip codes in Belgium, but that is way too much information and makes the map completely unreadable.