In our last blog we reviewed all the free Microsoft Excel templates we offer for CDXZipStream, that can help you make tasks like route optimization, zip code radius analaysis,  and geocoding that much easier.  You also have the option to customize the appearance of these templates, particularly to help highlight specific data using Excel built-in functionality.  Let’s take a look at the Zip Code Radius Calculations template as an example.

This particular template includes optional data columns B through E that can be used for information like name, telephone number, or any other fields associated with the listed addresses.  You can fill in the appropriate field names at the top of the data columns, or if you don’t need to use these columns, just hide them.  To hide the columns In Excel 2003, select Format (Column) from the main toolbar, or in Excel 2007 and 2010 use Format (Visibility) from the Home tab.   Do not delete these columns, since this will change the location of the input data required for the analysis, and will make the template inoperable.

We also recommend that to ensure you don’t lose any data along the way, you should save the template under a new name both before and after making any major changes.  

Now let’s review a few methods to change the display of the template data worksheet.  Keep in mind that once these changes are made, you can then copy and paste the results to another blank workbook to permanently save them:

1.  Sort - Most users of Microsoft Excel have used the sort function at some point, and it can certainly be used here if you need to sort the input or output data in the worksheet.  After pressing “Calculate” in the Zip Code Radius Calculations template, the worksheet will already be autofiltered in order to show the addresses or zip codes that fall within the specified radius distance of the target.  But even after autofiltering, the addresses will be shown in the order in which they were entered into the worksheet.  If you would like to the addresses in order of their distance to the target zip code, for example, select Data (Sort) from the main toolbar in Excel 2003, or in Excel 2007 and 2010 use Sort from the Data tab.  From here you can sort by the appropriate column in either ascending or descending order.  For more information about sorting, check out these links for Excel 2003 and Excel 2007-2010.

2.  Autofilter – Autofiltering hides any rows that do not meet the filter criteria you specify.  For instance, you may want to show only address data for a particular city, zip, or other data field.  Since the Zip Code Radius Calculations template automatically autofilters the results based on the distance from the target zip code, you will need to first turn off and then turn on the autofilter if you would like to use this for some other filtering criteria.  You can also filter based on two data fields, for example, if you want to show only those addresses within the target radius that are in a single town.  To autofilter  make sure a cell in the white data area is selected, then select Data (Filter - Autofilter) from the main toolbar in Excel 2003, or in Excel 2007 and 2010 use Filter from the Data tab.  For more details about autofiltering, see the following links for Excel 2003 and Excel 2007-2010.

3.  Conditional Formatting – This approach usually uses color to highlight data of interest.   Just specify the font color, background color, or other format option using similar specifications to autofilter;  in this case however, rows are formatted as specified if they meet your criteria, not hidden if they don’t.   For instance, you can highlight address and/or distance values as desired, and just like in sorting or filtering, this can be done simultaneously for one or more data fields.  To apply conditional formatting, select Format, then Conditional Formatting from the main toolbar in Excel 2003, or in Excel 2007 and 2010 use Conditional Formatting from the Home tab.  For more information about conditional formatting, see these links for Excel 2003 or Excel 2007-2010.

However, please remember to retain and make visible the instructions worksheet in the template that includes the Hughes Financial Services copyright.

Over the last several months we've been developing Microsoft Excel templates that use CDXZipStream to perform a variety of tasks, ranging from route optimization to zip code radius analysis.  They are pre-formatted and automated so that users simply input their data, click a button or two, and the desired output is returned to the worksheet. The templates are free, work in Microsoft Excel 2003 and up, and can be evaluated with free trials of CDXZipStream and Microsoft MapPoint, which is also used in some of the templates.  (See the top of our links page to download.) At this point there are enough templates that we thought it would be a good idea to review in one place all their functionality and provide links to their YouTube tutorials:

Radius Calculations Template:  Input a list of addresses or zip codes, and this template will identify which ones are within a specified radius distance of a target zip code.  The calculations are based on the straight-line distance between the centroid (center) locations of the zip codes.  This is a one of our more popular templates and can be used for a variety of applications, such as filtering customer addresses around a store or other key location.  You can view the tutorial here.

Nuclear Facility Radius Calculator:  An example application of our Radius Calculations template that identifies nuclear facilities in a radius around a target zip code.   You can view the tutorial here.

Driving Distance Template:  This template works with Microsoft MapPoint to calculate driving time, driving distance, cost, or total trip duration between two lists of addresses. Input the same address for one of the lists if you need driving data around a single location.  This is a very easy way to validate travel expenses.  Please view the tutorial here.

Route Optimization Template (with GPX export):  This template also works with Microsoft MapPoint, to re-order and optimize intermediate stops on a driving route to achieve the shortest possible driving time.  The optimized route can then be exported to a GPX file that is compatible with many GPS devices.  This is a very easy-to-use, economical approach to route optimization that can reduce transportation costs for applications ranging from food delivery services to customer sales calls.  You can view the tutorial here.

Driving Matrix Template:  Input a matrix of addresses or zip codes to calculate driving distance, driving time, cost or trip duration between all combinations of routes.  For instance, the input matrix would look something like this:

Driving calculations will be performed for the route between Address 1 and Address A, Address 1 and Address B, Address 1 and Address C, etc., until all combinations are covered.  This is a quick and easy way to assign driving destinations, such as for delivery, taxi, or limousine services.  You can view the tutorial here.   

Geocoding Template:  Get latitude and longitude data for a long list of addresses.  This template works with Microsoft MapPoint, and can also be used to verify the accuracy of mailing lists based on the quality of the address match to the MapPoint database.   View the tutorial here.   

Reverse Geocoding Template:  Working with Microsoft MapPoint, this template reverse geocodes by finding the closest address to a geocode (latitude and longitude) point. This can be extremely useful for processing points from a gps device.  You can view the tutorial here.   

Although these templates cover many uses and will fit the bill for most clients, please contact us at customsolutions@CDXTech.com if you have a special need and require template customization.