Master data problems start when records that should identify the same thing no longer agree. A customer appears under several names. A supplier code changes in one file but not another. A SKU is reused for a different product. The workbook may still open, calculate and look familiar, while quotes, orders, reports and approvals are working from conflicting records.
Master data is the set of records reused across business processes, such as customers, suppliers, products, employees, price lists and cost centres. These records need more care than an occasional cleanup because a change can affect several teams at once.
For an operations or commercial manager, the key question is not simply whether there are duplicates. It is whether the business can identify the authoritative record, control changes to it and explain what happened when a record is challenged.
Common Excel Master Data Problems
Excel is useful for modelling, analysis and temporary operational work. It can also support basic controls through tables, data validation, protected sheets, sharing permissions and version history.
Those controls can be helpful, but they need consistent setup and administration. They can become difficult to maintain when a workbook acts as the live master list for several processes, people and locations.
The most common problems are below.
| Problem | What it looks like | Why it matters |
|---|---|---|
| Duplicate records | One customer, supplier or product appears under several names or codes | History, credit checks, orders and reports can be split across records |
| Version drift | Different teams use different copies of a list | Quotes, prices and operational decisions may be based on different information |
| Inconsistent identifiers | Names are typed differently or codes are reused | Lookups, imports and reports can match the wrong record or fail to match at all |
| Weak change control | A record is changed without a clear owner, reason or approval route | Important changes may be hard to check, reverse or explain later |
| Fragile workbook structure | Links, formulas, imports and manual workarounds depend on specialist knowledge | Routine updates become slow, risky or dependent on one person |
These are record-identity problems, not merely duplicate data-entry problems. The issue is deciding which record represents the real customer, supplier or SKU and ensuring other processes use that same identity.
How Duplicate Customers and Broken Codes Develop
A duplicate record usually begins with an understandable local decision.
Sales enters Northgate Ltd to raise a quote. Finance imports Northgate Limited from an accounts export. Operations already has NorthGate Ltd on an older order list. Each entry may look reasonable on its own, but the business now has several records for one customer.
The consequences can spread quickly:
- A report shows incomplete customer history because activity is split between records.
- A lookup finds the first close match rather than the intended record.
- A credit or pricing check is performed against only part of the relationship.
- A team creates another record because it cannot find the existing one.
SKU and supplier-code drift follows a similar pattern. A code may be changed in a purchasing list but remain unchanged in a production planning workbook. Someone may reuse an old code because its original meaning is not visible. A supplier may be renamed following an acquisition, but the old record remains active in another process.
The problem is rarely a lack of effort. It is that the process allows a new identity to be created, amended or copied without a reliable way to check whether it already exists.
What Excel Can Control, and Where Its Limits Appear
A well-managed workbook can reduce master-data risk. For example, Excel can support:
- tables with consistent columns and filters
- drop-down lists through data validation
- conditional formatting to highlight duplicate IDs
- protected cells and sheets to reduce accidental changes
- controlled sharing permissions in Microsoft 365
- version history for shared files
- manual change-log tabs and reconciliation checks
These are worthwhile controls, especially where the workbook is a small, stable tool with clear ownership.
However, their effectiveness depends on how the file is shared, who can edit it, whether protection is managed properly and whether people follow the process around the workbook. Version history may help identify a previous file state, for example, but it may not provide the field-level approval evidence or business context needed for every important master-data change.
Similarly, data validation can discourage inconsistent entry, but it does not by itself resolve existing duplicates or establish which of two conflicting records should survive. A manually maintained change log can be useful, but it relies on people completing it accurately and consistently.
The practical distinction is this:
Spreadsheet controls can reduce risk. A business-critical master-data process may need controls that are built into the way records are created, approved, changed and used.
Practical Excel Fixes to Apply Now
If Excel remains the current system of record, improve the control environment before attempting a large cleanup. The aim is to stop new inconsistencies while the existing records are reviewed.
1. Create one master list for each entity
Keep customers, suppliers and products in clearly defined master tables. Make it clear which table is authoritative and which sheets only use or report on that data.
Avoid maintaining separate “official” lists for the same entity in sales, finance and operations. Each team may need different fields, but they should work from the same core identifier.
2. Use permanent IDs rather than names as keys
Customer names, supplier names and product descriptions can change. Use a stable ID for matching, reporting and importing wherever possible.
A name can remain visible to users, but it should not be the only way a process identifies a record. Define how IDs are created and make sure old IDs are not reused without a deliberate decision.
3. Apply validation to routine entry points
Use controlled lists for fields such as status, currency, product family or account manager where a defined set of values is appropriate.
For customer, supplier and SKU selection, use the master list rather than free-text entry where the workflow allows it. Review the list regularly so that validation does not become a barrier to legitimate new records.
4. Flag duplicates for review
Set up a visible duplicate check for permanent IDs and consider separate checks for close name matches. A duplicate flag is not an automatic instruction to delete a row. Someone needs to confirm whether the records represent the same real-world entity.
When records are merged, document the surviving record, the reason for the decision and how related transactions should be handled.
5. Separate imports from approved master data
Keep raw imports separate from the approved master table. Review new, changed and unmatched records before they are added to the authoritative list.
This helps prevent a one-off export, supplier upload or copied list from silently creating duplicates in the live master.
6. Protect formulas and structural fields carefully
Protect calculation areas and structural fields where appropriate, while ensuring the people responsible for maintenance can still carry out their work.
Protection should support a documented process, not replace one. If a person needs to change a controlled field, the business should know who is permitted to do so and what evidence is needed.
7. Keep a useful change record
For higher-impact fields, record the date, person, record, field changed, previous value, new value and reason. This is particularly relevant for information such as trading terms, supplier payment details, product status and price lists.
A change record is only useful if it can be reviewed and linked to the business decision behind the change.
8. Reconcile after imports and major updates
Check record counts, unique IDs, unmatched entries and important control totals against the source before the data is used in downstream reporting or operations.
A reconciliation will not catch every data-quality issue, but it gives the team a repeatable point at which to identify unexpected changes.
Give Each Master Field an Owner
A clean spreadsheet can become unreliable again if responsibility is unclear. Master-data governance works best when ownership is assigned at field level rather than treating an entire customer or supplier record as one undivided responsibility.
For example, finance may own legal name and trading terms, sales may maintain contact details, and operations may maintain delivery notes or operational instructions.
| Master field | Typical owner | Change consideration |
|---|---|---|
| Customer legal name and registration details | Finance | Check against reliable business documentation |
| Trading terms and credit information | Finance or credit control | Follow the business approval process |
| Product description and active status | Commercial or product owner | Confirm the effect on orders, reports and planning |
| Supplier payment details | Procurement or finance | Use a controlled verification process |
| Delivery and operational notes | Operations | Confirm that the change is current and actionable |
The exact roles depend on the business. What matters is that people know who owns the accuracy of a field, who may request a change and how an exception is resolved.
Three rules are particularly useful:
- Use effective dates for information that changes over time, such as prices and trading terms.
- Define survivorship rules for duplicates, including which record should remain active and how conflicting values are resolved.
- Review exceptions regularly, including unmatched imports, suspected duplicates and pending changes.
When a Managed Web App May Be a Better Fit
A move away from Excel should not be based only on company size, workbook size or number of editors. A single-user process can still need reliable approvals, field-level accountability, integration with other systems or continuity when the current owner is unavailable.
Equally, a shared workbook with several users may remain suitable if the process is low risk, stable and well controlled.
Consider a managed web application when the master-data process needs capabilities such as:
| Requirement | Excel may remain suitable when | A managed web app may be appropriate when |
|---|---|---|
| Record identity | IDs and duplicates can be reviewed reliably as part of a simple process | The process needs consistent record creation and duplicate prevention across workflows |
| Permissions | Basic sharing and workbook protection meet the operational need | Different roles need controlled access to particular actions or information |
| Approvals | Changes are infrequent and a documented manual process is workable | Changes need a repeatable workflow with accountable approval steps |
| Auditability | Version history and a maintained log provide sufficient evidence | The business needs a durable record of changes linked to users, records and workflow actions |
| Integrations | Imports are occasional and can be checked manually | Data must move reliably between quoting, orders, finance, production or other operational processes |
| Continuity | The workbook is understood and maintainable by the people responsible for it | The process depends heavily on one person or hidden workbook knowledge |
The decision is about the control and workflow required, not whether the spreadsheet is “big enough” to deserve replacement.
A managed application can be designed around the actual master-data process: a defined customer or SKU record, controlled fields, responsible roles, approval steps, exception handling and connections to the downstream workflows that use the data.
For a broader view of the decision, see when to replace Excel with a web app.
A Sensible Way to Plan the Change
If the master list has become operationally important, avoid treating replacement as a file-conversion exercise. The first task is to understand the rules currently hidden in the workbook and in the team’s day-to-day habits.
A practical sequence is:
- Identify the authoritative source for each customer, supplier and product field.
- List duplicate records, obsolete codes, manual imports and recurring exceptions.
- Map who creates, changes, approves and uses each important field.
- Decide which controls can be improved safely in Excel now.
- Identify the workflow requirements that need a more durable managed system.
- Plan a staged transition, retaining the existing workbook as a controlled reference until records and processes have been checked.
Spreadsheet Upgrade builds and manages custom web applications for business-critical operational spreadsheet workflows. For master data, that can mean designing a controlled source of customer, supplier or product records around the way your teams actually quote, order, plan and report.
Start with a Free Fit Check
If duplicate customers, drifting SKUs or conflicting supplier records are affecting day-to-day operations, the normal first step is a no-cost conversation about whether the workflow is suitable for replacement.