Toolbars are very different between versions of Microsoft Excel  (in Excel 2007 and 2010 it’s actually called the “ribbon”), and we’re going to review here how to access and troubleshoot the CDXZipStream toolbar in all recent versions of Excel.

In Excel 2003, the CDXZipStream toolbar is immediately visible below the main Excel toolbar at the top of the worksheet. You can view (or not view) the toolbar by using the View – Toolbars option. Excel 2007 and 2010 groups third-party add-ins in a new tab at the top of every workbook labeled "Add-Ins". After installing the application in these versions you can see the CDXZipStream toolbar by pressing the "Add-ins" tab as shown here: 

If the CDXZipStream toolbar isn't visible, please try the following: 

In Excel 2003 select the Excel Menu item "Help" and then click on "About Microsoft Excel". In the lower right hand corner of the screen that appears press "Disabled Items". If any add-ins beginning with CDXZipStream appear, click on them to re-enable. Then restart Excel. 

If you don't see the CDXZipStream toolbar under the "Add-ins" menu item in Excel 2007 click the Microsoft Office Button (the big circle in the upper left hand corner), click Excel Options, and then click Add-Ins. Check the "Inactive Application Add-ins" or "Disabled Application Add-ins" for add-ins that start with CDXZipStream.  If any appear, use the "Manage" function at the bottom of the screen. Select the type "Disabled Application Add-ins" and then press the "Go" button. Enable any add-ins that begin with “CDXZipStream”.

In Excel 2010 use File-Options and then click Add-Ins. Check the "Inactive Application Add-ins" or "Disabled Application Add-ins" for add-ins that start with CDXZipStream:

If any appear, use the "Manage" function at the bottom of the screen. Select the type "Disabled Application Add-ins" and then press the "Go" button. Enable any addins that begin with CDXZipStream.  

If no CDXZipStream add-ins are disabled, use the "Manage" function to select the type "Com Add-ins" and then press the "Go" button. In the screen that appears make sure that add-ins with “CDXZipStream” are checked and press "OK". 

In Excel 2007-2010, if the above steps do not resolve the problem please try the following:

1. In Excel Options – Add-ins use the Manage Function at the bottom of the screen. 

2. Select the type "Com Add-ins" and then press the "Go" button:

3. Click on the unchecked CDXZipStream add-in function, then press "Remove". 

4. Click "Add" and then browse to the file C:\Program Files\CDXZipStream\CDXZipStream.dll and press OK.  In 64 bit versions of Windows the Program Files directory is named Program Files (x86).

5. The CDXZipStream add-in should be added and be checked. Then click OK.

Now you're ready to use CDXZipStream.

In today’s blog we’re going to highlight a neat little right-click function in CDXZipStream called CDXClosest Zip, which identifies which zip code out of a list is the closest distance (as the crow flies) to a target zip code.

CDXClosestZip is a fast, easy way to determine which store location is closest to a client, or which distribution center should be used for a product delivery.  Just input the zip code list of interest into Microsoft Excel, along with the target zip code, and then right-click on any empty cell in the worksheet to select the CDXClosestZip function:

Let’s say we have a customer located in zip code 33607, and all store location zip codes to be searched are in the named range called ZipList.  (As an alternative, we could also use a list range such as “C1:C100”.)

Here’s what the input would look like:

In this case we’re requesting that the closest zip code is returned to the worksheet – this would be the zip code in ZipList that is closest to 33607.  As shown in the drop down list, we could also request the actual closest distance to the closest zip code in either miles, kilometers, or nautical miles.   

CDXClosestZip can also be applied to a longer list of target customer zip codes.   Just use the cell location of the first target zip code, like this:

The resulting custom function formula is returned to the worksheet cell:

= CDXClosestZip (B2,0,ZipList)

Where “B2” is the cell address of the first target zip, “0” indicates we’re requesting the closest zip code is returned to the worksheet, and “ZipList” is the named range of the zip code list that will be searched.

Once this result is returned to the worksheet, all we need to do is copy this formula along each customer zip code.   For a short tutorial showing CDXClosestZip in action, please refer to the YouTube video Zip Code Distance Function in Microsoft Excel.  

Keep in mind that CDXClosestZip is a relatively complex function.  It calculates the distance for every zip code combination in both the targeted and searched lists.  If the targeted and searched lists are both 1000 zip codes long, one million (1000 x 1000) distance calculations must be performed, along with subsequent sorting and displaying of results.  

For very large lists, we do recommend an alternative approach (available with our CDXZipstream MapPoint and Basic versions) that works in conjunction with Microsoft MapPoint.   In this case, an Excel matrix is set up for each zip code combination, like this:

Using the function CDXLocateMP, the latitude and longitude of each zip code location is found (this process is called geocoding), then the function CDXDistance2WP uses this data to calculate the distance between each location.  This approach is faster than CDXClosestZip since no database calls to get the zip code locations need be made once the geocoding is completed.  It’s then very easy to use Excel’s sorting function to find the closest distance to each target zip.  For more information, you can download a spreadsheet that applies this approach to a real-life car dealership location analysis.