How to Standardise Picklist Values Before a CRM Import

Your CRM import “succeeded” but half the pipeline fields are blank. No error. No warning. Just missing data.

The culprit is almost always a picklist column. Your file has closed won. HubSpot expects Closed Won. Salesforce expects Closed Won on a restricted picklist. Pipedrive expects Won. Each CRM handles the mismatch differently, and none of them quietly let the wrong value through.

Picklist normalisation is the step that fixes this before you upload, not after.

What a picklist field actually requires

A picklist field (also called a dropdown, select, or enumeration field) only accepts values from a predefined list. If the value in your CSV doesn’t match one of those allowed values exactly (same capitalisation, same spacing, same spelling), the CRM handles it differently depending on the platform:

HubSpot flags the mismatch in the import wizard and asks you to remap the value manually before the import completes
Pipedrive silently drops the value and imports the field as blank
Salesforce either leaves the field blank (standard picklists) or rejects the row entirely (restricted picklists)

The match is character-for-character. A trailing space, a lowercase letter, a hyphen instead of a space: any of these will trigger the mismatch. Cleaning this before you upload means no manual remapping step in the wizard and no risk of mismatches slipping through.

Common picklist columns where this breaks down:

Lead Source (web, Web, Website, Organic Web)
Industry (technology, Tech, Technology, Information Technology)
Deal Stage (Closed Won, closed won, Won, CLOSED WON)
Lifecycle Stage (lead, Lead, MQL, Marketing Qualified Lead)
Country (US, USA, United States, United States of America)

A single column with 10,000 rows might contain 30 different spellings of 6 valid values. Normalising it means collapsing all 30 into the correct 6.

The IF statement approach, and why it breaks

The first instinct for most people is to handle this in Excel or Google Sheets with a formula. It looks like this:

=IF(A2="closed won","Closed Won",IF(A2="CLOSED WON","Closed Won",IF(A2="won","Closed Won",IF(A2="closed-won","Closed Won",A2))))

Or the slightly cleaner IFS version:

=IFS(
  LOWER(A2)="closed won","Closed Won",
  LOWER(A2)="won","Closed Won",
  LOWER(A2)="closed-won","Closed Won",
  TRUE,A2
)

This works. For a while.

The problem is that it breaks in predictable ways.

It only catches what you’ve already seen. The formula handles the 4 variants you wrote down during this import. Next month’s export will have Closed - Won or Clos. Won or a French equivalent if your sales team spans regions. Each new variant requires a manual formula edit.

One column, one formula. Ten columns, ten formulas. Deal Stage, Lead Source, Industry, Country, Lifecycle Stage: each needs its own chain. A 20-column file can mean 400+ IF branches spread across your spreadsheet.

It doesn’t travel with the data. You build the formula, paste-special-values to strip it, and then the formula is gone. Next import, you’re rebuilding from scratch. If someone else handles the next import, they’re starting from zero.

Nested IFs hit a wall. Excel has a 64-level nesting limit on IF statements. IFS is slightly more readable but still has to be maintained by hand. Neither scales to a column with 50+ observed variants accumulated over a year of imports.

LOWER() is half a fix. Wrapping with LOWER() catches capitalisation differences but misses spelling variants, abbreviations, and language differences. tech and technology are both lowercase but still not the same value.

The formula approach treats each import as an isolated problem to solve. Treating the mapping as a reusable asset is the better approach.

What each CRM actually expects

Every CRM has its own set of default picklist values, and most allow custom values on top of those. The defaults are a starting point, your org’s actual allowed values may differ.

HubSpot

HubSpot matches imported values against the option label in the property settings. The match is case-sensitive. If the label is Marketing qualified lead and your file has Marketing Qualified Lead, HubSpot’s wizard will flag it and ask you to remap it manually before the import completes.

For lifecycle stage, HubSpot uses internal values (all lowercase, no spaces): subscriber, lead, marketingqualifiedlead, salesqualifiedlead, opportunity, customer, evangelist, other.

For deal stage, the allowed values depend on your pipeline configuration. Always check the actual property settings rather than assuming the defaults.

Salesforce

Salesforce has two types of picklist fields: unrestricted (Salesforce accepts the value even if it’s not in the list, and may create a new option) and restricted (any value not in the list causes the row to error with INVALID_OR_NULL_FOR_RESTRICTED_PICKLIST).

Standard values for Lead Status: New, Working, Nurturing, Unqualified, Qualified — case-sensitive, exact match.

Standard values for Lead Source: Web, Phone Inquiry, Partner Referral, Purchased List, Other.

Custom picklists are common in Salesforce orgs. The only reliable source of truth is Setup → Object Manager → [Object] → Fields & Relationships → [Picklist Field] → Values.

Pipedrive

Pipedrive’s option fields behave like HubSpot: unrecognised values are silently dropped. Stage names are pipeline-specific, so the same stage name can exist in multiple pipelines with different capitalisation or spelling. Always pull the exact option labels from Pipedrive Settings → Data Fields before importing.

Building a canonical mapping instead of rewriting formulas

The sustainable fix is to define the canonical mapping once and reuse it on every import.

A canonical mapping has two parts:

The target values: the exact strings your CRM accepts (pulled from the CRM itself, not guessed)
The source variants: every spelling you’ve seen this value arrive as

For a Lead Source column going into HubSpot, the mapping might look like:

Source variant	Target value
`web`	`Website`
`website`	`Website`
`organic`	`Website`
`organic search`	`Website`
`linkedin`	`LinkedIn`
`LinkedIn Ads`	`LinkedIn`
`paid`	`Paid Ads`
`google ads`	`Paid Ads`

Once this mapping exists, applying it is a substitution, not a logic problem. Every new import from the same source runs through the same mapping. New variants get added when they appear; old ones accumulate.

The most common way teams build this is a lookup table in Google Sheets combined with a VLOOKUP or XLOOKUP on the source column. This is more maintainable than nested IFs because the logic is in the data, not the formula. Adding a row to the table is simpler than adding a branch to a formula.

The remaining gap is coverage: what happens when a variant appears that isn’t in the table yet? With VLOOKUP, the cell returns an error or the original value. You need to catch those before uploading.

Before your next import: the checklist

List every picklist column in your file
Pull the allowed values from your CRM directly, don’t assume the defaults
Audit the unique values in each column (sort and deduplicate to see what you’re working with)
Build or update your canonical mapping for each column
Apply the mapping and check for any values not yet covered
Verify no blank cells were introduced in picklist columns that are required fields

If you’re doing this across multiple CRMs or normalising the same source format repeatedly, Asphorem’s CSV Normalizer handles the mapping and normalisation in one pass, including AI-assisted matching for variants that don’t have a direct string match (typos, language differences, abbreviations you haven’t seen before). The mapping saves, so the next import from the same source skips straight to download.

Picklist values: frequently asked questions

What’s the difference between a restricted and unrestricted picklist in Salesforce?

A restricted picklist rejects any value not on the predefined list (returns INVALID_OR_NULL_FOR_RESTRICTED_PICKLIST). An unrestricted picklist accepts the value and may create a new option for it. Always check the field’s “Restrict picklist to values defined in the value set” setting before importing.

How do you bulk update picklist values in a CSV?

Build a two-column lookup table mapping every source variant to the target value, then apply it via VLOOKUP, a script, or a CSV value converter tool. See how to bulk replace values in a CSV column for the full approach including how to handle variants you haven’t mapped yet.

Can AI standardise picklist values automatically?

Yes, for cases where exact string matching would fail. AI matching handles typos (Frnace → France), language variants (Rouge → Red), and case differences (tech → Technology) without needing every variant in your mapping. Good tools surface AI suggestions for review before applying.

How do I find the allowed picklist values in HubSpot?

Settings → Properties → search for the property → click into it → the option list is shown under “Field options”. The match is against the label (display name), not the internal value. Always copy from here rather than assuming default values. See why your HubSpot CSV import keeps failing for the broader set of HubSpot import gotchas.

What happens if a picklist value doesn’t match in Pipedrive or Salesforce?

Pipedrive silently drops the value and imports the field as blank. Salesforce blanks the field on standard picklists, or rejects the row entirely on restricted picklists. HubSpot flags the mismatch in the import wizard for manual remapping. None of them quietly let the wrong value through.