Start by checking what the cell is telling you
Spreadsheet formulas can make a budget, contact list or volunteer rota easier to maintain. They can also produce an unexpected result when a cell changes. Before rewriting a formula, look at what appears in the cell: an error code, an implausible number, a row of hash marks, or the formula itself. Each points to a different kind of problem. Error labels and menu names vary between spreadsheet programs, but the checks below work in many of them.
Select the problem cell and read the formula in the formula bar. Check its cell references against the values you intended to use, then inspect the referenced cells. If several formulas have changed at once, start with the first affected row rather than editing every result. A single empty input, moved column or incorrectly entered value may explain the whole group.
Fix division and value errors at the source
A #DIV/0! error means a formula is dividing by zero or by an empty cell that the program treats as zero. Suppose you divide total expenses by the number of participants: if no participant count has been entered, the calculation cannot produce a useful answer. Check that the divisor points to the right cell and that the input is present. If an empty input is expected while a sheet is being filled in, you can use a conditional formula to leave the result blank until the divisor is greater than zero. That is more informative than hiding every error without checking its cause.
A #VALUE! error often means the formula received a kind of input it cannot use, such as text where it expects a number. A price copied from a message might contain a currency symbol, an unexpected space or punctuation the sheet does not recognize as part of a number. Inspect the input cell, remove unwanted characters and enter the value in a format the spreadsheet recognizes. Changing a cell’s appearance to a number format may not, by itself, convert stored text into a number.
Repair names, references and copied formulas
A #REF! error usually signals that a formula points to a cell or range that is no longer available, perhaps because a referenced row, column or sheet was deleted. Open the formula and replace the broken reference with the intended range. If you have not made other changes you need to keep, undoing the deletion may be simpler. Before removing rows or columns from a shared sheet, check whether formulas elsewhere depend on them.
A #NAME? error generally means the spreadsheet does not recognize part of a formula. Check function spelling, named ranges and whether text values need quotation marks. If you copied a formula from a different spreadsheet program, its function names or separators may not match your program or regional settings. If a formula works but gives the wrong number after you copy it down a column, compare the original and copied versions. Cell references normally move when copied; use an absolute reference, often written with dollar signs, when a reference such as a fixed tax rate must stay in the same cell.
Understand failed lookups and unusual displays
A #N/A result commonly appears when a lookup cannot find a match. Check the value being searched for, the lookup range and whether the entries are stored consistently. For example, an identifier stored as text may not match the same-looking identifier stored as a number. Extra spaces can also get in the way. If you use a lookup that allows approximate matches, check its settings carefully: an approximate result may look valid even when it is not the entry you wanted. Where available, an exact-match option is often easier to verify for names and identifiers.
A row of hash marks, such as ####, is often a display issue rather than a failed calculation. Try widening the column or checking the cell’s date and number format. If the cell shows a formula beginning with an equals sign instead of a result, check whether it was entered as text or whether a setting to show formulas is turned on. A message about a circular reference means a calculation depends on its own result, directly or through other cells; trace the referenced cells to find the loop.
Make future errors easier to find
Keep input cells and calculated cells easy to distinguish, and label the units you expect, such as hours, people or currency. When building a new sheet, test a formula with a few simple values before copying it across many rows. After copying, check a formula near the beginning, middle and end of the range. This helps catch shifted references and ranges that stop too early.
When an error appears, fix the underlying input or formula before adding a function that replaces errors with a blank or a message. Error-handling functions can make a finished sheet easier to read, but they can also conceal a mistake. Save a copy before making extensive changes to an important workbook, and leave a short note beside any input rule that another person might not know. A spreadsheet is easier to trust when its calculations are understandable as well as error-free.
