If you have a list of postal codes and would like to those postal codes to create a map in Excel, you may be disappointed. If the program succeeds at giving you anything at all without the dreaded “Map charts need geographical data such as country/region, state/province, county or postal code.” message, you probably won’t be happy with the output. The message even suggests using postal codes, so why is it so hard to use them? I can’t say, but there is a data adjustment you can make for more satisfactory results.
Step 1: Convert Postal Codes to Forward Sortation Areas (FSAs)
A forward sortation area (FSA) is an area based on the postal code. Forward Sortation Areas contain many postal codes, all with the same first three characters. Lots of data provided by Statistics Canada is available in this format. You will lose some specificity compared with postal codes, but Excel will like the map much better.
To convert a postal code to a forward sortation area, you just need to use the first three characters. You can use the formula
=LEFT(A1, 3) to convert a postal code in cell A1. Postal code T5K 1T1 would become simply T5K.
Step 2: Use the FSAs to Create a Map
To create the map, select all data and use the Maps dropdown to create a filled map. It’s okay to leave the forward sortation areas labelled as postal codes.
Step 3: Adjust the Map to Show the Correct Area.
Your map may be correct, but may show too broad of an area. In my case, these postal codes from Edmonton showed the entire province of Alberta on the map.
Right click and select “Format Plot Area…”.
Next, use the Plot Area Options dropdown to select the data series.
Select “Only regions with data” as the map area, and it will zoom in on the appropriate area. Make any other adjustments you would like, such as adding labels and changing colour.
There you have it, the final map. I hope this has been useful for those looking to create a map from Canadian postal codes in Excel.