Day 25: Error Handling & Troubleshooting

#DIV/0! — division by zero (or empty denominator). #N/A — lookup value not found, or formula missing required input. #VALUE! — wrong data type (e.g. text where number expected). #REF! — formula references a deleted or invalid cell. #NAME? — formula contains an unrecognised function name or missing quotes around text. #NUM! — formula produces a number too large or contains invalid numeric input. #NULL! — space used instead of comma or colon as reference operator. ##### — column too narrow to display the number (widen the column).

Formula Auditing tools: Formulas → Trace Precedents draws arrows showing which cells feed into the selected cell's formula. Trace Dependents shows which cells use the selected cell in their formula. This is essential for understanding complex workbooks you did not build — following the arrows reveals the calculation logic without reading every formula. Remove Arrows clears them. Evaluate Formula (Formulas → Evaluate Formula) steps through a complex formula one calculation at a time — invaluable for debugging nested formulas.

A circular reference is when a formula refers back to its own cell, either directly or through a chain. Excel warns you when this happens. Circular references can cause Excel to calculate infinitely or return 0. Find them: Formulas → Error Checking → Circular References — Excel lists all circular cells. Fix them by rewriting the formula logic to break the cycle. In rare intentional cases (iterative calculations), circular references can be enabled: File → Options → Formulas → Enable iterative calculation — but this should be a deliberate choice.

Systematic approach: (1) Read the sheet tab names to understand the structure. (2) On each sheet, press Ctrl+End to understand the data extent. (3) Use Ctrl+` (grave accent) to toggle "Show Formulas" mode — see all formulas at once. (4) On the most important output cell, use Trace Precedents repeatedly to follow back to the source data. (5) Check for external links: Data → Edit Links (if this option is available, the workbook pulls data from another file). (6) Check for named ranges: Formulas → Name Manager. (7) Check for macros: Alt+F11 to open VBA editor.

Silent errors in Excel calculations: (1) Text-formatted numbers — appear as numbers but are treated as text by SUM (returns 0 or wrong total). Check by summing — if SUM disagrees with visual addition, you have text numbers. (2) Hidden rows or filters — AVERAGE and SUM include hidden rows. Use SUBTOTAL(1,range) and SUBTOTAL(9,range) to aggregate visible rows only. (3) Trailing spaces in lookup keys causing VLOOKUP to find a different record. (4) Floating-point precision — =0.1+0.2=0.3 returns FALSE in Excel due to binary floating-point. Use ROUND for financial comparisons.