Excel formulas help you calculate totals, test conditions, find values, and reshape text without repeating manual work.
This guide explains useful formulas with sample ranges and expected results, showing how each one fits into a worksheet.
How Excel formulas work
A formula begins with an equals sign (=).
It can contain numbers, cell references, operators, and functions.
For example, =A2+B2 adds the values in cells A2 and B2.
If A2 contains 12 and B2 contains 8, the result is 20.
Functions use parentheses to hold their inputs, called arguments.
A range such as A2:A10 refers to every cell from A2 through A10.
In the examples below, formulas use commas between arguments, as in many English-language Excel installations.
Some regional settings require semicolons instead.
Basic calculation formulas
Arithmetic operators let you calculate directly from cell references.
Excel uses + for addition, - for subtraction, * for multiplication, and / for division.
Parentheses control the order of operations.
=B2-C2subtracts an expense in C2 from a value in B2.
If B2 is 75 and C2 is 20, the result is 55.=B2*C2multiplies a unit price by a quantity.
If B2 is 4.5 and C2 is 6, the result is 27.=(B2+C2)*D2adds B2 and C2 before multiplying by D2.
Use references rather than typing fixed values into a formula when the values already appear in the worksheet.
That makes the calculation easier to update when source data changes.
SUM, AVERAGE, MIN, and MAX
These functions summarize a range of numeric values.
Suppose B2:B6 contains 12, 18, 20, 25, and 25.
=SUM(B2:B6)returns 100.=AVERAGE(B2:B6)returns 20.=MIN(B2:B6)returns 12.=MAX(B2:B6)returns 25.
SUM ignores text in referenced cells, while a number stored as text may not be included as expected.
AVERAGE ignores blank cells but includes zero, which can affect the result.
Check whether zeros represent real measurements or missing data before interpreting an average.
COUNT and COUNTA
COUNT counts cells containing numbers; COUNTA counts cells that are not empty.
If A2:A5 contains 7, the text “Pending,” a blank cell, and 0, then =COUNT(A2:A5) returns 2, while =COUNTA(A2:A5) returns 3.
These formulas are useful for checking how much data a list contains.
A formula that returns an empty string ("") may look blank but can still be counted by COUNTA, so test your actual data structure when blank-looking cells matter.
IF and logical tests
IF returns one result when a condition is true and another when it is false.
For example, if a score is in B2, use =IF(B2>=70,"Pass","Review").
A score of 82 returns Pass; a score of 64 returns Review.
Comparison operators must appear as formula text, not as HTML markup: > means greater than, < means less than, and >= means greater than or equal to.
Other useful operators include <=, =, and <> for not equal.
To test two conditions together, combine IF with AND or OR.
For example, =IF(AND(B2>=70,C2="Complete"),"Ready","Check") returns “Ready” only when the score is at least 70 and the status is Complete.
SUMIF and COUNTIF
Conditional functions summarize only the rows that meet a criterion.
Suppose A2:A5 contains North, South, North, and West, while B2:B5 contains 120, 90, 80, and 110.
=SUMIF(A2:A5,"North",B2:B5)returns 200, adding the two North values.=COUNTIF(A2:A5,"North")returns 2, counting matching labels.
For a criterion stored in a cell, reference that cell instead of typing the criterion.
Wildcards can help match text: an asterisk represents any number of characters, while a question mark represents one character.
For example, =COUNTIF(A2:A20,"Pro*") counts entries beginning with “Pro.”
Look up matching values
XLOOKUP searches one range and returns a corresponding value from another.
If product IDs are in A2:A6, prices are in B2:B6, and the ID to find is in D2, use =XLOOKUP(D2,A2:A6,B2:B6,"Not found").
If D2 matches the ID in A4, the formula returns the price from B4.
XLOOKUP is available in Microsoft 365 and newer perpetual versions such as Excel 2021 and Excel 2024; it is not available in Excel 2019 or earlier.
For older versions, =VLOOKUP(D2,A2:B6,2,FALSE) can perform an exact match when the lookup IDs are in the first column of the table.
The FALSE argument matters: it requests an exact match rather than an approximate one.
Join and clean text
To combine a first name in A2 and a last name in B2 with a space between them, use =A2&" "&B2.
If A2 is “Maya” and B2 is “Chen,” the result is Maya Chen.
For a longer list, =TEXTJOIN(", ",TRUE,A2:A5) combines values with a comma and space, ignoring empty cells.
TEXTJOIN is available in Microsoft 365 and Excel 2019 or later.
TRIM removes extra ordinary spaces from text: =TRIM(A2).
It removes leading and trailing spaces and reduces repeated spaces between words to one.
It does not remove every kind of invisible character; imported nonbreaking spaces may require additional cleanup.
Handle errors and check formula results
A formula may show an error when a value is missing, a reference is invalid, or a calculation cannot be completed.
IFERROR lets you provide a fallback result: =IFERROR(B2/C2,"Check inputs").
This displays “Check inputs” if the division produces an error, such as when C2 is zero.
Use error handling where a fallback is meaningful.
Replacing every error with a blank can conceal a broken reference or unexpected data, so consider displaying a clear message during review.
When a result looks wrong, inspect the referenced cells, confirm that numbers are stored as numbers, and check whether copied formulas shifted their references.
A dollar sign makes a reference absolute: $A$1 stays fixed when a formula is filled across or down, while A1 adjusts relative to its new position.
Excel and Google Sheets compatibility
Common functions such as SUM, AVERAGE, IF, COUNTIF, and VLOOKUP are available in both Excel and Google Sheets.
Newer functions, including XLOOKUP, may depend on the application and version.
If a formula returns #NAME?, check the function name and whether your spreadsheet version supports it.
If Excel rejects the formula syntax, check the argument separator used by your regional settings.