An Excel formula is an expression that begins with an equals sign; most formulas use functions such as SUM, IF, or XLOOKUP to calculate or retrieve information. Below is a practical A-to-Z list of commonly used Excel functions, followed by examples and guidance on choosing, entering, and troubleshooting formulas in Microsoft Excel for Microsoft 365 on Windows.
Formula or function: what is the difference?
A formula is anything you enter in a worksheet that starts with an equals sign. It can use numbers, cell references, operators such as plus or minus, and functions. A function is a named, built-in operation that performs a particular task. For example, a formula can add cells with the plus operator, or use the SUM function to add a range. A formula may contain several functions, while a simple formula may contain none.
Most functions follow a pattern: a function name, an opening parenthesis, one or more arguments, and a closing parenthesis. Arguments can be cell references, ranges, numbers, text, or other functions. In the examples below, commas separate arguments, as they do in standard English-language Excel installations. Some regional settings use semicolons instead; Excel may adjust separators to match the computer’s settings.
Excel includes hundreds of built-in functions, and the available set depends partly on the version. This list is a useful starting index, not a complete catalog of every function or every specialized compatibility function.
Excel functions A to Z
These commonly useful functions are grouped by their first letter. A few letters have no widely used general-purpose worksheet function in everyday work; the list is not intended to force a function under every letter.
- A: ABS returns an absolute value; AND tests whether all conditions are true; AVERAGE calculates a mean; AVERAGEIF and AVERAGEIFS average values that meet criteria.
- B: BASE converts a number to a text representation in a specified base, such as binary. BAHTTEXT converts a number to Thai text and is a specialized function.
- C: CHOOSE returns an item from a list by position; CONCAT joins text; COUNT counts numbers; COUNTA counts nonempty cells; COUNTIF and COUNTIFS count cells that meet one or more criteria.
- D: DATE creates a date from year, month, and day values; DAY extracts the day number; DATEDIF calculates the difference between dates; SUM is often used to total values but does not begin with D. D-functions such as DGET work with database-style ranges.
- E: EDATE shifts a date by a number of months; EOMONTH returns a month-end date; EXACT compares text with case sensitivity; EXP raises e to a specified power.
- F: FILTER returns rows or columns that meet conditions; FIND locates text and is case-sensitive; FLOOR.MATH rounds down to a specified multiple; FV calculates a future value under supplied assumptions.
- G: GCD returns the greatest common divisor; GETPIVOTDATA retrieves values from a PivotTable.
- H: HLOOKUP searches across the first row of a table; HOUR extracts the hour from a time; HSTACK combines arrays horizontally in supported versions.
- I: IF returns one result when a condition is true and another when it is false; IFERROR handles an error result; INDEX returns a value at a row and column position; INDIRECT interprets text as a reference; INT rounds down to an integer; IFS checks multiple conditions.
- J: There is no widely used general-purpose J function in ordinary worksheet work. Excel has specialized compatibility functions, but most users do not need to memorize one function for every letter.
- K: KURT calculates kurtosis for a data set, a statistical measure of distribution shape.
- L: LARGE returns the nth-largest value; LEFT extracts characters from the start of text; LEN counts characters; LET assigns names to intermediate calculations; LAMBDA lets users define reusable custom functions in supported versions; LOG calculates a logarithm.
- M: MAX and MIN return the largest and smallest values; MEDIAN returns the middle value; MID extracts text from a specified position; MONTH extracts a month number; MODE.SNGL returns the most frequent value.
- N: NA returns the #N/A error value; NOT reverses a logical result; NOW returns the current date and time; NPV calculates net present value based on periodic cash flows and a rate.
- O: OR tests whether at least one condition is true; OFFSET returns a reference shifted from a starting reference.
- P: PMT calculates a periodic payment for a loan using supplied assumptions; PRODUCT multiplies values; PROPER capitalizes the first letter of each word; PERCENTILE.INC returns a percentile in a data set.
- Q: QUOTIENT returns the integer portion of a division result.
- R: RAND generates a random number; RANK.EQ returns a value’s rank; RIGHT extracts characters from the end of text; ROUND rounds to a specified number of digits; ROW and ROWS return row numbers or a range’s row count.
- S: SEARCH locates text without case sensitivity; SEQUENCE creates a sequence of numbers; SORT orders an array; SUM adds values; SUMIF and SUMIFS add values that meet criteria; SUBSTITUTE replaces specified text.
- T: TEXT formats a value as text; TEXTJOIN joins text with a chosen delimiter; TODAY returns the current date; TRIM removes extra spaces between words and at the ends of text.
- U: UNIQUE returns distinct values from an array; UPPER converts text to uppercase.
- V: VALUE converts text that represents a number into a numeric value; VLOOKUP searches down the first column of a range and returns a value from another column.
- W: WEEKDAY returns a day-of-week number; WORKDAY returns a date a specified number of working days before or after a start date.
- X: XLOOKUP searches a range or array and returns a corresponding result; XMATCH returns the relative position of a matching item.
- Y: YEAR extracts the year from a date; YEARFRAC calculates the fraction of a year between two dates.
- Z: Z.TEST returns a one-tailed probability value for a z-test, with assumptions that matter for statistical analysis.
Names such as HSTACK, FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, XMATCH, and LET require Microsoft 365 or Excel 2021 or later. LAMBDA and HSTACK are available in Microsoft 365 and Excel 2024; availability can depend on the particular function and update channel. For a workbook that must open in older Excel versions, check compatibility before relying on newer functions.
Examples you can adapt
Start with a small, clearly defined range and check that the referenced cells contain the expected data. These examples use ordinary worksheet ranges and return the stated results when the sample values are present.
- Add a range: With sales figures 120, 85, and 95 in B2:B4, enter
=SUM(B2:B4). The result is 300. - Return a decision: With a score of 78 in C2, enter
=IF(C2>=70,"Pass","Review"). The result is Pass because 78 is at least 70. - Find a matching item: With product codes P-101, P-104, and P-108 in A2:A4; their names Notebook, Pen, and Folder in B2:B4; and P-104 in E2, enter
=XLOOKUP(E2,A2:A4,B2:B4,"Not found"). The result is Pen. XLOOKUP searches for E2 in the code range and returns the value on the same row from the names range.
In the second example, the greater-than-or-equal comparison is part of the formula, not a separate function. In the lookup example, the optional “Not found” text makes the outcome easier to interpret when the code is absent. If you change the data layout, update the references rather than copying the formula blindly.
How to enter and copy a formula
On Excel for Microsoft 365 on Windows, select a cell, type an equals sign, enter the formula, and press Enter. You can type a reference or select the relevant cells while building the expression. Excel displays the result in the cell and the formula in the formula bar. Use Formulas > Insert Function to browse or search for a function and see its argument prompts.
To copy a formula, select its cell and drag the fill handle at the lower-right corner, or copy and paste it. Relative references change as the formula moves: B2 becomes B3 when copied down one row. Add dollar signs to make a reference absolute, such as $B$2, when a formula should keep pointing to the same cell as it is copied. Mixed references, such as B$2 or $B2, lock only the row or column.
In Excel for Mac, the core formula behavior is the same, though ribbon placement and keyboard shortcuts can differ. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V. Excel for the web also supports many common formulas, but newer-function availability and some editing details can differ from the desktop app.
Choosing the right function
Choose a function based on the question you need the worksheet to answer, not simply because a function name looks familiar. For example, COUNT counts numeric entries, COUNTA counts nonempty cells, and COUNTIF counts entries matching a condition. SUM adds values, while SUMIF adds only values that meet a condition. If the condition is more complex, SUMIFS can apply multiple criteria.
- For totals, averages, or counts, begin with SUM, AVERAGE, COUNT, or their criteria-based versions.
- For decisions, use IF for one test or IFS for several tests; combine conditions with AND or OR when appropriate.
- For finding a related value, consider XLOOKUP in a current version. VLOOKUP remains common in older workbooks, but it searches from the leftmost column of its table.
- For extracting or reshaping data, FILTER, SORT, and UNIQUE can make dynamic results that update when the source changes.
- For text cleanup, consider TRIM, SUBSTITUTE, LEFT, RIGHT, MID, or TEXTJOIN, depending on whether you need to remove, replace, extract, or combine characters.
Many functions return arrays that spill into neighboring cells. If the spill area is blocked by existing content, Excel may show a #SPILL! error. Make room for the output before assuming the function or source data is wrong.
Common formula problems and fixes
A formula error usually points to a specific issue, although the displayed message alone may not reveal the cause. Check the formula bar, the cells referenced, and the expected data types before rebuilding a formula.
- #NAME? often means Excel does not recognize a function name or text was not enclosed in straight quotation marks. Check spelling and whether the function exists in your Excel version.
- #N/A commonly means a lookup did not find a match. Check for extra spaces, mismatched data types, and differences such as a number stored as text.
- #VALUE! can indicate an argument has the wrong type, such as text where a calculation expects a number.
- #REF! means a reference is invalid, often because a referenced row, column, or cell was deleted.
- #DIV/0! appears when a calculation divides by zero or a blank cell treated as zero.
- Formula displays as text: check whether the cell is formatted as Text, or whether an apostrophe appears before the equals sign. Change the format to General, then re-enter the formula.
For criteria functions, text conditions such as greater than or equal to a number are commonly written in quotation marks inside the formula. Also confirm that the criteria range and sum range have matching dimensions in SUMIF or SUMIFS. When a result seems incorrect but no error appears, check whether calculation is set to automatic under Formulas > Calculation Options.
Function list versus formula library
An A-to-Z function list is an index of available tools; it is not a set of ready-made formulas for every task. A formula library usually groups complete expressions by purpose, such as cleaning imported text, calculating a running total, or matching an order to a customer. The same function can appear in many different formulas, and the right cell references depend on the workbook’s layout.
Excel’s Function Library on the Formulas tab groups functions into categories such as Financial, Logical, Text, Date & Time, Lookup & Reference, and Math & Trig. Selecting a category or using Insert Function can be more useful than memorizing a long alphabetic catalog. Microsoft’s function reference is also helpful when you need exact argument requirements or need to confirm which versions support a function.
Frequently Asked Questions
How many formulas are in Excel?
Excel has hundreds of built-in functions, but there is no single fixed count that applies to every version and update. Also, formulas are not limited to built-in functions: users can combine references, operators, and functions in many ways.
What is the most commonly used Excel formula?
SUM is one of the most familiar functions because it adds numbers in cells or ranges. IF, COUNTIF, and lookup functions are also widely useful, but the best choice depends on the task.
Are Excel formulas case-sensitive?
Most ordinary function names and text comparisons are not case-sensitive. EXACT is a notable function for comparing text while distinguishing uppercase and lowercase letters.
Why does Excel show the formula instead of its answer?
The cell may be formatted as Text, the entry may begin with an apostrophe, or Show Formulas may be enabled. Check the cell format and the Formulas tab’s Show Formulas setting, then re-enter or recalculate the formula as needed.
Can I use XLOOKUP in every Excel version?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older perpetual versions. For older workbooks, VLOOKUP or an INDEX and MATCH combination may be suitable alternatives.
Where can I find a complete official function list?
Use the Function Library on the Formulas tab or search Microsoft’s Excel function reference. These resources include specialized functions and version information that a short practical A-to-Z list cannot cover.
Related
- Excel Find and Delete Text: Replace, Clear, or Remove
- Excel Flip Data Upside Down: Reverse Rows in Excel
- Excel Find Table Names: Where to Look and How
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Excel Criteria: Syntax, Examples, and Common FixesIn Excel, SUMIF criteria tell the function which cells to check before adding matching values. For example, with…
- Excel DATE Code: Formula, Examples, and Format CodesExcel’s date code is the DATE function, which creates a real date from a year, month, and day.…
- Excel IFS Function Examples for Multiple ConditionsExcel’s IFS function checks conditions in order and returns the value paired with the first condition that is…
- Excel SUBTOTAL Formula Examples for Filtering and TotalsWith sales values in B2:B6, use =SUBTOTAL(9,B2:B6) to add them; for the example values 120, 80, 150, 70,…
- Excel Formula for a List of Names: 10 Useful OptionsTo create an alphabetized list of distinct names, put the source names in A2:A20 and enter =SORT(UNIQUE(A2:A20)) in…
- Excel INDEX and AVERAGE: How to Combine ThemExcel has no single function called INDEX AVERAGE; instead, combine INDEX with AVERAGE so Excel averages the value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.