Last updated: October 2026
Build any Excel formula in 4 steps
Short answer: 1. pick the category and task above (e.g. Math & Trig → Addition → SUMIF). 2. Fill in your cell ranges and values — the form tells you exactly what each argument wants. 3. Click Generate Formula and copy the result into any cell. 4. Read the how-to-use panel for a worked example and the fixes for the errors you'll most likely hit.
Math & Trig formulas
The workhorses: Addition (SUM, SUMIF, SUMIFS, SUMPRODUCT), Rounding (ROUND, ROUNDUP, ROUNDDOWN, INT, TRUNC) and More (AVERAGE, AVERAGEIF(S), COUNT, COUNTA, MAX, MIN, ABS, POWER, SQRT, MOD, SUBTOTAL, AGGREGATE, RANDBETWEEN). The two argument-order traps live here: SUMIFS puts the sum range first (SUMIF puts it last), and SUBTOTAL's function numbers (109 = sum of visible cells) are worth memorizing for filtered lists.
Text formulas
Combine (CONCAT, TEXTJOIN), Extract (LEFT, RIGHT, MID, TEXTBEFORE, TEXTAFTER, TEXTSPLIT) and Clean & format (UPPER, LOWER, PROPER, TRIM, CLEAN, SUBSTITUTE, REPLACE, TEXT, VALUE, LEN, EXACT, REPT, FIND, SEARCH). Know the pairs: SUBSTITUTE matches content while REPLACE matches positions; FIND is case-sensitive while SEARCH is not; TRIM fixes the invisible spaces that break lookups.
Lookup & Reference formulas
VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH, XMATCH, CHOOSE, OFFSET, INDIRECT, ROW, COLUMN, ADDRESS and LOOKUP. If you learn one thing here: XLOOKUP replaces VLOOKUP for new work (exact match by default, looks left, built-in not-found message), and INDEX+MATCH is the classic formula that looks left when you're stuck on old Excel.
Date & Time formulas
Current date (TODAY, NOW), Build dates (DATE, DATEVALUE), Parts of a date (YEAR, MONTH, DAY, WEEKDAY, WEEKNUM, HOUR, MINUTE), Date math (DATEDIF, DAYS, EDATE, EOMONTH) and Workdays (NETWORKDAYS, WORKDAY). Remember: DAYS takes the end date first, and DATEDIF with "Y" is the reliable age formula.
Logic formulas
IF, IFS, AND, OR, NOT, XOR, SWITCH, LET plus Error handling (IFERROR, IFNA). The two upgrades that clean up ugly sheets: IFS replaces nested-IF pyramids, and IFNA catches only #N/A so real bugs stay visible while lookup misses get a friendly message.
Statistics formulas
Counting (COUNTIF, COUNTIFS), Conditional extremes (MAXIFS, MINIFS), Ranking (LARGE, SMALL, RANK.EQ), Averages (MEDIAN, MODE.SNGL) and Spread (STDEV.P, PERCENTILE.INC). MEDIAN beats AVERAGE whenever one outlier would drag the mean somewhere silly.
Financial formulas
Loans (PMT, IPMT, PPMT, RATE, NPER), Savings (FV, PV), Investment (NPV, IRR) and Depreciation (SLN). Two rules prevent 90% of finance errors: the rate and periods must use the same unit (5%/12 with 360 for monthly), and NPV assumes the first cash flow is one period out — add any upfront investment outside the function.
Information formulas
Cell checks: ISBLANK, ISERROR, ISNA, ISNUMBER, ISTEXT, ISEVEN, ISODD and TYPE. These are debugging tools: ISNUMBER exposes numbers-stored-as-text, TYPE tells you what a mystery cell really holds, and ISNA distinguishes "not found" from genuine errors.
Database formulas
Database (DSUM, DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP, DGET): run SUM, AVERAGE, COUNT-style analysis on a database list using criteria ranges. Every one takes the same three arguments — the database range, the field, and the criteria range — so learn DSUM and you know them all. They shine when your filter conditions live in cells and change often.
Engineering formulas
Engineering (BIN2DEC, BIN2HEX, BIN2OCT, COMPLEX, CONVERT): convert between binary, decimal, hexadecimal and octal number systems, and convert measurements between units. CONVERT alone handles weight, distance, temperature, volume, time and more — meters to feet, kilograms to pounds, Celsius to Fahrenheit — with a single function.
When to use this
Use it when you know what you want — "add sales for the East region", "find the price for this code", "how many workdays until launch" — but not the exact function or argument order. It's also the fastest way to sanity-check a formula someone sent you: rebuild it here and compare. If you already know the function by heart, the per-formula error tables are still worth a skim — they're the fixes for the errors Excel users hit most.
How it was tested: every formula's syntax and argument order was verified against Excel's documented signatures, the builder's output was checked for all 291 formulas (including optional-argument handling), and each worked example was hand-verified.
Frequently asked questions
How do I write a SUMIF with multiple conditions?
Use SUMIFS — and note the argument order flips: the range to add comes FIRST: =SUMIFS(C2:C100, A2:A100, "East", B2:B100, ">50"). SUMIF puts the sum range last; SUMIFS puts it first. The builder above generates both correctly.
Why is my VLOOKUP returning #N/A?
Almost always one of four things: the lookup value is not in the first column (extra spaces are the classic culprit — clean with TRIM), a number-vs-text mismatch ("123" does not equal 123), the table range shifted when you copied the formula down (lock it with $, e.g. $A$2:$C$100), or you used TRUE as the last argument, which needs sorted data. For new work, XLOOKUP avoids most of these.
Should I use XLOOKUP or VLOOKUP?
XLOOKUP if your Excel has it (Microsoft 365 or Excel 2021 and later): exact match by default, looks left or right, no column counting, and a built-in "not found" message. Use VLOOKUP only when the file must open in old Excel versions.
Why does my formula show as text instead of calculating?
The cell is formatted as Text. Change the format to General, then re-enter the formula (click the cell, press F2, press Enter). Formulas typed into Text-formatted cells never calculate.
Why is my SUM returning 0 when I can see numbers?
The "numbers" are stored as text — common after pasting from the web. SUM ignores text. Select the range, click the warning icon that appears, and choose Convert to Number.
How do I stop #DIV/0! errors?
Wrap the division: =IFERROR(A2/B2, "—") shows a dash instead of the error. Better still, check the divisor first: =IF(B2=0, "—", A2/B2).
How do I keep a cell reference from changing when I copy a formula?
Lock it with dollar signs: $A$1 locks both row and column, $A1 locks the column, A$1 locks the row. Press F4 while editing a reference to cycle through the four states.
What is the difference between COUNT, COUNTA and COUNTBLANK?
COUNT counts only numbers; COUNTA counts every non-empty cell (text included); COUNTBLANK counts empty cells. One frequent gotcha: formulas returning "" count as non-empty for COUNTA.
How do I join names with a space between them?
=TEXTJOIN(" ", TRUE, A2, B2) — the space is the delimiter and TRUE skips blanks. For a one-off pair, =A2&" "&B2 works too.
How do I show formulas in Excel instead of their results?
Press Ctrl+` (the grave accent key, usually above Tab) to toggle between results and formulas — or go to the Formulas tab and click Show Formulas. Press Ctrl+` again to switch back. If only one cell shows its formula as text, that cell is formatted as Text: change it to General and re-enter the formula.
How do I lock formulas so nobody can change them?
Two steps. First, select the cells people may edit, press Ctrl+1, open the Protection tab and uncheck Locked. Then go to Review, click Protect Sheet, set a password and click OK. Every still-locked cell — including all your formulas — can no longer be edited until the sheet is unprotected.
How do I hide formulas but keep the results visible?
Select the formula cells, press Ctrl+1, open the Protection tab and check Hidden (leave Locked checked too). Then go to Review and click Protect Sheet. The results stay visible, but the formula bar goes blank when those cells are selected.
What is the syntax of an Excel formula?
Every formula starts with = followed by a function name and its arguments in parentheses: =FUNCTION(argument1, argument2, ...). Arguments can be numbers, text in quotes, cell references like B2:B10, or other formulas. In documentation, square brackets mean optional: =XIRR(values, dates, [guess]) requires values and dates, while guess is optional.
How do I convert formulas to their values?
Copy the cells, then right-click and choose Paste Special, then Values (or Home, Paste, Values). The results stay but the formulas are gone — useful before sharing a sheet so nobody can break your calculations.
Is the Excel Formula Builder private?
Completely. The whole formula database ships with the page and everything runs in your browser — nothing you type is sent to a server, stored, or tracked.
