Google Sheets for client records: a practical mini-CRM for insurance agents

Every insurance agent or broker needs some form of client and policy record from day one. Dedicated software often seems too expensive or too complex a first step, so many people first reach for Google Sheets — a tool that's free, accessible from a browser, and something practically everyone already knows how to use.

Google Sheets has a clear advantage over a plain notebook or separate Word documents: the data is structured, searchable, and can be linked together. With a bit of invested time, you can turn a general-purpose spreadsheet tool into a workable mini-CRM that covers an agent's basic needs — a client record, a policy overview, commission tracking, and a task list.

A spreadsheet isn't a substitute for specialized software, but it can be a perfectly legitimate first system — one that teaches you which data actually matters for your work.

When a spreadsheet is the right choice

• You're just starting out and have fewer than a few hundred active policies.
• You want a quick solution with no software installation and no monthly cost.
• You work alone or in a small team where coordinating access isn't demanding.
• You already know spreadsheet basics and formulas don't put you off.

If any of these assumptions don't hold — for example, because you work in a team of five agents or have more than a thousand policies — it's worth considering a dedicated solution sooner. More on that in the final section of this article.

The most common mistake when building a client spreadsheet is cramming all the data into a single tab. The result is an unwieldy table with duplicated client data on every policy row, which becomes impossible to maintain. The fix is to split the data into four linked tabs that reference each other through unique identifiers.

1. The "Clients" tab

Each client is recorded once, regardless of how many policies they hold. Recommended columns:

• Client ID (e.g., C-0001) — a unique code that other tabs reference,
• first and last name, or company name,
• tax ID or national ID number (only where needed and with data protection in mind),
• phone, email, address,
• client source (referral, website, cold call, etc.),
• date first acquired,
• notes.

2. The "Policies" tab

Each row is one policy, linked to a client via the ID:

• Policy ID,
• Client ID (reference to the Clients tab),
• insurer,
• product (auto, life, property, etc.),
• policy number,
• inception date, start and end of coverage,
• premium and payment method,
• status (active, in renewal, cancelled, expired).

3. The "Statements" tab

Used for tracking commissions:

• Policy ID,
• statement period,
• base, rate, expected and paid amount,
• difference,
• status (reconciled, discrepancy, pending).

4. The "Tasks" tab

A simple list of to-dos tied to clients or policies:

• Client ID or Policy ID,
• task description,
• deadline,
• owner (if working in a team),
• status (open, in progress, done).

The key discipline is that client data (name, phone) is written only once, in the Clients tab. Every other tab pulls it in with a formula when needed and never retypes it manually.

Free text is the biggest source of errors in any record. If you type "active" into the "policy status" column once, "Active" another time, and "activ" a third time, no formula will reliably count all three as the same category. The fix is data validation (Data → Data validation).

Where to use dropdown lists

• Policy status: active, in renewal, cancelled, expired.
• Insurer: a list of every insurer you work with.
• Product: auto, life, accident, property, health, etc.
• Payment method: monthly, quarterly, semi-annual, annual.
• Task status: open, in progress, done.
• Client source: referral, website, cold call, renewal.

How to set it up

1. Select the column (e.g., the whole of column D in the Policies tab).
2. Open Data → Data validation.
3. Choose the "Dropdown" criterion and enter the options, or link them to a helper tab where the list is defined once.
4. Turn on the option to reject invalid input, not just flag it.

A helper tab of reference lists

It's worth keeping a separate "Lists" tab with all the allowed values recorded (insurers, products, statuses). Link the dropdowns in your main tabs to this reference tab. The benefit: when you add a new insurer, you add it in one place, and it's automatically available everywhere it's needed.

Discipline alone can't achieve consistent data entry, no matter how clear the instructions to colleagues. A dropdown is the only reliable way to prevent ten variations of the same entry from piling up.

Conditional formatting (Format → Conditional formatting) turns a spreadsheet from a static table into a tool that flags what needs attention on its own. Instead of manually checking every day which policy is about to expire, a colored row tells you at a glance.

Recommended rules for the Policies tab

• Red: the expiry date is before today (the policy has expired but is still marked active).
• Orange: the expiry date falls within the next 30 days (time to call about renewal).
• Yellow: the expiry date falls within the next 60 days (time to prepare a renewal offer).
• Green: status is "active" and expiry is more than 60 days away.

Example formula for conditional formatting

For the "renewal within 30 days" rule, with the expiry date in column F, use a custom formula:

`=AND($F2>=TODAY();$F2<=TODAY()+30)`

Recommended rules for the Statements tab

• Red: the difference between expected and paid commission exceeds an agreed tolerance (e.g., EUR 0.50).
• Gray: the statement for the period hasn't arrived yet, or the field is blank.

Colors should be used consistently across the whole spreadsheet. If red means "urgent" in one place and "entry error" in another, conditional formatting loses its purpose.

Manually counting and copying data between tabs is the most common reason people give up on a spreadsheet. A handful of functions can automate most of it.

FILTER — showing only the rows that matter

To display all policies expiring in the next 30 days without manual filtering:

`=FILTER(Policies!A2:H; (Policies!F2:F<=TODAY()+30) * (Policies!G2:G="active"))`

The result is an automatically updated list you can place on a separate "Overview" tab and check every morning.

SUMIFS — summing by condition

To calculate the total expected commission per insurer for the current month:

`=SUMIFS(Statements!D:D; Statements!B:B; "Insurer A"; Statements!C:C; "2026-08")`

XLOOKUP or VLOOKUP — pulling in client data

To automatically show a client's name in the Policies tab based on the Client ID, without manual retyping:

`=XLOOKUP(B2; Clients!A:A; Clients!B:B)`

Where XLOOKUP isn't available, this also works:

`=VLOOKUP(B2; Clients!A:D; 2; FALSE)`

UNIQUE — a list without duplicates

For a quick list of every insurer you actually work with (for use in a dropdown or for review):

`=UNIQUE(Policies!C2:C)`

COUNTIFS — counting by condition

To count a client's active policies:

`=COUNTIFS(Policies!B:B; A2; Policies!G:G; "active")`

Enter formulas once, at the top of a column, then extend them downward or use ARRAYFORMULA so they apply automatically to every new row.

Because the spreadsheet contains client personal data, sharing access isn't a technical detail — it's a data protection question.

Basic rules

• Never use "anyone with the link." Always restrict access to specific email addresses.
• Separate roles. Give colleagues who only need to view data "viewer" rights, not "editor."
• Turn on two-factor authentication on the Google account that holds the spreadsheet.
• Restrict downloads. For viewers, you can disable downloading, printing, and copying the file.
• Review the list of people with access regularly, especially after a colleague or assistant stops working with you.

Data minimization

Don't collect data you don't need. A national ID or tax number is only worth including if you actually use it in your dealings with the client; otherwise a name and contact detail are enough. Less data in the spreadsheet means less risk if access is ever compromised.

Version history as a safety net

Google Sheets automatically logs changes (File → Version history). This means:

• you can restore the spreadsheet to a state before an accidentally deleted row,
• you can see who changed what and when, which is useful when working in a team,
• you don't need to manually create backups for every possible entry mistake.

Even so, it's worth downloading a monthly copy of the spreadsheet as .xlsx or .csv to a local or cloud backup, separate from your Google account — in case the account is deleted, access is blocked, or an error occurs that version history doesn't capture (e.g., the entire file being deleted after a long time).

Version history isn't a substitute for a backup, but it's an excellent first line of defense against everyday mistakes.

The examples below are illustrative — built from typical situations agents encounter when managing clients in spreadsheets. They don't describe a specific person or company, and the numbers are simplified for clarity.

Example 1: An agent with no system for renewals

Starting point. A self-employed agent managed around 150 policies in a single tab, with no separate client data and no dates formatted for automatic checking. He relied on memory and manual phone reminders for renewals.

What the review revealed. While organizing the data into a structured mini-CRM, it became clear he had missed renewals on eight policies over the previous six months, because the expiry date wasn't recorded in a consistent format and couldn't be sorted.

Process. He split the data into Clients and Policies tabs, applied a consistent date format, and introduced conditional formatting to flag policies expiring within 30 and 60 days. He added a Tasks tab to track renewal calls.

Result. Over the following three months he didn't miss a single renewal, and for four of the eight previously missed policies, he was still able to reach the clients and renew the offer, albeit with a delay.

The takeaway: the problem wasn't a lack of data, but that it wasn't structured in a way the spreadsheet could sort and flag automatically.

Example 2: A broker with a team of two colleagues

Starting point. A broker shared a single spreadsheet with two colleagues, all with editing rights. Data for one client was recorded in three different places because each colleague managed "their" part of the portfolio their own way.

What the review revealed. When trying to calculate the total number of active policies, UNIQUE returned eighteen different entries for the name of the same insurer (with varying capitalization, abbreviations, and full names).

Process. She introduced a Reference Lists tab with an approved list of insurers and products, linked to dropdowns across all other tabs. She rearranged access so that only she could edit the Clients tab, while the two colleagues entered data into separate tabs linked via the client ID.

Result. The number of variants for the same insurer's name dropped to one, and the formulas summing commissions by insurer finally returned correct totals.

The takeaway: when working in a team, discipline in data entry isn't enough — you need technical constraints (dropdowns, separate permissions) that prevent bad entries before they happen.

Google Sheets is an excellent first system, but it has built-in limits that become more noticeable as the portfolio grows.

Typical limitations

• Matching data is manual. When an insurer's statement arrives in a different format than your formula expects, you have to reshape it manually before you can compare it.
• Speed drops as data grows. Functions like FILTER and ARRAYFORMULA noticeably slow down with thousands of rows across several tabs, and the spreadsheet starts to lag.
• Human error remains possible. Even with dropdowns and conditional formatting, someone can paste over a formula or delete a row by accident.
• No automatic alerts outside the spreadsheet. You only see a color-coded warning if you actually open the file; there's no email or notification.
• Coordinating with colleagues is demanding. Multiple editors working on the same spreadsheet at once increases the risk of conflicting changes.

Signs it's time to upgrade

• The portfolio exceeds two to three hundred active policies.
• You work with more than three insurers using different statement formats.
• Checking renewals and commissions takes more than an hour every month.
• You're more than one person on the team, and data gets overwritten or lost.
• You increasingly find out about an error only after a client points it out.

What Commission Clarity brings at this point

Moving from a spreadsheet to a dedicated tool doesn't mean retyping your data or abandoning the system you've already built. Commission Clarity reads data directly from your Google Sheets, so your record stays exactly where it is — with you. The upgrade is an added layer of automation on top of your existing structure, not a replacement for the system you set up following this article.

Commission Clarity takes over the most tedious part of spreadsheet work: automatically matching policies with statements, flagging renewals and deadlines without needing to open the file, and giving you a clear view of your client list and commission status in one place. The structure you built with Clients, Policies, Statements, and Tasks tabs is an excellent foundation for this — it's exactly the kind of data organization Commission Clarity expects and knows how to read.

A spreadsheet teaches you which data matters. A dedicated tool makes sure you don't have to check that data by hand every day.