How to export Outlook contacts to Excel
In this article I will show how you can quickly export contacts from Outlook 365 - 2007 to an Excel spreadsheet. First I will explain how to use the build-in Outlook Import / Export function, and after that we will create a custom contacts view and copy / paste it to an Excel file.
All of us need to export contacts from the Outlook address book to Excel once in a while. There may be various reasons to do this. You may want to update all or some of your contacts, backup the contacts or make a list of your VIP clients so that your partner can take care of them during your vacation.
Today we will dive into 2 possible ways of exporting Outlook contacts to Excel and I'm going to show how you can quickly do this in different Outlook versions:
Tip. To perform an opposite task, this article will be helpful: How to quickly import contacts to Outlook from Excel.
Export Outlook contacts to Excel using the Import and Export function
The Import/Export function is available in all Outlook versions. However Microsoft failed to find little room for it on the ribbon (nor on the toolbar in earlier versions) so that it would be within easy reach. Instead, they seem to have been trying to hide this function deeper and deeper with each new version of Outlook, which is funny, because it's really useful.
Read this article to learn how you can quickly export all needed details of all your Outlook contacts to an Excel worksheet at a time.
Where to find Import/Export function in different Outlook versions
Well, let's see where exactly the Import/Export wizard resides in each Outlook version and after that I will walk you step-by-step through exporting Outlook contacts into an Excel file.
Tip. Before exporting your contacts to Excel, it makes sense to merge duplicate contacts in Outlook
Import/Export function in Outlook 2021 - 2013
On the File tab, choose Open & Export > Import/Export:
Alternatively, you can open the same wizard by going to Options > Advanced > Export, like you do in Outlook 2010.
Export function in Outlook 2010
On the File tab, choose Options > Advanced > Export:
Import and Export function in Outlook 2007 and Outlook 2003
Click File on the main menu and choose Import and Export... It was pretty easy, wasn't it? ;)
How to export Outlook contacts to Excel using the Import/Export wizard
Now that you know where the Import/Export feature is located, let's have a closer look at how to export contacts from your Outlook address book to an Excel spreadsheet. We are going to do this in Outlook 2010, and lucky you are if you have this version installed :)
- Open your Outlook and navigate to the Import/Export function, as shown in the screenshots above. I'll remind you that in Outlook 2010 you can find it on the File tab > Options > Advanced.
- On the first step of the Import and Export wizard, choose "Export to a file" and then click Next.
- Select "Comma Separated Values (Windows)" if you want to export your Outlook contacts to Excel 2007, 2010 or 2013 and click the Next button.
If you want to export the contacts to earlier Excel versions, then select "Microsoft Excel 97-2003". Note that Outlook 2010 is the last version where this choice is available, in Outlook 2013 your only option is "Comma Separated Values (Windows)".
- Select the folder to export from. Since we are exporting our Outlook contacts, we choose Contacts under the Outlook node, as shown in the screenshot below, and click Next to continue.
- Well, you have just chosen the data to export and now you need to specify where you want to save them. Click the Browse button to select a destination folder to save the exported file to.
- In the Browse dialog, type a name for the exported file in the "File name" field and click OK.
- Clicking the OK button will bring you back to the previous window and you click Next to continue.
- In theory, this might be your final step, i.e. if you clicked the Finish button right now. However, this would export absolutely all the fields of your Outlook contacts. Many of those fields contain inessential information like Government ID number or Car Phone, and they may only clutter your Excel file with redundant details. And even if your Outlook contacts do not contain such details, empty columns would still be created in an Excel spreadsheet (92 columns altogether!).
Given the above, it makes sense to export only those fields that you really need. To do this, click the Map Custom Fields button.
- In the "Map Custom fields" dialog window, firsts click the Clear Map button to remove the default map on the right pane and then drag the needed fields from the left pane.
You can also drag the selected fields within the right pane upwards and downwards to rearrange their order. If you have accidentally added an unwanted field, you can remove it by simply dragging it back, i.e. from the right pane to the left.
When you are done, click the OK button. For example, if you want to export a list of your clients, your settings may resemble the screenshot below, where only the business related fields are selected.
- Clicking OK will bring you back to the previous window (from step 7) and you click the Finish button.
That's it! All of your Outlook contacts are exported to a .csv file and now you can open it in Excel for reviewing and editing.
How to export contacts from Outlook to Excel by copying / pasting
Someone may call "copy / paste" a newbie way, not suitable for advanced users and gurus. Of course, there is a grain of truth in it, but not in this particular case :) In fact, exporting contacts by copying / pasting has several advantages compared to the Import and Export wizard that we have just discussed.
Firstly, this is a visual way, i.e. what you see is what you get, so you wouldn't see any unexpected columns or entries in your Excel file after exporting. Secondly, the Import and Export wizard lets you export most, but not all of the fields. Thirdly, mapping the fields and re-arranging their order might also be quite burdensome especially if you are choosing many fields and they do not fit within the visible, above the scroll, area of the window.
All in all, copying and pasting Outlook contacts manually might be a faster and more convenient alternative to the build-in Import/ Export function. This approach works with all Outlook versions and you can use it to export your contacts to any Office application where copy / paste works, not just Excel.
You start by creating a custom view that displays the contacts' fields you want to export.
- In Outlook 2013 and Outlook 2010, switch to Contacts and on the Home tab, in the Current View group, click the Phone icon to display a table view.
In Outlook 2007, you go to View > Current View > Phone List.
In Outlook 2003, it is almost the same: View > Arrange By > Current View > Phone List. - Now we need to choose the fields we want to export. To do this, in Outlook 2010 and 2013, switch to the View tab and click the Add Columns button in the Arrangement group.
In Outlook 2007, go to View > Current View > Customize Current View... and click the Fields button.
In Outlook 2003, the Fields button is under View > Arrange By > Customize… - In the "Show Columns"" dialog, click on the needed field in the left pane to select it and then click the Add button to add it to the right pane that contains the fields to be shown in your custom view.
By default, only the frequently fields are displayed, if you want more fields, open the drop-down list under "Select available columns from" and choose All Contact fields.
If you want to change the order of columns in your custom view, select the field you want to move on the right pane and click either Move Up or Move down button.
When you added all the desired fields and set the order of columns to your liking, click OK to save the changes.
Tip: An alternative way of creating a custom contacts view is to right click anywhere on the row of field names and choose Field Chooser.
After that you simply drag the fields you need to where you want them in the row of fields' names, as shown in the screenshot.
Voila! We have created a custom contacts view, which was actually the main part of the work. What is left for you to do is press a couple of shortcuts to copy the contacts' details and paste them to an Excel document.
- Press CTRL+A to select all the contacts and then CTRL+C to copy them to clipboard.
- Open a new Excel spreadsheet and select cell A1 or any other cell that you want to be the 1st cell of your table. Right click the cell and choose Paste from the context menu, or press CTRL+V to paste the copied contacts.
- Save your Excel sheet and enjoy the results :)
That's how you export Outlook contacts to an Excel worksheet. Nothing difficult, is it? If you have any questions, or know a better way, don't hesitate to drop me a comment. Thanks for reading!
You may also be interested in
Table of contents
