Formula Generator

Formula fundamentals that prevent most spreadsheet bugs

Almost every broken spreadsheet traces back to one of a handful of misunderstandings. None of them are advanced, and all of them cost hours once a file has grown. This section covers the ones worth knowing before you build anything you intend to keep.

Absolute and relative references — the dollar signs

A reference like A1 is relative: copy the formula one row down and it becomes A2. That is usually what you want, and it is also the single most common cause of a formula that works in the first row and produces nonsense in the rest.

A dollar sign locks the part it precedes:

• A1 — both move when copied
• $A1 — column locked, row moves
• A$1 — row locked, column moves
• $A$1 — nothing moves

The classic case is a tax or commission rate sitting in one cell. Write =B2*$E$1 and you can fill the column safely; write =B2*E1 and row three multiplies by whatever happens to be in E2, which is often blank — giving you zeros rather than an error, which is far worse because nothing looks wrong. Pressing F4 while editing a reference cycles through the four states.

XLOOKUP, INDEX/MATCH, and why VLOOKUP keeps breaking

VLOOKUP has two design flaws that cause real damage. It can only look to the right of the lookup column, and it addresses the return column by position number rather than by name. Insert a column anywhere in the middle of the range and every VLOOKUP pointing past it silently returns the wrong field. No error, no warning — just wrong numbers in a report.

Two replacements avoid this:

• XLOOKUP — takes a lookup array and a return array as separate ranges, so inserting columns cannot break it. It searches in either direction, has a built-in "if not found" argument, and defaults to exact match. Use it wherever it is available.
• INDEX + MATCH — the older combination that does the same job: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). More verbose, but it works in every version and in Google Sheets.

If you must use VLOOKUP, always pass FALSE (or 0) as the fourth argument. Omitting it requests an approximate match against data that is assumed to be sorted, and on unsorted data that returns confidently wrong results rather than an error.

What each error actually means

• #N/A — a lookup found nothing. Usually a trailing space, a different capitalization, or a number stored as text.
• #REF! — the formula points at a cell that no longer exists, typically because a row or column was deleted. This one is unrecoverable by editing; the original reference is gone.
• #VALUE! — the wrong type of thing. Arithmetic on text, most often a number that was imported as text.
• #DIV/0! — division by zero or by an empty cell.
• #NAME? — a function or named range the spreadsheet does not recognize. Usually a typo, or a function that exists in Excel but not in Sheets.
• #NUM! — a mathematically impossible result, such as the square root of a negative number.
• #SPILL! — a dynamic array formula cannot expand because something is in the way. Clear the cells below or to the right.

Wrap a lookup in IFERROR to show something friendlier: =IFERROR(XLOOKUP(...), "Not found"). Use it deliberately, though — IFERROR hides every error, including the #REF! that would have told you the formula is broken. IFNA is the safer choice when you only want to catch failed lookups.

Numbers stored as text

This is the most common data problem in any imported spreadsheet. Values that look like numbers are actually text, so SUM quietly returns zero and lookups fail to match. The usual signs are left-aligned values (numbers align right by default) and a small green triangle in the cell corner.

Causes include leading apostrophes, non-breaking spaces from a web copy-paste, thousands separators, currency symbols, and CSV imports where the column was typed as text. Fixes range from multiplying by 1, to =VALUE(TRIM(A1)), to Text to Columns with the right format. TRIM removes ordinary spaces but not non-breaking ones — for those you need SUBSTITUTE(A1, CHAR(160), "") first.

Dates are numbers wearing a costume

A date is stored as a serial number counting days from a fixed origin, and the date you see is formatting applied on top. That is why subtracting two dates gives a plain number of days, and why a date occasionally appears as something like 45,292 — the value is fine, the format was lost.

It also means dates imported as text will not sort, filter or subtract correctly no matter how right they look. And be careful with regional order: 03/04/2026 is March 4th or April 3rd depending on locale, and a mixed import can silently convert only the rows where the interpretation is unambiguous.

Use the plural forms

SUMIFS, COUNTIFS and AVERAGEIFS accept multiple conditions, and their argument order is more logical than the singular versions — the range to sum comes first, followed by condition pairs. Prefer them even for a single condition, so adding a second criterion later does not mean rewriting the formula backwards.

Excel and Google Sheets are not quite the same

• Argument separators differ by locale. Many European settings use semicolons where the examples here use commas.
• ARRAYFORMULA and QUERY are Sheets-only. QUERY in particular is genuinely powerful and has no Excel equivalent.
• Dynamic arrays exist in both but behave differently, and older Excel versions need Ctrl+Shift+Enter for array formulas.
• Volatile functions — NOW, TODAY, RAND, OFFSET, INDIRECT — recalculate on every change. A few are harmless; hundreds will make a large file crawl.

Habits that pay off later

• Put constants such as tax rates in their own labeled cells and reference them, rather than typing the number into twenty formulas. When the rate changes you edit one cell.
• Format your data as a Table (Excel) or use named ranges, so references read Sales[Amount] rather than D2:D500 and grow automatically as rows are added.
• Build complex formulas one piece at a time, checking each in its own cell before nesting them. Debugging a single 200-character formula is far harder than testing four short ones.
• Never hide a hard-coded adjustment inside a formula. Someone — possibly you — will spend an afternoon trying to work out why the total is off by 3%.

Frequently asked questions