<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=1581599555431982&amp;ev=PageView&amp;noscript=1">

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

Posted by Wes Schaeffer | Feb 9, 2020 12:00:00 PM

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.

infusionsoft_database_import_excel_tips_insert.png

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.” 

infusionsoft_database_import_excel_tips_proper_formula_2.png

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.

 infusionsoft_database_import_excel_tips_paste.png

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!

 infusionsoft_database_import_excel_tips_proper_formula.png

Select that column againColumn B in my exampleand Copy.

infusionsoft_database_import_excel_tips_copy.png

Select your original columnColumn A in my exampleand right click and select “Paste Special” then select “Values.”

infusionsoft_database_import_excel_tips_paste_special.png

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.

infusionsoft_database_import_excel_tips_delete.png

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.” 

infusionsoft_database_import_excel_tips_insert_2.png

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

infusionsoft_database_import_excel_tips_text_to_columns.png

 

infusionsoft_database_import_excel_tips_text_to_columns_delimited.png

infusionsoft_database_import_excel_tips_text_to_columns_space.png

 

infusionsoft_database_import_excel_tips_text_to_columns_general.png

Relabel the columns as First and Last. 

infusionsoft_database_import_excel_tips_review.png

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

infusionsoft_database_import_excel_tips_save_csv.png

Get your own CRM butler to take control of your CRM so it makes you money.

Topics: CRM, Keap Infusionsoft CRM

Written by Wes Schaeffer

Wes and his wife just celebrated their 25th wedding anniversary. They have seven kids, which means Wes is motivated to find what works and help you apply it to grow your sales so he can buy diapers, groceries, braces...and bourbon.

Subscribe To The Sales Podcast

 Listen to Wes Schaeffer host The Sales Podcast on iTunes   Listen to The Sales Podcast with Wes Schaeffer, The Sales Whisperer® on Stitcher Listen to Wes Schaeffer host The Sales Podcast on TuneIn.Listen to Wes Schaeffer host The Sales Podcast on CastBox.

  Subscribe to The Sales Podcast with Wes Schaeffer, The Sales Whisperer® on iHeart Radio.   Subscribe to The Sales Podcast with Wes Schaeffer, The Sales Whisperer® on Google Play.  Subscribe to The Sales Podcast with Wes Schaeffer, The Sales Whisperer® on Spotify.

Subscribe to get our latest blogs

Which CRM Is Right For You?

See Your True Sales Numbers

Posts by Tag

See all