How-tos
Step-by-step fixes for failed CRM imports, duplicates, messy headers and other spreadsheet problems.
CRM imports & cleanup
16 how-tosGet a list into your CRM without errors or duplicates, and clean up the records already sitting in it.
CRM migration: a step-by-step checklist
A CRM migration checklist: what moves cleanly and what needs work, four ways to migrate, cleaning and mapping the export, load order, testing and cutover.
24 min readHow to format a CSV for HubSpot import
Get your contact list into a CSV HubSpot imports cleanly the first time: the right columns, dates and phone numbers HubSpot accepts, and no duplicate contacts.
3 min readHow to fix Salesforce import errors
What each import error means, why duplicates get flagged when nothing is duplicated, and the imports that succeed but still break something downstream.
9 min readClean data before or after a CRM import
Clean data before you import it, so duplicates and bad formats don't reach the CRM. Records already there can be exported, cleaned and pushed back.
19 min readCombining Apollo, ZoomInfo, Cognism and Lusha exports
Export each list on its own, combine them by column name, and dedupe on email, then name and company. Pick a winner for each field, verify, and import once.
7 min readTrade show lead management: get show leads into your CRM
What to do with trade show leads after the show: what the lead export holds, importing it into HubSpot, Salesforce or Pipedrive, and who follows up.
16 min readCRM data quality: how to measure and improve it
Data quality is how accurate, complete, consistent and current your CRM records are. How to measure it with five numbers, and how to improve it.
8 min readCRM data cleanup and hygiene: a checklist and best practices
How to clean up CRM data in eight steps, from the export you keep as your undo to the push back, plus a checklist, an owner and how often to check.
13 min readWhy your HubSpot import is failing
What each import error actually means and how to fix it, plus the imports that do not fail at all and quietly overwrite what you already had.
17 min readHow to find, merge and prevent duplicate contacts in HubSpot
Why HubSpot creates duplicate contacts, how to find and merge them, and how to clean a list before import so new ones don't get in.
6 min readSalesforce duplicate rules: what they do to imports and APIs
How Salesforce duplicate and matching rules work, alert vs block vs report, how to set one up, and why they refuse imported and API rows even under Allow.
17 min readLead qualification: steps, BANT and a checklist
What lead qualification is, MQL vs SQL, the steps, BANT, questions to ask, where it lives in HubSpot, Salesforce and Pipedrive, and a checklist.
12 min readLead scoring: how to build a lead scoring model
What lead scoring is, fit vs engagement vs predictive, how to build a model in seven steps, criteria examples, and what HubSpot, Salesforce and Pipedrive score.
14 min readRound robin lead assignment in Salesforce, HubSpot and Pipedrive
How round robin lead assignment works, when it fails, and how to set it up in Salesforce, HubSpot and Pipedrive, and why Salesforce assignment rules don't fire.
13 min readHow to split a large file for a CRM import
When a file is over your CRM's import limit: check its real size, pick a safe size for each part, split it in Excel or Operelio, then import the parts in order.
7 min readHow to measure speed to lead in HubSpot and Salesforce
Measure speed to lead from when a lead arrives, not only from when it gets an owner, with HubSpot reports, a Time between property and a Salesforce formula.
11 min read
Spreadsheet cleanup
16 how-tosRemove duplicates, fix messy headers, merge and compare files, and clean a list without Excel formulas.
How to remove duplicates in Excel
Remove duplicates in Excel with Remove Duplicates, the UNIQUE formula, conditional formatting or Power Query, and choose which row to keep.
8 min readFuzzy matching: how it finds duplicate contacts
Fuzzy matching treats two values as the same when they differ only in ways that don't change the meaning. How it works, with a contact list example.
5 min readHow to clean up messy column headers
Fix inconsistent, duplicated, or blank column headers so your file imports cleanly and every tool can find the right column.
4 min readHow to merge two Excel files into one
Merge two or more Excel files into one with Power Query or a merge tool, even when the columns do not line up, and without duplicate rows.
5 min readHow to compare two Excel files for differences
Find what was added, removed or changed between two versions of a spreadsheet. Match on a key column, because position is not identity.
4 min readHow to use VLOOKUP in Excel and Google Sheets
VLOOKUP finds a value in one column and returns a value from the same row. The formula, worked examples, wildcards, two criteria, errors and XLOOKUP.
14 min readHow to remove duplicates in Google Sheets
Remove duplicates in Google Sheets with Data cleanup or UNIQUE, keep the last copy or remove both, count repeats, and check one list against another.
11 min readHow to transpose data in Excel and Google Sheets
Turn rows into columns in Excel with Paste Special, the TRANSPOSE function or Power Query. Plus Google Sheets, and when to unpivot instead.
8 min readHow to remove spaces in Excel
Remove extra spaces in Excel with TRIM, only leading or only trailing ones, every space with Find and Replace, spaces between numbers, and what TRIM misses.
9 min readHow to use Text to Columns in Excel and Google Sheets
Split one column into several with Text to Columns in Excel, or Split text to columns in Google Sheets. Delimiters, fixed width, and joining text back.
9 min readHow to format phone numbers in Excel
Format phone numbers in Excel with the Special > Phone Number format, a custom format for dashes or brackets, or a formula when the numbers are text.
5 min readHow to import a CSV into Google Sheets
Import a CSV into Google Sheets with File > Import: new sheet, append or replace, the separator, and how to keep leading zeros and dates intact.
6 min readHow to clean a customer list without Excel formulas
Remove duplicates, fix inconsistent formats, clear placeholder cells and catch bad emails and phone numbers, without writing a single Excel formula.
4 min readHow to split a lead list by territory
Break one lead list into a file per territory, region, or rep, based on a column value, without filtering and copying by hand.
4 min readHow to convert a PDF to Excel
Convert a PDF to Excel with Get Data on Microsoft 365 for Windows, copy a PDF table in any Excel, go through Word, read scans, and save it as CSV.
11 min readHow to convert Word to Excel
Convert Word to Excel by copying a Word table, turning text into a table or columns, or saving as text and importing it, then fix zeros, numbers and dates.
11 min read
Rather not do it by hand?
Operelio does these fixes for you: upload the file and pick the tool. Free to start, no card needed.