Top 5 Excel Hacks for a Successful HubSpot Import

So, you’ve been tasked with importing all your organization’s data into HubSpot. In preparation for this monumental task, you’ve read the HubSpot how-to article, you’ve downloaded a sample spreadsheet, you’ve watched the import lesson from HubSpot Academy, and you’re feeling pretty good about this whole import thing.

Then, you open up your file, take a good look at it, and think to yourself, “This thing is a mess!" It doesn’t look anything like the crisp, clean sample file that you downloaded from HubSpot. Instantly, you start to feel like David, and this spreadsheet has become your Goliath.

Never fear! In this post, we've got some tips and tricks that will help you conquer your spreadsheet and carry on.

Hack #1: Split Up the Names

After looking at the HubSpot sample spreadsheet, you realize that the contact's first and last name need to be split into two separate columns, but your file has them together. For this, you can use an Excel function called Text to Columns. Here’s how it works:

• Select the range with full names, but be sure not to include the header.

• Click the Data tab. In the Data Tools group, click Text to Columns.

• Click the Delimited radio button.

• In the Delimiters column, select the Space radio button.

• Click Finish.

• As a final step, be sure to add the correct headers to the columns.

Hack #2: Take Care of Duplicates

Another common scenario is that you have duplicate contacts in your spreadsheet that you want to clean up before import. HubSpot will automatically de-duplicate based on email address of a contact, but what if you only have their phone number?

Find and Remove Duplicates is an Excel function that can help you here. Here’s how you can use it:

• Decide what your unique field will be. If you don't have email addresses, phone number or street address will be your next best bet. These are good ways to de-duplicate because you want to choose something that's unique to each individual contact. In this example, we'll use Phone Number as the unique field.
• Select the cells you want to check for duplicates. 

• Click Home, then Conditional Formatting.

• Click Highlight Cells Rules > Duplicate Values. 

• Pick the formatting you want to apply to the duplicate values, and then click OK.

• At this point, Excel will show you where the duplicates are by highlighting them in color.

Now that the duplicates are easy to see, you can go through the file and decide if you want to update the duplicate values or remove the contacts completely. As you update, the colored cells will go away. 

Hack #3: Replace Commas

A lot of teams will also have data that's stored as items in a list. An example of this might be if you have a list of products that a contact has purchased in the past. Most teams will store this information with commas separating the values. However, if you want to store that information in HubSpot, you'll need to replace the commas with semi-colons and put a semicolon before the first item. Here's a trick that will allow you to easily locate and replace those commas.

• Highlight the column with the data that you want to edit.

• Click Edit > Find > Replace.

• In the Find what field, type in a single comma, ","
• In the Replace with section, type in a single semi-colon, ";"
• Click Replace All. 

• After the text has been replaced, click Close.
• Don't forget to go through and add semi-colons before each list. 

Hack #4: Assign Ownership

A very common challenge during the import process is assigning contact owners during import. The key to doing this successfully on the first try is to make sure you add the users to your HubSpot account first, and then use their email addresses to signify ownership.

Pro tip: If you already have a column for the current owner but have their name there, you’ll first need to sort the contacts by owner and then use the Find and Replace option from "Hack #3" to replace the names with email addresses. If you don't already have an owner column, here's how you can add one:  

• Add a column with a header of HubSpot Owner.

• Fill in the cells with the email address of the user that you want to assign as the owner.  

Hack #5: Correct the Grammar

Finally, for the real heroes of data clean up: If you want to make sure that the names of all your contacts have proper capitalization, Excel has a tool for you. Using the Proper function, you can make sure that all your contacts come in with correct grammar. Here’s how it works:

• If the column you're looking to format doesn't have a blank column next to it, add a blank column. 

• In the top cell, under the header row, type in =Proper.

• Highlight the columns that you want to edit with this function.

• You'll notice that only the first value appears to populate. 

• However, if you click on the cell and expand the selection down, the rest of the values will populate. 

• Give your newly formatted column a header, and don't forget to delete the original column.  

Now that you know the top 5 Excel tricks for a successful HubSpot import, you can conquer your own spreadsheet. Keep calm and import on! 

Want to hear more from Juli? She’s hosting an Ask Me Anything (AMA) on the HubSpot Community from January 14, 2019 – January 28, 2019. Join the conversation here.