A spreadsheet can produce a tidy chart from inconsistent data. Good formatting helps people read the result, but reliability begins with the meaning of each row and column. Before adding more formulas, write a small set of checks that match the decision the spreadsheet supports. These checks are useful even when the workbook is modest.
Define what one row represents
Decide whether each row represents a customer, an order, an order line or an event. Mixing those units can produce misleading totals. A customer appearing on five order rows is not necessarily five different customers. Write the row definition above the data or on an instructions sheet so another person can understand what is being counted.
Choose a stable identifier when one exists. An order number is generally easier to match than a customer's display name. Preserve identifier formatting: a code such as 00127 is a label, not necessarily a quantity. When importing, check that leading zeroes and long identifiers survive the conversion. A changed identifier can make two legitimate records appear unrelated.
Keep column meanings consistent
Give each column one purpose. Record a measurement and its unit clearly, rather than mixing kilograms and pounds in an unexplained “weight” column. Use separate fields when a combined value would be hard to interpret later. If dates cover multiple regions, choose and document an unambiguous representation before collecting more rows.
Treat missing values deliberately. Blank might mean unknown, not applicable or not yet collected; those meanings should not silently collapse into zero. In a delivery log, a missing delivery time is different from a delivery that took zero minutes. Use an explicit status or separate note when the distinction affects your calculation.
Validate entry where practical
Microsoft documents data validation as a way to restrict entries and show prompts or error messages. Use it for predictable fields such as a status list, an allowed range or a required date. The exact rules should reflect the dataset. Restricting a quantity to whole numbers is useful for seats, but inappropriate for measured materials.
Validation helps guide entry; it is not a substitute for checking imported or existing data. Test the rules with valid values, invalid values and blanks. Explain the intended input in ordinary language. A clear message such as “Choose pending, confirmed or cancelled” helps more than a generic error that leaves the person guessing.
Investigate duplicates before removing them
A repeated row may be an error, but similar rows may represent separate legitimate events. Compare the identifier, date and context before deleting anything. Work on a copy when cleaning a dataset and record the rule used. “Removed duplicates” is not enough information for someone who needs to reproduce the result.
For example, two purchases from the same customer on the same day should remain if they have different order numbers. Two imports of the same order may need consolidation. Define the uniqueness rule first, then use the spreadsheet's tools to find candidate duplicates. The human question is what the records mean.
Reconcile and spot-check
Compare counts and totals with a trustworthy reference. If an export claims to contain 120 orders, explain why the workbook has 118 or 122 before using it for reporting. Examine a small sample of source records across different dates and statuses. Include edge cases such as refunds, cancelled orders and unusually large quantities.
Keep raw data separate from cleaned data and calculations. That separation makes it possible to revisit a decision without requesting the export again. Record the source, import date, cleaning rules and known limitations. A useful dataset is one where another person can trace a total back to meaningful rows and understand the checks that support it.
Sources and further reading
This original guide was prepared with AI assistance. Examples are illustrative. The edition date is an assigned date in this 2026 catalogue. See our editorial policy and report a correction.