Collate Customer Data In Excel
Once you’ve started building a community, you need to keep contact details and notes for your customers. The best way to do this is with a CRM, but an Excel spreadsheet will allow you to collect data from all the places it’s stored and collate it for upload into the CRM you end up deciding on.
Firstly, download contacts from all the places they’re stored (Outlook, Gmail etc.). If you’re not sure how to do it, my instructions for downloading contacts in a previous Tip of the Month (Uploading Contacts to your CRM) will help.
Create one spreadsheet to use as the “master” and copy & paste data from the exported lists into it. You might need to separate data to split first and last names, or addresses into several fields. If you do, my previous post Separating Data in a List will show you how.
It’s essential that you take the time to get the data in the spreadsheet right. Don’t leave anything to be fixed later. It will be much harder to fix once it’s been uploaded to a CRM. It’s harder to use the spreadsheet as well.
Highlight each source in a distinct colour so you know which is which. This helps you to know which is the most recent entry when you have multiple entries for the same person
Use the conditional formatting tool to highlight duplicate values in the Email address column to check for duplicate entries. Then transfer all the data into one line before deleting the duplicates
Use the A-Z sort in the Last name column to look for duplicate entries with several email addresses. Delete any old or incorrect email addresses, and merge any new ones into one record.
Once you’re sure the data is correct, you can start using the file as your database, or even better import it into a CRM.
