The spreadsheet mistakes that cause wrong answers in Excel and Google Sheets: hard-coded numbers, broken references, VLOOKUP's approximate match, numbers stored as text, merged cells, date formats and more, with fixes for each.
Spreadsheet errors are common, and some have cost organisations dearly. Most come from a few habits. Fix these and your workbooks become more accurate and easier for others to follow.
1. Hard-coding numbers inside formulas
A formula like =B2*0.2 hides an assumption (here, a 20% rate). When the rate changes, you have to find every formula. Put inputs in labelled cells, such as a "VAT rate" cell, and refer to them, ideally using a named range.
2. Getting relative and absolute references wrong
When you copy =B2*C1 down a column, C1 becomes C2, C3 and so on. If C1 holds a fixed rate, lock it with $C$1. Press F4 in Excel (or on Windows in Sheets) to cycle through reference types.
3. Forgetting VLOOKUP's default is approximate
If you leave out VLOOKUP's last argument, it defaults to approximate match, which can return the wrong row on unsorted data. Always use FALSE (or 0) for exact match: =VLOOKUP(A2, Prices!A:B, 2, FALSE). Better still, use XLOOKUP, available in Microsoft 365 and Google Sheets, which defaults to exact match and can look left.
4. Numbers stored as text
Imported data often stores numbers as text, so SUM ignores them and lookups fail. Signs include left-aligned numbers or a green triangle in Excel. Fix with VALUE(), multiplying by 1, or Excel's "Convert to Number" option. In Sheets, check Format, then Number.
5. Merged cells
Merged cells break sorting, filtering, copying and many formulas. Use "Centre across selection" in Excel's alignment settings, or simply leave headings unmerged.
6. Mixing data and presentation
Keep raw data in a simple table, one row per record and one column per field, with no blank rows or subtotals mixed in. Do summaries on a separate sheet with pivot tables or formulas. This makes data easy to filter, analyse and update.
7. Date confusion
Dates entered as text, or interpreted in US month/day order, cause errors. Check your locale settings: in Excel, it follows your system's region; in Sheets, File, then Settings, then Locale should be United Kingdom. Use DATE(year, month, day) in formulas to avoid ambiguity.
8. SUM including hidden or filtered rows
SUM adds everything, including rows hidden by a filter. To total only visible rows, use =SUBTOTAL(109, range) or, in Excel, AGGREGATE.
9. Rounding and display
A cell formatted to two decimal places still stores the full value, so totals can appear a penny out. Use ROUND where values must be exact, such as invoices: =ROUND(B2*C2, 2).
10. No checks and no version history
Add simple checks, such as a cell confirming that totals across and down match, and use conditional formatting to flag errors. Rely on version history: OneDrive and SharePoint keep versions for Excel files, and Google Sheets has File, then Version history. Name important versions before big changes.
Bonus: whole-column references
References like A:A are convenient but can slow large workbooks, especially in array formulas. Use Excel Tables, which expand automatically, or bounded ranges.
A quick review checklist
- Are all assumptions in labelled input cells?
- Do lookups use exact match?
- Are numbers really numbers?
- Do totals reconcile?
- Can someone else follow it?
Frequently asked questions
How do I find errors quickly?
In Excel, use Formulas, then Error Checking and Trace Precedents. In both apps, Ctrl + ` (backtick) shows formulas instead of results.
Should I use IFERROR everywhere?
No. It hides genuine problems. Use it only where an error is expected.
Sources and further reading
- Microsoft Support: Excel help and learning
- Microsoft Support: XLOOKUP function
- Google Docs Editors Help: Google Sheets
- European Spreadsheet Risks Interest Group
Facts checked on 20 October 2026. Spotted an error? Tell us and we will correct it.