What are treacherous errors?

Many formula configuration errors in Excel result in visible errors that propagate all the way down to your final output sheet. 

Think of using a reference to the wrong cell in a VLOOKUP and seeing #N/A appearing everywhere. In this case symptoms are visible and they can therefore be traced back to their origin, although this is oftentimes not easy.

However, some of these errors will produce data that is silently wrong, i.e. the result of the calculations looks right but it is actually not. That's what we call treacherous errors. If you don't have a specific mechanism in place to catch them, they will be very difficult to spot.

How is that even possible? 

Most of them are due to 3 Excel features:

• Positional cell references: $A$1 is a cell address by column and row. If you look at a formula using it, nothing tells you that A is the right column to use.
• Formulas reference individual cells: adding a new column to a table of 100 rows means adding 100 formulas, each with a slight change.
• Excel makes silent conversions to make life easier for the user: text to numbers being the most common. However these are not consistent across formulas.

⚠️The list below compiles the 12 most frequent treacherous Excel errors. 

🚨4 of them are marked are super-treacherous since they are not reference errors (of which people are aware) but even more insidious silent behavior.

What can I do?

In plain Excel (without River)

Unfortunately, you're left with very manual solutions. Let's list them:

• Reference discipline: choose relative vs absolute refs deliberately (so formulas drag correctly); prefer Table references over cell addresses for reliability.
• Organize inputs & structure: keep hard inputs on dedicated sheets (inputs/assumptions) and reference them; for large models use auditing tools to find hard-coded values.
• Lookup safety: always use explicit match modes (e.g., FALSE for VLOOKUP); if using XLOOKUP verify return ranges and add uniqueness checks on the lookup array.
• Validate cell contents: check for blanks (ISBLANK), look for “number stored as text” markers, and use sanity checks to ensure all input data is accounted for.
• Error & consistency checks — Watch for Excel’s “inconsistent formula” marker, be cautious with error-hiding (IFERROR/IFNA).

If you're using the River Add-In in Excel 

You're in luck! This is exactly what River was designed to do. In particular:

• Reference errors are always to a named column, drastically reducing possible errors
• There's no cell-by-cell formulas so you can't make a pull down error
• There's no unnecessary treacherous parameters
• Columns have a defined type so numbers can't be stored as text.
• Missing values are explicit
• Uniqueness and missing values checks are automatically added to your model
• The Lookup is a modern implementation of Excel's, with integrated duplicated and no-match checks

 The full list

Category	Error	When does it produce a visible error?	When does it produce a silent error?
Cell references	Using relative references (A1) and dragging them, thinking they are absolute, or vice-versa	#N/A if reference is invalid	⚠️If the misreferenced cells have valid data
	Reordering rows in a table changes the formulas referring to its cells	#N/A if reference is invalid	⚠️Most of the time
	Adding cells to a table but not pulling formulas down to the bottom	Never	⚠️Every time
Inconsistent formula	Different formula in the middle of a column, e.g. due to a "pull-down" error	"Inconsistent formula" marker (green corner)	⚠️If formula is valid, which is often the case
VLOOKUP	Wrong index column in Vlookup	Yes, if index outside of table or errors in calculation	⚠️If the column has valid data
	Values in the lookup table not unique	Never	🚨In all cases
	Forgetting to set FALSE in VLOOKUP	Never	🚨In all cases
INDEX-MATCH-MATCH	Use wrong column index offset if columns change	Yes, if index outside of table or errors in calculation	⚠️If the misreferenced cells have valid data
Cell values	Empty values silently converted to 0 in formulas	Never	⚠️If empty cells are not intended and not detected with ISBLANK
	Numbers stored as text have varying behavior depending on the formula that uses them.	The discrete Excel marker informs you	🚨It depends on  the formula: * converts text to number, but SUM() ignores them and XNPV produces an error.
	Hardcoded values stored in cells with no visibility `=A1*1.05`	Never	🚨In all cases
Mishandling errors	Wrapping formulas with IFERROR() or IFNA() improperly hides errors	Never	⚠️If data changes and produces valid error that are hidden.