Clean donor spreadsheets without losing the story

Cleaning a donor spreadsheet means making its records consistent enough to review and use while preserving the original meaning. The work includes separating people from transactions, standardizing fields and resolving uncertain matches with evidence.

Start with a copy. Keep the original export, its source, the date it was produced and the filters used. A tidy file is not useful if nobody can explain why a gift disappeared or a communication preference changed during cleanup.

Decide what each row represents

Identify whether the file contains donors, gifts, event attendance or a mixture. A donor with five gifts may legitimately appear on five rows. Removing repeated names would delete giving history, not just duplicates.

Keep identity fields and transaction fields distinguishable. Preserve original donor IDs and transaction IDs as text so leading zeros survive. Record source names consistently. If multiple files use the same ID format, include the source as part of the reference instead of assuming the number is globally unique.

Create a small field dictionary before making changes: the column name, what it means, its format and whether a blank means unknown or not applicable. A blank contact preference must never silently become permission to contact.

If the file will feed a particular product, check that product's mapping instructions. Relae's spreadsheet import guide describes the product workflow; this article covers the preparation work that comes before it.

Standardize formats while preserving originals

Work in new columns where a change could obscure evidence. Keep the original name alongside any cleaned display name until review is complete. Separate address components where the receiving system expects them, but do not invent missing postal codes or countries.

Use unambiguous dates and confirm the source's date convention. A value such as 04/05/2026 is ambiguous without context. Amount columns should distinguish gross gifts, fees, refunds and payouts. Keep currency explicit, and treat reference numbers as text rather than money.

Check spreadsheet settings before saving. They can transform long identifiers, dates and leading zeros without an obvious warning. Inspect the exported CSV as well as the workbook. When opening untrusted files, use your organization's approved tools and treat cells that begin with formula characters as potentially executable spreadsheet input.

Do not normalize away meaningful information. A preferred name, an organizational donor and a household record may need different treatment. Preserve the source value until the team agrees what the new structure represents.

Review duplicates with a decision trail

Create candidate groups using reliable identifiers and combinations of fields. A matching email can be useful evidence, but it can also belong to a shared household or an administrative contact. Similar names alone should prompt review.

For each proposed merge, record which records were involved, the evidence, the decision and the reviewer. Choose which value wins when fields disagree. Retain transaction references so repeated imports can be detected later.

The Fundraising Regulator's guidance on documenting decisions is a UK reference on recording fundraising decisions. The operational lesson here is to make important changes explainable. This guide's cleanup workflow is a recommendation, not a statement of a worldwide legal requirement.

A fictional example: two people share an address

In this fictional example, River Museum has two donor rows, one for Lee Morgan and one for Pat Morgan. They share a postal address but have different donor IDs and separate gifts. A third row, “L. Morgan,” has the same transaction reference as one of Lee's existing gifts.

The shared address does not justify merging Lee and Pat. The repeated transaction reference is evidence that the third row may be a duplicate export of Lee's gift. The reviewer checks the source, marks the repeated transaction and records the decision while preserving both people's separate histories.

If the evidence remains uncertain, keep the records separate and flag the question. An unresolved item is easier to investigate than an incorrect merge that has already spread into reports.

Test a sample and compare the result

Use a representative approved sample before a large import. Include an ordinary gift, a refund, a duplicate candidate, an organizational donor and a record with a contact restriction. Review how the receiving system maps each one.

Compare record counts and monetary totals by currency and transaction status before and after cleanup. Explain every reduction in rows. A valid duplicate removal, a filtered failed payment and an accidentally dropped gift are different outcomes.

Relae provides matching review for uncertain donor links. Verify the preview and source mappings before committing an import, then inspect the resulting records. Do not assume a clean-looking preview proves that all values have the intended meaning.

Finish with a change log, an exception list and a named owner for remaining questions. Use the reconciliation guide to connect the prepared file to source totals, then the weekly follow-up guide to put the improved records to work. The point of cleanup is a stronger basis for decisions and relationships.