By Betty Hughes
Microsoft Excel “custom functions” can do a variety of tasks related to address and zip code information, such as creating zip code lists, checking the validity of mailing addresses, even calculating driving time and driving distance.
Custom functions, also referred to as User-Defined Functions or UDFs, perform complex tasks or calculations and are used in cell formulas just like the standard Excel functions SUM or AVERAGE. In this article we will describe how you can use custom functions to import demographic data straight into an Excel worksheet.
Demographic information is invaluable for business planning and operation, and can help guide product design, advertising, even the selection of store location or website design. However, demographic data, primarily due to the sheer amount of data available, is not always easily accessible. It either requires the user to import from a large database into another application, or learn a new application designed specifically for demographic analysis.
Custom functions in Excel, however, can automate interactions with a demographic database and import data easily into a worksheet, allowing the user to chart and analyze the data using Excel’s powerful built-in capabilities. Since all interactions with the demographic database occur in the background, the user can work entirely within the familiar Excel environment, with no need to learn a new application.
How would this work in an Excel worksheet?
Let’s say we need demographic data, such as median income, for the zip code 08034. The custom function formula in the worksheet cell would be
= CustomFunction (08034, Median Income)
The function called CustomFunction results in an automatic call to a local demographic database, with the value returned to the worksheet cell being the median income for the zip code 08034.
If we need data for a list of zip codes in a worksheet, we can also input a formula that uses the zip code worksheet cell location.
For example, if the zip codes are listed in column A, the first custom function formula would be
= CustomFunction (A1, Median Income)
where A1 refers to the cell location of the first zip code. We can then just copy and paste this formula to apply it to the rest of the list. From the user’s point of view, the custom function formula acts just like any other formula within a worksheet.
The custom function can be designed to grab any combination of information available in the database. Not only can custom functions be used to access a wide range of demographic data, such as median income and age, household income, and population estimates, but demographics can be obtained based on zip code, as in the example above, or other variables such as city, state, county, area code, etc.
This is a good example of how Excel custom functions can automate specialized tasks or calculations, with no need to leave the familiar environment of Excel or learn a new application. From checking the validity of address information to importing demographic data into Microsoft Excel, custom functions are very valuable tools for analyzing zip code and other location-based information.
Please see the YouTube video at http://www.youtube.com/watch?v=gHPlMDP3d3k for step-by-step instructions on how to use custom functions to import demographic data into Excel. The author of this article, Betty Hughes, helped develop CDXZipStream, an Excel add-in that provides zip code data and other demographic information using custom functions. Please visit http://www.cdxzipstream.com for more information or to download a free evaluation copy. This site also includes example spreadsheets and tutorials showing how to use custom functions in a variety of applications.