Importing and Exporting Contacts from Outlook using Excel

MS Excel 2002 10.6501.6735 SP3
MS Outlook 2002 10.6515.6735 SP3

If you try and use the import/export feature of Outlook to manage contacts, good luck. Outlook is heavily design biased to be the primary manager of the contacts. It doesn't like to export its contacts, have them manipulate and the imported back in. If you manually export your contacts think of it as a one way operation. And importing contacts gets a little more challenging.

There are great third party applications (e.g. Intellsync in the ubiquitous Blackberry Desktop Manager) but you think you can outsmart things and use MS Excel to quickly manage and manipulate your contacts outside of Outlook. Maybe you have no reason, but here is the trick to round trip edit your contacts using MS Excel: Named Ranges.

The best way to get a template XLS file is to first export the MS Outlook contacts. I'm sure you can figure out how to do this. If not, you probably wouldn't want to anyway.

Take a look at the exported file: It will have the columns that you want, and the data that is expected. But it is easy to miss the 'Named Range' that was created in the Excel XLS file. You can't simply manipulate your contact data without understanding that the Named Range must be managed too.

Named Ranges are managed by the menu options Insert->Name->Define. Once you see the Define Name window things get obvious. You will see that MS Outlook created a Named Range called 'Contacts'. In the Refers To field at the bottom of the Define Name window, you will be able to click on the 'show me the range' icon in the bottom right and see the extent of the Named Range.

There is also another trick to know what is in the Excel document. Take a look at the menu File->Properties and select the Contents tab. This is an excellent way to get the gist of what is in the XLS file.

Edit away on your contacts! But then update the Named Range. "=Contacts!$A$1:$CN$178" may be the Named Range exported, so make sure to edit the Named Range to reflect added rows. How do you add a new or update your Named Range? If you read this far, it will be a no brainer to figure out.

When you wish to Import back into MS Outlook your contacts, be sure to define or update your Named Range. And good luck with the two or three other issues you will hit - e.g. be sure to put a tick (') in front of all your entered fields in the XLS file!

Named Range. Get it?