How to Find and Fix Data Entry Errors in a Spreadsheet (2026)

To find and fix data entry errors in a spreadsheet, you work through a fixed audit rather than reading rows: preserve the original, let the software flag what it can see, hunt the categories no tool catches for you, correct each value against its source, and log what changed. Most files are clean up within 30 to 90 minutes once the checks are written down.

The reason this matters is that spreadsheet errors rarely announce themselves. A mistyped digit, a value stored as text, or a blank that quietly means missing will sit there and quietly corrupt every total, pivot table, and statistical test built on top of it. Research into spreadsheet error rates, first published by Panko back in 1998 and still cited in auditing literature, has found error rates in spreadsheets of up to 88%, with roughly 40% of those errors coming from ordinary human mistakes such as typos and bad references, and only about half of them caught during manual review.

Before you start, it helps to sort errors into three families, because each one needs a different tool and a different fix:

• Entry or typing errors. Wrong number, transposed digits, text typed into a numeric column, stray characters, an answer left blank when the codebook required a value, or the same question answered four different ways across respondents. These are the errors Excel’s green triangle can sometimes see, and the errors nobody notices.
• Structural or data-shape errors. Merged cells, two tables stacked on one sheet, units buried in a header, several values crammed into one cell, renamed columns, a header row that repeats halfway down. No built-in tool reports these, and every one of them breaks the import to SPSS, Stata, R, or SQL.
• Formula errors. The #VALUE!, #REF!, #N/A and #DIV/0! family. These are the loud ones, and they are what most people picture when they search for how to find errors in an Excel spreadsheet. They are also the easiest to find and usually the easiest to fix.

Start with the loud ones if you want fast wins, but do not stop there. A file that shows no error codes at all is not a clean file. It is a file whose problems are of the first two families, which are the ones that reach your analysis.

Table of Contents

1. 1What You Need Before You Start
2. 2Step-by-Step: How to Find and Fix Data Entry Errors in a Spreadsheet
3. 31. Preserve the Original Spreadsheet
4. 42. Check the Structure and Required Fields
5. 53. Scan for Missing, Invalid, and Inconsistent Values to Find Data Entry Errors
6. 64. Find Duplicates and Contradictory Records
7. 75. Correct Errors Without Creating New Ones
8. 86. Validate and Document the Cleaned File
9. 9Common Mistakes
10. 10Frequently Asked Questions
11. 11How do I find errors in an Excel spreadsheet?
12. 12How do I trace errors in Excel?
13. 13How to fix corrupted data in Excel?
14. 14How do I fix an entry in Excel?
15. 15What is the difference between a blank cell and a zero in a dataset?
16. 16Should I always delete duplicate rows in a spreadsheet?
17. 17Conclusion

What You Need Before You Start

Four things. Missing any of them turns a clean audit into guesswork, and guesswork in a research dataset is how a fabricated value ends up in a results table.

1. A protected original. Make a copy of the file as it arrived, store it somewhere you will not edit it, and set it read-only. Every subsequent change happens in a working copy. If the file came from a client, a colleague, or a survey platform, this is also your evidence of what was originally submitted.

2. The source documents. The questionnaire, the codebook, the data dictionary, the protocol, or the paper forms. You cannot correct an ambiguous value to anything other than missing without the source, and missing is a real analytical decision that changes your denominators later.

3. The expected counts. How many rows should be here, how many columns, what date range, how many participants per site. A file with 412 rows when recruitment stopped at 380 is a finding, and you only know it if you knew the number in advance.

4. The built-in checks your software already has. Filters and sort, conditional formatting, duplicate detection, data validation with drop-down lists, the error checking dialog, formula auditing tools, and text-to-columns. Menu names differ across Microsoft Excel for Windows, Excel for Mac, Google Sheets, and LibreOffice Calc, so I will name the ribbon path and the equivalent where the two diverge.

Step-by-Step: How to Find and Fix Data Entry Errors in a Spreadsheet

Six steps, run in order. The order matters because each step depends on the previous one being right. Correcting values before you have checked the structure means you are typing into columns that may be about to change shape.

Step 1 is the only step you must never skip.

1. Preserve the Original Spreadsheet

Duplicate the file before typing anything. Name the copy something like survey_raw_2026-03-14.xlsx, mark it read-only, and keep it out of the folder where the working version lives. Then duplicate that as survey_clean_working.xlsx and open the working copy.

Before editing, confirm three things: both files open without a repair prompt, the row and column counts match what the sender said to expect, and the working copy is not accidentally linked to a shared drive that someone else edits while you work. A file that prompts to repair on open is telling you something useful, and you want to know what before you add your own changes.

In Google Sheets, version history does the same job better than a file copy. Use File > Version history > Name current version to snapshot the original, and every later restore is one click.

2. Check the Structure and Required Fields

Compare every column heading against the questionnaire or codebook, one by one, and write down each mismatch. You are looking for four specific things.

Missing fields. A column in the codebook with no corresponding column in the file. This is the serious one, because downstream software will simply drop the variable without warning and your sample size for that question will quietly shrink.

Duplicated fields. Two columns with the same name. Some import routines rename the second one silently, or overwrite it.

Renamed fields. sex arriving as Sex, SEX, and Gender (1=M, 2=F). The second version usually means someone edited the header, which means somewhere on this project, two people were using different variable names for the same thing.

Structural damage. Merged cells, colour used to carry meaning, units inside the header, a second table starting a few hundred rows down, a frozen or repeated header block in the middle of the data. These are the errors no validation tool will report, and they are what turns a clean import into a corrupted one.

While you are here, apply the one cell, one observation rule. If a column called observation contains 1M, 1F, 1M, 2F, split it into a count column and a sex column rather than trying to parse it later. Every analysis software package handles two clean columns and one awkward text column very differently, and not in your favour.

Freeze the header row now if the file is long: View > Freeze > Freeze Top Row in Excel, View > Frozen rows and columns in Google Sheets. You will be scrolling for the rest of this audit.

3. Scan for Missing, Invalid, and Inconsistent Values to Find Data Entry Errors

This is the long step, and it is where the real errors are. Work column by column rather than row by row, because errors cluster in columns.

Start with blanks. Select the data range, go to Home > Find & Select > Go To Special > Blanks in Excel (Edit > Find & Replace, Other options, Blanks in Google Sheets) and the cursor jumps to each empty cell. Then ask the only question that matters for that cell: was the answer skipped, or was it not applicable? Those two blanks mean different things and only the codebook can tell you which is which.

Next, hunt values that are impossible. Conditional formatting is the fastest way: Home > Conditional Formatting > New Rule > Use a formula, then a rule like =OR(D2<18, D2>70) on an age column, or =AND(C2<2020, C2>2026) on a date of birth. Every match is a cell to check against the source, not to correct on sight.

Then hunt values that are merely strange, which is where inconsistent formatting lives. A yes/no question answered Y, y, Yes, YES, 1, and 2 is one question with six answers, and your cross-tab will show you a category for each. Find every distinct value in a column before you assume you know the categories. In Excel, that is a PivotTable on one column, or Data > Remove Duplicates on a helper column holding that field copied out.

Here is the error code reference table. These are the loud errors, and the fix is almost always in the last column.

Code	What it means	Typical cause	How to fix
#DIV/0!	Division produced nothing to divide by	Empty cell, or a filtered-out row, used as a denominator	Test the denominator: =IF(B2=0,"",A2/B2)
#N/A	A lookup found no match	Lookup value not in the table, or a missing key	=IFNA(lookup,""), then fix the underlying key
#VALUE!	Wrong data type in the operation	Text sitting in a numeric column, or "25 kg" in a cell that needs 25	Coerce the text with =VALUE() or =NUMBERVALUE() after cleaning the text
#REF!	The reference points at a cell that no longer exists	Rows or columns deleted, or a cut-and-paste instead of copy-and-paste	Find the broken reference in the formula bar and repoint it; recover deleted cells first if the data is gone
#NAME?	Excel does not recognise the name or function	Misspelled function name, or a named range that was deleted	Check the function name and any defined names in Formulas > Name Manager
#NUM!	The operation has no valid result	Square root of a negative, or an impossible exponent	Guard the input with =IF(A2>=0,SQRT(A2),"")
#NULL!	Two ranges in the operator do not intersect	A space where a range reference should be, as in =SUM(A1:A5 B1:B5)	Insert a comma or an intersection operator between the ranges
#SPILL!	A dynamic array result cannot fit the space it needs	A non-blank cell blocking the spill range	Clear the blocking cell or use @ to return a single value
#CALC!	The formula could not be evaluated as written	Newer Excel functions, or an argument the function rejects	Check the function’s argument order against the documentation
#FIELD!	A linked data field is unavailable	A linked workbook, table, or query that is not reachable	Restore the data source, or convert the formula to values once fixed

The other tool in this step is the error checking dialog itself. In Excel for Windows it is Formulas > Error Checking; in Excel for Mac, Excel > Preferences > Error Checking. Run it across the whole sheet and use the Next button to walk cell by cell. Each hit gives you an Ignore, Edit in Cell, or Help choice, and Ignore is a legitimate answer for an error you have already reviewed.

To see every flagged cell at once rather than one at a time, add conditional formatting with a formula rule: Home > Conditional Formatting > New Rule, and =ISERROR(A1) applied to the whole data range. Every error lights up on one screen. For errors in formulas rather than typed values, =ISERR() is the faster test, because it returns FALSE for #N/A and does not slow down a large range the way ISERROR() can.

Google Sheets has no Error Checking dialog and no green triangle. What it does have is visual error markers, Data > Data validation for blocking bad entry, Format > Conditional formatting > Conditional format rules with a custom formula such as =ISERROR(A1), and Tools > Script editor if you want to automate a scan. The same audit logic applies; only the names move.

While scanning, settle the blank-versus-zero question, because it is the one that quietly distorts results. A blank cell means no value was supplied. A zero means the value was supplied and it was zero. Analysis software treats them differently, and so should you.

What you type in the cell	How the software reads it	Effect on the analysis
Blank cell	Missing, excluded from most summaries	Reduces the denominator for that variable only
0	A real measured zero	Counts as an observation, pulls the mean down
-999, 999, 88, or 66	A perfectly valid number	Silently included, wrecking the mean, the SD, and any regression
NA or N/A typed as text	Text, not a missing value	Breaks arithmetic, may be read as the string “NA” by the import
Blank text, a single space, or a hyphen	Usually missing, but inconsistently so	Different packages treat these differently, so results are not reproducible

Sentinel values are the dangerous ones. -999 and 999 look like missing data to the person who typed them and look like real measurements to every piece of software that reads the column. In a 200-row survey with five non-responses coded -999, your mean for that item is wrong by 25 percent, and nothing in the output says so. Same for 66 and 88 as missing markers for gender in health survey data: harmless to the eye, catastrophic to a proportion.

Decide your missing-value convention once, write it in the codebook, and stick to it. The usual choice is a genuinely empty cell plus a separate missing flag variable, and that flag is what you can later use for listwise or pairwise deletion without guessing which rows were incomplete.

4. Find Duplicates and Contradictory Records

Duplicate participant IDs are the error people worry about most and the one they check least. The test takes about a minute.

Select the ID column, then Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, which flags every repeat. For a count rather than a highlight, put =COUNTIF($A$2:$A$5000,A2) in an empty helper column next to the data and fill it down. Anything above 1 is a duplicate key. COUNTIFS() does the same across two or more columns when your identifier is a combination, for example =COUNTIFS($A$2:$A$5000,A2,$B$2:$B$5000,B2).

In Google Sheets, use Data > Data validation with a custom formula rule, or Data > Pivot table grouped on the ID column, which lists the distinct values with counts. In LibreOffice Calc, Conditional > Condition applies the same test.

Duplicate keys and duplicate records are not the same thing, and the difference decides what you do next. A repeated ID with two different sets of answers is two submissions, or one submission plus a correction. A repeated ID with identical answers across every column is a double click. A repeated row with no ID column at all is usually a copied block, and it inflates your sample size, which changes your standard errors.

Before deleting anything, compare the suspected pairs side by side. Sort by the key column, then read down the two rows. Look for a timestamp, a submitter name, or a version marker that says which is later. The top answer on spreadsheet forums for a clean approach is exactly this: flag and report first, decide afterwards. Use Data > Remove Duplicates on a copy, or better, do not use it at all until you have confirmed which row should survive, because the Remove Duplicates tool keeps the first occurrence by your sort order and will happily keep the older one.

Contradictory records are harder and deserve their own pass. A record with a completion date before its start date. A respondent marked as a non-smoker with a 40-pack-year smoking history. A row where the age recorded is 14 but the education level implies secondary school completion. These are not typos to be corrected from inside the file; they are records to be checked against the source and, where the source is silent, coded as missing with a note.

Also check for records that should not be in the file at all: test rows, a header row pasted into the middle of the data, a footer’s “Page 1 of 3” as a row, a summary total row. Sort a numeric column and look at the largest values. An age of 999, an income of 50,000,000, or a survey duration of 3 seconds is almost always a header, a total, or a mistyped value.

5. Correct Errors Without Creating New Ones

The rule for this step is simple: every correction is verified against a source, and every value you cannot verify becomes missing rather than a guess. A plausible number is not a correct number, and plausible numbers are what get fabricated into published tables.

Text in a numeric column. The single most common entry error, and the reason #VALUE! shows up. Numbers stored as text are left-aligned and get a green triangle. Select the column, Data > Text to Columns, choose Finish without changing the format, and they convert. For a mixed column with something like 25 kg in it, clean the text first: a helper column with =VALUE(SUBSTITUTE(A2," kg","")) pulls the number out, and you can read off anything that still fails.

Whitespace and invisible characters. Leading and trailing spaces make "Yes" and "Yes " different categories in a pivot table. A helper column with =TRIM(CLEAN(A2)) strips them, and pasting the result back over the original cleans the column in one go. Watch for non-breaking spaces and smart quotes: on Windows, Alt+0176 inserts a non-breaking space, and Excel’s Find & Replace with ~* in Find what, nothing in Replace with, will strip trailing spaces across a range.

Transposed digits and typos. Take these to the source. The codebook, the paper form, the platform’s export, or the respondent’s confirmation email. If the source does not settle it, it is missing.

Values in the wrong row. A whole block shifted down by one, usually from a paste that started one row too high. Detect it by looking for one row’s values sitting above the values that should precede them, and fix by deleting the empty row above or the misplaced one below, never by retyping the block.

Values in the wrong column. If two adjacent questions were swapped for a run of respondents, check whether the pattern is systematic. If every respondent has them swapped, swapping the headers is the correct fix. If only some do, those are individual errors and each needs a source check.

Wrong codes. When a value is coded outside the allowed set, use the codebook’s missing code, not the nearest valid one, and never recode to whatever makes your analysis run. Changing codes to fit the analysis is not a fix; it is a second error layered on the first one.

Throughout this step, use copy and paste rather than cut and paste. Cutting a row or column shifts everything below or to the right of it, and every formula that pointed at the moved cells turns into #REF!. If you must move data, insert a blank row or column first, paste into the gap, then delete the original.

6. Validate and Document the Cleaned File

Rerun the checks from steps 2 to 4 on the cleaned file. A dataset that was correct before cleaning can easily be broken by cleaning it, and the only way to know is to check again.

Add control checks where they will be visible, on a totals sheet or below the data rather than off to the side where nobody will see them. =COUNTA(A2:A5000) for expected row counts. =D10-A10 for a difference that should be zero between two routes to the same total. =SUMIF(range,"Yes",values) to reconcile a category total against a single cell. If a control check is anything other than zero, the cleaning introduced something.

Then spot-check a sample by hand. Pick a first row, a last row, and ten rows at random, and read each against the source. Ten rows is not statistically meaningful, but it reliably catches systematic problems that formulas miss, because a systematic problem shows up in a small sample more often than not.

Keep an error log. This is the step almost everyone skips, and it is the one that makes cleaning reproducible rather than a one-off. A simple table with these columns is enough:

Row or cell	Original value	Corrected value	Reason	Source checked	Date	By
Row 118, age_group	6	blank	Code 6 not in the codebook; source form illegible	Paper form 118B	2026-03-16	KA
Rows 240 to 244, smoker	y, Y, Yes, yes, YES	1	Six spellings of one category, recoded to the codebook value	Export schema v2	2026-03-16	KA
Row 77, income_band	50,000,000	deleted row	Grand total row pasted into the data block	Row count 380 expected, 381 found	2026-03-16	KA

An error log like this answers the questions that come up months later: was this value ever checked, by whom, against what, and can I reverse it? Corrections made without a log cannot be audited, and in a thesis or an audited report that is a real problem rather than a formality.

Save the result under a clear filename that says what it is, not what it might be. survey_clean_v1_2026-03-16.xlsx beats survey_FINAL_final2.xlsx. Keep the raw file alongside it, unchanged.

Finally, re-check after the import. When the file moves into SPSS, Stata, R, or SQL, run a frequency on every variable and read the output. Values that were text in the spreadsheet, trailing spaces, sentinel codes, and duplicate IDs all surface again at this stage, and the frequency table is the fastest place to catch what the import mangled. If the file will be imported repeatedly, do the cleaning once, save a cleaned file with only the columns analysis needs, and import that instead of re-cleaning each time.

Common Mistakes

Most spreadsheet damage is self-inflicted during the cleaning process rather than in the original data. These are the ones I see repeatedly.

Overwriting the original. You cannot tell a corrected typo from a corrupted value later, and you cannot rebuild the original from memory. Protect the raw file first; it takes ten seconds.

Changing codes to fit the analysis. If a category will not run, the problem is the design, the codebook, or the analysis, not the data. Recoding to make output appear is a research misconduct issue, not a convenience.

Treating every blank as a zero. A blank is missing. A zero is measured. Filling the blanks with zeros fabricates observations and changes every summary statistic computed from that column. The mirror-image mistake, replacing missing values with the column mean, is worse because it shrinks the variance of your estimate in a way nobody can see.

Deleting duplicates automatically. Remove Duplicates keeps whichever row sorts first, which is not necessarily the correct or most complete one, and it does not tell you what it deleted. Look at each pair first.

Using -999, 999, 99, or 66 as a missing marker. These are real numbers to every formula and every statistical package. Use an empty cell, or a real missing value such as the dot that R and Stata use, and document it.

Cut and paste. It shifts neighbours and silently breaks dependent formulas with #REF!. Copy and paste, or insert a blank row first.

Mixed date formats. Some rows in a date column holding 03/04/2026 and others holding 2026-04-03 is a coin flip every time anyone reads it, and European and US conventions disagree. Pick ISO 8601, yyyy-mm-dd, store all dates as true dates, and check the display format on the whole column at once.

Two things in one cell. “Male 34” is two variables. Text such as “about 3 hours” or “2-3” is unusable without parsing, and parsing it introduces guesswork. Split it at the source or code it as missing.

Merged cells and formatting as data. Colour that means something is invisible to every piece of software reading the file. If a red fill means follow-up required, that is a column with a value in it.

Three quality-control habits cover most of it. Check the file against an independent count, not against your own arithmetic. Have someone else review your error log, not your data, because a second pair of eyes on the log catches the same value corrected twice or a reason that does not support the change. And when an error class shows up three times, change the setup rather than the data: add a drop-down list, a data validation rule, or a conditional formatting rule so the fourth occurrence cannot happen.

Frequently Asked Questions

Conclusion

Start by protecting the original file. How you find and fix data entry errors in a spreadsheet comes down to this: compare every column and every value against the source rather than against your own expectations, correct only what you can verify, and code the rest as missing.

A documented, repeatable cleaning process is worth far more than a spreadsheet that merely looks tidy. The log is what lets you, or anyone else, repeat the audit next month and know exactly what changed and why.