Spreadsheet errors are rarely dramatic. They are small, quiet mistakes that lead to the wrong number in a meeting. Here are ten of the most common, and simple habits that prevent them.
1. Hard-coded numbers inside formulas
=B2*1.08 hides an assumption (8% tax). Put the rate in its own labeled cell and reference it. When it changes, you update one place.
2. Ranges that do not grow with the data
=SUM(D2:D100) silently ignores row 101. Use tables (Ctrl+T in Excel) or whole-column references where appropriate.
3. Numbers stored as text
Imported data often contains numbers with spaces or apostrophes. They look fine but are excluded from sums. Check alignment (text aligns left) and use VALUE or "Convert to number".
4. Inconsistent categories
"Email", "email" and "E-mail" become three channels in a pivot table. Use drop-down lists (data validation) for categories.
5. Mixing units or currencies
Combining thousands and units, or USD and EUR, in the same column produces meaningless totals. Label column headers with units.
6. Merged cells in data tables
Merged cells break sorting, filtering and pivot tables. Keep raw data in a simple grid; use formatting only in the presentation layer.
7. Overwriting formulas with values
Typing a number over a formula "just this once" creates inconsistencies that are hard to find. Use a different cell color for inputs versus calculations.
8. Double counting
Including subtotal rows inside a range you then sum doubles the result. Keep subtotals out of raw data.
9. Unclear time periods
"Q2" may mean calendar or fiscal quarter. State the exact dates in the report header.
10. No checks
Add simple control totals: does the sum of all regions equal the grand total? Does the row count match the export? A quick check catches many issues.
A simple structure that prevents most errors
- Raw data tab: untouched export.
- Clean data tab: tidied table with consistent formats.
- Calculations tab: formulas and pivot tables.
- Report tab: charts and summary text.
We practice this structure throughout Week 1. You may also like essential formulas for business.
