Data Analysis · Tutorial 7 of 10
Fix Wrong Results in Excel: formulas, duplicates and bad data types
By the FUTEAC Team · 21 November 2026 · 12 min read · Beginner
In short
A wrong Excel result usually comes from the data, not the formula. Numbers stored as text are ignored by SUM, a VLOOKUP without FALSE guesses instead of matching, and a discount typed as 25 instead of 25% turns a price negative. Eight cases, each with the exact wrong number, the fix, and a check.
- A formula that returns a number can still be wrong. Errors such as
#VALUE!are easy to spot. A total of 500 instead of 900 is not, and that is the dangerous kind. - Check the data type first.
ISTEXTandISNUMBERtake ten seconds and explain half of all wrong results. - Never trust a lookup that can guess. A
VLOOKUPwithoutFALSEas the fourth argument returns the nearest value below your search term, even when your search term is not in the table at all. - Duplicates hide in spelling.
COUNTIFtreatsAli,aliandALIas the same person, so one customer can be counted three times while looking like three customers. - Verify every fix with a second calculation. Add the numbers by hand, or total them a different way, and compare. If the two answers disagree, the fix is not done yet.
- This tutorial feeds the Spreadsheets for Data Analysis course (/course/spreadsheets-for-data-analysis) and the Excel Formulas (/skill/excel-formulas) and Data Cleaning (/skill/data-cleaning) skills.
Before you start
The sample data and the five-minute check that catches most of these
Every case below uses a small table you can type in two minutes. The point is not the shop, it is the habit: before you trust any total, run these three checks on the range it covers.
- Click a cell in the range and look at the status bar. Excel shows a sum for numbers only. If the sum is missing or too small, some cells are text.
- Type
=ISTEXT(A2)next to a suspect cell.TRUEmeans the cell holds text that looks like a number. - Type
=COUNT(A2:A5)and compare it with how many cells hold numbers.COUNTcounts numbers only, so a short count means text is hiding in the range.
That check is the whole method of this tutorial. Each section below is one cause, one wrong number, one fix, and one check that proves the fix.
Why does `SUM` ignore some of my numbers?
This is the most common wrong result in Excel, and it has no error message at all.
Type this into a blank sheet:
| Cell | Value | What it really is |
|---|---|---|
| A2 | 250 | Text, typed with a leading apostrophe or pasted from a CSV |
| A3 | 300 | Number |
| A4 | 150 | Text |
| A5 | 200 | Number |
Now type =SUM(A2:A5) in B7. The answer is 500, not 900. SUM adds 300 and 200 and silently skips the two text cells. =ISTEXT(A2) returns TRUE, which confirms it. Numbers stored as text are like price tags written in words: they look like numbers to you, and Excel reads them as labels.
The fix depends on how many cells are affected. For a few cells, click the cell, press F2, then Enter: Excel re-reads it as a number. For a whole column, use one of these:
- Type
1in an empty cell, copy it, select the text numbers, then Paste Special, Multiply. Multiplying text by 1 converts it to a number. - Or select the column and use Data, Text to Columns, Finish. That forces Excel to re-read every value in the column.
Check the fix: =SUM(A2:A5) must now read 900, and =COUNT(A2:A5) must read 4. If COUNT still reads 2, some cells are still text.
Why does `VLOOKUP` return the wrong price?
Set up a small price table and one lookup:
| Code | Price |
|---|---|
| A101 | 10 |
| A102 | 20 |
| A103 | 30 |
Now look up a code that is not in the table: =VLOOKUP("B205",D12:E14,2). The answer is 30. That is not an error, and it is not a price for B205 either. It is the price of A103, the last code below B205. When you leave out the fourth argument, VLOOKUP works in approximate match mode: it assumes the table is sorted and returns the nearest value below your search term. It never tells you the code was missing.
The fix is one argument. Always write the fourth argument, and always make it FALSE:
=VLOOKUP("B205",D12:E14,2,FALSE)That returns #N/A, which is the honest answer: the code is not there. In Excel 2021 or Microsoft 365 you can use XLOOKUP instead, which matches exactly by default:
=XLOOKUP("B205",D12:D14,E12:E14)That also returns #N/A. We could not test XLOOKUP in LibreOffice, it is not available there, so treat the VLOOKUP with FALSE as the fix that works in every version.
Check the fix: look up a code you know is in the table, such as A102, and confirm you get 20. Then look up a code you know is missing and confirm you get #N/A, not a price.
Why does my total count one customer three times?
Type these four rows:
| Name | Amount |
|---|---|
| Ali | 50 |
| ali | 50 |
| ALI | 50 |
| Sara | 80 |
There are two real people here, Ali and Sara, and the true total for Ali is 50. But =COUNTIF(D2:D5,"ali") returns 3, and =SUMIF(D2:D5,"ali",E2:E5) returns 150. COUNTIF and SUMIF ignore case, so Ali, ali and ALI all match. The formula is doing what it was told. The data is the problem: the same person was typed three different ways.
There is a second hiding place for the same mistake. Type Ali in one cell and Ali with a trailing space in another. =D16=E16 returns FALSE, because the second cell is four characters long and the first is three: LEN gives 4 against 3. You cannot see a trailing space, which is exactly why it survives so long. =TRIM(E16)=D16 returns TRUE, because TRIM strips the space.
The fix has two steps, in this order:
- Select the name column and use Data, Text to Columns or
=TRIM(A2)in a helper column to strip leading and trailing spaces. Copy the helper column and Paste Special, Values, back over the originals. - Select the table and use Data, Remove Duplicates, with the name column ticked. Excel keeps the first row of each set and deletes the rest.
Check the fix: =COUNTIF(D2:D5,"ali") must now read 1, and the total for Ali must read 50. If it still reads 3, a trailing space is still hiding in one of the names.
Why is my discount total negative?
A shop item costs 400 and the discount is 25 percent. Type this:
=400*(1-25)The answer is -9600. The formula is fine. The number is wrong: 25 means twenty-five whole units, not twenty-five percent. Excel stores a percentage as a fraction, so 25 percent is 0.25. The check is quick: =25%=0.25 returns TRUE. The correct formula is:
=400*(1-25%)That returns 300, which is 400 less a quarter of 400. This mistake is common because the cell and the formula disagree about units: the cell holds a plain number, the formula expects a fraction. It is the same class of error as adding a distance in miles to a distance in kilometres and trusting the total.
The fix: type percentages with the % sign, or format the discount cell as a percentage before you type the number. If a column already holds plain numbers such as 25, 10 and 5 that were meant as percentages, divide them by 100 in a helper column, or select them and use Paste Special, Divide, with 100.
Check the fix: the discounted price must be between 0 and the full price. A negative price, or a discount bigger than the price itself, always means the units are wrong.
Why does my date maths give `#VALUE!`?
A date typed as text looks exactly like a date. Type 2026-01-05 into a cell after pasting it from another system, then test it:
=ISTEXT(D30)returnsTRUE.=ISNUMBER(D30)returnsFALSE.
Excel stores real dates as numbers, day 1 is 1 January 1900, so a real date answers TRUE to ISNUMBER. A text date is a label, and arithmetic on a label gives #VALUE!. The fix is DATEVALUE, which reads the text as a date:
=DATEVALUE(D30)+30That returns 4 February 2026, thirty days after 5 January. For a whole column, select it and use Data, Text to Columns, Finish, which converts text dates to real dates in place.
Check the fix: =ISNUMBER(D30) must read TRUE after conversion, and sorting the column must put the dates in calendar order. Text dates sort alphabetically, so January dates mixed with November dates are the giveaway.
Why does `=0.1+0.2=0.3` say FALSE?
This one looks like a bug, and it is not. Type =0.1+0.2 and the cell shows 0.3. But the stored value is 0.30000000000000004. Excel stores numbers in binary floating point, and 0.1 has no exact binary form, so tiny rounding error creeps in. The value was checked in Python 3.12.3, which uses the same IEEE 754 arithmetic as Excel: 0.1 + 0.2 is 0.30000000000000004, and 0.1 + 0.2 == 0.3 is False. A comparison such as =A1=0.3 then fails even though the cell displays 0.3.
The fix is to round before you compare:
=ROUND(0.1+0.2,1)=0.3That returns TRUE. As a rule, never test money or measurements for exact equality. Compare rounded values, or test whether the difference is smaller than a tolerance, such as =ABS(A1-B1)<0.01.
Check the fix: widen the cell or raise the displayed decimals. If the value shows more digits than you typed, floating point is the cause, and rounding is the cure.
Why does my formula show `#DIV/0!`?
=10/0 returns #DIV/0!. Division by zero has no answer, so Excel says so. The usual cause is not a zero you typed, it is a blank or missing value in the divisor cell, often after a lookup failed or a row was deleted. Find the divisor first, not the formula.
The fix depends on what a missing divisor should mean. If it should count as zero in the result, guard the formula:
=IFERROR(10/0,0)That returns 0. If a missing divisor means the row is incomplete, fix the data instead and leave the error visible: an error you can see is better than a zero you cannot explain.
Check the fix: put a real number in the divisor cell and confirm the formula returns the expected quotient. Then delete the divisor again and confirm you get the guard value, not a silent wrong number.
How to verify the fix worked
Work through this list after any fix in this tutorial, in order. Do not skip step 3: it is the one that catches the fixes that only looked right.
- Re-run the check that found the problem.
ISTEXTmust now readFALSE,COUNTmust count every number, or the lookup must return#N/Afor a missing code. - Re-calculate the answer a second way. Add the column by hand, or total it with a pivot table, and compare with the formula.
- Change one input value and watch the result move. If the total does not change, the formula is reading a different range from the one you think it reads.
- Save a copy of the fixed sheet. The original, broken version is useful: it is the test case you will use next time the same error appears.
Try it yourself
Take a sales sheet you actually use, or build the four-row table from the SUM section above, and run the five-minute check from the top of this tutorial: status bar sum, ISTEXT on one cell, COUNT against the number of cells. Then deliberately break one thing, type a number as text, remove the FALSE from a VLOOKUP, or hide a row under a filter, and watch the total go wrong. Fix it, run the four verification steps above, and write down which check caught it. That note is the start of your own debugging checklist.
Continue in the free course: Spreadsheets for Data Analysis →
Common mistakes
- Fixing the formula when the data is wrong. If
SUMis short, the numbers are text. Rewriting the formula asSUMPRODUCTor adding cells one by one hides the cause and leaves the next total wrong too. - Leaving the fourth argument off
VLOOKUP. Approximate match is the default, not the exception. EveryVLOOKUPwithoutFALSEis a guess waiting for a missing code to return a real-looking wrong price. - Trusting a total under a filter.
SUMignores filters. Any total above a filtered table must beSUBTOTALwith 109, or it counts rows nobody can see. - Deleting duplicates before trimming spaces.
AliandAlilook like different people to Remove Duplicates. Trim first, then remove duplicates, or the hidden duplicates stay. - Hiding an error with
IFERRORbefore understanding it.IFERRORturns#DIV/0!into a tidy zero, which is the right fix only when a missing divisor really means zero. Used blindly, it turns a visible error into an invisible wrong number.
Frequently asked questions
Why does Excel show the right number but SUM still misses it?
Because the cell holds text that looks like a number. Excel aligns text to the left and numbers to the right, so a left-aligned "number" is the visual clue. ISTEXT confirms it, and Paste Special with Multiply by 1 converts it.
Is VLOOKUP with approximate match ever the right choice?
Yes, for sorted bands such as tax rates or grade boundaries, where you genuinely want the nearest value below. For looking up a price, a name or a code, always use FALSE, or XLOOKUP in Excel 2021 or Microsoft 365.
Why does my total change when I sort the sheet?
Usually because a formula references cells by position and the sort moved the data but not the reference, or because a VLOOKUP in approximate mode depends on the table being sorted. Sort the lookup table first, then use exact match, and the total stops moving.
Should I use IFERROR everywhere?
No. Use it where a missing value has a known meaning, such as a divisor of zero meaning zero. Do not use it to silence errors you have not understood, because it also silences the clue that would have told you the data was wrong.
What is the one check I should do before sending any spreadsheet to someone else?
Total every important column twice, two different ways, and compare. A SUM and a pivot table that disagree mean one of them is reading different data, and that disagreement is worth finding before your manager does.
Follow the Data Analyst Roadmap
Turn raw data into decisions with spreadsheets, SQL, Python and dashboards. Free, step by step, at your pace.
View the Roadmap