Spreadsheet function comparisons

Head-to-head guides for the functions people mix up — what actually differs, which to use when, and how support varies across Excel, Google Sheets, and LibreOffice (Google Sheets and LibreOffice results executed by our test harness; Excel from official documentation). For the app-level picture, see the Excel vs Google Sheets formula guide.

• AND vs OR: all conditions or any condition

  The two workhorses of logic: AND is true only when EVERY condition is true; OR is true when AT LEAST ONE is. On their own they return TRUE/FALSE, but you'll...

• ARRAYFORMULA vs dynamic arrays: the same idea, three dialects

  Google Sheets makes formulas operate over whole ranges by wrapping them in ARRAYFORMULA; modern Excel and LibreOffice instead auto-spill any array...

• AVERAGE vs AVERAGEA (and COUNT vs COUNTA): the A matters

  The A-suffix functions treat non-numeric cells as values (text counts as 0, TRUE as 1) instead of skipping them. That's occasionally what you want and more...

• AVERAGE vs MEDIAN: which 'typical value' to trust

  Both answer 'what's the typical value?', but differently: AVERAGE (the mean) adds everything up and divides, so a few extreme values drag it around; MEDIAN...

• CHAR vs CODE vs UNICHAR: characters by number

  CHAR turns a number into a character and CODE goes the other way — but they speak 8-bit: numbers up to 255. UNICHAR/UNICODE are the same pair with the full...

• CONCATENATE vs CONCAT vs TEXTJOIN: which to use?

  Three generations of the same idea: CONCATENATE is the legacy function, CONCAT adds range support, and TEXTJOIN adds the two things everyone actually wants...

• COUNT vs COUNTA: numbers only, or everything?

  They look like twins but count different things: COUNT tallies only numbers, while COUNTA tallies every cell that isn't empty — text, dates, errors,...

• COUNTIF vs COUNTIFS: one letter, one big difference

  COUNTIFS is COUNTIF's multi-condition sibling — and unlike the SUMIF/SUMIFS pair, the argument order doesn't flip, so the safest habit is simply: always use...

• COUNTIF vs SUMIF: counting rows vs adding values

  Same 'if' idea, two different questions. COUNTIF answers 'how many rows match?' — it tallies cells. SUMIF answers 'what's the total of the values in...

• DATE vs DATEVALUE: build a date or parse one

  Both hand you a real date value, but from opposite starting points. DATE builds one from separate year, month, and day NUMBERS. DATEVALUE parses one out of...

• DATEDIF vs YEARFRAC: whole units or a fraction

  Both measure the distance between two dates in year-terms, but they answer different questions: DATEDIF counts COMPLETED calendar units (a person is 34...

• EDATE vs EOMONTH: same-day or month-end

  Both jump a date by whole months; they differ in where they land. EDATE keeps the day-of-month (the 15th stays the 15th); EOMONTH always lands on the...

• FILTER vs QUERY: Sheets' two ways to slice data

  In Google Sheets these overlap heavily, so people ask which to learn. The portability answer is decisive: FILTER now exists in Excel and LibreOffice too...

• FILTER vs VLOOKUP: return every match, not just the first

  VLOOKUP finds the first row that matches and returns one value. FILTER returns every row that matches, spilling them into a range. If a key can appear more...

• FIND vs SEARCH (vs MATCH): locating text the right way

  FIND and SEARCH both return where a substring starts inside a text cell — they differ on exactly two things: case sensitivity and wildcards. MATCH sounds...

• IF vs IFS: when to stop nesting

  IFS exists to kill the nested-IF pyramid: =IF(a,x,IF(b,y,IF(c,z,...))) becomes one flat list of condition/value pairs. The catch: IFS has no built-in...

• IF vs SWITCH: comparisons or exact matches

  SWITCH tests ONE expression against a list of exact values — perfect for mapping codes to labels. IF (and its flat sibling IFS) handle ranges and...

• IFERROR vs IFNA: don't hide the errors you need

  Both replace an error with a fallback value — the difference is scope. IFERROR swallows EVERY error; IFNA only catches #N/A, the "not found" signal. Around...

• IPMT vs PPMT: where each payment actually goes

  Every fixed loan payment is the same size but a different mixture: IPMT returns the interest slice of payment N, PPMT the principal slice, and for any...

• ISBLANK vs ="": the two kinds of empty

  A cell can LOOK empty two ways: truly empty (nothing in it) or holding a formula that returns "". ISBLANK only believes the first; A2="" accepts both. Half...

• ISERROR vs ISERR vs ISNA: how wide a net

  Three error detectors that differ only in scope: ISERROR catches everything, ISNA catches only #N/A (the 'not found' signal), and ISERR is the odd middle...

• LOOKUP vs VLOOKUP: why the old one still shows up

  Bare LOOKUP predates VLOOKUP and survives for two reasons: its vector form is pleasantly direct (lookup range and result range as separate arguments — like...

• MAX vs LARGE: the biggest, or the nth biggest

  MAX answers one question; LARGE answers a family of them: LARGE(range,1) IS the max, LARGE(range,2) the runner-up, and so on down. The subtlety worth...

• MOD vs QUOTIENT: remainder and integer division

  Divide 17 by 5 and you get two useful integers: how many times it fits (QUOTIENT → 3) and what's left over (MOD → 2). Together they decompose any division —...

• NPV vs PV: which discounting function do you need?

  Both discount future money to today. PV handles the special case of IDENTICAL payments at regular intervals (annuities, loans); NPV takes a list of...

• OFFSET vs INDEX: same tricks, very different cost

  Both can build references that move and resize — the difference is that OFFSET is VOLATILE: it recalculates on every change anywhere in the workbook, and...

• PROPER vs UPPER vs LOWER: the three text-case functions

  Three one-argument functions that reformat text case: UPPER shouts everything, LOWER quiets everything, and PROPER capitalizes the first letter of each...

• RAND vs RANDBETWEEN vs RANDARRAY: three flavors of random

  One value or a grid, decimals or integers: the random family splits by shape and type. All three share the same gotcha — volatility — and the same cure...

• ROUND vs INT vs TRUNC: they disagree below zero

  On positive numbers these three feel interchangeable and people use whichever they learned first. The differences bite with negatives and with digits: INT...

• ROUND vs MROUND: to decimals or to a multiple

  ROUND snaps to a number of decimal PLACES; MROUND snaps to the nearest MULTIPLE of whatever you give it. "Round to 2 decimals" is ROUND; "round to the...

• ROUNDUP vs CEILING: digits or multiples

  Both push numbers up, but along different grids: ROUNDUP works in DECIMAL DIGITS (up at the 2nd decimal), CEILING in MULTIPLES (up to the next 0.25, 5, or...

• SUBSTITUTE vs REPLACE: by text or by position

  They sound like synonyms; they're opposites in how they aim. SUBSTITUTE finds TEXT you know ("replace every dash") — REPLACE overwrites a POSITION you know...

• SUM vs SUBTOTAL: why your filtered totals are wrong

  Filter a table and SUM keeps adding the rows you just hid — that's the bug that leads people here. SUBTOTAL(9,...) counts only what's visible after...

• SUM vs SUMPRODUCT: when the extra power is worth it

  SUM adds up numbers. SUMPRODUCT multiplies corresponding values across ranges and THEN adds — which quietly makes it a Swiss-army knife for weighted totals...

• SUMIF vs SUMIFS: what's the difference?

  SUMIFS does everything SUMIF does and handles multiple conditions — but the two put their arguments in OPPOSITE orders, which is the bug most people...

• SUMIF vs SUMPRODUCT: conditional sums two ways

  SUMIF (and SUMIFS) cover the common cases fast; SUMPRODUCT is the escape hatch when conditions get interesting — OR logic, calculations inside the...

• SUMIFS vs SUMPRODUCT: when the clean one isn't enough

  SUMIFS is the clean, fast way to sum with several AND conditions. SUMPRODUCT is the swiss-army knife you reach for when SUMIFS can't do the job — OR logic,...

• TEXT() vs cell formatting: same codes, different consequences

  The TEXT function and custom number formats speak the same format-code language ("0.0%", "yyyy-mm-dd", "+0;-0;0") — but TEXT converts the value into a...

• TEXTBEFORE/TEXTAFTER vs LEFT/MID/RIGHT: delimiter or position

  Every 'extract part of a cell' task used to mean LEFT/MID/RIGHT wrapped around FIND arithmetic. TEXTBEFORE and TEXTAFTER say what you mean — 'the part...

• TODAY vs NOW: date, or date plus time?

  TODAY() is midnight at the start of today; NOW() is this exact second. The fractional day NOW carries is what quietly breaks date comparisons — and neither...

• TRIM vs CLEAN: the import-cleanup pair (and what both miss)

  Pasted and imported text carries invisible junk that breaks lookups and duplicates 'identical' values. TRIM and CLEAN each remove a different kind — and the...

• UNIQUE function vs Remove Duplicates: live formula or one-time cleanup?

  Same goal, opposite philosophies: the Remove Duplicates menu command edits your data once and destructively; the UNIQUE function leaves the source untouched...

• VALUE vs NUMBERVALUE: parsing text to numbers, and locales

  Both turn text that looks like a number into a real number so you can do math. VALUE uses your spreadsheet's locale settings to interpret separators;...

• VLOOKUP vs HLOOKUP: vertical or horizontal?

  They're the same function rotated 90°: VLOOKUP searches the first COLUMN and reads across; HLOOKUP searches the first ROW and reads down. VLOOKUP dominates...

• VLOOKUP vs INDEX/MATCH: which is better?

  INDEX/MATCH was the power user's lookup long before XLOOKUP existed, and it still matters because it runs in every spreadsheet app and every version — while...

• VLOOKUP vs XLOOKUP: which should you use?

  XLOOKUP is VLOOKUP's modern replacement — but VLOOKUP still wins on one thing: it runs everywhere, including old Excel versions and every LibreOffice...

• WORKDAY vs NETWORKDAYS: two sides of business days

  Mirror twins: NETWORKDAYS answers "how many business days between these dates?", WORKDAY answers "what date is N business days from here?". Mixing them up —...

• XLOOKUP vs INDEX/MATCH: is there still a reason to nest?

  XLOOKUP does in one call nearly everything INDEX/MATCH does in two — so the real question is portability: INDEX/MATCH works in every spreadsheet ever...