--- title: Top 5 Excel Hacks for a Successful HubSpot Import description: Get ready for successful HubSpot imports with these 5 Excel hacks. image: https://www.hubspot.com/hubfs/assets/hubspot.com/buzz/HubSpotOpenGraph.png --- - - [ Marketing ](https://www.nlopchantamang.com/marketing) - Top 5 Excel Hacks for a Successful HubSpot Import # Top 5 Excel Hacks for a Successful HubSpot Import [Free Marketing Plan Template ](https://offers.hubspot.com/thank-you/marketing-plan-template?hubs_post-cta=author) [Juli Gaudette](https://www.nlopchantamang.com/marketing/author/juli-gaudette) Published: October 05, 2021 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](https://knowledge.hubspot.com/articles/kcs_article/contacts/import-contacts-companies-deals-tickets-products?_ga=2.225603487.468947212.1540240181-1549946529.1539194746), you’ve downloaded a [sample spreadsheet](https://knowledge.hubspot.com/articles/kcs_article/contacts/set-up-your-import-file?_ga=2.228101016.468947212.1540240181-1549946529.1539194746), you’ve watched the [import lesson from HubSpot Academy](https://academy.hubspot.com/learning-track/import-contacts-crm), 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. ### **![Names selected](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/Names%20selected-1.png?width=266&name=Names%20selected-1.png)**   - Click the **Data** tab. In the *Data Tools* group, click **Text to Columns**. ### **![Text to columns](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/Text%20to%20columns-Oct-05-2021-05-31-07-49-PM.png?width=589&name=Text%20to%20columns-Oct-05-2021-05-31-07-49-PM.png)**   - Click the **Delimited **radio button. ### **![Delimited](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/Delimited-4.png?width=615&name=Delimited-4.png)**   - In the *Delimiters* column, select the **Space **radio button. ### **![SPACE](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/SPACE-2.png?width=608&name=SPACE-2.png)**   - Click **Finish**. **![finish](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/finish.png?width=608&name=finish.png)** - As a final step, be sure to add the correct headers to the columns. ![headers-1](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/headers-1-1.png?width=444&name=headers-1-1.png)    ## **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](https://knowledge.hubspot.com/articles/kcs_article/contacts/how-does-hubspot-deduplicate-contacts?_ga=2.200590379.468947212.1540240181-1549946529.1539194746) 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.  **![new with dupes](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/new%20with%20dupes-4.png?width=604&name=new%20with%20dupes-4.png) ** - Click **Home**, then **Conditional Formatting**. **![conditional formatting-1](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/conditional%20formatting-1-1.png?width=941&name=conditional%20formatting-1-1.png)**   - Click **Highlight Cells Rules **> **Duplicate Values**.  **![cell rules](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/cell%20rules-1.png?width=472&name=cell%20rules-1.png)** - Pick the formatting you want to apply to the duplicate values, and then click **OK**. **![formatting rule](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/formatting%20rule-2.png?width=513&name=formatting%20rule-2.png)** - At this point, Excel will show you where the duplicates are by highlighting them in color. **![dupes](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/dupes.png?width=549&name=dupes.png)** 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.  ****![fixed!](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/fixed!-1.png?width=546&name=fixed!-1.png)****   ## **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,](https://knowledge.hubspot.com/articles/kcs_article/contacts/what-are-the-different-property-field-types-available?_ga=2.125077263.468947212.1540240181-1549946529.1539194746) you'll need to replace the commas with semi-colons and [put a semicolon before the first item](https://knowledge.hubspot.com/articles/kcs_article/contacts/how-do-i-add-values-to-a-checkbox-field-instead-of-overwriting-them-in-a-file-import?_ga=2.233595675.468947212.1540240181-1549946529.1539194746). 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.  ![Screen Shot 2018-10-18 at 9.57.49 PM](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/Screen%20Shot%202018-10-18%20at%209_57_49%20PM.png?width=596&name=Screen%20Shot%202018-10-18%20at%209_57_49%20PM.png) - Click **Edit** > **Find** > **Replace.** ![find > replace](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/find%20%3E%20replace-3.png?width=538&name=find%20%3E%20replace-3.png) - In the *Find what* field, type in a single comma, "," - In the *Replace with *section, type in a single semi-colon, ";" - Click **Replace All**.  ![Screen Shot 2018-10-26 at 7.11.26 PM](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/Screen%20Shot%202018-10-26%20at%207_11_26%20PM-2.png?width=461&name=Screen%20Shot%202018-10-26%20at%207_11_26%20PM-2.png) - 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](https://knowledge.hubspot.com/articles/kcs_article/contacts/how-to-set-an-owner?_ga=2.137145525.468947212.1540240181-1549946529.1539194746) 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*. ![column](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/column-2.png?width=721&name=column-2.png) - Fill in the cells with the email address of the user that you want to assign as the owner.   ![owner](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/owner-Oct-05-2021-05-31-00-83-PM.png?width=851&name=owner-Oct-05-2021-05-31-00-83-PM.png)   ## **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.  ![add column](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/add%20column-Oct-05-2021-05-30-39-07-PM.png?width=895&name=add%20column-Oct-05-2021-05-30-39-07-PM.png) - In the top cell, under the header row, type in **=Proper**. ![proper formula](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/proper%20formula-2.png?width=228&name=proper%20formula-2.png) - Highlight the columns that you want to edit with this function. ![proper select](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/proper%20select-1.png?width=496&name=proper%20select-1.png) - You'll notice that only the first value appears to populate.  ![just top one](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/just%20top%20one-Oct-05-2021-05-31-08-58-PM.png?width=237&name=just%20top%20one-Oct-05-2021-05-31-08-58-PM.png) - However, if you click on the cell and expand the selection down, the rest of the values will populate.  ![drag down-1](https://www.nlopchantamang.com/hs-fs/hubfs/Imported_Blog_Media/drag%20down-1-1.png?width=247&name=drag%20down-1-1.png)   - 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](https://community.hubspot.com/t5/Tips-Tricks-Best-Practices/Ask-Me-Anything-Growing-Better-In-2019-Using-HubSpot-Free-and/td-p/245218?hubs_post=blog.hubspot.com/customers).* [![New Call-to-action](https://www.nlopchantamang.com/hubfs/Imported_Blog_Media/445c4457-af7e-487a-9640-12e2455b72a6-Oct-05-2021-05-30-37-55-PM.png)](https://blog.hubspot.com/cs/c/?cta_guid=9671a5d4-7fba-4881-b535-c30e583e74e1&signature=AAH58kFo4rzbmeQmPNvyvcFqwfMM2yCM4A&pageId=6356104892&placement_guid=ade677c4-101e-4443-865e-940c64d85d52&canon=https%3A%2F%2Fblog.hubspot.com%2Fcustomers%2F5-excel-hacks-hubspot-import&portal_id=53&__hstc=20629287.ea8d4ab54f76966e9cf68dd2adaab70c.1633391080852.1633391080852.1633391080852.1&__hssc=20629287.1.1633391080852&__hsfp=1561754925&hsutk=ea8d4ab54f76966e9cf68dd2adaab70c&contentType=blog-post) Originally published Oct 25, 2018 3:51:07 PM, updated January 13 2019 ### Don't forget to share this post! [mailto:?body=https://www.nlopchantamang.com/marketing/customers/5-excel-hacks-hubspot-import&subject=Top%205%20Excel%20Hacks%20for%20a%20Successful%20HubSpot%20Import](mailto:?body=https://www.nlopchantamang.com/marketing/customers/5-excel-hacks-hubspot-import&subject=Top%205%20Excel%20Hacks%20for%20a%20Successful%20HubSpot%20Import) FREE MARKETING PLAN TEMPLATE FREE MARKETING PLAN TEMPLATE Outline your company's marketing strategy in one simple, coherent plan. DOWNLOAD THE FREE TEMPLATE DOWNLOAD THE FREE TEMPLATE ```json { "@context" : "http://schema.org", "@type" : "ItemList", "itemListElement" : [ ] } ``` ```json { "@context" : "https://schema.org", "@id" : "https://www.nlopchantamang.com/marketing/customers/5-excel-hacks-hubspot-import#article", "@type" : "NewsArticle", "author" : { "@type" : "Person", "name" : "Juli Gaudette", "url" : "https://www.nlopchantamang.com/marketing/author/juli-gaudette" }, "dateModified" : "2021-10-05T17:32:09+0000", "datePublished" : "2018-10-25T04:00:00+0000", "headline" : "Top 5 Excel Hacks for a Successful HubSpot Import", "image" : [ "" ], "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "height" : 112, "url" : "https://fs.hubspotusercontent00.net/hubfs/53/assets/hubspot.com/web-team/WBZ/Blog%202021/Images/Logos/HubSpot%20Logo%20112X112.svg", "width" : 112 }, "name" : "HubSpot", "sameAs" : [ "https://www.facebook.com/hubspot", "https://twitter.com/hubspot" ], "url" : "https://www.hubspot.com" }, "speakable" : { "@type" : "SpeakableSpecification", "xPath" : [ "//title", "/html/head/meta[@name='description']/@content" ] } } ```