Every spreadsheet formula that actually functions contains five structural components: an equals sign, a function name or cell reference, a valid argument or operator chain, balanced parentheses and separators, and a working numeric value to evaluate. Miss any one and Excel either rejects the entry outright or β€” worse β€” accepts it and returns a plausible-looking wrong answer. The five components are the same in Google Sheets, LibreOffice Calc, and Numbers; the failure paths differ. Below is the breakdown, the exact point where each component breaks, and the version-specific gaps that confuse experienced users most.

The Equals Sign Is the Component Most Often Missing

Excel treats any entry starting with = as a formula and anything else as text or a number. Type SUM(A1:A5) without the leading equals and the cell displays that literal string, left-aligned. That left alignment is the quickest diagnostic: formulas return values in Excel's General number format, usually right-aligned.

The equals sign is a switch, not a decoration. Once typed, the cell enters formula-edit mode and Excel starts parsing what follows as an expression. Google Sheets behaves identically. LibreOffice Calc has a settings path β€” Tools > Options > Calc > Formula β€” that historically allowed other starting characters, so a Calc sheet shared with colleagues may rely on + instead of =. Excel accepts + as a formula starter only inside an existing formula, not at the start of a fresh entry.

An apostrophe forces text even when a valid formula follows it. '=SUM(A1:A5) displays the formula string. Users who paste data from web pages often inherit apostrophes invisibly; the cell looks like a formula but never calculates. Strip the apostrophe in the Formula Bar and press Enter.

Power Query and CSV import complicate this. When Excel imports a column containing =1+1, it may treat the value as text depending on the import wizard settings, leaving the formula inert. If a downloaded CSV shows raw formulas, use Data > From Text/CSV and set the column type explicitly rather than opening the file by double-click.

Cell References and Function Names Are Two Different Component Types

A cell reference names a location: A1, $B$7, Sheet2!C4, or a structured table reference such as Sales[Amount]. A function name names an operation: SUM, VLOOKUP, IF, XLOOKUP. Most workable formulas combine both, and each has a distinct failure mode.

Enter =A1 in an empty cell and Excel returns 0 β€” not an error. Blank cells evaluate as zero in arithmetic. That default catches people who build formulas against empty ranges and assume a blank means "no data"; the formula silently computes a zero as if it were a real measurement.

Function names are case-insensitive in Excel (sum and SUM do the same thing), but spelling is not. =SUMM(A1:A5) returns #NAME? because Excel cannot resolve the identifier. Modern Excel's AutoComplete dropdown reduces typos but not eliminated them β€” pasting a formula from a web tutorial into a locale where SUM is translated can break. French Excel spells it SOMME. German spells it SUMME. A formula pasted from an English tutorial into a French installation fails with #NAME? on every translated function name.

Ranges use a colon: A1:A5 spans a vertical block, A1:E1 a horizontal one. A comma as the separator inside a range is a locale difference, not a range. In most of Europe the argument separator is a semicolon (SUM(A1:A5;B1:B5)), while in the US and UK it is a comma. SUM(A1:A5,B1:B5) in a French install reads as SUM(A1:A5,B1:B5) in the wrong structure and returns an error or, worse, silently sums only part of the intended block.

Arguments and Operators Must Match the Function's Expected Shape

Every function has a signature. VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) requires at least three arguments and accepts a fourth. Feed it two and Excel returns #N/A or, in newer versions, a formula-parse error before the cell is even committed. The signature is exposed through the Formula Bar's function tooltip in Excel 365, which is the fastest way to confirm what an unfamiliar function actually wants.

Operator precedence matters inside an argument. =1+2*3 returns 7, not 9, because multiplication binds tighter than addition. Users expecting sequential reading get a wrong answer with no error. Parenthesize to force sequence: =(1+2)*3 returns 9. The same trap fires inside function arguments: =SUM(A1:A5)2 doubles a sum, =SUM(A1:A52) is an array formula that may return a single value or spill, depending on the Excel version.

Comparison operators return TRUE or FALSE. =A1>5 returns TRUE if A1 holds 6 and FALSE if it holds 4. Coerce to a 1 or 0 with -- (double unary) or wrap in IF. Spreadsheets expose this through the SUMPRODUCT trick β€” =SUMPRODUCT(--(A1:A10>5)) counts how many cells exceed 5, because the double unary converts TRUE/FALSE to 1/0 before summing. It is one of the few ways to get a count in older Excel versions before COUNTIFS was reliable.

Text and numbers are not interchangeable. =A1+B1 when A1 contains "5" as text and B1 contains 3 returns 8 in most modern Excel versions β€” the coercion is forgiving. ="5"+3 returns 8, but ="five"+3 returns #VALUE!. Text that looks numeric converts; text that does not returns an error. Dates stored as text are worse: =A1+1 where A1 is a text-stored date returns #VALUE!, and the cell may have looked correct in the display.

The Version-Specific Components Excel 365 Ships Without Previous Versions

Excel 365 introduced dynamic arrays in 2020, and that reshaped what a "working formula" means. Before dynamic arrays, =A1:A5*2 in a single cell returned the first element only, requiring Ctrl+Shift+Enter to enter as an array formula. Excel 2019 and earlier still require that keystroke. Excel 365 spills the entire result into adjacent cells automatically, and the #SPILL! error appears when the destination cells are not empty.

The spill behavior means a formula that worked in Excel 2016 may look broken in 2019 or the other way around. If a workbook built with Excel 365 spills into a range of cells and the user later opens it in Excel 2019 without dynamic array support, the dependent cells show #NAME? or blank. Microsoft's guidance on dynamic array compatibility is to convert spilled ranges to values before sharing across versions if the recipient lacks Excel 365.

XLOOKUP and FILTER and SORT are Excel 365 and Excel 2021 only. XLOOKUP replaces VLOOKUP in many tutorials but is unavailable in Excel 2019. Users opening a workbook in an older install see #NAME? on those cells with no obvious cause. Chrome and Edge browsers with the free Excel web app follow Excel 365 capabilities, but the desktop Excel for Mac 2019 release does not.

Google Sheets diverges here. Sheets has had dynamic arrays since 2017 and supports FILTER, SORT, XLOOKUP (added 2023), and ARRAYFORMULA, which is the Sheets equivalent of Ctrl+Shift+Enter for older Excel. The #SPILL! error does not exist in Sheets; oversized array results return #REF! instead. A user moving a workbook between the two must recheck every array-style formula.

How to Fix the Five Most Common Component Failures

Most formula breakage traces to a handful of causes. The following ordered steps lead to the fault fastest:

1: Click the broken cell and read the Formula Bar. The Formula Bar shows exactly what was entered, including invisible leading characters and missing equals signs. 2: Check for #NAME? first. This error almost always means a misspelled function, an unquoted text string, or a function name in a translated locale (SOMME vs SUM). 3: Check for #VALUE! next. This appears when an arithmetic operator sees text that cannot convert to a number β€” most often a date stored as text or a stray apostrophe. 4: Check for #REF!. A deleted row, column, or sheet reference produces this; the referenced cell location no longer exists. 5: Check for #DIV/0!. A denominator evaluates to zero. This catches users who divide by a count or sum that is empty. 6: Check for #N/A. This is the classic VLOOKUP miss: the lookup value is not present in the first column of the table. 7: Check paren counts. Excel's Formula Bar highlights the matching parenthesis in matched pairs when the cursor is on one; unmatched pairs highlight inconsistently. Count them manually if the highlight is unclear.

The failure case that breaks most beginners: Excel's Formula Bar autocompletes function names but not function signatures. Type =IF( and Excel waits; type an incomplete argument list and press Enter and the error is a parse failure with no helpful hint. Press Ctrl+A after typing the function name with an open paren and Excel opens the Function Arguments dialog, which names each required argument and flags any that are missing.

Locale mismatch deserves its own troubleshooting step. If a workbook from a colleague in a different region shows wrong results, check the File > Options > Advanced > "Use system separators" toggle. In French and German Excel installations, the list separator is a semicolon by default. Opening a US-authored workbook in that install can misread SUM(A1:A5,B1:B5) as SUM(A1:A5,B1:B5) with extra commas that no longer separate arguments.

Comparison: Excel, Google Sheets, and LibreOffice Calc on Component Requirements

Component Excel 365 Google Sheets LibreOffice Calc
Formula starter = (required) = (required) = (default; configurable)
Function names localized Yes (e.g. SOMME in French) No (English only) Yes (translated)
Argument separator (US locale) Comma Comma Comma (default)
Argument separator (EU locale) Semicolon Comma regardless of locale Semicolon
Dynamic array auto-spill Yes, since Excel 365 Yes, since 2017 Yes, since Calc 7.1
Spill-overflow error #SPILL! #REF! #VALUE! (partial)
Array entry keystroke Ctrl+Shift+Enter (Excel 2019 and earlier) ARRAYFORMULA() Ctrl+Shift+Enter (legacy)
XLOOKUP availability Excel 365, Excel 2021+ Added 2023 Added 24.8

The table reveals a structural truth: three products, four separator rules, and three different errors for the same underlying condition. Users who move spreadsheets between tools need to verify the argument separator and function-name locale on first open, not after a wrong answer.

Google Sheets' locale-independent comma behavior is a deliberate design choice; Sheets uses the user's display locale for dates and currency but keeps formula syntax in English. That makes international collaboration easier in Sheets and harder in Excel. The cost: a formula built in Sheets and pasted into Excel in a French install works, but the reverse fails.

LibreOffice Calc's configurable formula starter means a LO-generated spreadsheet could use + as the starter and open in Excel as text. When trading files with Calc users, check Tools > Options > Calc > Formula > Recalculation on File Load, and confirm the "Formula syntax" setting matches the recipient.

Relative and Absolute References Change What "Working" Means

A formula that returns the right number once may return the wrong number when copied. The reference type decides this: A1 is relative, $A$1 is absolute, $A1 locks the column only, A$1 locks the row only. Get the lock wrong and a copy operation silently changes the reference.

The dollar-sign lock is the least intuitive component because the cell shows no visual warning when it is missing. Copy =A12 from B1 to B2 and it becomes =A22. Copy =$A$12 from B1 to B2 and it stays =$A$12. Both are "working formulas"; only one computes what the user intended when copied down a column.

The F4 key toggles the reference type while editing. Press F4 on A1 and Excel cycles to $A$1, then A$1, then $A1, then back. In Google Sheets, F4 on a Mac may trigger Mission Control or the browser address bar; use Command+Shift+4 alternatives or the keyboard shortcut Cmd+Shift+F4 in some setups, or edit the dollar signs manually.

Structured table references add another layer. Sales[@Amount] means "the Amount column in the current row of the Sales table." Copy down and it stays row-relative to the table, not the sheet. Copy to a cell outside the table and Excel returns #NAME?. Tables were introduced in Excel 2007; spreadsheets with structured references opened in Excel 2003 fail at the table level.

Named ranges substitute a word for a reference. =SUM(Revenue) works after Revenue is defined as =Sheet1!$B$2:$B$100. Delete the row that anchors the named range and the formula silently changes what it sums. Microsoft's Name Manager (Formulas > Name Manager) shows every defined name and its current reference; it is the fastest diagnostic for a formula that returns "slightly off" results after row edits.

Text and Number Coercion: The Component No Formula Checks

The sixth implicit component β€” data type β€” receives no syntax check from Excel. A cell can look like a number, display like a number, and still be text. Formulas that reference it return #VALUE! on arithmetic or silently coerce when coercible, and the user spends hours chasing a formula that is structurally perfect.

The telltale sign is alignment. Text cells are left-aligned; numeric cells are right-aligned in Excel's General format. A column of imported prices that refuses to sum is almost always text. =ISTEXT(A1) returns TRUE when A1 contains any text, including text that looks numeric. =ISNUMBER(A1) returns TRUE only for genuine numeric values.

Convert text-stored numbers with one of three methods. Multiply by 1 in an adjacent column (=A1*1), then copy-paste values. Select the range and use Data > Text to Columns > Finish β€” this forces reparse and converts common formats. Or use =VALUE(A1) in a helper column, then copy-paste values and delete the helper.

The dates-as-text trap is worse. Excel stores dates as serial numbers (January 1, 1900 = 1 in the Windows version; January 1, 1904 = 0 in the legacy Mac version, still selectable in File > Options > Advanced). A date entering as text looks identical in the cell but fails =A1+30. The Mac 1904 date system fires a distinct failure: a workbook shared between Mac and Windows may show dates shifted by four years and one day, from January 1, 1904 versus January 1, 1900 anchor. Microsoft's own documentation on the 1904 date system warns to check File > Options > Advanced > "Use 1904 date system" when moving workbooks across platforms.

Wrapping the Working Formula: LET, LAMBDA, and the Component Count Nobody Mentions

Excel 365's LET lets you name intermediate results inside a formula, and it changes the practical component checklist. A formula like =LET(x, A1:A5, y, SUM(x), y/COUNT(x)) has no visible arguments in the classic sense; x and y are local variables the user defines, and they must be valid names (no spaces, no cell-looking strings) or Excel returns #NAME? on the whole formula.

LAMBDA takes this further by letting a user define a reusable function. Passing =LAMBDA(x, x+1)(5) returns 6. The lambda's parameter name (x) is a component the user introduces; miss it in the definition or the call and the formula fails with #CALC! or #VALUE! depending on the mismatch.

Neither LET nor LAMBDA exists in Excel 2019 or earlier. A workbook that uses them opens with #NAME? in older versions. Google Sheets supports LET since 2022 and LAMBDA since 2022; LibreOffice Calc supports LET in recent versions but not LAMBDA as of Calc 24.8. The component list is growing, and that growth is itself a compatibility hazard.

The practical rule for teams sharing workbooks: keep formulas on the lowest common feature set of the oldest Excel version anyone will open. That usually means XLOOKUP-free, LET-free, dynamic-array-free workbooks. Microsoft's own compatibility checker runs on File > Info > Check for Issues > Check Compatibility and flags unsupported functions before sharing.

FAQ

Why does a formula work in one Excel file and return #NAME? in another?

The recipient's Excel version lacks the function. XLOOKUP, FILTER, UNIQUE, LET, and LAMBDA all require Excel 365 or Excel 2021 (LET/LAMBDA require 365). A workbook opened in Excel 2019 or on Excel for Mac 2019 returns #NAME? on every cell using those functions with no warning at open. Microsoft's compatibility checker (File > Info > Check for Issues) lists every unsupported function and the target version before you save.

Should I use a comma or semicolon to separate arguments?

Use what your Excel installation's list separator is set to: File > Options > Advanced > "Use system separators." The US default is a comma; French and German installations default to a semicolon. If a shared formula returns #VALUE! or misreads arguments after opening, check this setting first β€” it does not adjust automatically when files travel between locales. Google Sheets always uses a comma, regardless of locale.

Why does =A1 return 0 instead of blank when A1 is empty?

A blank cell evaluates as zero inside arithmetic. Excel's formula engine treats an empty reference as 0 for addition, subtraction, and comparison, but returns "" for text functions. To force an empty-looking result on a blank source, wrap it: =IF(A1="","",A1). The zero-default is documented behavior, not a bug, and it catches users who assume blank equals missing.

Can a formula start with something other than =?

Excel requires = at the start of a fresh entry. LibreOffice Calc historically allowed + and other starters via Tools > Options > Calc > Formula. A Calc-generated .ods file that uses + as the starter, saved and reopened as .xlsx in Excel, displays as text. For portable workbooks, confirm every starter is = before sharing. An apostrophe before the equals forces text and defeats the formula even in Excel.

What is the difference between #VALUE! and #REF!?

#VALUE! means the formula syntax is correct but a value is the wrong data type β€” most often text that cannot be coerced to a number, or a text-stored date being used in arithmetic. #REF! means a reference points to a cell that no longer exists, usually from a deleted row or column. Both errors can also arise from a broken defined name; Formulas > Name Manager shows every named reference and its current target.

How do I build a formula that works in Excel and Google Sheets both?

Restrict yourself to functions that exist in both: SUM, IF, VLOOKUP, INDEX, MATCH, SUMIF, and COUNTIFS work across versions. Avoid XLOOKUP, FILTER, LET, LAMBDA, and structured table references if the formula must run in both. Also confirm the list separator before sharing: Sheets accepts commas only, while Excel's separator is locale-dependent. Test in a throwaway copy of the destination platform, not the live workbook.

Why does copying a formula produce the wrong answer on the destination cell?

The reference type is wrong. A1 shifts when copied; $A$1 stays fixed. Press F4 on a reference while editing in Excel to cycle through $A$1, A$1, and $A1. The F4 shortcut behaves differently in Google Sheets (it can trigger Mission Control on Mac), so edit dollar signs manually there. A formula whose result changes unexpectedly on copy almost always has one unlocked reference that should have been locked.