Handling Errors and Missing Data in Google Sheets Data Enrichment

You’re about to launch a prospecting campaign. Your cold email sequence is ready, your 500 leads are in Google Sheets — and then you realize 30% of emails are missing, 15% of company names are inconsistently formatted, and dozens of rows are showing cryptic errors like #N/A or #VALUE!.

If you work with B2B data, you know this scenario all too well. The good news: with the right approach, handling errors and missing data during Google Sheets enrichment becomes a systematic process — not a time-sink you dread every time you prep a new list.

This guide walks you through how to identify, fix, and prevent these issues so you can keep your database clean and ready to use.

Enrich your leads directly in Google Sheets

Derrick automatically fills in missing fields — emails, phone numbers, company info — without CSV exports or complex setup.

Try for free →

Why Is Your B2B Data Always Incomplete?

Before fixing missing data, it’s worth understanding why it shows up in the first place.

The reality is stark: 70% of CRM databases contain outdated, incomplete, or inaccurate information. This isn’t a tool problem or a negligence problem — it’s just the nature of B2B data.

Three mechanisms drive this constant degradation:

Natural data decay. B2B contact data degrades at roughly 2.1% per month — that’s over 22% annually. A “clean” database in January is already compromised by March. People change jobs, phone numbers get updated, professional email addresses become invalid.

Incomplete data entry. When a sales rep manually enters a lead, they often don’t have every detail at hand. The result: empty fields, rough formats, and “fill this in later” values that never get filled in.

Process errors. An import from LinkedIn Sales Navigator, a copy-paste from a website, a merge of two CSV files — every manipulation introduces inconsistencies: extra spaces, different date formats, inconsistent capitalization.

According to ZoomInfo, bad data wastes over 27% of a B2B salesperson’s time. On a five-day workweek, that’s more than a full day lost to validating, correcting, and working around broken records.

With that context in mind, let’s look at how to actually spot these problems in your Google Sheets.

The 4 Types of Errors and Missing Data in B2B Enrichment

When enriching data in Google Sheets, you’ll consistently run into four categories of problems. Naming them clearly is the first step to fixing them efficiently.

1. Empty Cells (Missing Data)

The most visible problem: a critical field — email, phone number, company name, job title — is simply absent. These gaps prevent you from segmenting, personalizing, or even sending a message.

Example: Mike, an SDR at a B2B SaaS company, imports 300 profiles from Sales Navigator. He finds 40% of emails are missing — because LinkedIn doesn’t expose them natively. Without additional enrichment, he can only reach 180 out of 300 prospects.

2. Google Sheets Formula Errors

During enrichment, you use formulas to cross-reference sources, run VLOOKUPs, or calculate derived fields. These formulas break when source data is inconsistent:

3. Poorly Formatted Data (Inconsistencies)

The data exists, but it’s not usable as-is. This is the most insidious category because nothing flags as an error.

Typical examples in B2B data enrichment:

• Company names: “Apple Inc.”, “Apple”, “apple inc”, “APPLE INC” — four values for the same entity
• Phone numbers: “+1-212-555-0100”, “2125550100”, “+12125550100”, “(212) 555-0100”
• Job titles: “CEO”, “Chief Executive Officer”, “Co-Founder & CEO”, “Founder/CEO”

These inconsistencies block deduplication, distort segments, and break personalization in your outreach campaigns.

4. Duplicates

According to Dun & Bradstreet, duplicate data is a major issue for 91% of B2B companies. The same prospect appearing twice with slightly different emails, or a company imported from two different sources — duplicates skew your analytics and send the same person through your email sequence twice.

Now that you know what to look for, here’s how to find it.

How to Detect Missing Data and Errors in Google Sheets

Audit before you fix. Here are the most effective methods for assessing the state of your enrichment database.

Step 1: Count Empty Cells by Column

The COUNTBLANK() formula immediately tells you how many values are missing in a column:

=COUNTBLANK(B2:B500)

To get a completeness rate as a percentage:

=1-(COUNTBLANK(B2:B500)/COUNTA(B2:B500))

Expected result: A percentage per column. If a critical field like email or phone number is below 80% complete, your database needs enrichment before you can run any meaningful campaign.

Step 2: Highlight Incomplete Rows

Use conditional formatting to instantly visualize rows with missing critical fields:

1. Select your entire data range
2. Format → Conditional formatting
3. Custom formula: =OR(ISBLANK(B2),ISBLANK(C2),ISBLANK(D2))
4. Pick an alert color (red or orange)

Expected result: Every row missing an email (column B), phone (column C), or company (column D) lights up immediately. At a glance, you know exactly how much of your list needs enrichment.

Step 3: Detect Duplicates on a Key Field

To flag duplicates on the email column, add a helper column with:

=IF(COUNTIF($B$2:$B$500,B2)>1,"Duplicate","OK")

Filter on “Duplicate” to isolate and handle all duplicated records before running enrichment — so you don’t pay twice for the same contact.

Expected result: A clear list of emails that appear more than once, ready to merge or remove before your enrichment run.

These three audit steps give you an accurate picture of your database. Now let’s fix what’s broken.

How to Fix Errors and Missing Data: 4 Steps

Step 1: Handle Formula Errors with IFERROR

The IFERROR() function prevents formula errors from blocking your calculated columns during enrichment:

=IFERROR(VLOOKUP(A2,TargetSheet!A:C,2,FALSE),"Not found")

Instead of showing #N/A when a value isn’t found, the cell shows “Not found” — making it easy to filter the rows that need attention.

Pro tip: Use IFNA() instead of IFERROR() when you only expect #N/A errors. This way, other error types remain visible and alert you to unexpected problems in your data.

Expected result: No more formula errors blocking your filters or skewing your totals. Missing values are now clearly separated from formula errors, and you can handle each category on its own terms.

Step 2: Normalize Inconsistent Formats

For poorly formatted data, Google Sheets has built-in cleaning functions that do most of the heavy lifting.

Remove extra spaces:

=TRIM(A2)

Standardize company name capitalization:

=PROPER(A2)

Extract domain from an email address (to derive company):

=MID(B2,FIND("@",B2)+1,LEN(B2))

That last formula is especially useful in B2B enrichment: if you have an email but not the company domain, you can extract it automatically — giving you a usable input for company-level enrichment.

Expected result: Consistent values in your key columns, enabling reliable joins between sources and accurate deduplication.

Step 3: Deduplicate Cleanly Before Enriching

Google Sheets has a native function: Data → Data cleanup → Remove duplicates.

Important: Always make a copy of your sheet before deduplicating. The automatic process keeps the first occurrence and deletes the rest — which can cause data loss if the first row is actually the least complete one.

A safer two-step approach:

1. Add a “Completeness score” column that counts filled fields: =COUNTIF(B2,"<>")+COUNTIF(C2,"<>")+COUNTIF(D2,"<>")
2. Sort by email (your deduplication key), then by score descending. Then run Remove Duplicates — Google Sheets will keep the first row, which is now the most complete.

Expected result: A duplicate-free database where the retained row is always the richest in data — the ideal starting point for B2B data enrichment.

Step 4: Fill Missing Data Through Automated Enrichment

This is the core step. You’ve identified missing fields, cleaned up inconsistencies, removed duplicates. Now it’s time to fill the gaps.

The manual approach — hunting each email on LinkedIn, checking company websites one by one — breaks down past 50 rows. The efficient approach is automated enrichment directly from Google Sheets.

Sarah, a Growth Marketer at a lead generation agency, is working a list of 800 prospects. After her audit, she identifies 340 missing emails and 120 missing phone numbers. With an enrichment tool connected to Google Sheets, she fills those gaps in under 30 minutes — without leaving her spreadsheet, without exporting a single CSV.

Best Practices to Prevent Enrichment Errors

Fixing errors is necessary. Preventing them is better. Here are the habits of B2B teams that keep their databases clean long-term.

1. Define a Data Schema Before Importing

Before pulling anything into Google Sheets, define which columns are required and what format is expected:

This schema lets you use Google Sheets’ Data Validation (Data → Data validation) to block non-conforming inputs at the source.

2. Always Verify Enriched Emails Before Sending

A found email isn’t necessarily a valid email. Databases enriched from a single source show bounce rates of 5–7%, compared to under 1% for real-time validated data. Before loading emails into a cold email sequence, an SMTP verification step is non-negotiable.

Email verification checks several things: domain existence (MX record), address syntax validity, and the mail server’s response. It also catches common traps: catch-all addresses, disposable emails, and spam traps. Your sender reputation — and your deliverability rates — depend on it.

3. Enrich in Batches, Not on the Fly

It’s tempting to enrich each new prospect the moment they enter the database. In practice, this fragmented approach creates format inconsistencies and wastes credits on potential duplicates.

The right approach: enrich in bulk, once a week or every two weeks. Deduplicate first, enrich second. This routine keeps your database consistent and optimizes your cost per enrichment.

4. Document Your Data Sources

Add a “Source” column and note where each record came from: Sales Navigator import, website scraping, manual entry, automated enrichment. When a source’s data quality degrades, you can immediately target the affected records for re-enrichment — without reviewing your entire database.

5. Run Quarterly Data Audits

Create a “Data Quality” tab in your Google Sheets with your COUNTBLANK formulas and completeness rates by column. A 30-minute audit every quarter prevents the unpleasant discovery — right before a campaign launch — that 40% of your list is unusable. Gartner estimates that poor data quality costs organizations an average of $12.9 million per year.

How Derrick Automates Missing Data Management in Google Sheets

Manual error handling works up to a few hundred rows. Beyond that, automation becomes essential for maintaining a clean, up-to-date B2B database.

Derrick App installs directly in Google Sheets and handles the most time-consuming steps of data enrichment.

For missing emails: The Lead Email Finder locates a contact’s professional email address from their first name, last name, and company domain — with real-time validation built in. You get verified emails, not just guessed ones.

For missing phone numbers: The Phone Finder from LinkedIn extracts phone numbers directly from a LinkedIn profile. Ideal when you have the LinkedIn URL but no contact details.

For duplicates: The Remove Duplicates feature cleans your sheet in one click, using whichever fields you define as the deduplication key.

For normalization: Data Normalization automatically corrects inconsistent formats — splitting full names into first/last, detecting mismatches, cleaning raw data.

For validating existing emails: Email Verifier reviews an email column to flag invalid addresses before you send anything — protecting your sender reputation and keeping bounce rates low.

Every one of these functions runs directly inside your Google Sheets — no CSV exports, no manual syncing, no separate tool to learn.

Key Takeaways

• 70% of CRM databases contain incomplete or outdated records — this isn’t the exception in B2B, it’s the norm
• 4 error types to watch for: empty cells, formula errors, inconsistent formats, duplicates
• COUNTBLANK() and IFERROR() are the two essential formulas for detecting and handling errors in Google Sheets
• Always deduplicate before enriching — don’t waste credits enriching the same contact twice
• Always verify emails after enrichment — a found email is not necessarily a valid email
• Batch enrichment produces cleaner, more consistent results than enriching records one by one

Conclusion: Clean Data, Effective Prospecting

Missing data and enrichment errors aren’t inevitable. They’re symptoms of a process that lacks structure — and they can be fixed with a handful of well-placed formulas, a regular audit habit, and the right tools.

The real cost isn’t the time spent cleaning. It’s the opportunities lost when a prospect stays out of reach because their email was never found, or when a campaign underperforms because the underlying data was too degraded to be useful.

Start with the audit: run your COUNTBLANK formulas by column today. You’ll have an accurate picture of your enrichment database in five minutes — and you’ll know exactly where to focus your next effort.

Fill your missing data in just a few clicks

Derrick automatically enriches your leads in Google Sheets — verified emails, phone numbers, company info. No more empty fields in your B2B database.

Try for free →

FAQ

Why does my B2B data degrade so quickly? B2B contact data decays at roughly 2.1% per month — over 22% annually. Professionals change jobs, titles, and phone numbers. A database enriched in January will have lost meaningful value by spring. Quarterly enrichment, at a minimum, is essential to maintain data quality.

What’s the difference between IFERROR and IFNA in Google Sheets? IFERROR catches all error types (#N/A, #VALUE!, #REF!, etc.), while IFNA only catches #N/A errors. For enrichment workflows involving VLOOKUPs, IFNA is usually the safer choice — other error types remain visible and alert you to unexpected issues in your source data.

How do I know if an enriched email is actually valid? SMTP verification confirms that an address exists on the mail server without sending a message. Tools like Derrick’s Email Verifier run this check directly inside Google Sheets, with detection for catch-all addresses and disposable emails that would otherwise inflate your bounce rate.

Should I enrich before or after deduplicating? Always deduplicate first, then enrich. You avoid spending credits on the same contact twice, and you work from a clean base that makes cross-source joins more reliable.

How long does it take to audit a 500-row prospect list in Google Sheets? With native formulas (COUNTBLANK, conditional formatting, duplicate detection), a full audit takes 15 to 30 minutes. Filling the gaps through automated enrichment then takes a few minutes for 500 rows — versus several hours of manual research.