Before moving a service business’s contact spreadsheet into HubSpot, separate cleanup from import. A useful approach is to keep the original untouched, prepare a working copy, and produce a small test file only after reviewing its columns and values. This tutorial focuses on controlling choice-based fields with Google Sheets dropdowns, then checking whether the resulting records match your intentions. Treat the test as a decision point: unresolved mismatches should stop the larger import, not become cleanup work for later.
Define the destination before cleaning the rows
HubSpot accepts CSV, XLSX, and XLS files with a single sheet and a header row corresponding to HubSpot properties. Column order does not matter. Check whether each intended destination property exists; create a custom property beforehand if needed. Import capacity depends on your subscription, and some features require additional subscriptions, so check your account’s applicable limits before preparing the final file. [1]
As a practical starting point, import only information your team needs for its immediate contact workflow. Make a separate preparation note pairing each spreadsheet header with its intended destination property. Keep uncertain columns out of the first batch rather than guessing where they belong. Preserve the original spreadsheet so you can resolve questions without relying on edited data.
Build dropdowns from approved CRM options
For HubSpot enumeration properties, imported values must match a defined option’s label or internal value. Default enumeration properties, including Lifecycle stage and Industry, require an internal value or an English label. Use those approved values as your spreadsheet’s vocabulary rather than inventing shorter labels that merely seem equivalent. [1]
In Google Sheets, select the data cells for the relevant column. Click Data, Data validation, then Add rule. Under Criteria, choose Dropdown and enter the approved values, using Add another item for additional options. Alternatively, choose Dropdown from a range and select cells containing your approved list. Click Done. Build the rule from checked destination options, not from the messy values you are trying to clean. [2]
Google Sheets rejects entries outside the dropdown list by default. Advanced options lets you change this to Show a warning, but retaining rejection is the safer suggestion for import preparation. For a single-choice destination field, leave multiple selections disabled. Review existing entries against the approved vocabulary as well; configuring a list is not a substitute for checking the rows already present. [2]
Resolve ambiguity instead of hiding it
Hypothetical example: a cleaning business has a contact sheet where staff used different informal descriptions for lifecycle stages. The administrator reviews each description against the intended HubSpot option, selects an approved value where the meaning is clear, and holds ambiguous rows outside the import file. This is preferable to choosing an option simply because it passes validation.
Other field types need separate attention. HubSpot supports specified date arrangements, and imported values must satisfy any configured validation rules. Files containing foreign-language characters must use UTF-8 encoding. A dropdown check therefore covers only part of preparation: review date fields and character handling separately rather than assuming a tidy-looking sheet is ready. [1]
Use a representative small test batch
Create a separate test file containing a small selection of reviewed records. Include ordinary rows and relevant edge cases, such as accented names or populated date fields. Before importing, write down the destination values you expect to see. Use your account’s import process, then inspect the resulting records against that note, paying attention to field placement, option values, and readable text.
Avoid testing only the easiest rows or treating completion as proof of correctness. If a result differs from your expectation, stop and revise the working copy before proceeding. Keep track of which records were included in the test and decide how to handle them in the remaining import; do not blindly submit them again.
Run a final release checklist
Use this short checklist before releasing the remaining file: preserve the original, confirm destination properties, review approved option values, resolve or withhold ambiguous rows, and compare test results with expectations. Keep preparation notes and helper lists outside the deliverable. Assign someone to approve the cleaned version, and retain that exact file so later questions can be traced to what was submitted.
Sources
Prepared with AI assistance from the linked documentation, checked on 2026-09-22. Examples are suggested workflows, not claims of hands-on product testing.