Microsoft Excel "custom functions" can do a variety of things related to address and zip code information, such as importing demographic data, finding zip codes within a radius, even calculating driving time and distance. Custom functions (also called User-Defined Functions or UDFs) perform complicated calculations or tasks and are used in cell formulas just like the standard Excel functions SUM, AVERAGE, or LOOKUP. In this article we will show you how to use custom functions, in conjunction with Microsoft MapPoint, to check the validity of street addresses.

Incorrect address information is a common problem. The street or city names can be misspelled, the zip code may not match the city information, or the street number may be invalid. You can use a web-based program like MapQuest or Google Maps to check the validity of a single address, but for multiple addresses in a mailing list or delivery route, the best approach is to use an automated program that can check large lists of data all at one time.

An Excel custom function working in conjunction with a stand-alone mapping program like Microsoft MapPoint can validate long lists of addresses automatically and return the results directly to your worksheet. Since all interactions with MapPoint occur in the background, you can work completely within the Excel environment - there's no need to learn a new application. For example, to check the validity of an address in worksheet cell A1, you can input the appropriate custom function formula (inserted in cell B1, for instance) which would look something like this: "= CustomFunction (A1)". If the address appears to be valid, the "best match" address is returned to cell B1. If no match is found, the message "Invalid address" is returned. MapPoint also has the capability to provide best match information even when there is a minor error in the address. In cases where there is a city or street misspelling, or transposed numbers in the zip code, the best match returned to the worksheet will have these errors corrected.

For a long list of addresses in column A, the custom function formula can be copied and pasted as needed in column B, so you can automatically validate many addresses without the time-consuming manual input required for other mapping programs.

It is also possible to specify that other types of data be returned to the worksheet if the address is determined to be valid. For example, geocode information for the address, such as latitude and longitude, can be returned. Street name, city, state, zip code, or country information can also be returned, providing a reliable way to parse the address into separate worksheet cells.

This is a good example of how Excel custom functions can use other applications like Microsoft MapPoint to automate specialized tasks and calculations, with no need to learn a new application or even leave the familiar environment of Excel. From checking the validity of address information to identifying zip codes within a radius, custom functions can be valuable tools for analyzing zip code and other location-based information.

Please see the YouTube video at Address Validation in Excel for more information about using Excel custom functions for address validation. The author of this article, Betty Hughes, helped develop CDXZipStream, an Excel add-in that provides zip code data, demographics by zip code, driving route optimization, zip code radius analysis, geocoding and more using custom functions. A free 30-day trial is available as a download from our website, as well as example spreadsheets and tutorials showing how to use custom functions in a variety of applications.

When you are using Microsoft Excel 2007, you usually need to copy a part of the content to other location in the worksheet or copy to another worksheet, sometimes different workbook. You can do it easily by using the copy and paste function in Microsoft Excel 2007.

Let's open a blank worksheet (by now you should be able to do it by just opening Microsoft Excel 2007 or by clicking on Office Button and select New). We have to input some content into the worksheet before I could demonstrate the Copy and Paste functions to you. Let's just randomly key in "Copy" into cells from A1 to A10. Now you have a series of text "Copy". Say now you want to copy the whole series of text "Copy" to Cells D1 to D10.

Follow the simple steps as what I am going to show you now. Firstly select cell A1 to A10, then on your Keyboard, press Ctrl+C to copy the cells to the Clipboard. Alternatively you could put your cursor on the select area (A1 to A10), Right Click and select Copy, Or you could select Home-- >Clipboard-- >Copy.

You will find that the selected areas are now covered by dotted line (this is the indication that the area selected for copying). Next is move the cursor to cells D1 and press Enter (Alternatively you may want to Right Click on cell D1 and select Paste, or you could follow the path Home-- >Clipboard-- >Copy ), you will paste the content of A1 to A10 all the way to D1 to D10.

Copy and Paste functions are not only limited to text only, you can copy chart, formula, logical functions and any other features in Microsoft Excel 2007 to any location you prefer within Excel.

Luis T is the owner of http://www.excelexpertuser.com. Find out more on how you could be a master of Microsoft Excel on the website.

Whether you work at an accounting firm, a marketing company, an auto dealership, a school attendance office, a manufacturing plant's human resources department, or an office associated with city, county, state or federal government, chances are, you'll be called upon to use and learn Excel.

Just about every workplace has a demand for Excel, the computing world's most commonly used software program for comparative data analysis. Excel has been available in various incarnations for more than a decade. Each subsequent release takes the program to new territory.

Popularly known as the best spreadsheet program on the market, Excel is powerful, easy to use, and remarkably efficient. Excel is highly interactive. Its spreadsheet cells are arranged in a collection of rows and columns, each of which can hold a number, a text string, or a formula that performs a function, such as calculation. It's easy to copy and move cells as well as modify formulas. The spreadsheet is displayed on the computer screen in a scrollable window that allows the document to be as deep or as wide as required.

Working for a major newspaper in Northern California, I was one of several reporters involved in the annual evaluation of our county's economy. The job involved collecting data that would be punched into Excel spreadsheets that ultimately ranked information according to the category of statistics being reviewed.

The beauty of Excel, from the standpoint of newspaper research projects, is that you can use formulas to recalculate results by changing any of the cells they use. With this model, you can use the same spreadsheet data to achieve various results by simply defining and changing formulas as desired. It is this feature that makes Excel so useful in so many different arenas.

With a click of the mouse, we reporters were able to get answers to a wide variety of questions. Which employers had the greatest number of workers? Which ones had the highest amount of gross annual receipts? Which ones appeared to be growing and which ones had declining sales? What was the volume of real estate loans and had there been a decline or increase from the previous year?

We looked at local and national retail, services, financial institutions, government entities, agriculture, the wine industry, tourism and hospitality, manufacturing, residential and commercial real estate, everything imaginable.

Excel allowed us to examine ratios, percentages, and anything else we wanted to scrutinize. Finally, we were able to use Excel to compare the results to data from previous years.

Since reporters tend to be former English majors, most of those who worked on this annual project were more familiar with Microsoft Word than any other software program. Therefore, most were required to undergo Excel training. For some, learning Excel was easier than for others. A few relied on guides such as Microsoft Excel Bible. Some reporters underwent an Excel tutorial while others learned by doing.

Not only were the Excel spreadsheets crucial to the research, the format of each was published in the newspaper. Here is where some additional Excel functions came into play. Editors were able to make the spreadsheets more visually appealing by using colors and shading, borders and lines, and other features that made the spreadsheets easy for readers to decipher.

Wearing another of my several hats in the newsroom, I often wrote articles concerning the local job market. I found proficiency in Excel was a requirement for a wide variety of employment positions and that area recruiting firms offered their clients opportunities to take free or low-cost Excel tutorials in preparation for the workplace. Most employers expect job candidates to already know the software that the work will require and don't want to have to train new hires.

Don't kid yourself. If you're seeking any kind of office work, you'll need to know not only Microsoft Word but also Excel.

Excel and Microsoft are trademarks of Microsoft Corporation, registered in the U.S. and other countries.

About Video Professor: Video Professor is the industry leader in self-paced computer software tutorials. Since 1987, the core of Video Professor's learning initiative has been to provide home users as well as corporate professionals with valuable and user-friendly learning solutions for today's most common software titles. Tutorials are offered on CD-ROM and through Video Professor Online, an Internet-based program.

About the Author: Sheri Graves is an award-winning writer who freelances from her home in Santa Rosa, California.