This tip will explain a method of creating Contact Groups in Outlook from an Excel spreadsheet by importing the data, sorting the new contacts by “Company” and then creating Contact Groups.
This method assumes you have a large number of contacts (30+) in an Excel spreadsheet. If you only need to create a smaller number of Contact Groups, use the quick copy/paste method.
To import contacts into Outlook from a spreadsheet, use a CSV file (Comma Separated Values text file). Excel can open, edit and save CSV files. You could just save a copy of your master spreadsheet as a CSV file. But unless the file has the correctly named columns, you will need to manually map (match) the data to contact info during the import.
To skip the mapping step, download and use our blank CSV template here: Blank Contact Template.csv
For simplicity, our template doesn’t include all the possible column types. If you need more, Microsoft provides a larger example CSV file.
Step one: Prepare the CSV file.
Open both your master spreadsheet and the CSV template file in Excel. Select data from your spreadsheet and paste it into the CSV file. Do this one column at a time. Avoid pasting over the top header line or renaming the headers. Use the example first line as a guide.
Important: Because we want Outlook to sort contacts around program enrolments (groups), paste the group names into column [C] Company. In the example below you can see 11 children are enrolled into ‘Bandicoots’, others into ‘Bilbies’ etc.
If you need a refresh on reorganizing or combining columns refer to this related tip: Tip – Reordering columns and combining columns in Excel.
When you have all the info you want copied, save the CSV file. Excel will ask you to confirm you want to save the files as a non-Excel format.
After saving the CSV file, close it otherwise the import will fail.
Step two: Import CSV data.
Open Outlook and switch from the Mail view to the People view. Create a new Address Book folder by right-clicking on Contacts and select New Folder. Name the folder appropriately such as “2023Families”.
Once created select the new address book folder and select List from the Current View options.
Click File on the ribbon menu to access the Backstage view. From the Open & Export menu select Import/Export.
Once the Import and Export Wizard opens, select “Import from another program or file”. Then click Next.
Select Comma Separated Values. Then click Next.
Browse to and select the CSV file you updated, saved and closed earlier.
Our example includes multiple lines for families with more than one child enrolled so we will select “Allow duplicates to be created”.
Select Next.
Ensure that the destination folder selected is the new one you created. The contacts will be created there.
Since we used the CSV template, there is no need to click the “Map Custom Fields”.
Click Finish. The contacts will be imported.
If you have List view selected, the contacts will be sorted by the Company field.
Step three: Create Contact Groups
The options for searching though large numbers of contacts when creating Contact Groups is very limited, so we will temporarily move each “Company” to a separate folder.
Right-click on the new address Book Folder to add more folders using the New Folder menu option. Create and name an appropriate number of folders so each group has a folder.
Right-click on the first Company group (in our example ‘Bandicoots’) and select Move to assign the contacts to the appropriate folder.
Continue until all the company contacts you imported have been moved to their own subfolders.
Once done, click on the main new address book folder and confirm it is empty.
Select New Contact Group.
Name the group appropriately.
Select Add Members.
From the Address Book drop-down select the appropriate folder for the Contact Group you want to create.
The contacts that you moved to the folder will appear.
Hold down the shift key and select all the contacts so they highlight blue. Then click the Members button. Then select OK.
Finally click Save and Close.
The Contact Group will now appear in the main folder.
Continue until all the groups are created and appropriate contacts added.
Step four: Move contacts back to a single address book folder and clean up
For each address book subfolder, select it so the contacts are displayed in List view. Right-click on the company and use the Move menu to return the contacts to the original new address book folder you created.
In our example we have multiple contacts that are very similar as they are children from same families but in different enrolment groups. Since the contact information is nearly identical Outlook will pop up with the Duplicate Contact Detected alert asking if we want to combine them into a single contact when we move them. We can make the decision to Add the similar duplicates back to the main folder or Update them into merged contact. Since in our example we have children from the same family in different groups we selected Add new contact. This does mean that there will be multiple instances of the family’s contact details to update if they change email address during the year. If you select Update information… only the first instance of the family’s contacts details will remain and subsequent ones deleted. The Contact Groups will still have the email address info you added in Step Three.
Once you have moved the contacts back the empty subfolders can be deleted.
You should now have a single Address Book folder containing all the year’s enrolments as well as their Contact Groups. With this method the groups remain linked to the individual contacts, so if you update an individual contact (ie first name, last name or email address) the Contact Group details should update as well, and vice versa.
If some contacts were merged using Update option in Step Four, the link is lost for those contacts, any change to that family’s info will need to be edited in both the Contact Group and the family’s contact.