An Excel formula list is a practical reference for choosing the right calculation: use =SUM(B2:B6) to total a range, =AVERAGE(B2:B6) to find its mean, and functions such as IF or XLOOKUP to make decisions or retrieve data. The examples below are written for Microsoft Excel for Microsoft 365 on Windows and explain the cell layout and result each formula assumes.
What Excel formulas do
A formula is an instruction that calculates a value in a worksheet. In Excel, a formula begins with an equals sign, followed by a value, cell reference, operator, or function. For example, adding two cells uses an operator, while SUM applies a built-in function to a range. When a referenced cell changes, Excel normally recalculates formulas that depend on it.
Functions are named, reusable calculations. SUM adds values, AVERAGE calculates their arithmetic mean, and IF returns one result when a condition is true and another when it is false. A function’s inputs are called arguments; parentheses enclose them, and commas separate arguments in the examples here. Excel may use a different argument separator under some regional settings, so if a comma-separated formula is rejected, check the list-separator setting for your installation.
Cell references make formulas reusable. A reference such as B2 points to a cell, while B2:B6 identifies a range from B2 through B6. A formula copied to a new row usually adjusts relative references automatically. Dollar signs lock a reference, which is useful when one calculation repeatedly uses the same rate or assumption.
Core formulas for totals and summaries
Suppose B2:B6 contains 120, 80, 150, 50, and 100. These examples cover common ways to summarize a short list of numbers.
- With the values 120, 80, 150, 50, and 100 in B2:B6, enter
=SUM(B2:B6). The result is 500. - With those same values in B2:B6, enter
=AVERAGE(B2:B6). The result is 100, because the total of 500 is divided by five numbers. - With numeric entries in B2:B6, enter
=COUNT(B2:B6). The result is 5; COUNT counts cells containing numbers, not every nonblank cell. - With names entered in A2:A6, enter
=COUNTA(A2:A6). If all five cells contain names, the result is 5. COUNTA counts nonempty cells, including cells containing text.
SUM and AVERAGE ignore text in referenced cells, but it is still worth checking whether data was imported as text when a total seems unexpectedly small. COUNT and COUNTA answer different questions: use COUNT to count numerical entries, and COUNTA to count populated cells. Neither function counts cells based on a particular label; COUNTIF is designed for that.
Conditional formulas for rules and criteria
Conditional functions help turn worksheet rules into calculations. IF tests one condition, while SUMIF and COUNTIF apply a criterion to a range. They are useful for status reports, category totals, and simple checks.
- With the status in C2 equal to Paid, enter
=IF(C2="Paid","Complete","Follow up"). The result is Complete. If C2 contains another value, the result is Follow up. - With categories Food, Travel, Food, Supplies, and Travel in A2:A6 and their amounts 40, 120, 35, 25, and 60 in B2:B6, enter
=SUMIF(A2:A6,"Food",B2:B6). The result is 75, the sum of the two Food amounts. - With Paid, Pending, Paid, Paid, and Pending in C2:C6, enter
=COUNTIF(C2:C6,"Paid"). The result is 3.
In SUMIF, the first range contains the values Excel checks, the second argument is the criterion, and the final range contains the numbers to add. COUNTIF has just the checked range and criterion. Criteria can be text or expressions; when a criterion uses an operator such as greater than, put the operator and number inside quotation marks, for example ">100". For a more complex rule involving multiple conditions, SUMIFS and COUNTIFS are often more suitable.
Lookup formulas for finding matching information
Lookup functions find a key, such as a product code, in one range and return related information from another. XLOOKUP is a straightforward choice in current Excel, while INDEX with MATCH remains a useful alternative in older workbooks and versions.
- With product codes in A2:A5, corresponding product names in B2:B5, and P-104 in the lookup example, enter
=XLOOKUP("P-104",A2:A5,B2:B5,"Not found"). If P-104 is in A4 and its name in B4 is Desk Lamp, the result is Desk Lamp. If the code is absent, the formula returns Not found. - With the same codes and names in A2:A5 and B2:B5, enter
=INDEX(B2:B5,MATCH("P-104",A2:A5,0)). If P-104 is in A4 and B4 contains Desk Lamp, the result is Desk Lamp. The zero asks MATCH for an exact match.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. INDEX and MATCH are useful when sharing a workbook with people using older Excel releases that do not include XLOOKUP. Whichever method you choose, check that lookup keys are consistent: extra spaces, different data types, or a missing code can prevent a match. XLOOKUP’s optional not-found argument makes a missing match easier to explain than an unexplained error.
Text, dates, rounding, and error handling
Many worksheets contain more than numbers. Excel has functions to format displayed text, round calculated values, and substitute a clear result when a calculation fails. These examples illustrate common everyday cases.
- With a valid Excel date in A2 representing November 3, 2026, enter
=TEXT(A2,"mmm d, yyyy"). The result is Nov 3, 2026. TEXT converts the value to text in the requested display format; it is not a replacement for storing a date as a date when later date calculations are needed. - With the number 12.345 in A2, enter
=ROUND(A2,2). The result is 12.35, rounded to two decimal places. - With 100 in B2 and 0 in C2, enter
=IFERROR(B2/C2,0). Division by zero would normally return an error, but this formula returns 0 instead.
IFERROR can make a report easier to read, but replacing every error with zero may hide a problem. For example, a zero result could be mistaken for a genuine measurement. Consider returning a message such as Not available instead, or correcting the source data, when that is clearer to the reader. Likewise, ROUND changes the calculated value, while setting the cell’s number format changes only how many decimal places are displayed.
Relative and absolute cell references
Reference behavior matters when a formula is copied. Relative references such as B2 adjust as the formula moves. An absolute reference such as $F$1 stays fixed in both row and column. Mixed references lock only one part: $F1 fixes the column, and F$1 fixes the row.
For example, suppose B2 contains 125 and F1 contains 1.08, a multiplier that represents an illustrative 8% increase. With those values in B2 and F1, enter =B2*$F$1 in another cell. The result is 135. Copying the formula down a column changes B2 to B3, B4, and so on, but keeps the multiplier reference at $F$1. In Excel for Windows, F4 cycles through reference styles while editing or selecting a reference in a formula; on a Mac, the equivalent shortcut is typically Command+T, though keyboard behavior can vary with the keyboard and system settings.
Absolute references are especially helpful for assumptions used across many rows, such as a fixed conversion factor or an example rate. Put the assumption in one clearly labeled cell rather than typing the same number into each formula. That makes the workbook easier to audit and update. For real loan, tax, or investment calculations, verify the terms and inputs with the lender or a qualified professional; a spreadsheet example is not financial, tax, or legal advice.
How to choose the right formula
Start by describing the task in ordinary language. “Add these amounts” points to SUM; “count cells that say Paid” points to COUNTIF; “show a label depending on a status” points to IF. Choosing by purpose is usually faster than memorizing a long catalog of function names.
- For an unconditional total or mean, consider SUM or AVERAGE.
- To count numbers or filled cells, distinguish COUNT from COUNTA.
- To summarize only entries meeting a rule, consider SUMIF or COUNTIF; use their plural forms when the calculation depends on multiple criteria.
- To retrieve a value by key, use XLOOKUP in a supported version, or consider INDEX and MATCH for compatibility.
- To control a result based on a test, use IF. Use IFERROR only when the replacement value communicates the situation honestly.
- For calculations copied across a table, decide which references should move and which should remain fixed.
Not every formula needs a function. A short arithmetic calculation can be clearer with operators, and a complex formula can become difficult to review even if it works. Descriptive column headings, consistent data types, and a small number of well-explained helper columns often make a workbook more reliable than a single very long formula.
Common formula problems and fixes
When a formula does not behave as expected, check its structure and the data it references before rewriting it. Excel displays different errors for different problems, and the error itself can offer a useful clue.
- The formula appears as text: The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the format to General, then edit and re-enter the formula.
- A formula is rejected: Check the leading equals sign, parentheses, quotation marks, and argument separators. In some regional settings, Excel expects semicolons rather than commas.
- A lookup does not find a visible match: Check for extra spaces, inconsistent number-versus-text values, and small differences in the key. A value that looks identical on screen may not be stored identically.
- A total is too low: Some entries may be text rather than numbers, or the selected range may omit rows. Inspect the source cells and confirm the range boundaries.
- A copied formula changes unexpectedly: Check whether a reference should be absolute or mixed. Add dollar signs to the row, column, or both when a reference must stay fixed.
For a quick review, select the formula cell and inspect the formula bar. You can also use Formulas > Evaluate Formula in Excel for Windows to step through parts of a calculation. On Mac, the Formula Evaluation command is available from the Formulas tab in supported desktop versions; names or availability may vary by release. A small test with known inputs can help distinguish a formula issue from a source-data issue.
Excel formula list: quick reference
This compact list groups familiar functions by the job they do. It is a starting point rather than a complete catalog, since Excel includes many functions for specialized analysis, engineering, finance, and data work.
- Math and summaries: SUM adds values; AVERAGE calculates a mean; ROUND rounds to a chosen number of digits.
- Counting: COUNT counts numbers; COUNTA counts nonempty cells; COUNTIF counts cells meeting one criterion.
- Conditions: IF returns one result for a true test and another for a false test; SUMIF adds values that meet one criterion.
- Lookups: XLOOKUP retrieves a corresponding value in Microsoft 365 and Excel 2021 or later; INDEX and MATCH can be combined for a lookup in older versions.
- Text and errors: TEXT formats a value as text; IFERROR returns a chosen alternative when an expression produces an error.
Several newer functions are tied to particular Excel releases. XLOOKUP, XMATCH, FILTER, SORT, UNIQUE, SEQUENCE, and LET require Microsoft 365 or Excel 2021. LAMBDA, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. If a workbook must work across mixed versions, confirm function availability on the computers that will open it; an unsupported function may display as an unrecognized-function error.
Frequently Asked Questions
What are the most useful Excel formulas for beginners?
SUM, AVERAGE, COUNT, COUNTA, IF, and COUNTIF are a strong starting group. They cover basic totals, averages, counts, simple decisions, and criteria-based counting. Learn how cell references work at the same time, because most useful formulas apply functions to worksheet data rather than fixed values.
Do Excel formulas always start with an equals sign?
Yes. In a worksheet cell, a formula normally starts with = so Excel interprets the entry as a calculation. Without it, an entry such as SUM(B2:B6) is generally treated as text rather than an instruction to calculate.
Why does Excel show a formula instead of its answer?
The cell may be set to the Text number format, the formula may have a leading apostrophe, or Show Formulas may be enabled for the worksheet. Check the cell format and formula bar, and use Ctrl+` on Windows to toggle Show Formulas; on many Mac keyboards, the equivalent is Control+`, subject to keyboard layout.
Is XLOOKUP better than VLOOKUP?
XLOOKUP is more flexible for many lookup tasks because its lookup and return ranges are specified separately and it can include a not-found result. However, it is not available in older Excel releases, so VLOOKUP or INDEX with MATCH may be needed when compatibility with those versions matters.
Can I use these formulas in Excel for the web or on Mac?
Most basic functions shown here work in Excel for the web and Excel for Mac, although some commands, shortcuts, and newer-function availability can differ by version. Check the version requirements for newer functions and confirm that a workbook opens correctly in the specific Excel edition its users need.
Related
- Excel Data Validation List: Can You Select Multiple?
- Excel Data Validation List: Remove Duplicates
- Excel Number Format: How Many Digits Can It Really Show?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Forecast Dates in Excel with Useful FormulasTo extend a regular date schedule, add the interval to the previous date—for example, with a date in…
- Excel ROW Function Examples for Numbering Rows and MoreExcel’s ROW function returns the worksheet row number for a reference, or the row containing the formula if…
- Excel Test If a String Contains Text: Formula ExamplesTo test whether a cell contains a particular string in Excel, use =ISNUMBER(SEARCH("apple",A2)), replacing “apple” with the text…
- SUMIF Excel Cell Value: Criteria, Examples & FixesTo make SUMIF use a value stored in a cell, put the cell reference in its criteria argument.…
- Outlook Folder Rules: Create Rules to Sort EmailCreate an Outlook rule by choosing a condition, such as a sender or subject phrase, and an action…
- Excel IFS Function: Syntax, Examples, and Common ErrorsExcel’s IFS function contains pairs of logical tests and the values to return when those tests are true.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.