When Your CRM and Spreadsheet Disagree, Which Number Is Right?

When Your CRM and Spreadsheet Disagree, Which Number Is Right?

Someone asks a simple question:

"How many active customers do we have?"

You check the CRM.

Someone else checks a spreadsheet.

A manager has a report open.

Three numbers appear.

Now the question is no longer just how many customers you have.

The question is:

Which number should we trust?

This happens more often as a service business grows.

Information starts in one place, then gets copied somewhere else because someone needs it for a report, a meeting or a particular task.

Over time, the copies change.

Someone updates one.

Someone forgets another.

A new spreadsheet is created.

Eventually, the business has several versions of what should be the same information.

The problem is not simply messy data.

It is that people start spending time checking the data instead of using it.

How does this happen?

It usually starts for a reasonable reason.

A team needs information that is difficult to find in the main system.

So someone creates a spreadsheet.

Another person adds a few extra columns.

A manager asks for a different view.

Someone exports the data and makes another report.

None of these decisions seems particularly serious at the time.

But now the same customer may appear in:

• The CRM
• A team spreadsheet
• An Excel report
• A personal tracker
• An email
• Another system

And those places may not all be updated at the same time.

The business has not necessarily created bad data.

It has created multiple places where data can become different.

Start with one important piece of information

Don't try to clean everything at once.

Choose one piece of information that people regularly disagree about.

For example:

• Number of active customers
• Customer status
• Contract end date
• Outstanding amount
• Sales pipeline
• Job status
• Renewal date

Then ask:

Where does this information currently exist?

Write down every place you can find it.

You may be surprised by the answer.

Decide where the information should come from

For each important piece of information, there should ideally be a clear answer to:

What is the main source?

For example, if the CRM is the place where customer status is managed, then a separate spreadsheet should not quietly become another version of customer status.

This does not mean spreadsheets are bad.

A spreadsheet can be completely appropriate for analysis or a one-off piece of work.

The problem starts when a temporary copy becomes a second system that people rely on.

The question is not:

"Can we use Excel?"

The better question is:

"Is this the place where this information is supposed to be maintained?"

Look at how the information gets there

Finding the source is only half the job.

You also need to understand how information enters it.

Imagine a customer's contract end date.

It might be entered when the contract is created.

Then the customer changes the contract.

Someone updates the spreadsheet but not the CRM.

Later, someone exports the CRM to prepare a renewal list.

Now there are two dates.

Neither person necessarily did anything unreasonable.

The process allowed the information to split.

So ask:

• Who enters the information?
• When is it entered?
• Who can change it?
• What happens when it changes?
• Where should the change be recorded?
• How does another system get updated?

This is where many data problems actually begin.

Be careful when cleaning data

When people discover inconsistent data, the natural reaction is:

"Let's clean it."

Cleaning can help.

But first understand why the data became inconsistent.

Otherwise, you may spend time fixing thousands of records only to find that the same problem starts happening again next month.

For example, if a customer status is regularly wrong, don't just correct the existing statuses.

Ask why.

Maybe there is no clear definition of each status.

Maybe different teams use the same status differently.

Maybe nobody owns the update.

Maybe the information is being copied from somewhere else.

The lasting fix may have very little to do with data cleaning.

Agree what the fields actually mean

Sometimes two people have the same data but use it differently.

Take the word "active".

Does an active customer mean:

• They currently have a signed contract?
• They have used the service recently?
• They have an open invoice?
• Their contract has not yet expired?
• They are expected to renew?

If different people answer differently, your reports can disagree even when the underlying records are correct.

Before trying to fix the numbers, make sure everyone agrees what the numbers actually mean.

A short definition can be more useful than another report.

Watch out for manual copying

One of the clearest warning signs is information being copied between systems.

For example:

CRM → Spreadsheet → Report

Every manual step creates another point where information can be missed or changed.

Sometimes copying is necessary.

But ask whether it needs to happen every week, every day or for every customer.

If the same information is repeatedly moved between places, there may be a better way to handle it.

That could mean using an existing feature in the system.

It could mean changing the process.

It could mean connecting two systems.

Or, in some cases, the copy may simply not be necessary.

Try a simple data check

Pick one number that your business regularly reports.

Let's say it is the number of active customers.

Find the number in three places.

Then choose ten records and compare them.

For each one, ask:

1. Is the customer present in all three places?
2. Is the status the same?
3. Is the relevant date the same?
4. If something is different, which version is correct?
5. Who knows when the information should be updated?
6. Where should the correction be made?

Don't worry about fixing everything yet.

The purpose of this exercise is to find why the information is different.

If you find that the same type of difference keeps appearing, you have found something worth investigating.

Good reporting starts before the report

A report cannot make unreliable information reliable.

A dashboard may look professional, but if the underlying data is incomplete or inconsistent, it can still give you the wrong picture.

This is why reporting problems are often operational problems.

If it takes hours to prepare a simple report, ask what happens before the report is created.

Where does the information come from?

Who maintains it?

How many times is it copied?

How many manual checks are needed?

Where do people make adjustments before the number is considered "right"?

Those questions often reveal more than changing the report itself.

You don't need one system for everything

There is also a difference between having one source of truth and using one tool for everything.

A business can have several systems and still have reliable information.

The important thing is knowing what each system is responsible for.

For example, one system might manage customer records.

Another might handle accounting.

Another might be used for reporting.

That can work perfectly well if the boundaries are clear and information moves between them in a controlled way.

The problem is when nobody knows which system owns which information.

Make one number trustworthy first

You don't need to solve your entire data problem this week.

Choose one important number.

Find all the places it appears.

Understand why the numbers differ.

Agree what the number actually means.

Decide where the source information should live.

Then fix the process that keeps creating the differences.

Once you can trust one important number, move to the next.

That is usually more useful than starting a huge "data cleanup" project without understanding what caused the problem in the first place.

If your team keeps checking different systems to work out which number is right, tell us where work is getting stuck.