Spreadsheet or dedicated tool: when a spreadsheet is no longer enough

Before discussing the limits, it's fair to acknowledge the strengths. A spreadsheet is an excellent tool for an insurance agent, and the reasons aren't accidental.

• Nothing to learn. Anyone can type a row and sum a column.
• Total flexibility. You can add a new column in five seconds, with no developer to wait for.
• No cost. Google Sheets is free, and Excel is often already on your computer.
• Your data stays yours. The file lives on your computer or in your own Google account.
• Easy to share. You can send the file to your accountant or a colleague without setting up special permissions.

That's why a spreadsheet is often the best choice to start with, and it remains adequate for longer than software vendors like to admit. An agent with seventy policies at a single insurer has no reason to change anything.

The question isn't whether a spreadsheet is bad. The question is at what portfolio size it starts costing more time than it saves.

The limits don't appear all at once. They show up as a slowly growing pile of small tasks that at first don't even feel like work.

Manual reconciliation of statements

Every month you receive statements from several insurers. Each has its own layout, its own column order, and its own way of writing policy numbers. To compare them with your list, you have to retype or paste the data, standardize the format, and manually find matches. With fifty line items that's half an hour; with three hundred it's a whole afternoon.

Inconsistent formats for the same policy number

The same policy might appear in your records as "12-345678" and in the statement as "0012345678." A lookup function won't find that match. Result: the policy shows up as unpaid even though it was paid, and vice versa.

No alerts

A spreadsheet can't tell you a policy expires in thirty days. You can add conditional formatting, but you have to open the file to see it. Anything that requires you to remember isn't a system.

The risk of a silent error

A formula that covers a range up to row 250 won't throw an error when you add policy number 251. It will simply return a lower result. In practice, these are the most common cause of incorrect year-end totals.

Simultaneous access and field use

Google Sheets does allow simultaneous editing, but working with twenty columns on a phone is impractical. Logging notes after a visit gets pushed to the evening — and often just doesn't happen.

History and traceability

When someone overwrites a value, the previous one disappears. Version history can technically be recalled, but it isn't practical for everyday checks of "who changed this and why."

Duplicate records

The same client appears in the spreadsheet as "Smith John," "John Smith," and "SMITH JOHN." All three hold some of the policies. There's no single, complete view of the client.

Rather than general advice, a list of concrete signs is more useful. If you recognize three or more, a change is warranted.

1. Checking monthly statements takes more than an hour.
2. You work with three or more insurers.
3. At least one policy lapsed last year because you forgot to renew it.
4. You can't say with confidence how much commission you'll receive in the next three months.
5. The spreadsheet has more than fifteen columns and at least three tabs of formulas you couldn't rebuild from scratch.
6. You don't log notes the same day after visiting a client.
7. You need to open several files to find one piece of client information.

How to estimate the financial case

The math is simple. Assume administration takes four hours a month and your time is worth EUR 40 an hour. That's EUR 160 a month, or EUR 1,920 a year. Add one missed renewal a year worth EUR 60 in commission, and one unnoticed missing commission payment of EUR 100.

The total annual cost of manual management for a mid-sized portfolio typically runs between EUR 1,500 and EUR 3,000 — mostly in the form of time that could have gone into sales.

Between "everything in a spreadsheet" and "everything in a new system" there's a third path, which is often the best fit for agents.

How it works

Client, policy, and commission data stay in your Google Sheets. A dedicated tool connects to those sheets, reads them in real time, and handles what a spreadsheet can't:

• normalizing and matching policy numbers,
• calculating expected commission using rules with effective dates,
• sorting policies by status (paid, underpaid, not yet due, clawed back),
• alerts for renewals and due installments,
• a clear view on your phone,
• reports by insurer, product, and period.

Why this model is compelling

• Data ownership stays unambiguous. The file is yours; if you stop using the tool, your data stays exactly where it was.
• No migration. No retyping, no risk of data loss during a switch.
• Gradual adoption. You can still edit the spreadsheet by hand whenever you want.
• Less data with the provider. The provider's database holds only your settings and rules, not the content about your clients.

This is the principle behind Commission Clarity: the client list, policy list, and commission tracking read data from your spreadsheets, while calculations and overviews are prepared on the fly.

The quality of the outcome depends on the quality of the input data. Preparation takes a few hours and pays for itself many times over.

Step 1: one record per row

Every row should represent one policy or one statement line item. Merged cells, multi-row headers, and subtotals mixed into the data make machine processing impossible.

Step 2: standardize client names

Pick one format — we recommend "Last First" or "First Last," used consistently throughout the file. Remove double spaces and inconsistent capitalization.

Step 3: standardize policy numbers

Write them without spaces or hyphens. If your insurer uses leading zeros, keep them and format the column as text, otherwise the spreadsheet will strip them.

Step 4: dates as dates

The inception, start, and expiry dates should be formatted as dates, not text. This is a prerequisite for any calculation of deadlines and alerts.

Step 5: required columns

For meaningful commission tracking you need at least: insurer, product, policy number, client, inception date, premium, payment method, and policy status.

Step 6: remove duplicate records

Use a duplicate-detection function based on policy number. Duplicate policies are more common than they seem and lead to double-counting revenue.

Practical tip: make a copy of the file before cleaning it up. Cleaning is irreversible; a copy costs one click.

The examples below are illustrative and show the typical decision points when moving from a spreadsheet to a dedicated tool.

Example 1: A spreadsheet that worked fine up to 120 policies

Starting point. An agent kept records in a single spreadsheet with twelve columns. The system worked flawlessly until the portfolio passed roughly 120 policies.

Where it broke down. As the portfolio grew, three problems appeared at once: duplicate rows for the same client, inconsistent policy number formats (with spaces, hyphens, leading zeros), and manually checking statements, which took half a day.

What they did. They didn't abandon the spreadsheet. It stayed as the data source, while a tool took over matching policies to statements, alerting on renewals, and calculating expected commission. The critical step before switching was cleanup: standardizing policy number formats and removing duplicate records.

Result. The monthly statement review shrank to a few minutes, while the spreadsheet remained where it was strongest — fast data entry and custom exports.

The takeaway: switching isn't a choice between the spreadsheet and the tool, but a division of labor between them.

Example 2: A switch that failed the first time

Starting point. A broker tried to import her data into a new tool without cleaning it up first. The import succeeded; using the tool did not.

What went wrong. Because the data was inconsistent, the tool couldn't reliably link policies to statements. The result was a list of dozens of unmatched items, which gave the impression the tool didn't work. In parallel, she kept maintaining the old spreadsheet, which meant double work.

What helped on the second attempt. First, two days of data preparation: standardized policy number formats, consistent insurer and product names, and removal of policies closed years earlier. Then a month of running both systems in parallel — followed by consistently dropping the old file.

Result. The share of automatically matched items rose substantially, and manual work was limited to exceptions.

The takeaway: data quality determines the success of a switch more than the choice of tool. Inconsistent data will break even the best system.

The decision isn't between "old and new," but about how much of your own time you want to spend on administration.

A one-month test

1. For a month, track the time you spend entering data, checking statements, and looking up information. A rough daily estimate is enough.
2. Count how many policies lapsed last year without any contact.
3. Fully check one statement and see whether discrepancies show up.

These three data points tell you more than any software demo.

Questions to ask a provider

• Does the tool read data from my existing spreadsheets?
• Can I set my own commission rules per insurer and product?
• How are retroactive commission-rate changes handled?
• What happens to my data if I stop using the service?
• Can I try the tool with my own data before deciding?

A realistic expectation

A dedicated tool won't sell on your behalf and won't replace knowing your clients. But it will make sure no renewal deadline is missed and every statement gets checked. Those are two things a spreadsheet can't reliably handle once a portfolio grows — not because of any flaw, but simply because it wasn't designed for that.

A spreadsheet is a great tool for recording data. Oversight requires a system that alerts you even when you don't open the file.