CDXZipStream,our Microsoft Excel add-in, contains an array of demographic data that can provide invaluable insight into your current and potential customers.  For example, data such as household size, income, educational attainment, ethnic background, and housing value can be a critical input to your marketing program or business plan.  CDXZipStream demographic versions can provide these fields and more, based on resources such as the 2010 Census, the latest 5-year American Community Survey, and the Census Survey of U.S. Businesses   – please see our version comparison for more information.

Once you get the demographics you need from CDXZipStream, it may also be helpful to map the data to get a visual picture of the results.  In this case we recommend using Microsoft MapPoint, which allows you to easily import data from Excel and provides a wide range of mapping options as well.  

(Note:  MapPoint is available as a 14-day, fully-functional free trial, available for download from the Microsoft website.)

To map your data using MapPoint, click on “Data” on the main MapPoint toolbar at the top of the screen, and then select “Import data wizard …” from the drop-down list.  From here you can select the Excel file that contains the demographic data of interest; you can also select other types here such as text or Access database files.  After selecting the desired file, you will also be asked to select the appropriate worksheet (assuming the file was an Excel workbook). Then click “Next”, and you’ll see a MapPoint dialog like this:

From here the mapping parameters can be specified.  First select the applicable country or region, as well as whether the first row contains column headings.   Under each column shown in the dialog, you will also need to specify the data type for each column of data.  For example, the first column in the screenshot above is the list of zip codes that forms the basis of the demographic data, and so select “ZIP Code” from the drop-down list of data types. At least one column of data like this must be available in order to geo-locate the data on the map.  For the other columns of data, you can specify “Name” (to help identify the area or pushpin on the map), “Other Data” (which will provide the numeric value), or “Skip Column” (to exclude this data altogether).  Note that if multiple columns of data are identified as “Other Data” all values will be used in the mapping analysis.  Since data will overlap for the same location, these maps may be difficult to read; consider creating separate maps for each column of data as an alternative.

In the example above we want to map median age by zip code.  The first column of zip codes is used to geo-locate the data, so we select “ZIP Code” as the data type, and we also select median age as the “Other Data” type.  The column containing store number information can be used as the “Name” type so this data can be used to identify each point provided on the map.  All other columns of data are specified as “Skip Column”.  After clicking on “Finish”, all we need to do is specify the map type:

To create a map showing circles that are sized relative to the median age value, we click on “Sized Circle”, and the result is:

Passing the cursor over one of the circles will show the zip code and median age value.  Double clicking on the same circle will also show the store number value. 

Once the map has been created, you can also use MapPoint right-click features for each point.   Use your mouse to right-click on one of the circles to show or hide data, include the point in a route, or even create a drive-time zone around the point, like this: 

MapPoint can be an extremely powerful tool to help visualize your geographic data and support your business needs.  Consider evaluating the free trial of MapPoint the next time your get demographic data from CDXZipStream.  We also recommend MP2K as a reputable reseller of MapPoint software. 

CDXZipStream, our Microsoft Excel add-in for zip code and address analysis, has a number of built-in functions that perform area calculations around a given geographic location.  These can be invaluable tools for designating sales and service territories, finding customers for a brick-and-mortar store, designing delivery routes, and more.  Here’s a quick review of this capability:

Generate a zip code radius list:  The CDXZipStream function CDXRadius  can quickly find zip codes within a specified radius of a central zip code.  This is based on straight-line distance calculations between the centroid points of the target zip code and all nearby zip codes.  To use CDXRadius, right-click on any cell in your Excel worksheet, and from the menu click on  CDXZipStream Functions, then Insert CDXRadius Function.   The input box will look like this:   

After specifying the target zip and radius distance, you can ask for the resulting list of zip codes either as an Excel array formula (see here for more information on Excel arrays), or as a text string within a single worksheet cell.   The output will also include distance data from the target zip, with the data separated by vertical bars when listed as a text string: 

Find locations within a zip code radius:  Do you have a long list of addresses or zip codes, and want to determine if they fall within a specified radius of a store or other location?  It’s very easy to do this by cross-checking your address list against the list generated by CDXRadius.  First, run CDXRadius with the desired target zip code and radius distance, making sure to specify the output as an array formula.  Then for each location in your original list, use the Excel LOOKUP function to see if the address zip code exists in the array generated by CDXRadius.  A step-by-step tutorial of this method is shown in the YouTube video Find Zip Codes in a Radius Using Excel.

As an alternative, CDX Technologies also offers a free, downloadable Excel template that automatically identifies locations around a zip code.   This template is especially well-suited for use with very long address lists, since the template code accesses CDXZipStream functions directly (versus using Excel worksheet functions) which increases calculational speed.  Please refer to our links resource for CDXZipStream template downloads.

Find exact locations within a radius area:  Sometimes zip codes are just not accurate enough, so when you need to use exact locations for a radius analysis, use a combination of two CDXZipStream functions.  First use CDXLocateMP to find the latitude and longitude of each address, then CDXDistance2WP to calculate the straight-line distance between each latitude/longitude point and the central point of interest.   The Excel autofilter feature can then quickly find the locations that meet the radius distance criteria.  This multi-step process is explained in detail in our blog post How to Very Accurately Filter Adresses Based on Distance.

Map an Area:  You can also use the CDXRadius function to map a radius around a zip code.  Specify the target zip code and radius distance, and request the output as a radius map.  

Need a drive time map that delineates the area within a specific driving time of a location?  Specify the target zip and drive time, and request the output as a drive time map (using the CDXRadius function again).  The drive time area is shown in red and is typically a polygon, reflecting variations in road conditions and driving speeds:

Geographic area analysis can be a critical component of activities ranging from target marketing to product delivery.  Remember CDXZipStream when you need a fast and economical area analysis that meets your business needs.

CDXZipStream, our Microsot Excel add-in, has some very useful functions that perform a variety of location-based calculations, ranging from creating zip code lists for a given city to finding latitude and longitude for an address.  These functions are readily accessible by right-clicking with the mouse on any cell in an Excel worksheet - a "custom function" (similar to standard Excel functions like SUM or AVERAGE) is then inserted into the worksheet cell and performs the desired calculation.  Please refer to our previous blog for more general information about custom functions in Excel.

To help guide CDXZipStream users when accessing these functions, here is a quick review that includes short video tutorials (in Shockwave and Windows Media formats) on their use:

CDXFindZip:  Finds all the zip codes for a given city.  CDXFindZip.swf (1.56 mb)  CDXFindZip.wmv (1.03 mb)

CDXDistance:  Finds the distance between two zip codes.  CDXDistance.swf (1.88 mb)  CDXDistance.wmv (1.15 mb)

CDXRadius:  Finds all zip codes within a radius distance of a target zip code.  When used with Microsoft MapPoint, it can also create a radius map around a target zip code.  CDXRadius.swf (4.41 mb)  CDXRadius.wmv (3.10 mb)

CDXRouteMP:  Working with Microsoft MapPoint, calculates driving time, distance, and cost between two locations, and can also create a map of the route.  CDXRouteMP.swf (5.73 mb)  CDXRouteMP.wmv (3.77 mb)

CDXLocateMP:  Working with Microsoft MapPoint, verifies latitude, longitude, and address information for a given location, and can also create a map of the location.  CDXLocateMP.swf (4.24 mb)  CDXLocateMP.wmv (3.06 mb)

CDXDistance2WP:  Working with Microsoft MapPoint, calculates the distance between two waypoints.  A waypoint is a location defined by its latitude and longitude.  CDXDistance2WP.swf (2.27 mb)  CDXDistance2WP.wmv (1.47 mb)

CDXClosest: Finds the zip code from a list that's closest to a target zip code.  CDXClosest.swf (4.10 mb)  CDXClosest.wmv (2.70 mb)

CDXZipList:  Creates a list of zip codes for any city, county, or state.  CDXZipList.swf (3.08 mb)  CDXZipList.wmv (1.82 mb)

In upcoming posts we'll be reviewing these functions in more detail, showing how they can be used to solve real-world problems when dealing with zip code, address and location-based data in general.  Stay tuned!