Use Excel as a contact register only after defining record identity, follow-up ownership and who may access the file. Keep contacts distinct from their activities, use consistent table columns and prepare a migration map from the start. Decide whether the workbook fits your workflow by demonstrating the actual tasks your team must share.
A useful starting point is an unanswered enquiry. Someone should be able to identify the contact, see the latest relevant activity and know who owns the next response. Design the workbook around that journey rather than collecting every imaginable field. Include information only when the business has a clear purpose for keeping it.
How should contacts and activities be structured?
Give each contact a stable internal identifier and keep that identifier separate from the person’s display name. Names may be shared or changed. Write down how your proposed register distinguishes a person from an organisation, and how an activity refers to the relevant contact. These are design choices to agree with the people maintaining the workbook.
A contact table might contain the identifier, organisation, preferred contact route and responsible colleague. An activity table might contain its own reference, the contact identifier, a dated note and an agreed next action. Choose fields that support the actual work; a field without a maintainer becomes difficult to trust.
Microsoft’s structured-reference documentation provides practical table behaviour. Excel tables can use meaningful custom names. Use names that make a formula’s purpose understandable to the next person, and retain a short field dictionary explaining what each column means.
Excel creates a calculated column by copying a table formula down the column. Review whether a proposed calculated field belongs on every record, then inspect the populated result. A formula that produces a plausible value for the initial example still needs checking against missing data and the other cases relevant to the register.
Structured references adjust when table rows or columns are added or deleted. Renaming a table or column updates its structured references in the workbook. Include those actions in the handover exercise so the owner understands the behaviour they are relying on. Keep the naming decision deliberate even when the software updates references automatically.
Which protection decision addresses which problem?
Microsoft’s protection guidance separates several mechanisms. Worksheet protection is not intended as a security feature. Worksheet protection prevents changes to locked cells. Use it to discuss accidental changes to the workbook’s working areas, while making file-access decisions separately.
File encryption prevents other users opening the file without its password. Workbook protection can prevent adding, moving, deleting, hiding and renaming worksheets. These address different actions, so describe the requirement precisely: opening a file, altering its sheet structure and editing particular cells should not be merged into one vague promise of protection.
Information Rights Management permissions require an organisation to have IRM permissions set up. Confirm the organisation’s actual environment before putting that option into a handover. The documentation’s description is a starting point for an applicability check, not evidence that a feature has been configured in the business.
Create an access list naming the people who need the register and why. Review how they receive the authoritative file and how access is withdrawn when responsibilities change. A copied workbook deserves attention in that review: decide which copies are permitted and how the team avoids maintaining competing contact histories.
| Requirement | Proposed workbook decision | Demonstration to request |
|---|---|---|
| Recognise the same contact over time | Stable contact identifier | Activity still points to the intended person after a display-name change |
| See who owes the next response | Named follow-up owner and action | Another colleague can identify the responsible person |
| Reduce accidental formula edits | Deliberate worksheet controls | Intended entry remains possible while protected cells behave as specified |
| Control opening the register | Separately agreed file-access route | Authorised and unauthorised test accounts have the intended access |
| Move to a CRM later | Field dictionary and identifier map | Sample contacts and activities retain their relationship in a test import |
Treat the table as a working specification. Have the sales owner approve the meaning of a contact and follow-up before asking a developer to automate the workbook. A clean layout becomes more useful when its responsibilities are equally clear.
When does a workbook become difficult to maintain?
Look for operational friction rather than an arbitrary contact-count threshold. Ask whether colleagues regularly disagree about the current copy, whether an enquiry loses its owner during handover, or whether important activities live in unrelated notes. Record concrete examples from the business before deciding what a replacement must solve.
Be careful with a design that links several workbooks. An external link to an Excel table requires the source workbook open to avoid reference errors. If the proposed register depends on that pattern, include the source-file requirement in the exercise. Decide whether the resulting dependency is workable for the colleagues who will use it.
Keep the migration plan focused on relationships and meaning. List how contact identifiers, organisations, activities and owners would map into a future CRM. Record values that are ambiguous or duplicated, and give someone responsibility for resolving them before import. A field called “status” needs an agreed interpretation if the destination system will act on it.
Also decide what should be left behind. Old notes may need review for relevance and sensitivity rather than automatic transfer. The appropriate decision depends on the organisation’s requirements and responsibilities. Document that review with the record owner instead of treating every populated cell as a migration instruction.
Original enquiry-handover exercise
Use a fictional contact called River Projects and an enquiry about a website refresh. Prepare sample contact and activity records in a separate test workbook. The exercise follows an enquiry between colleagues and then into a proposed CRM mapping; it is designed to reveal whether ownership and relationships remain understandable.
Ask the first participant to record the enquiry and assign its next action. Give another participant the task of finding the current owner and the latest relevant note. Record which fields they use and any ambiguity they encounter. Revise the proposed field meanings where the handover depends on unexplained personal knowledge.
Next, change the sample contact’s display name while retaining its identifier. Inspect the activity relationship and any calculated fields. Record the observed result rather than assuming that a readable contact name is a sufficient link. If the workbook relies on a manual convention, make that convention explicit in the field dictionary.
Finally, map the same sample into the intended destination CRM’s test-import format. Identify any field with no clear destination or meaning. Have the business owner review those gaps and decide which are resolved, which require cleanup and which change the project scope. Retain the workbook version and mapping together for the next review.
For broader requirements, see business systems and software development. Get started with your field dictionary, handover problem and sample migration questions.
By Spout Team. AI-assisted drafting by Codex using the linked primary documentation.