In this article I will show how you can quickly export contacts from Outlook 2016, 2013, 2010 and 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:
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.
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.
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.
On the File tab, choose Options > Advanced > Export:
Click File on the main menu and choose Import and Export... It was pretty easy, wasn't it? ;)
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 :)
Given the above, it makes sense to export only those fields that you really need. To do this, click the Map Custom Fields button.
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.
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.
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.
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.
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.
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!
98 responses to "How to export Outlook contacts to Excel 2016, 2013 or 2010"
question- I have my contacts in different categories such as business, personal etc. What if I want to export only business contacts to Excel?
Thanks
Hi Jeff,
Thank you for your question. For me to be able to give you the most accurate and correct answer, please let me know your Outlook version and how exactly you grouped the contacts by categories. Did you simply assign a category via the "Categorize" context menu, or did you create an Additional Address book for each category, or several distribution lists?
If you used the Categorize option, then you can use the second approach described in the article - Export contacts from Outlook by copying / pasting.
Before you perform step 4, sort your contacts list by the "Categories" column, simply by clicking it. Usually this column comes last in the list, in the right hand part of the screen. Select only contacts of the business category and then copy/paste.
Alternatively, you can export all contacts to Excel, and then sort by the "Categories" column. This seems to be an easier way.
re Outlook 10 For me the File menu to options to advanced to export does not work because it does not allow me to choose the category in contacts that I want to export to excel.
I used to do this all the time in the past but now for some reason I can not figure out how to make this happen.
There used to be an export button on the ribbon and now it is not available.
I have used outlook for years and years and all of a sudden it is more difficult.
What am I doing wrong??
Hi Kari,
Regrettably, it is not possible to export contacts in the selected categories when using Outlook's Import/Export function. As an alternative, you can choose "Category" in the “Show Columns“” dialog (it is step 3 of the first solution explained in this article), then export all
contacts to Excel, and then filter or sort by the needed category.
Another solution is to export contacts manually by copy/pasting and copy only the contacts in the needed category in the custom Outlook view.
What about the information under "All Fields",how to export that to excel?
Hi Kayla,
Use the 2nd way described in the article (by copying / pasting).
In step 3, select all the contact fields in the right pane and click Remove, to avoid duplicates. Select 'All fields' under "Select available columns from" on the left, select all fields under "Available columns", and then click the Add button.
Here's what happens when I try the cut and paste method with OL2013 and Excel2013, the header line is below, with line break meaning column break, and all contacts underneath are similarly scrambled across columns (should be seven columns, now five):
First
"Name Last"
"Name Company Street"
"Address City State ZIP/Postal"
Code
Hello Matt,
This is a very strange issue, I have not heard about anything like this before. Could you send me a screenshot of what you see in Excel at alexander.frolov@ablebits.com?
Hi Alexander,
I have the same problem as Matt. Is there an easy fix ?
Thanks
Hello David,
Sorry, I do not have a fix at the moment. I will do some research later this week and then report you the results. Please let me know what your default browser is and if you have all the latest updates installed.
Hey Alexander,
I created a new Excel add-in which makes all those saving and locating the file etc. superfluous.
Simply load your table with your existing contacts in Excel and click on "ContactXPorter" - the rest is super easy.
More details here:
http://www.h-d-c.de/php/services_software_contactxporter_en.php
Would love to hear your and ll other user's comments on my add-in!
Hope it helps a bit to make exporting contacts from Excel to Outlook much easier... ;-)
Best,
Eric
I exported the pst file. I have changed my mind and do not want do do this as a back up. Every time I open outlook (20130) I get pop up windows saying that outlook can not locate the pst file. I have to cancel the windows so outlook will go ahead and open. How can I tell outlook to stop trying to export anything?
Thanks,
Byron
Will the contacts still be available in outlook if I use the import/export method you suggest? Which method is best for copying Distribution Lists, not individual contacts?
Hello Denise,
Of course, the contacts will still be available in Outlook. As for copying the distribution lists, both methods will work, so you can choose the one you like better.
Hi, I have followed these direction but my fields will not paste in a single row, they insist on stacking like this.
Full Name Mobile Phone E-mail Categories Home Address Home Address City
Marisela and Alfred Nuno Market Clients 2 Hurles St
Simi Valley, CA 93065 Simi Valley CA 93065
Dawn and Derek McLaughlin 80043886 mailittodawn@ Market Clients 36 Waco Ave
Simi Valley, CA 93063 Simi Valley CA 93063
Suggestions?
Hello Bonster,
How exactly did you copy the contacts – via a csv file or clipboard? What is your Windows version, Office version and localization?
It's probably old news by now, but the solution is to choose the "Home Address Street" column, not the "Home Address" column. The latter column is the standard Outlook column, and it includes that carriage return in the formatting behind the scenes.
There is no "Mailing Address Street" column, unfortunately.
Best ever instruction.
Thanks
I have exported my DL and am trying to reimport it. When I get to "select destination folder", there are no folders for me to pick and the "next" button is greyed out. My only option is to go back or cancel. How do I get a destination folder listed there? Thx.
Sorry, using a CSV file and Outlook 2010. Thx again.
Thanks for this article it was very useful, exporting as a CSV file has helped. However n the email extraction column it displays the email as
abc@abcinvest.com SMTP ( abc@abcinvest.com)
All in the one line, I don't want to have this SMTP and the email again in bracketts, any suggestions on how to extract without this showing. I just want the email abc@abcinvest.com
Please please help !!!
Hello Simon,
Please, open your source workbook and remove hyperlinks from the email column.
You can find how to remove all hyperlinks at once in the article below:
https://www.ablebits.com/office-addins-blog/2013/09/20/remove-hyperlinks-from-excel/
And then export to the CSV file again, all should be Ok
I'm having the same problem as Simon. I followed the steps to remove hyperlinks in Excel but how do you remove them from the source workbook?
I'm exporting them from my outlook contacts. I'm using Microsoft Office 2013. This is how the information looks when I export it:
Name Field:
lastname%,Firstname%
Email Field:
email@abc.com%FirstnameLastname(email@abc.com)
I've gone through both ways, export and copy and paste. Neither wokrs for me. When I export to the csv file all I get is a header and no contact info. When I change the view to phone and add the columns they are blank... help!
Hello Max,
It is difficult to say what can cause the problem. It may happen if you have several folders with contacts. In this case, please make sure you choose the right one. If it is not the case, you can probably try to find a solution on stackoverflow.com or answers.microsoft.com.
I use Excel 2013 and Outlook 365. When I export, everything ends up in column A. What am I doing wrong? Also,is it possible to choose a group to export rather than searching for each group member?
Hello Linda,
Most likely, the delimiter in your CSV file is different from the symbol that Excel uses to separate fields when importing the data (regional settings). Please look at this blog post how to cope with this problem:
https://www.ablebits.com/office-addins-blog/2014/05/01/convert-csv-excel/#csv-not-parsed
As for your second question, I am sorry I can't suggest you anything.
I have used the export wizard to excel which has worked fine apart from I have % signs appeared in many fields. I am using outlook 13. Any advise.
Thanks
how we take backup of all suggestions mail id list.
if we type 'a' letter in To or CC automatically visable mail id list start with letter 'a', so i need how we take backup of all address.
i want export company andress please sir
What are those % signs? Why do my email addresses look like this on my Excel spreadsheet: XXXXX@XXXXX.net %SMTP %XXXXX@XXXXX.net
And when I upload this email only list to constant contact, I get an "errors" message in constant contact. What now? Thanks for your advice.
Using Outlook 2013, Windows 8.
Hello Amy,
It looks strange. Please send your sample workbook to support@ablebits.com and we'll try to help.
Thanks, it works..!!
Great Job.
Is there a way to "Export" multiple Distribution List and send them to multiple people so they can import into their Office 2007\2013 Outlook Contact List?
Hi Alexander,
I have Outlook & Excel 2007. I used your 2nd method (copy and paste) and it worked fine, except for one thing.
The NAME of my Distribution Lists transferred over (just as all the Individual Contacts did), but the MEMBERS within the Distribution Lists didn't (and I didn't really expect that they would).
I can get around that by opening each Distribution List up and copying/pasting into Word (or wherever), but is there a way to use either your Method 1 or 2 above to accomplish that?
Thanks for your time and help - I appreciate it,
Mike
This was very helpful. Clear, easy-to-follow directions. Thanks
I too had an issue with extra % (percentage symbols) appearing in my CSV export. I found that, when exporting, if I went into "Map Custom Fields..." and then clicked "Clear Map" and "Default Map", the export worked again as expected. Something about the Custom export decides to add the %.
I'm using Windows 8 with Outlook 2013.
Hello Bob,
Thank you for the information. We have never encountered this problem. I'll try to investigate it.
Hi.
I would like to export my contacts (Some thousands) from Outlook to CSV, but, the option Export is not availlable (Gray) so ... I have a .ost file of 111Mb but I cannot exctract anything. (I wanted to export it on my gmail)
If ou can help me or explain why) on that i'll be happy. (I'm using office 365)
I'm using Outlook 2013 and Word 2013. If I export a contact list from Outlook or use the mail merge wizard in Word I get the same file. But it is NOT the contact list I see in Outlook. Some items that I deleted and no longer see in Outlook are in both the exported and mail merge file. I tried adding a new contact in Outlook. It does not show in Outlook but it is in the other 2 lists. I can manually edit the list but why aren't the results the same as what I see in Outlook? And why aren't new items showing up?
Sorry I didn't also mention if I export a CSV file to Excel I see the same file as I see in Word. Neither are the file I see in Outlook.
Hello,
Thanks for the exporting information. I originally imported my email contacts from Hotmail, but am now using Outlook 2013. When exported from Outlook, the names came through with "%" between all the names and email addresses.
Here's a sample name:
Mary, %Prendeville %
And a sample email:
joanjnsn@gmail.com %SMTP %Joan Johnson (joanjnsn@gmail.com)
Any suggestions?
Hello Maria,
We have never encountered this problem on our side. However you are the second person who mentions % in the separator.
If it is not difficult for you, please contact us at support@ablebits.com and describe in detail how you exported. We will look into the issue.
Hi Alexander,
Did you find a solution to this problem? Happens to me as well.
Thanks.
Robin
Hi Sir Alexander,
My problem is the following, i want to send mail to all my contacts.But my 'contacts'
is when i reply theme, i always use the first letter of the name of the recipient, then it shows the contact. i enter and email is there to send.
My question is how can i import these contacts without typing all of them please.
Thanks.
Vincent.
Correction : My question is how can i export these contacts without typing all of them please ?
Thanks.
Hi everyone,
First of all, great explananation!Nevertheless I have an issue when copy/pasting mij contacts to excel. When I changed my current view by selecting all the columns I want, there is a problem with the column "adress for adress list". When I use this column and copy all contacts to Excel, this information is placed in a different row (unerneath column A), this means that I have street name in one row, postal code and city in another. I added an screenshot (see link below). This result is very inconvenient while using it for lists etc. I really want to use the column "address for adress list" because I want one column with the adresses which are mostly used for sending stuff by mail. Some people have 3 addresses, one for work, one visiting address and one private address.
[IMG]http://i60.tinypic.com/28ivyug.png[/IMG]
The issue is that in the contactperson itself, the address has a automatic enter, while copying it to Excel it seems that this enter is copied. See image
How can I solve this?
(p.s. I am Dutch, so maybe I didn't use the common words for explaining my problem).
I have the same problem... Well bit different. I need to export huge amount of contact (Outlook 2013) for my boss to Excel 2013 spreadsheet...
I went for with "Copy/Paste" method.
I added extra columns to divide the address as "Street", "City", "Country/Region" and "Postal Code".
As quite lots of the addresses have SECOND LINE in the address section:
i.e. 123 E. Rose St
Suite 456
Phoenix, AZ 85789
The SECOND Line and the rest of the addresses ended up in Column A, which is the next row... Also there were some with another line like the name of the building, which created another row... So, one contact info was in THREE ROWS.
I have too many to fix each one of them!
How can I get ALL the Street address, Suite No. Building name/No. in ONE CELL???
Please HELP!
Your instruction was perfect... my problem is that the CONTACTS folder in the Outlook Data File does not contain my contacts. Through a series of computer issues, my contacts have moved to other "Contacts files in other *.PST files. Is there any way I can point the export to one of these files?
I followed your great instructions using the help of the wizard. However a significant amount of contacts were not exported. Did it twice, don't understand what I am doing wrong. I have outlook 2010.
I would have liked to see a finished sample of your excel spreadsheet after the export.
I use Outlook 2013 and I agree that MS is trying to make things more difficult in every new release. They started on this unholy path with that terrible ribbon where I as a so called welltrained and heavy user after all these years regularly stumbles on functions I cannot find. It frustrates and slows down my use of Excel but I know of no alternative.
Now this issue. Because all phone producers use different syntaxises with which they store telephone numbers I have to edit my telephone numbers before exporting them to my Siemens and to edit them another way for my Samsung. I call this consumer harassment. I used to export my Contacts to csv. Edited it in Excel and imported it again in Outlook. A fast and easy procedure.
But why keep a winning horse if you can make life miserable. Oulook 2013 generates a csv file with of all digits a simple KOMMA!. Do these people ever think or test there makings. Everywhere in a file there are komma's which now will be seen as field seperators and when you import this file again then you would get very funny results. I say "would" because now comes the worst part: You won't get anything at all. The file contains just one column where all the contents of a row are gathered in one field seperated by this notorious komma. Good importsoftware reconizes this and that system will work also. Of course a clever software maker would let you choose your own seperator (best is a exotic digit that is not otherwise present in your file. I usely choose a #). Anyhow when importing Outlook does not knwow what to do with the file and you cannot import it.
Hi,
Some of the people I e-mail with are not saved in my outlook contacts. Is there an easy way of exporting these e-mail addresses apart from going thorough each one manually and saving them as contacts?
Thank you,
Andreea
Hi Sir/MAdam ,
Good Day!
I would like to request support in my ms outlook 2013 . My problem is i want to import contacts "First Name","Lastname","Email Address","Full Name","Job Title" and Department i've tried to imported the CSV but only first name , last name , and email address appearing in the contacts the others column is missing?
regards,
Question 1: I have used both methods to export contacts - the EXPORT, and copying and pasting from an open VIEW.
However in both cases, the address field ends up crossing over more than one Excel ROW, even when I use .csv
Is it a problem in how my address field is entered?
Which raises Question 2: on the default OUTLOOK entry form Address is just a 'free-form' block. But in reality there are individual Address fields behind it and it seems to decide ARBITRARILY which words/numbers go into which field. Can I change the TEXT BLOCK in that default form to show indiv addr fields? Thank you
Thank you for your help. Everything worked great. You saved me heaps of time :)
THANK YOU SO MUCH! Super helpful!
Dear Sir/Madam,
Managed to export contact list to excel with your fantastic screenshots and simple explanations.
By the way, I would like to have a "search feature" where eg. If I type "Jenny" and only her detail contact will appear, instead of having to run thru the whole list. How do I do it? Kindly advise.
Also, I would like to share my excel contact list with another person having another computer. Is there a way to do it?
Thank you very much!
Hello,
can I use hyperlinking in 2010 excel to my 2010 outlook contacts ? I need to get, if I click the link, it needs to open the contact details in outlook.
I tried the following but it does not working.
example..... outlook:contacts/~John
Hi.
Great tutorial. My question would be following:
Is it possible to export all Contact Groups at once or it needs to be done one by one.
Very useful, thanks a lot. however, when I imported my contact to outlook the plus sign was removed and all he contact starts with number directly. (originally each contact was like +1 23233535 now it shows the number as 1 23233353) which make the dialing not possible. Any help .
My Boss is leaving the company and holds a lot of contact information that I will need. Is there a way to export someone elses contacts from my Outlook, if we share ontacts right now?
Or even transfer their contacts into mine?
Hi All,
Can you please tell me how can I export some outlook properties like- email, department, title, company etc. of all contacts in an email group at once. I have not all the group members added into my contact list.
Hi, I am using the "Export to .pst" function on an entire .ost mailbox(incl. subfolders) in order to backup the mail.
Question:
Does this create a backup, or will this move all the mail from the server to the pst file?
I am only trying to create a backup, not move the mailbox offline!
Thanks
:-)
I'm with Sadi on this one, same problem as in comment 42. Any wise internet wizard got an answer for us? :)
Thank you so much! You saved me at work today!
Thank you so much for this! I need to add all my contcats to MailChimp and didnt know how and this is just amazing! :) x
I tried doing this and not only does this not save as a .xlsx or .xls file but it doesn´t even open to show a correct visual of the contats. it´s all in code! I am trying to save into an excel not the .CSV file as is the only option...Constant contact does not recognize this file type. I don´t know what to do and I really don´t want to copy and paste every contact i have. please help!!!!
Hello, I am having the same problem with %smpt% being added to my exports. I am using Office 2011. did you ever get a fix for this problem?
thanks,
LaDonna
There is a little + beside "Email", then only pick email and drag it to the right box.
Hello:
I am using Outlook 2013. I am changing computers. I would like to be able to be able to autofill in the people that I have previously replied in an email. How do I make that happen after moving the PST file to the new computer ?
Is there a certain step that I need to go through using an export process on the old computer ??
Thank you
Frank
Can we schedule the export activity , on a daily basis?
With all the advances that have been made in hardware and software WHY IS IT SO HARD to copy and paste the info from my Outlook address book to Excell? Why can't we just highlight, copy and paste? Is there no one out there in cyber space who is smart enough to write such a simple programme? Just look at all the previous comments, someone has got to come up with a simpler and/or better way of doing this.
Nice one. Solved a big problem. Thankyou
Thanks for this tutorial
Your "tutorial" was useless. I don't have the same tabs in my Outlook 2013. Thanks for nothing.
Tutorial was simple and easy to follow. I have Outlook 2010 and used Export Wizard instructions. My map is simple. Just name, email, work phone, and mobile. Many contacts didn't come over at all with only one element.
Many were missing email even though that info is in my Outlook contact in proper position/field. I've tried process twice now with same result. What do I need to do differently?
At bare minimum, I need name and email.
Thanks!
I am in Ireland. All national area codes begin with zero [0],and all international are entered with either OR zero zero [00]. When the exported CSV file is opened in Excel all these numbers get corrupted. Can this be prevented, I have over 1,500 contacts and need to export. In other words how do I preserve the leading zeros AND +'s
I have several folders to separate all of my outlook contacts. How can I export all of them into a csv? As is currently stands right now, it will only export the column titles of the mapped fields.
Savvy article . Speaking of which , if your company is searching for a NJ DoT MFA-1 , my company used a template form here
https://goo.gl/EvXKwW
.Hello,
I follow the instructions to export email addresses from Outlook 2013 contact group. However, the CVS file is empty.
Thank you.
Why does microsoft have to make each new version of it's s/w & OS harder to find things in?
When I export my contacts to a csv file I cannot export all the information in the NOTE column due to the limitations on the amount of characters allowed in a cell in excel
Hi There,
Did you get an answer to your question above?
Cheers
Sadiq
Hi and thank you for this tutorial.
I exported the whole of my COntacts into Excel and it worked perfectly. I then repeated the process but this time, I specified which fields I wanted to export using the Field Chooser. Again, it did work, however, in the Excel file, it shows "&" signs everywhere, where I have used spaces but in the first one I created, it didn't. What's happened?
Thank you.
Louise
Hi there. I;m using Windows 2007 and have followed your instructions for exporting Outlook contacts to Excel. However, when I get to the screen where I'm supposed to be able to choose the folder to export from, there is no folder list. How do I get the folder list to show up there?
This information has been EXTREMELY helpful!! Thank you!
Hi,
What if i have already contact list in excel, but i need job positions for these contacts and other info from global contact book..? is there a way (vba incl.) to do that..?
Cheers!
kamil
Hi There,
When exporting data, will the data then be deleted form the outlook?
Cheers
Sadiq
Hi There,
When exporting data, will the data then be deleted from outlook?
Cheers
Sadiq
Hi Sadiq
When exporting data, the data WILL NOT then be deleted from Outlook. It is merely copying it over.
Hope this helps.
Outlook 2013 & Windows 10 - After cleaning up and deleting some of my Address Book entries I have repeatedly exported to a .csv file however all the deleted address book entries come as well. Is there a step I am missing where I need to delete the deleted items from a cash somewhere.
is it possible to contacts saved automatically in outlook
Pls me on this and i am using window 8
is it possible to contacts saved automatically in outlook??
Pls help me on this and i am using window 8
Thanks for the help
Dear
Trying to export the contacts from Outlook to Excel (via file, import/export and I've followed all steps for 2013. However the results is I get everything in cell/line (even it was indicated "comma separated values" and the file looks completely messed up. Any help for this?
Thanks!