CRM Best Practices: Use Excel Like a Pro When You Import Contacts

Excel tips to accelerate contact imports

I feel your pain.

Ever since I’ve been in sales I’ve been a networker.

As such, I kept all of the contact information I could find on clients, prospects, classmates, alumni, etc. In 2006 I ran across an early CRM/Marketing Automation tool that was created by a local software company and they asked me to become an affiliate of theirs in return for a free account.

I agreed but in order to begin using their software I had to export my list of 4,000+ contacts from Outlook and classify each of them as A, B, C or Personal before I could import them and begin sending direct mail and email to them.

Dude!

It took me over two weeks of every spare moment I had to get it done.

I LITERALLY had my file open and was sorting and tagging this list during every conference call, webinar, and boring sales meeting during that entire time.

What a pain in the backside! I’m not gonna sugar coat this. It was rough.

But you know what? It was the last time I ever had to do it.

From that point on I truly understood the concept of segmenting lists and when I made the switch to Infusionsoft in 2008 and then to HubSpot in 2014, both were seamless transitions.

If you haven’t yet imported your Contacts into your CRM, here are some Excel spreadsheet tips to help you bring in a clean, properly-formatted CSV file to accelerate your money-making ventures with your marketing automation platform.

Most Excel spreadsheets I see clients provide me are all jacked up with various capitalizations of names, or lack thereof, which is a nightmare when you are trying to do a mail merge to add the Contact’s name to your email and direct mail messages so you have to clean up the ALL CAPS names, the “no caps names” and the “some Caps names.”

Here’s how.

Insert at least one column to the right of the column you want to clean up. Here I am inserting a blank row in column B.

Type the Excel function “=proper(text)” in the cell to the right of the first cell you want to format properly and in place of “text” select the cell you want to format. In this case I am beginning with “Joe Smith,” which is in cell A2, and select “Enter.” 

Now select the cell with the formula, B2 in my example, and copy it. Then select the entire column that contains the formula—Column B in my example—and Paste.

This will paste the formula into every cell in that column, which comes in handy when you are updating over 28,000 names as I did just 10 days ago!

Select that column again—Column B in my example—and Copy.

Select your original column—Column A in my example—and right click and select “Paste Special” then select “Values.”

You will now have just the properly formatted names with no formulas in Column A. You can now Delete Column B because you no longer need the formula there.

But there is still a problem: you have the first and last name in one column and Infusionsoft needs the first and last name in separate columns. So let’s Insert a column again to the right of the Name Column by selecting that column and right clicking and selecting “Insert.” 

In the top navigation of Excel choose Data > Text to Columns and follow the Wizard.

Relabel the columns as First and Last. 

Save As a Comma Separated Values (.csv) file and you are ready to import.