Flag CRM Contacts Missing an Owner or Next Step

How does Google Sheets flag contacts missing an owner or next step?

Google Sheets can flag CRM contacts with an IF and OR formula that returns Incomplete when a contact ID exists but an owner or next step is empty.

Use column A for contact IDs, B for owners, C for next steps, and D for status. The formula returns Ready when both required fields contain values. Rows without contact IDs display no status, even if other cells contain information.

Ready describes completeness only, not contact qualification or follow-up quality.

What sample CRM data shows how the completeness check works?

The following sample CRM data demonstrates complete contacts, missing required fields, and an unused row.

Enter headers in row 1 and records in rows 2 through 7. Names and records are fictional. Empty table cells represent genuinely empty spreadsheet cells; do not type “blank” into them.

Contact ID	Owner	Next step
DEMO-1001	Fictional Alex	Email onboarding checklist
DEMO-1002		Confirm meeting availability
DEMO-1003	Fictional Casey
DEMO-1004
DEMO-1005	Fictional Alex	Send revised project outline

Keep sheet row 6 empty. Use stable contact IDs rather than row numbers that change when sorted; the formula does not enforce uniqueness. Owners can be names or team email addresses, while next steps should describe understandable actions.

How do you add the CRM status formula in Google Sheets?

Enter the following formula in D2 and fill it down to flag contacts missing an owner or next step:

=IF(A2="","",IF(OR(B2="",C2=""),"Incomplete","Ready"))

1. Confirm the layout. Put contact IDs in A, owners in B, and next steps in C. Adjust references for different layouts rather than overwriting existing columns.
2. Add the header. Enter Status in D1. Confirm D2 and the cells below are available for formulas.
3. Enter the formula. Paste the complete formula into D2 and press Enter. The first sample record should display Ready.
4. Fill down. Select D2:D1000 and choose Edit > Fill > Down. Relative references adjust to each row; rows without IDs display no status.
5. Compare results. Check rows 2 through 7 against the expected output below. Correct entry or reference errors before extending the pattern.
6. Assign formula maintenance. Keep routine edits in input columns. For shared editing, consider Data > Protect sheets and ranges to restrict changes to D2:D1000.

For a spreadsheet locale requiring semicolon separators, the equivalent working formula is:

=IF(A2="";"";IF(OR(B2="";C2="");"Incomplete";"Ready"))

Row 1000 is a coverage limit, not automatic future coverage. Extend formulas and related ranges as the CRM grows.

What status should each sample CRM contact display?

The sample CRM records produce two Ready results, three Incomplete results, and one blank status.

Sheet row	Contact ID	Status
2	DEMO-1001	Ready
3	DEMO-1002	Incomplete
4	DEMO-1003	Incomplete
5	DEMO-1004	Incomplete
6
7	DEMO-1005	Ready

DEMO-1002 lacks an owner, DEMO-1003 lacks a next step, and DEMO-1004 lacks both. Missing either or both required fields produces the same label.

The outer IF(A2="","",...) returns an empty display when the contact ID is empty. Otherwise, OR(B2="",C2="") checks whether either required field is empty, and the inner IF selects the status.

Cells containing formulas that return "" also count as empty under these comparisons.

Why do nested IF and OR flag contacts missing an owner or next step?

Nested IF functions and OR separate rows with contact IDs from unused rows, then label each contact according to whether either required field is empty.

First, the outer IF evaluates A2="". When that comparison is true, it returns "", leaving the status visually blank rather than assigning a completeness label to every spreadsheet row.

For a row with a contact ID, OR evaluates B2="" and C2="". These comparisons check the owner and next step individually. OR is chosen instead of AND because either missing value is enough to make the contact incomplete; both do not need to be missing.

The inner IF turns that logical result into readable text: true produces Incomplete, while false produces Ready. Thus, DEMO-1001 receives Ready, while DEMO-1002 receives Incomplete.

Finally, filling the formula down adjusts its relative references to each row. This reuses the same rule for every contact rather than requiring separately written formulas.

How do you filter incomplete CRM contacts without changing other editors’ views?

Create a Google Sheets filter view showing only Incomplete statuses to review missing CRM information without changing other editors’ views.

1. Select A1:D1000, including headers and all four columns.
2. Choose Data > Create filter view.
3. Open the filter control in D1.
4. Under Filter by values, select only Incomplete.
5. Name the view Missing owner or next step.

Fill missing values in the visible records. Status formulas recalculate after input changes; if a completed record remains visible, reapply the status filter.

Include the entire record range when sorting. Sorting only owners can attach them to incorrect contact IDs, and the completeness formula cannot detect that mismatch. The view identifies missing information, not the correct assignee.

How do you highlight incomplete CRM rows in Google Sheets?

Apply conditional formatting to A2:D1000 using a custom formula that checks each row’s status in column D.

Select A2:D1000, choose Format > Conditional formatting, and select Custom formula is under Format cells if. Enter:

=$D2="Incomplete"

Choose a light fill that keeps text readable, then click Done. $D fixes the status column, while the relative row number checks each record separately. A range beginning at row 2 requires $D2.

Keep status text visible rather than relying on color alone. Extend the formatting range separately when adding records below row 1000; filling more formulas does not necessarily expand manually defined formatting.

How do you fix misleading CRM statuses and formula errors?

Fix misleading Google Sheets CRM statuses by checking for hidden content, missing contact IDs, locale separators, and absent status formulas.

A cell looks empty, but the status says Ready

Symptom: A contact has an ID and an apparently empty required field, but displays Ready.

Cause: Spaces or invisible characters count as content.

Exact fix: Select the affected input cell and press Delete. For recurring ordinary spaces, replace D2 with this formula and fill down:

=IF(A2="","",IF(OR(LEN(TRIM(B2))=0,LEN(TRIM(C2))=0),"Incomplete","Ready"))

This variation handles ordinary spaces in owner and next-step cells, not every invisible character or whitespace-only contact IDs. Clear contaminated cells directly when necessary.

A partially entered contact has no status

Symptom: An owner or next step exists, but status is blank.

Cause: Column A has no contact ID.

Exact fix: Enter the contact’s ID in column A, or clear stray inputs if the row is not a real contact.

This check intentionally excludes records without IDs; it cannot identify every partially entered row.

Google Sheets reports a formula parse error

Symptom: The pasted formula produces a parse error.

Cause: The spreadsheet locale may require semicolons. An extra closing parenthesis or curly quotation marks can also cause errors.

Exact fix: For a semicolon-based locale, replace the expression with this complete, balanced formula:

=IF(A2="";"";IF(OR(B2="";C2="");"Incomplete";"Ready"))

Use straight quotation marks. The spreadsheet’s locale, not the team’s location, determines the argument separator.

A new contact has no status despite a populated ID

Symptom: A new record has an ID but no displayed status.

Cause: Its status formula was deleted, or the row falls beyond the filled range.

Exact fix: Copy a working status cell immediately above into the affected D cell. For multiple uncovered rows, select the last working formula through the new endpoint and choose Edit > Fill > Down.

When should you avoid relying on this CRM completeness flag?

Avoid relying on this Google Sheets completeness flag for information quality, overdue follow-ups, automatic assignments, or notifications.

Requirement	Why the flag is insufficient	Appropriate addition
Valid ownership	Unknown passes as nonempty	Owner dropdown with validation rejecting invalid entries
Useful next steps	Follow up passes as nonempty	Action-writing convention and review of vague entries
Overdue work	No date is checked	Next-action date and separate overdue check
Routing or reminders	Formulas do not assign or notify	CRM workflow with those capabilities

Even a valid owner selection does not prove that the assignment is appropriate. Use separate review or workflow rules when correctness matters beyond the presence of required values.