MS Excel Formula List: Essential Functions and Examples

Formulas & functions · Updated November 20, 2026

An MS Excel formula list is a guide to the expressions you can enter in a cell to calculate, compare, find, or reshape information. For Microsoft 365 on Windows, useful starting points include SUM for totals, AVERAGE for means, IF for decisions, XLOOKUP for matching records, and FILTER for returning rows that meet a condition.

What an Excel formula is

A formula is an instruction Excel calculates, and it begins with an equals sign. It can contain numbers, cell references, operators such as plus or division, and functions such as SUM. A function is a named operation with inputs, called arguments; a formula can use one function or combine several.

For example, adding two cells directly is a formula, while SUM is a function that can add a range. Cell references make formulas responsive: if a referenced value changes, Excel normally recalculates the result. In Microsoft 365, calculation is usually automatic; if results are not updating, check Formulas > Calculation Options and confirm that Automatic is selected.

In the examples below, each formula is shown in a code element, and the described cell layout tells you where the sample data belongs. Enter a formula in an empty cell, then press Enter. Excel uses commas between arguments in the examples; regional settings may require semicolons instead.

Basic calculation and summary formulas

These functions answer common questions about a numeric list: what is the total, what is the average, and how many numeric entries are present? Keep the input range consistent and check that values meant to be numbers have not been stored as text.

  • With the numbers 12, 8, 15, 10, and 5 in A2:A6, enter =SUM(A2:A6) in another cell. The result is 50.
  • With those same five values in A2:A6, enter =AVERAGE(A2:A6). The result is 10. AVERAGE ignores blank cells but includes zeroes.
  • With those same five numeric values in A2:A6, enter =COUNT(A2:A6). The result is 5 because COUNT counts numeric cells, not text.
  • With names in B2:B6 and every row containing a name, enter =COUNTA(B2:B6). The result is 5; COUNTA counts non-empty cells, including cells containing text.

COUNT and COUNTA are easy to confuse. If a column contains five labels and no numbers, COUNT may return zero while COUNTA returns five. For a conditional total, SUMIF adds only values whose corresponding labels meet a criterion.

With regions in C2:C6 (East, West, East, North, East) and sales in B2:B6 (120, 80, 150, 90, 60), enter =SUMIF(C2:C6,"East",B2:B6). The result is 330, the sum of the three East sales values.

Logic and conditional formulas

IF tests a condition and returns one result when it is true and another when it is false. This is useful for simple labels, flags, and status columns. Text results must be enclosed in straight double quotation marks.

With a score of 72 in D2, enter =IF(D2>=60,"Pass","Review") in E2. The result is Pass. If D2 is below 60, the result is Review. The comparison operator >= means “greater than or equal to.”

COUNTIF counts cells that match one condition, while SUMIF totals values associated with a matching condition. For example, a status column can be counted without manually filtering the table.

With statuses in C2:C6 containing Open, Closed, Open, Open, and Closed, enter =COUNTIF(C2:C6,"Open") in another cell. The result is 3. Criteria can also use comparison operators, such as a quoted “>=100” criterion for counting values at least 100.

IFERROR can display a helpful fallback when a calculation returns an error. It should make a workbook easier to read, not conceal a mistake that needs correction.

With 10 in E2 and 0 in F2, enter =IFERROR(E2/F2,"Check denominator") in G2. Since division by zero produces an error, the displayed result is Check denominator. If zero is a valid case, consider deciding explicitly what the result should mean instead of simply hiding the error.

Lookup and dynamic-array formulas

Lookup functions retrieve a related value from a table. XLOOKUP is a convenient option in current Excel because you specify the value to find, the lookup range, and the result range separately. It uses an exact match by default and can return a chosen message when there is no match.

With product codes in A2:A4 (P101, P104, P108), product names in B2:B4 (Notebook, Marker, Folder), and the code P104 in E2, enter =XLOOKUP(E2,A2:A4,B2:B4,"Not found") in F2. The result is Marker. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or earlier perpetual versions.

Dynamic-array functions can return multiple results from one formula, spilling them into adjacent cells. FILTER is useful when you want matching rows to update as the source data changes.

With item names in A2:A5 (Pen, Pad, Clip, Case) and quantities in B2:B5 (40, 120, 75, 160), enter =FILTER(A2:B5,B2:B5>=100,"No matches") in D2. It returns the Pad row with 120 and the Case row with 160, spilling into two columns and two rows. Keep the spill area clear or Excel may show a #SPILL! error.

FILTER, SORT, UNIQUE, SEQUENCE, XMATCH, and LET are available in Microsoft 365 and Excel 2021 or later. LAMBDA, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. Availability depends on the Excel edition, so an unfamiliar-function error may indicate an older version rather than a typing mistake.

Text, rounding, and date formulas

Text functions help combine labels or split information into usable pieces. TEXTJOIN combines text from multiple cells and can skip empty cells, which is helpful when building a display label from separate fields.

With North in A2, Q2 in B2, and Online in C2, enter =TEXTJOIN(" - ",TRUE,A2:C2) in D2. The result is North – Q2 – Online. TEXTJOIN is available in Microsoft 365 and later Excel releases, including Excel 2019 and Excel 2021.

ROUND controls the number of decimal places shown in the calculated value, unlike changing only the display format. This matters when later calculations should use a rounded result.

With 18.567 in A2, enter =ROUND(A2,2) in B2. The result is 18.57. By contrast, applying a number format may display two decimal places while retaining the underlying precision for subsequent calculations.

Date formulas work with Excel date values, which are stored as serial numbers and displayed according to the cell’s number format. TODAY returns the current date and updates when the workbook recalculates; it is not a permanent record of the date a row was entered. For a fixed timestamp or date, enter a value rather than relying on a changing formula.

How to choose the right function

Start with the question you need the sheet to answer, then choose the simplest function that fits. A short, readable formula is generally easier to audit than a deeply nested one. For a recurring report, a well-structured table and clear headings can also make formulas easier to copy and maintain.

  • To add every value in a range, choose SUM; to add values only for a matching label, choose SUMIF.
  • To count numeric entries, choose COUNT; to count filled cells, choose COUNTA; to count cells meeting a condition, choose COUNTIF.
  • To return one of two outcomes based on a test, choose IF. If errors need a deliberate alternative display, consider IFERROR.
  • To retrieve a related value by key, use XLOOKUP when your Excel version supports it. In older versions, a combination such as INDEX and MATCH may be an option.
  • To return all rows meeting a condition, use FILTER when supported; for a one-time review, Excel’s built-in filter controls may be sufficient.

Consider where the formula will be copied. A reference such as A2 changes to A3 when copied down. An absolute reference such as $A$2 stays fixed. In Windows, press F4 while editing a reference to cycle through reference types; on many Mac keyboards, use Command+T, though the exact key behavior can depend on keyboard settings. You can also type the dollar signs directly.

Common formula problems and fixes

Many formula errors come from a mismatch between what the formula expects and what the cells contain. Before rewriting a complex expression, inspect the inputs, ranges, and displayed error. Excel’s formula bar can help distinguish a stored value from its appearance in the grid.

  • #NAME? often means Excel does not recognize a function or a name. Check spelling, quotation marks around text, and whether your Excel version supports the function.
  • #N/A commonly means a lookup did not find a match. Check for extra spaces, different data types, or codes that look alike but are not identical.
  • #DIV/0! means a formula attempted division by zero or a blank denominator. Check the denominator and decide how that case should be handled.
  • #SPILL! can occur when a dynamic-array result cannot occupy the cells it needs. Clear obstructing content or move the formula to a less crowded area.
  • A formula displayed as text may have been entered into a cell formatted as Text, or may have a leading apostrophe. Change the cell format to General and re-enter the formula.

When a copied formula points to the wrong cells, check relative and absolute references. Also verify that all ranges in a conditional formula cover the same number of rows. Avoid merging cells in areas where dynamic arrays need to spill.

Frequently Asked Questions

What is the most useful Excel formula for beginners?
SUM is a practical first function because it totals a range, but the best one depends on the task. Learn cell references and basic arithmetic alongside SUM, then add IF, COUNTIF, and lookup functions as your work requires them.

How do I see a list of functions in Excel?
Select the Formulas tab and use Insert Function to search or browse functions. You can also begin typing an equals sign and a function name in a cell to see matching suggestions, with availability depending on your Excel version.

Why does my Excel formula use semicolons instead of commas?
Excel’s argument separator can depend on regional settings. If a formula using commas is rejected, your installation may expect semicolons; use the separator shown in Excel’s own function suggestions.

Are Excel formulas and functions the same thing?
No. A formula is the complete expression entered in a cell, and a function is a built-in operation that can be part of that expression. A formula can also use operators and cell references without using a function.

Can I use these formulas in Excel for the web or on Mac?
Most standard functions are also available in Excel for the web and Excel for Mac, but newer functions depend on the edition and update channel. Ribbon locations and keyboard shortcuts can differ; the examples use Microsoft 365 for Windows and comma separators.

Can I copy a formula down a column?
Yes. Select the formula cell and drag its fill handle, or copy and paste it into the target cells. Excel adjusts relative references as it copies; use dollar signs for references that should remain fixed.

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.