Flag CRM Contacts Missing an Owner or Next Step
How do I flag CRM contacts missing an owner or next step in Google Sheets and filter them for follow-up?
Put contact IDs in column A, owners in B, and next steps in C. Enter =IF(A2="","",IF(OR(B2="",C2=""),"Incomplete","Ready")) in D2, fill it down, then filter Status for Incomplete.
How do I flag CRM contacts missing an owner or next step?
Use IF and OR to mark a contact Incomplete if its ID is present and either its owner or next step is missing. Put contact IDs in column A, owners in B, next steps in C, and the formula in D. When both required fields contain values, the result is Ready. If the ID is missing, the status stays blank. Google documents IF and OR.
This formula checks for values in required fields; it does not qualify contacts. A Ready result says nothing about whether the owner is correct or the next step is useful. For a broader look at tools a small company might use, see the small business stack topic page.
What sample CRM data shows how the check works?
The sample includes complete contacts, records with missing information, and one unused row. Add the headers in row 1, then enter the records in rows 2 through 7. The names and records are fictional. Leave empty cells empty; do not type the word blank.
| 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. Contact IDs are more dependable references than row numbers, since sorting can change the order of records. This formula does not detect duplicate IDs. Enter an owner’s name or team email address, and make each next step specific enough that someone else can tell what to do.
How do you add the CRM status formula in Google Sheets?
Put the formula in D2 and fill it down across the rows you want checked. IF leaves the status blank when there is no ID and selects a result for other rows. OR tests whether either required field is empty.
=IF(A2="","",IF(OR(B2="",C2=""),"Incomplete","Ready"))
- Confirm the layout. Put contact IDs in A, owners in B, and next steps in C. If your records use other columns, change the references in the formula.
- Add the header. Type
Statusin D1. Leave D2 and the cells below it open for the formulas. - Enter the formula. Paste it into D2 and press Enter. The first sample record should show
Ready. - Fill down. Select D2:D1000, then choose Edit > Fill > Down. Each copied formula adjusts its row references.
- Compare results. Before filling additional rows, check rows 2 through 7 against the output table below.
- Protect the formula range if needed. Make routine changes in the input columns. If several people edit the sheet, consider Data > Protect sheets and ranges to limit changes to D2:D1000.
If your spreadsheet locale separates arguments with semicolons, use this formula instead:
=IF(A2="";"";IF(OR(B2="";C2="");"Incomplete";"Ready"))
The formula currently covers rows through 1000. Rows below that will not show a status until you extend the formula range. If you also track follow-up activity, find open CRM contacts with no recent follow-up to catch missing activity rather than missing required fields.
What status should each sample CRM contact display?
The sample returns two Ready statuses, three Incomplete statuses, and one blank status. DEMO-1002 is missing an owner, DEMO-1003 has no next step, and DEMO-1004 has neither. The formula uses the same label when one or both required fields are missing.
| 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 |
The outer IF(A2="","",...) keeps the status blank if the contact ID is empty. For a row with an ID, OR(B2="",C2="") tests both required fields. If either comparison is true, the inner IF returns Incomplete. If both are false, it returns Ready. A required-field formula that returns "" also counts as empty in this check.
There are six rows in the displayed range. The row without an ID gets no status; the other five rows have IDs. Of those five, three are missing at least one required value, while two have both values. The results are three Incomplete and two Ready.
How do you filter and highlight incomplete CRM contacts?
Filter the status column to show Incomplete and find contacts with missing information. Conditional formatting can make those rows easier to spot. These steps find missing fields, but cannot establish whether the listed owner is the right person.
- Select A1:D1000, including the headers and all rows the filter should cover.
- Choose Data > Create filter view.
- Open the filter control in D1.
- Under Filter by values, leave only
Incompleteselected. - Name the view
Missing owner or next step.
Add the missing values to the listed records. If a completed record still appears, apply the status filter again. When sorting, select the entire record range. Sorting only the owner column can detach names from contact IDs, and the formula will not catch that error.
To shade incomplete rows, select A2:D1000, choose Format > Conditional formatting, and select Custom formula is under Format cells if. Enter:
=$D2="Incomplete"
Choose a light fill that leaves the text easy to read, then click Done. $D fixes the formula to the status column, while the relative row number changes for each record. Because the selected range starts at row 2, the formula also starts with $D2. Keep the status words visible instead of depending on color alone. Extend the formatting range if new records go beyond row 1000.
Does Google Sheets count as a CRM?
A spreadsheet can store contact details and flag missing fields, but this formula checks only whether those fields are filled in. It does not assign an owner, decide if a contact qualifies, judge the usefulness of an action, or notify anyone. Use it for a small tracking rule with a clear boundary, not as proof that the entire CRM process is covered.
Here, Ready has a limited meaning: the contact ID, owner, and next-step cells are nonempty according to the formula’s comparisons. Values such as Unknown or Follow up pass the basic check, even if they do not help anyone complete the work. For a different way to track follow-up, count open tasks by owner with Google Sheets QUERY.
How do you fix misleading statuses and formula errors?
If a status looks wrong, check the cell contents, confirm the contact ID, check whether your locale requires different argument separators, and make sure the row contains a formula. The formula reads cell contents as they are. It cannot tell if a value is accurate or useful.
A cell looks empty, but the status says Ready. The cell may contain spaces or invisible characters, which the formula treats as content. Select it and press Delete. If owner or next-step cells often contain ordinary spaces, replace the formula in D2 with the version below and fill it down:
=IF(A2="","",IF(OR(LEN(TRIM(B2))=0,LEN(TRIM(C2))=0),"Incomplete","Ready"))
This version removes ordinary spaces from the owner and next-step checks. It will not catch every invisible character, or a contact ID made only of whitespace. When needed, clear cells that contain unwanted content by hand.
A partially entered contact has no status. If column A has no contact ID, the formula leaves the status blank even when the row has an owner or next step. Add the ID, or remove stray entries if the row is not a real contact. By design, the check skips records without IDs.
The formula produces a parse error. Your spreadsheet locale may expect semicolons, or the formula may have an extra parenthesis or curly quotation marks. Use straight quotation marks. If your locale uses semicolons, replace the expression with the full formula below:
=IF(A2="";"";IF(OR(B2="";C2="");"Incomplete";"Ready"))
A new contact has an ID but no status. It may be below the filled range, or its row may not contain a formula. Copy a working status cell into the affected cell in D. To cover several rows, select from the last working formula to the new endpoint and choose Edit > Fill > Down. Extend the filter-view and conditional-formatting ranges too. Copying formulas does not expand either range.
The status says Ready, but the work is not ready. The formula does not check whether the owner is right, the next step is useful, or the action is late. Unknown counts as a nonempty owner, and Follow up counts as a nonempty next step. Use validation or a review rule to check the values themselves. If timing matters, add a next-action date and a separate overdue check.
| 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 cannot establish that the assignment is appropriate. If you need to check more than whether required fields contain values, add a separate review or workflow rule.