Data Validation and Reconciliation in Excel Explained

Data validation controls what information can be entered in a spreadsheet; data reconciliation checks whether records or totals from different sources agree. In Excel for Microsoft 365, validation helps prevent common entry errors, while reconciliation helps find missing, duplicated, or mismatched data after it has been collected.

What data validation and reconciliation mean

These terms describe related but distinct parts of data quality. Validation is a rule applied to data, often as it is entered or imported. For example, a form might allow only dates in a specified period, whole numbers from 1 to 100, or values selected from an approved list. The rule can prevent an invalid entry or display a warning, depending on how it is configured.

Reconciliation is a comparison process. It checks whether two sets of records, or two ways of calculating a result, correspond as expected. A business might compare an order export with an invoice file, or compare a bank statement with transactions recorded in its books. Reconciliation does not necessarily mean that every row is identical: the process may need to account for timing differences, grouped transactions, fees, rounding, or different identifiers.

A useful distinction is that validation asks, “Does this value meet the rule?” Reconciliation asks, “Does this record or total agree with the other source, and if not, why?” Validation can reduce errors before they spread, but it cannot prove that the information is true. A syntactically valid customer number, for instance, can still belong to the wrong customer.

Why the two processes matter

Errors can enter a workbook through manual typing, copy and paste, imported files, inconsistent naming, outdated reference lists, or changes made after an initial check. A dropdown may prevent someone from typing “In progress” when the approved status is “In Progress,” but it cannot establish whether the selected status is factually correct. Likewise, matching totals do not guarantee that every individual transaction is present: one missing amount and one duplicate amount can cancel each other out.

Using validation and reconciliation together provides stronger controls. Validation makes the intended structure clearer and catches some errors at the point of entry. Reconciliation checks actual records against another source or an independent expectation. Both are especially useful when a workbook informs reporting, inventory tracking, billing, payroll preparation, or operational decisions. They support careful review; they do not replace the review or establish that a source is authoritative.

Set up data validation in Excel

In Excel for Microsoft 365 on Windows, select the cells to control, then choose Data > Data Validation. On the Settings tab, choose a rule such as Whole number, Decimal, Date, Time, Text length, or List. Enter the permitted values or limits. The Input Message tab can show guidance when a cell is selected, and the Error Alert tab controls the response to an invalid entry.

For example, to offer a status dropdown, put the approved choices in a range such as H2:H5, select the cells for status entry, and open Data > Data Validation. Set Allow to List and use the approved range as the source. A list stored on a worksheet is easier to maintain than typing choices into the Source box, especially when several worksheets use the same options. If the source list changes, check that the validation source still covers the full list.

Choose the error-alert style to suit the situation. A Stop alert blocks an entry that fails the rule. Warning lets the user proceed after confirming, while Information displays a message but does not impose the same restriction. A blocking rule can be appropriate for a required category; a warning may be more practical when legitimate exceptions occur. Validation can also be bypassed in some workflows, including by pasting data, so do not assume a rule has checked every value in a populated range.

To remove a rule, select the affected cells, open Data > Data Validation, and choose Clear All in the dialog. To locate cells with validation, use Home > Find & Select > Data Validation. Excel for Mac offers the Data Validation feature through its Data tab, though the dialog layout can differ. Excel for the web supports common validation tasks, but some workbook features and editing options can vary; if a setting is unavailable, use the desktop app.

Use validation rules thoughtfully

A good rule reflects the real data requirement rather than an assumption about what users should enter. If a delivery date must fall between two known dates, use a Date rule with those bounds. If a quantity must be a positive whole number, use Whole number and set the minimum to 1. If blank cells are allowed during drafting, account for that explicitly rather than unintentionally rejecting them.

Custom validation formulas can express more specific checks. With a date in A2 and a rule applied starting at A2, a custom formula could be =AND(ISNUMBER(A2),A2>=DATE(2026,1,1),A2<=DATE(2026,12,31)). This accepts a numeric Excel date from 2026; the range should be changed to match the actual business rule. When applying a custom formula to multiple rows, use references that adjust by row, and check that the formula is written relative to the top-left cell in the selected range.

Validation rules should be documented and tested with an allowed value, a disallowed value, and a boundary case. Also consider how imported data will be checked. A rule applied to a cell is not a substitute for checking a pasted column, and a valid dropdown choice may become outdated if the underlying process changes. For dependable workbooks, combine entry controls with a later review of the complete data set.

How reconciliation works

A reconciliation begins by defining what is being compared and what counts as a match. Identify the source files, the period covered, the key fields, and any expected differences. A transaction ID is usually a better matching key than a customer name because names can vary in spelling or formatting. If no unique identifier exists, a combination of fields—such as date, account, and amount—may be needed, with clear rules for ambiguous matches.

Prepare both sources. Confirm that the data covers the same period and that columns have consistent types and meanings.
Choose a matching key. Prefer a stable identifier, and check whether it is unique in each source.
Compare records. Identify items found in one source but not the other, as well as duplicates and field-level differences.
Compare totals where useful. Check counts and sums, but treat them as supporting checks rather than proof that every row matches.
Investigate exceptions. Record the cause, decision, and any correction without silently changing source data.

In Excel, filters, conditional formatting, PivotTables, and formulas can all help. The right method depends on the size and structure of the files. A small one-off check may need only a formula and a review of exceptions; recurring reconciliations are usually more reliable when the process is repeatable and its assumptions are written down.

Compare two lists with an Excel formula

Suppose the first source has transaction IDs in A2:A100, and the second has IDs in D2:D100. With the first source’s IDs in A2:A100 and the comparison IDs in D2:D100, enter this in C2 and fill it down to label each first-source ID as found or missing:

=IF(COUNTIF($D$2:$D$100,A2)=0,"Missing","Found")

If A2 contains T104 and T104 appears in D2:D100, the result is Found; if it does not appear, the result is Missing. This checks whether an ID occurs at least once, not whether the number of occurrences is correct. To check for duplicates in the second source, inspect repeated keys separately, for example by sorting the IDs or using a PivotTable. Also confirm that both sources use the same identifier format: a number stored as text may not behave as expected in some comparisons.

Amounts can be compared after matching the key, but the comparison rule needs to reflect the data. Currency values may differ by a small rounding amount, and a single source record may correspond to multiple entries in another source. A tolerance should be justified, not chosen merely to make exceptions disappear. Where transactions are grouped, compare their appropriate aggregate and retain enough detail to trace the result back to individual records.

Totals, exceptions, and common problems

Control totals provide a useful second view. With amounts in B2:B100, the total is calculated as =SUM(B2:B100). If the rows contain an illustrative 12, 18, and 20, the result is 50. Before interpreting a difference between sources, make sure both totals cover the same dates, include the same transaction types, and use the same sign convention. A total that agrees is reassuring, but it does not show that the underlying rows are identical.

Several common problems can make a reasonable comparison appear to fail:

Extra spaces or inconsistent text: “North” and “North ” may look alike but differ as text. Clean a copy of the data and verify the result rather than editing the original source without a record.
Different date or number types: One file may hold dates as text, or amounts may include currency symbols as text. Standardize types before comparing.
Duplicate or non-unique keys: A repeated ID can make a simple found/not-found test misleading. Establish whether repeats are legitimate and compare occurrence counts or transaction detail.
Different coverage or timing: A payment initiated before the period ends may appear in one system but not another. Document the cutoff and review timing differences separately.
Rounding and aggregation: Comparing a rounded report total with unrounded transaction detail can produce a small difference. Agree on the level of precision and grouping before investigating.

Keep an exception list that includes the key, the amount or field at issue, the source, the reason if known, the action taken, and the reviewer or date where appropriate. This makes the result explainable later. Avoid “fixing” a mismatch by changing an imported figure simply to force the totals to agree; trace the discrepancy to its source and preserve an audit trail that suits the work’s requirements.

Choosing a method and maintaining the process

For a one-time comparison of two modest lists, ordinary Excel formulas and filters may be sufficient. For recurring imports with consistent columns, Power Query can help create a repeatable transformation and comparison workflow. It is available in Excel for Microsoft 365, though available connectors and interface details can depend on the platform and account. Power Query does not decide what constitutes a valid match: the user still has to specify keys, transformations, and how exceptions should be handled.

Choose a method based on the data volume, frequency, risk of error, and need to explain results. A manual check may be understandable for a small file but difficult to reproduce every month. A formula-based workbook can be transparent if inputs, formulas, and exceptions are clearly separated. For sensitive or regulated processes, follow the organization’s approved controls and retention requirements rather than treating a spreadsheet technique as a compliance guarantee.

Finally, test the process when source formats or rules change. Keep a clean copy of original inputs, label the reporting period, note the matching logic, and have another person review material exceptions when the consequences warrant it. These steps help distinguish a real discrepancy from a formatting issue and make the reconciliation repeatable.

Frequently Asked Questions

What is the difference between data validation and data reconciliation?
Validation checks whether a value meets a defined rule, such as being a date or an approved status. Reconciliation compares records or totals from separate sources to find agreement and explain differences.

Does Excel data validation check existing entries?
It is primarily an entry control, not a complete audit of a populated range. Pasting or importing data can leave values that do not follow the intended rule, so review existing entries separately.

Can two matching totals prove that data reconciles?
No. Different errors can offset each other and produce the same total. Compare records as well as useful control totals, and investigate exceptions.

What is the best key for reconciling two files?
A stable, unique identifier such as a transaction ID is generally preferable to a name. If there is no unique key, define a combination of fields and a process for ambiguous or duplicate matches.

How often should data be reconciled?
There is no universal schedule. Frequency depends on how quickly the data changes, the consequences of an undetected error, and the needs of the process. Set a documented schedule that fits those factors.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.