Microsoft Excel Formula List: Functions and Examples

Formulas & functions · Updated November 20, 2026

A Microsoft Excel formula list is a reference to formulas and built-in functions used to calculate, compare, search, and reshape worksheet data. Start with =SUM(B2:B10) to total a range; this article groups other useful functions by task and explains what their examples return in Microsoft Excel for Microsoft 365 on Windows.

What an Excel formula list includes

A formula is an instruction that begins with an equals sign. It can use numbers, cell references, operators such as plus or minus, and functions such as SUM or IF. A function is a named operation with a defined purpose; a formula can combine one or more functions. For example, a formula might add a range directly or use a condition to decide which values to add.

Excel has hundreds of built-in functions, so a useful list is organized around what you need to do rather than presented as an undifferentiated catalog. Common groups include:

  • Math and totals: SUM, AVERAGE, MIN, MAX, ROUND.
  • Counting and conditions: COUNT, COUNTA, COUNTIF, SUMIF, IF, AND, OR.
  • Searching: XLOOKUP, INDEX, MATCH, and XMATCH.
  • Text and dates: LEFT, RIGHT, TEXTJOIN, DATE, YEAR, and NETWORKDAYS.
  • Dynamic arrays: FILTER, SORT, UNIQUE, and SEQUENCE.

Function availability depends on the Excel version. XLOOKUP, XMATCH, FILTER, SORT, UNIQUE, SEQUENCE, and LET require Excel 2021 or Microsoft 365. LAMBDA, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. Older Excel versions may show an unrecognized-name error for newer functions.

Basic math and summary formulas

For routine reports, begin with totals and summary statistics. The examples below specify the cells they expect, so you can adapt the ranges to your own sheet. When copying a formula, check that its references still point to the intended cells.

  • With values 12, 8, 15, and 5 in B2:B5, enter =SUM(B2:B5). It returns 40, because 12 + 8 + 15 + 5 = 40.
  • With the same values in B2:B5, enter =AVERAGE(B2:B5). It returns 10, because 40 divided by four numbers is 10. AVERAGE ignores blank cells but includes zeroes.
  • With the same values in B2:B5, enter =MAX(B2:B5). It returns 15, the largest value. MIN applied to that same range would return 5.
  • With the amount 18.746 in B2, enter =ROUND(B2,2). It returns 18.75, rounding the number to two decimal places. Changing the cell’s number format only changes how a value looks; ROUND changes the calculated result.

SUM is generally preferable to typing a long chain of additions because a range is easier to check and expand. Excel can also create a total with the AutoSum button on the Home tab or Formulas tab. Review the highlighted range before accepting it, particularly when blank rows or labels sit near the data.

Formulas for conditions and counting

Conditional functions let a worksheet respond to its contents. IF tests one condition and returns one value when it is true and another when it is false. COUNTIF counts matches, while SUMIF adds values associated with matching labels. For several conditions, Excel also provides COUNTIFS and SUMIFS.

  • With a test score of 72 in C2, enter =IF(C2>=60,"Pass","Review"). It returns Pass, because 72 is at least 60. If C2 were 58, it would return Review.
  • With the words Paid, Due, Paid, Paid, and Due in A2:A6, enter =COUNTIF(A2:A6,"Paid"). It returns 3, the number of cells matching Paid.
  • With categories Travel, Food, Travel, and Food in A2:A5 and amounts 12, 8, 5, and 3 in B2:B5, enter =SUMIF(A2:A5,"Travel",B2:B5). It returns 17, adding 12 and 5 because their category is Travel.

Text criteria such as Paid need quotation marks inside a formula; cell references do not. Criteria can also contain comparison operators. If you need to count dates after a cutoff, for instance, keep the date in a cell and build the criterion carefully rather than treating the date as ordinary text. Check whether a criterion should match exact text or a partial pattern before relying on a count.

Lookup formulas for matching records

Lookup functions retrieve a related value from a table. XLOOKUP is a convenient choice in supported versions because it searches one range and returns a value from another range, without requiring the return column to be to the right. Exact matching is its default behavior.

With product names in A2:A4 (Notebook, Mug, Pen), their prices in B2:B4 (4, 12, 2), and the lookup text Mug in E2, enter =XLOOKUP(E2,A2:A4,B2:B4,"Not found"). It returns 12, the price on the row where Mug appears. If the lookup text is absent, the formula returns Not found.

In older Excel versions without XLOOKUP, INDEX and MATCH can perform a similar exact lookup, though the formula is more involved. Whichever approach you use, the lookup values must be comparable: extra spaces, inconsistent spelling, or numbers stored as text can stop an apparent match from working. If a key appears more than once, a standard lookup returns a matching occurrence rather than summarizing every matching row.

Text and date functions

Text functions help clean or combine labels, while date functions help calculate calendar values. TEXTJOIN can combine several cells with a chosen separator. DATE constructs a date from year, month, and day numbers; YEAR extracts a year from a date value. Excel stores valid dates as serial numbers, even when the cells display them in a human-readable format.

  • With Alice, Ben, and Cara in A2:A4, enter =TEXTJOIN(", ",TRUE,A2:A4). It returns Alice, Ben, Cara. The separator is a comma followed by a space, and TRUE tells the function to ignore empty cells.
  • With a start date of January 1, 2026, in A2 and an end date of January 10, 2026, in B2, enter =B2-A2. It returns 9, the number of elapsed days between those dates. Format the result as a number if Excel displays it as a date.
  • With the number 45292 in A2, enter =YEAR(A2). It returns the year represented by that Excel date serial, 2024 in the standard 1900 date system.

For date differences involving working days, NETWORKDAYS can count weekdays between two dates and can optionally exclude a holiday range. Its answer is not the same as simple subtraction: weekends and supplied holidays are omitted. Check the workbook’s date system and date formatting if a result looks unexpectedly large or appears as a date.

Dynamic-array formulas and newer functions

In Microsoft 365, some functions return multiple results that spill into neighboring cells automatically. This can eliminate the need to copy a formula down a column. FILTER returns rows meeting a condition, SORT orders a range, and UNIQUE returns distinct entries. These functions are available in Excel 2021 or Microsoft 365; the more recent functions listed earlier have their own version requirements.

With item names in A2:A5 (Pen, Mug, Notebook, Eraser) and statuses in B2:B5 (In stock, Out, In stock, In stock), enter =FILTER(A2:A5,B2:B5="In stock","None"). It spills the three matching names, Pen, Notebook, and Eraser, into cells below the formula. The destination cells must be empty; otherwise Excel displays a spill error. The optional None value appears if no row matches.

Dynamic arrays are useful for live summaries because results update when the source data changes. They are not a good fit if the results need to be independently edited: spilled cells are controlled by the formula in the top-left cell. For a portable workbook shared with older Excel versions, check compatibility before building the main workflow around a newer function.

How to choose a function and troubleshoot errors

Choose the simplest function that expresses the task, then test it on a small, known example. A useful selection process is:

  1. State the goal: total amounts, count matches, return a corresponding value, or reshape a list.
  2. Identify the input cells and whether blanks, duplicates, text, or dates need special treatment.
  3. Pick the function that matches the goal, and confirm that the people opening the workbook have a compatible Excel version.
  4. Compare the result with a manual calculation or a small set of known rows before using it in a report.

Common errors often point to a specific problem. #NAME? can mean a function is unavailable in that Excel version or a function name is misspelled. #N/A often means a lookup found no match. #VALUE! can occur when a formula receives an unsuitable type of value. #REF! indicates an invalid cell reference, often after referenced cells were deleted. A spill error means the output area is blocked.

Also check whether numbers are stored as text, whether formulas use the correct range, and whether copied references shifted. In Excel, a reference such as A1 changes when a formula is copied; a reference such as $A$1 stays fixed. The F4 key cycles through reference styles while editing a reference in a formula on Windows. On Mac, the shortcut can depend on the keyboard and system settings; use the formula bar to add dollar signs if F4 is assigned to a system feature.

Formulas normally recalculate when their inputs change. If results seem stale, check Formulas > Calculation Options and select Automatic if appropriate. Excel for the web has many of the same formulas, but the available features and interface can differ from desktop Excel. On Mac, the function behavior is generally similar, while some keyboard shortcuts differ; verify a shortcut in the app rather than assuming a Windows key combination applies.

Frequently Asked Questions

What is the most useful formula in Excel?
There is no single best formula for every workbook. SUM is a practical starting point for totals, while IF, XLOOKUP, and COUNTIF are common choices for decisions, matching records, and counting categories. The right function depends on the question your data needs to answer.

How do I see a cell’s formula instead of its result?
Select the cell and inspect the formula bar above the worksheet. To show formulas across the sheet, use Formulas > Show Formulas in desktop Excel. On Windows, Ctrl+` toggles formula display; on Mac, the equivalent shortcut can vary by keyboard layout, so the ribbon command is a reliable option.

Why does Excel show the formula as text?
The cell may be formatted as Text, or the entry may begin with an apostrophe or a space before the equals sign. Change the cell format to General, then edit and re-enter the formula. Merely changing the format may not make an existing text entry calculate until it is entered again.

Can I use an Excel formula in Google Sheets?
Many familiar functions and basic formulas work in both products, but function availability, behavior, and separators can differ. Check the target spreadsheet’s function documentation before sharing a formula-dependent file; do not assume every newer Excel function has an exact Sheets equivalent.

Where can I find the full official function list?
Microsoft Support maintains an alphabetical Excel functions reference with descriptions and version notes. It is useful when you need a less common function or want to confirm which release introduced it. A workbook’s Insert Function feature can also help search for functions available in the installed Excel version.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.