Living on the edge: Spreadsheets and data reconciliation

It starts with one spreadsheet built to compare positions, transactions, or cash balances from two sources. New data feeds, recurring adjustments, exception tracking, and approval steps add more formulas, tabs, dependencies, and manual controls to the original file.

Macros, formulas, and new tabs are added to support each requirement, leaving one person with most of the knowledge needed to understand its logic and dependencies.

The spreadsheet is now essential to daily operations, even though nobody deliberately chose it as a business-critical system. The risk grows, as a practical tool takes on more data, logic, and responsibility than it was designed to manage.

Spreadsheet reconciliation is the use of tools such as Microsoft Excel to compare records from two or more sources, identify differences, and document how those differences were resolved.

Why Financial Teams Continue to Reconcile in Spreadsheets

Financial teams continue to use spreadsheets for reconciliation because they solve an immediate problem with very little friction.

Teams tend to already have access to Excel, know how to use it, and can build a new reconciliation without buying software or waiting for a development team. Formulas, matching rules, tolerances, and data layouts can also be changed quickly as requirements evolve, which makes spreadsheets genuinely helpful when a process is small or newly introduced.

Existing spreadsheet reconciliations could also appear to be working well. Reports are completed, breaks investigated, and deadlines met. Thus, replacement often falls behind projects tied to new regulations, client demands, system upgrades, or business growth.

But the same flexibility that makes a spreadsheet quick to build can weaken control as the process expands. Additional data sources, users, formulas, macros, and manual steps increase its complexity, while poorly documented changes leave the process dependent on the people who understand how it developed.

The reconciliation can keep producing results even after its controls have stopped providing adequate assurance. “It has not failed yet” cannot therefore be evidence that the reconciliation is accurate, resilient, or adequately governed.

When Is Spreadsheet Reconciliation Still Appropriate?

Spreadsheets remain practical for narrow, low-volume, temporary, and low-risk reconciliations. Common examples include a one-off investigation, a control used during a system transition, or a piece of non-critical analysis.

They can also support a tightly controlled process with few users, clear ownership, documented checks, and regular review.

The decision to use a spreadsheet should ultimately depend on the demands of the reconciliation. Teams need to consider the data volume, matching complexity, processing frequency, number of users, and speed at which exceptions must be resolved.

Financial materiality and regulatory importance are also important, as is the extent to which the process relies on macros, manual workarounds, or knowledge held by one person.

A quarterly reconciliation covering limited data places far fewer demands on a spreadsheet than a daily process spanning several portfolios and data sources. Recurring, material, complex, or time-sensitive reconciliations demand stronger evidence that the spreadsheet controls remain effective.

Six Risks of Spreadsheet-Based Reconciliation

1. Formula and Control Changes Can Go Undetected

Spreadsheet logic is easy to alter. Accidental edits and intentional process changes alike can overwrite formulas, modify macros, widen tolerances, or exclude rows from the selected data range.

The real risk lies in whether the process preserves a clear record of the change.

Teams struggle to establish what changed, who changed it, when the change was made, whether it was reviewed, and which previous reconciliations may have been affected. Version history, even when available, might struggle to explain the business reason behind a change.

Automated reconciliation software does not remove the possibility of mistakes. A governed system can, however, ensure that rule changes, approvals, overrides, and user actions are easier to track and review.

2. Version Conflicts Weaken the Audit Trail

Spreadsheet reconciliations are usually shared through email, local downloads, shared drives, and messaging platforms.

Before long, several copies could exist, each with a slightly different name: “final”, “final_v2”, or “final_v7_revised”. The latest source data may be stored in one file, the completed investigation in another, and the approved version in a third.

This creates a difficult question: which file represents the official reconciliation?

A clear audit trail identifies the file used, confirms the completeness of the source data, records the exceptions found and the actions taken, and captures the final review and approval.

The spreadsheet records the closing balance and resolved breaks, while the decisions, adjustments, reviews, and approvals behind them remain scattered across emails, notes, and separate files.

3. Complex Matching Logic Becomes Difficult to Maintain

Basic row-by-row comparisons are sufficient when two files share the same structure and every record has a clear counterpart.

Investment operations, however, involve far more complex matching relationships.

Investment reconciliation involves matching one transaction to several records or combining several trades against a single settlement entry. The rules must also account for identifiers, timing differences, currencies, partial settlements, fees, rounding, and agreed tolerances.

Nested formulas, lookup functions, macros, and separate tabs leave the matching logic cumbersome to maintain and harder to review. Cash proofing, position, NAV, P&L, cost-basis, and total-equity reconciliation all depend on matching logic that remains clear, testable, adjustable, and maintainable.

Even a minor update can require someone to trace formulas across several worksheets, making the process harder to review, explain, and control.

4. Volume and Time Pressure Magnify Small Weaknesses

Spreadsheet-led reconciliation works smoothly while volumes remain manageable and the process covers a limited number of portfolios, custodians, and transactions

As the business expands, the spreadsheet has to support more transactions, portfolios, custodians, asset classes, and intraday runs. The team must then collect, prepare, compare, and investigate a much larger volume of data.

Large files slow down or become unstable. Manual data preparation takes longer, while more frequent reconciliation runs reduce the time available to resolve exceptions. Concurrent work by several team members increases the risk of version conflicts and duplicated effort.

Compressed settlement cycles and tighter reporting deadlines leave less time to identify, investigate, and resolve breaks. Issues now require resolution before settlement, valuation, or reporting cut-offs instead of the following morning.

5. Critical Knowledge Becomes Concentrated in One Person

Many spreadsheet reconciliations depend on knowledge that has never been fully captured.

Critical process details remain undocumented, including the order in which macros run, the causes of failed data imports, the rules for manual overrides, the treatment of known exceptions, the links between files, and the workarounds used at month-end.

Reliance on this concentrated knowledge exposes the process whenever the usual owner is unavailable, whether because of annual leave, illness, a role change, a demanding reporting period, or permanent departure.

Another team member might be able to open the file and run the process, but that does not mean they can explain the logic, identify a silent failure, or safely change the reconciliation.

6. Demonstrating Compliance Becomes Harder

Compliance depends on the controls surrounding the process and the firm’s ability to show that each spreadsheet-led reconciliation was completed consistently, accurately, and under appropriate review.

A defensible audit history records the source data used, the exceptions identified, the adjustments authorised, the reviewers involved, and the controls applied at each stage of the reconciliation.

Spreadsheets can support some of these requirements, particularly when the process is tightly controlled and well documented. The burden of maintaining that evidence, however, generally falls on manual checklists, file-naming rules, emails, and local procedures.

As the reconciliation becomes more important, complex, or frequent, proving that those controls worked can turn almost as demanding as performing the reconciliation itself.

How to Move Reconciliation Away From Spreadsheets

Moving reconciliation away from spreadsheets does not require replacing every process at once. A phased approach allows teams to understand the current process, prove the value of automation, and reduce operational risk during the transition.

1. Inventory Spreadsheet-Led Processes

Start by documenting where spreadsheets are used and how each reconciliation works. The inventory should capture process ownership, data sources, frequency, transaction volume, matching logic, manual adjustments, exception volumes, downstream dependencies, and review and approval requirements.

This exercise reveals hidden dependencies, duplicated work, and controls that rely on local knowledge instead of formal documentation.

2. Prioritise by Risk and Operational Value

Not every spreadsheet reconciliation needs to be replaced immediately. Prioritise processes that combine high data volumes, complex matching logic, material balances, frequent breaks, or time-sensitive resolution. Weak auditability, heavy manual effort, and reliance on one person should also increase the priority.

The best pilot is not always the largest or most visible reconciliation. Choose a process that is important enough to demonstrate measurable value, but contained enough to test safely without disrupting a critical reporting or settlement cycle.

3. Configure and Run a Controlled Pilot

Translate the spreadsheet’s formulas, tolerances, and manual decisions into documented matching rules. Run the spreadsheet and the automated process in parallel for an agreed period. Compare the outputs, investigate any differences, refine the rules, and confirm that all source data is being captured correctly.

The pilot should also give users time to learn the new process and clarify who owns each type of exception. Parallel testing helps the team build confidence before the spreadsheet is retired.

4. Formalise Exception and Approval Workflows

Modernisation should strengthen matching, exception handling, approvals, escalation, and auditability across the entire reconciliation process.

Every exception should have a named owner from the moment it is identified. The workflow should record how the break was classified, when it was escalated, who changed the record, who approved the change, and what evidence supported the decision.

Clear ownership, escalation rules, approval requirements, and evidence standards create a workflow that is consistent and easy to review.

5. Scale and Retire the Spreadsheet Deliberately

Once the pilot has been validated, expand the platform to additional portfolios, data sources, and reconciliation types. Assign clear ownership for rule maintenance, user access, performance monitoring, and future changes. Review the new process regularly as volumes and requirements develop.

Finally, retire the old spreadsheets. Leaving them available as unofficial fallback tools can create duplicate processes, conflicting results, and uncertainty over which system should be trusted.

What To Look For In Reconciliation Software

Reconciliation software should fit the way your operations team works, the data it handles, and the controls the business needs. A long feature list is of less significance than whether the platform can support your actual reconciliation processes.

Flexible Data Ingestion

Reliable data ingestion depends on direct connections to internal systems, custodians, prime brokers, banks, administrators, and other counterparties, along with support for different file formats, layouts, and delivery methods. This reduces the manual preparation required before reconciliation begins.

Configurable Matching

Look for support for exact, tolerance-based, one-to-many, many-to-many, and multi-field matching. Operations teams should be able to configure rules for timing differences, currencies, identifiers, fees, rounding, and partial settlements.

Controlled Exception Workflows

Identifying a break is only the beginning. The platform should assign ownership, track progress, support escalation, record comments, and preserve the full investigation history. Teams should be able to see which exceptions remain open, how long they have been outstanding, and who is responsible for the next action.

Complete Auditability

A complete audit trail links every change in data, matching rules, exceptions, adjustments, and approvals to the person who made it, the time it occurred, and the reason behind it.

Financial Services Support

The software should accommodate the reconciliations your team performs, including cash, transactions, positions, NAV, cost basis, P&L, and total equity. Generic comparison tools may struggle with the structures and relationships involved in these processes.

Scalability And Accessible Configuration

The platform needs enough capacity for rising data volumes, additional portfolios, and daily or intraday processing. Routine changes to rules and workflows should remain with operations teams, supported by controlled access and approval requirements.

Implementation And Ongoing Support

Consider the support available for migration, data connections, rule configuration, testing, training, and ongoing operation. Some firms may also benefit from managed reconciliation services when they lack the internal capacity to run or modernise the process themselves.

Gresham’s reconciliation solutions, including Control Cloud for Investment Management and its cash and stock reconciliation capabilities, are built around these requirements.

The Business Case Is About Control (Not Merely Efficiency)

The case for automated reconciliation should not rest only on the number of hours saved. Efficiency is crucial, but the wider value comes from stronger control over the process. This provides teams with a clearer view of outstanding issues, their owners, how long they have remained open, and whether the required approvals are complete.

A controlled platform creates stronger audit evidence and applies the reconciliation process consistently across portfolios, teams, and data sources. Matching rules, workflows, and responsibilities remain documented within the system, giving team members a clear reference during onboarding and knowledge transfer.

As the business expands, reconciliation needs to accommodate additional portfolios, counterparties, and data sources without forcing headcount to grow in line with transaction volume.

In one engagement, we helped a global asset manager reduce reconciliation processing from hours to minutes while improving accuracy and expanding data coverage.

Speed was only part of the value - the faster process gave the operations team more time to investigate genuine exceptions and stronger control over a larger and more complex reconciliation environment.

Final Word: Know When the Spreadsheet Has Outgrown Its Controls

A spreadsheet will remain a practical tool until the reconciliation process stays within the limits of its controls. Risk emerges once greater complexity, frequency, and operational responsibility push the process beyond those confines.

Familiarity can conceal weakness simply because the process still produces an output. Changes become harder to explain, exceptions harder to track, reviews harder to evidence, and continuity increasingly dependent on the usual owner.

Investment managers should assess spreadsheet-led reconciliations before a failed control, missed cut-off, audit finding, or employee departure forces the decision. The right time to modernise is while the process is still understood and manageable.

See how Gresham helps investment managers replace spreadsheet-led reconciliation with controlled, scalable workflows.

Speak To An Expert