Test Excel Data: Practical Checks for Accuracy and Errors

How-to basics · Updated November 18, 2026

To test Excel data, check for missing values, duplicates, invalid entries, and formulas that do not match the underlying records. In Microsoft Excel for Microsoft 365 on Windows, combine filters, Data Validation, conditional formatting, and a few worksheet formulas; together, these checks can reveal problems before you sort, summarize, or share a workbook.

What It Means to Test Excel Data

Testing data means checking whether the values in a worksheet are complete, consistent, and plausible for their intended use. A list of customer records, for example, might need a unique customer ID on every row, valid dates, and a properly formatted email address. A sales table might also need nonnegative quantities and totals that agree with quantity multiplied by unit price.

There is no single Excel command that confirms an entire dataset is correct. Some errors are visible, such as a blank cell or repeated ID. Others require business context: Excel cannot tell whether a date is reasonable for a particular project unless you define the permitted date range. A clean-looking worksheet is not necessarily accurate, so decide what rules the data must meet before testing it.

It helps to distinguish two related tasks:

  • Validation limits what people can enter, such as requiring a whole number between 1 and 100.
  • Testing checks existing values, including data pasted into the sheet or imported from another system.

Use both when possible. Validation can prevent some future mistakes, while tests can find issues already present.

Prepare a Safe, Consistent Dataset

Start by preserving the original data. Save a separate copy of the workbook or duplicate the worksheet before making changes, especially before removing records or replacing values. Then check that each column has one clear meaning and that the first row contains useful headers. A column that mixes dates, notes, and amounts is much harder to test than separate, consistently defined columns.

For a structured list, select a cell in the data and press Ctrl+T on Windows to create an Excel table. Confirm whether the selection includes headers. Tables make it easier to filter records and extend formulas when rows are added. On a Mac, use Insert > Table or the Table command on the ribbon; the exact ribbon layout can vary by Excel version.

Before checking values, decide which rows and columns count as records. A title above the list, a totals row, or notes beneath it should not accidentally be included in a test range. If an imported file has extra spaces, inconsistent capitalization, or dates stored as text, note those as potential issues rather than assuming the worksheet has interpreted every value correctly.

Find Blank Cells and Missing Records

Filters are a quick way to inspect blanks in one column. Select a cell in the table, choose Data > Filter, open the filter arrow for the column, and select the blank entry if it appears. This reveals records missing that particular field. Repeat for required columns; a blank middle name may be acceptable, while a blank order ID may not be.

For a count across a small section, with required fields in A2:D5 and exactly one missing cell in that range, enter =COUNTBLANK(A2:D5) in an empty cell. The result is 1. COUNTBLANK counts empty cells in the specified range; it does not decide whether those blanks are errors. A cell containing a formula that returns an empty string can also require separate interpretation, so inspect the underlying formulas when the count seems surprising.

To test for missing whole records, check a field that should always be present, such as a record ID. A completely empty row may not appear in a filtered list if it falls outside the table, so inspect the table boundaries as well. A missing row is different from a row with a missing value: the latter can be detected within the dataset, but identifying an absent expected record may require comparing the sheet with a source list.

Check Duplicates, Data Types, and Valid Ranges

Duplicate values are not automatically errors. Two people can share a surname, and two orders can have the same date. Test uniqueness only for fields that are meant to identify one record, such as an invoice number. To highlight repeated values, select the relevant cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Review the highlighted records before deleting anything.

For a formula check, assume IDs are in A2:A6, with the values 101, 102, 103, 102, and 104 in that order. In a helper cell on row 5, enter =COUNTIF($A$2:$A$6,A5)>1. Because A5 contains 102 and that ID occurs twice, the result is TRUE. Copy the formula down to test each row. A TRUE result identifies a repeated ID, but it does not determine which record should be kept.

Check that values have the right type and fall within sensible limits. With a score in B2, where B2 contains the number 87 and acceptable scores run from 0 to 100, enter =AND(ISNUMBER(B2),B2>=0,B2<=100). The result is TRUE. If the score is stored as text, ISNUMBER returns FALSE even if the cell appears to show digits. With a date in C2, to accept dates from January 1 through December 31, 2025, enter =AND(ISNUMBER(C2),C2>=DATE(2025,1,1),C2<=DATE(2025,12,31)). A real date within that range passes; text that merely looks like a date does not.

Verify Formulas and Totals

In calculated columns, test whether each result follows the expected rule. Suppose quantity is in B2, unit price in C2, and line total in D2. If B2 is 3, C2 is 4.50, and D2 is 13.50, enter =D2=B2*C2 in a helper column. The result is TRUE because 13.50 equals 3 multiplied by 4.50. Copy the check down the records and investigate FALSE results. Differences can point to an incorrect formula, a manually overwritten value, or a rounding rule that needs to be specified.

Totals provide another useful cross-check. With amounts of 12, 18, and 20 in B2:B4 and an independently recorded expected total of 50 in E2, enter =SUM(B2:B4)=E2. The result is TRUE because the three amounts add to 50. This comparison is useful only if E2 is genuinely independent; if it is calculated from the same values, it cannot reveal an error shared by both calculations.

When a formula error appears, inspect the formula bar and check whether the formula was copied consistently. Common spreadsheet errors include references pointing to the wrong row, numbers stored as text, and formulas that omit the final record. A result that looks reasonable is not proof that its formula is correct.

Use Excel Tools to Review Results

Conditional formatting can make exceptions easier to scan. In addition to highlighting duplicates, you can apply color scales to identify unusually high or low values. Use these visual cues to prioritize review, not as a substitute for a defined rule: a low sales amount may be valid, while a value outside a stated range may not be.

Sorting and filtering help you inspect questionable records. Choose Data > Filter, then filter a column for blanks, a particular category, or a numeric condition. When sorting, include the complete table so values stay attached to their records. Sorting just one column can scramble the relationship between IDs, names, and amounts.

A PivotTable can summarize records by category and help expose unexpected counts or totals. Select a cell in the table and choose Insert > PivotTable, then place a category in Rows and an ID or amount in Values. Check whether Excel is counting or summing the field as intended. A PivotTable is a summary, not a proof of correctness: incorrect source values will still feed into its results.

Prevent New Errors with Data Validation

For future entries, select the cells to control and choose Data > Data Validation. In the Settings tab, choose a suitable rule, such as Whole number, Decimal, Date, or List. You can set permitted limits, provide an input message, and configure an error alert. For example, a score column could allow whole numbers from 0 through 100. Validation can reduce accidental entries, but pasted data or other import processes may bypass or replace expected controls, so continue to test the completed dataset.

For a category column, a validation list can restrict entries to approved choices such as Open, Pending, and Closed. This avoids variations such as pending, Pending , and In progress that can split a later summary into separate categories. Keep the approved list somewhere maintainable and review it when the categories change. Data Validation is available in Excel for Microsoft 365 on Windows; the Data Validation command is also available in Excel for Mac, although ribbon placement can differ.

Common Testing Mistakes and Limitations

  • Assuming formatting proves the value is valid: a cell formatted as a date may still contain text or a date outside the allowed period.
  • Deleting duplicates immediately: repeated IDs may indicate a real issue, but two rows may represent separate events. Confirm the record rules first.
  • Checking only totals: an incorrect positive amount and an incorrect negative amount can cancel each other out.
  • Ignoring source and scope: a test cannot find a record that was never imported unless you compare the results with a reliable source list.
  • Overlooking formulas hidden by formatting: a displayed rounded value may differ slightly from the stored value. Decide whether the check should use exact equality or an agreed rounding tolerance.

Excel is a flexible analysis tool, but it does not know your organization’s definitions of a valid customer, complete order, or acceptable amount. Document the rules and have a responsible person review exceptions. For sensitive or regulated records, follow the applicable organizational controls rather than relying on a worksheet check alone.

Frequently Asked Questions

How do I check whether Excel data is correct?
Define rules for required fields, unique identifiers, permitted values, and calculated results. Then use filters, formulas, conditional formatting, and a comparison with a trustworthy source where available. No single check establishes that every value is correct.

How do I find duplicate data in Excel?
Select the relevant range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. For a row-by-row test, COUNTIF can flag values that occur more than once. Confirm that the field is supposed to be unique before treating duplicates as errors.

Can Excel detect missing data?
Yes. Filter a required column for blanks or use COUNTBLANK on a defined range. These checks find empty cells within the selected data, but they cannot identify an entirely missing record unless you compare against an expected list.

Does Data Validation check data already in a worksheet?
Data Validation mainly controls entries made after a rule is set. It is not a complete audit of existing or imported values. Use separate formulas, filters, or other review checks to find existing problems.

Is a PivotTable enough to test a dataset?
No. A PivotTable can reveal unexpected counts and totals, but it summarizes the source values as they are. Check individual records and the source data rules as well as the summary.

Related

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.