Excel

Wrapping an XLOOKUP error in IFNA is the fix almost every guide recommends, but it can turn a real data problem into a wrong answer that never shows an error.

Excel’s REF error looks fixable with Ctrl+Z, but that window closes the moment AutoSave commits the change, and most guides never explain what still works.

A dropdown list looks locked down until someone pastes into it. Excel only checks typed entries, and a paste can even delete the rule itself, not just skip it once.

Percentage formatting multiplies whatever’s stored by 100 for display. Type a plain number instead of the percent sign, and the math still runs, just on the wrong starting value.

FILTER’s #CALC! and #SPILL! errors look similar but mean opposite things: one is about nothing matching, the other is about matches with nowhere to land.

The normal right-click method needs a visible row on both sides of the hidden one. Row 1 has no row above it, which is exactly why it needs a different fix.

VLOOKUP trusts a number typed once and never rechecked. INDEX/MATCH searches for the column by name every time, which is the specific reason it survives an inserted column and VLOOKUP doesn’t.

XLOOKUP searches any direction and defaults to exact match. It’s also completely absent from Excel 2016 and Excel 2019, a compatibility gate most comparisons skip entirely.

VLOOKUP’s col_index_num is a number typed once and never rechecked. Insert a column anywhere inside the range, and it silently returns the wrong value with no error at all.