Excel Sheet Formula List: Essential Functions and Examples

Formulas & functions · Updated November 19, 2026

An Excel sheet formula list is a reference to formulas and functions you can use to calculate, summarize, look up, and transform worksheet data. For Microsoft Excel for Microsoft 365 on Windows, every formula starts with an equals sign; the examples below show common functions using comma-separated arguments and real results.

What an Excel formula is

A formula is an instruction Excel evaluates in a cell. It can use numbers, cell references, operators such as plus and division, or built-in functions. A function is a named operation with a defined syntax: for example, SUM adds values in a range. Formulas recalculate when referenced cells change, so a total can update automatically after a new transaction is entered.

In the examples below, assume a small sales list has names in A2:A6 and sales amounts in B2:B6: Mina has 120, Omar 80, Priya 150, Luis 0, and Hana 50. The total is 400. Each example states its cell layout and expected result so you can adapt it to a workbook rather than copy it without checking the references.

Excel formulas shown here use commas between arguments, as is standard in many English-language installations. Depending on regional settings, Excel may expect semicolons instead. If a formula is rejected despite being otherwise correct, check the list separator in your installation. Formula names and menu labels can also vary with language.

Basic arithmetic and cell references

For simple calculations, use arithmetic operators: plus (+), minus (-), multiplication (*), division (/), and exponentiation (^). Parentheses control the order of operations, just as they do in ordinary mathematics. Cell references let a formula use worksheet values instead of fixed numbers, which makes it easier to update inputs.

  • Add: With a value of 120 in B2 and 80 in B3, =B2+B3 returns 200.
  • Subtract: With 120 in B2 and 80 in B3, =B2-B3 returns 40.
  • Multiply: With 120 in B2 and 2 in C2, =B2*C2 returns 240.
  • Divide: With 120 in B2 and 3 in C2, =B2/C2 returns 40.

These short arithmetic examples identify their cell inputs directly: enter the stated values in those cells, then place the formula in a different cell. A formula can also combine a fixed value with a reference. If that value should stay fixed when copying the formula, use an absolute reference such as $E$1. Dollar signs lock the column and row; a reference such as A2 changes when copied to another row, while $A$2 remains fixed.

With 120 in B2 and a multiplier of 2 in E1, =B2*$E$1 returns 240. The layout assumption is that B2 contains the amount and E1 contains the fixed multiplier. If you copy the formula down the column, B2 can change to B3 while E1 stays locked. This is useful for applying a tax rate, conversion factor, or target multiplier stored in one input cell.

Totals, averages, and counts

Summary functions are among the most useful entries in an Excel formula list. They save you from writing a long chain of arithmetic and make the range being summarized explicit. When you add rows later, update the range or use an Excel Table so the data range can expand with the table.

With sales amounts 120, 80, 150, 0, and 50 in B2:B6, =SUM(B2:B6) returns 400. SUM adds the numeric values in the specified range. Blank cells are ignored, but a cell containing text that looks like a number may not behave like a true numeric value.

With those same five amounts in B2:B6, =AVERAGE(B2:B6) returns 80, because 400 divided by five numeric entries is 80. A zero counts as an entry in an average; an empty cell does not. That difference matters when zero means a real result rather than missing data.

With the five amounts in B2:B6, =MIN(B2:B6) returns 0, and =MAX(B2:B6) returns 150. MIN finds the lowest numeric value and MAX the highest. Both formulas use the same stated layout: B2:B6 contains 120, 80, 150, 0, and 50.

With numeric sales values in B2:B6, =COUNT(B2:B6) returns 5 because COUNT counts numeric cells. With the five names in A2:A6, =COUNTA(A2:A6) returns 5 because COUNTA counts nonempty cells, including text. COUNT and COUNTA answer different questions; choose according to whether you need to count numbers specifically or any populated cell.

Conditional formulas and error handling

Conditional formulas let a worksheet apply a rule rather than repeat a decision manually. IF checks a condition and returns one result when it is true and another when it is false. Text values in formulas need straight double quotation marks.

With a sales amount of 120 in B2, =IF(B2>100,"Target","Below") returns Target. The layout assumes B2 contains the amount being assessed; the comparison is greater than 100, so an amount of exactly 100 would return Below. This kind of formula can label a result, but it does not change the underlying number.

With amounts in B2:B6, =COUNTIF(B2:B6,">100") returns 2 because 120 and 150 are greater than 100. With those same values in B2:B6, =SUMIF(B2:B6,">=100",B2:B6) returns 270, adding 120 and 150. COUNTIF counts cells that meet one criterion; SUMIF adds corresponding values for cells that meet a criterion. The criterion is written as text in quotation marks.

With 120 in B2 and 0 in C2, =IFERROR(B2/C2,"Check input") returns Check input instead of displaying a division-by-zero error. The assumed layout is an amount in B2 and a divisor in C2. IFERROR is useful when an error is an expected possibility, but it should not conceal a problem you need to diagnose. During workbook development, make sure the fallback wording helps a reader identify what needs attention.

Rounding, text, and dates

ROUND changes a number to a chosen number of decimal places. With 12.345 in B2, =ROUND(B2,2) returns 12.35. The layout assumes the number is in B2 and the second argument requests two decimal places. Rounding is not the same as merely changing the display format: formatting can hide decimal places while leaving the stored value unchanged, whereas ROUND returns a rounded result for use in further calculations.

Text functions can join labels or extract parts of text. With names in A2:A4—Mina, Omar, and Priya—=TEXTJOIN(", ",TRUE,A2:A4) returns Mina, Omar, Priya. The assumed layout is those three names in A2:A4; the delimiter is a comma followed by a space, and TRUE tells Excel to ignore empty cells. TEXTJOIN is available in Microsoft 365 and Excel 2019 or later.

For current dates, TODAY returns the current date according to the computer’s date settings and updates when the worksheet recalculates. With no input cells required, =TODAY() displays today’s date in the formula cell. The displayed appearance depends on the cell’s number format; if it shows a serial number, apply a date format from Home > Number. Because the result changes over time, do not use TODAY when you need a permanently fixed date stamp.

Lookups and dynamic arrays

Lookup formulas retrieve a related value from a list. In Microsoft 365, XLOOKUP is often a clear option because its lookup and return ranges are specified separately. With names in A2:A6, sales in B2:B6, and the lookup name Mina in D2, =XLOOKUP(D2,A2:A6,B2:B6,"Not found") returns 120. The layout assumes the names and amounts correspond by row; the optional fourth argument supplies a message if Mina is not in the list. XLOOKUP requires Microsoft 365 or Excel 2021 and later; it is not available in Excel 2019 or Excel 2016.

Dynamic array functions can return multiple results from one formula. With sales values in B2:B6 and names in A2:A6, =FILTER(A2:B6,B2:B6>100,"No matches") returns the rows for Mina and Priya, whose amounts are 120 and 150. The source range is A2:B6 and the condition tests the corresponding values in B2:B6. Excel places the results in neighboring cells automatically, a behavior called spilling; those destination cells must be clear. FILTER requires Microsoft 365 or Excel 2021 and later.

Newer functions are helpful but not universal. FILTER, XLOOKUP, SORT, UNIQUE, SEQUENCE, XMATCH, and LET require Microsoft 365 or Excel 2021 or later. LAMBDA, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. If a workbook will be opened in an older Excel version, check function compatibility before relying on these formulas; an unsupported function can display a name error.

Choosing the right formula

Start with the question you want the worksheet to answer, then choose the simplest formula that answers it. A total calls for SUM, while a count of qualifying values calls for COUNTIF. A calculation that depends on a test calls for IF; a value retrieved by matching a label calls for a lookup. Keeping the intention clear makes formulas easier to review and maintain.

  • Use a direct cell reference or arithmetic when the calculation is short and straightforward.
  • Use a function for a repeated or recognizable task, such as adding a range, averaging values, or counting matches.
  • Use absolute references for inputs such as a fixed rate that should not shift when a formula is copied.
  • Use a lookup when one value identifies a corresponding value in another range, rather than building a long chain of IF formulas.
  • Check whether everyone who will open the workbook has a version that supports the functions you select.

Prefer transparent formulas over compactness for its own sake. A very long formula may be possible, but helper columns can make each step visible and easier to troubleshoot. Descriptive table headings and structured references can also make a workbook easier to read than unexplained cell coordinates, especially when rows are added. Excel Tables can be created with Insert > Table or Ctrl+T on Windows; on Mac, use Command+T. A structured reference may adjust as table rows are added, but always confirm the formula refers to the intended column.

Common formula problems

A formula that appears as text may have been entered into a cell formatted as Text, or it may have a leading apostrophe. Change the cell format to General, then edit and re-enter the formula. If Excel displays a result that does not update, check Formulas > Calculation Options and make sure calculation is set to Automatic. These settings can affect a workbook’s behavior even when the formula itself is correct.

Errors often point to a specific kind of issue. #DIV/0! indicates division by zero or a blank divisor; #N/A commonly means a lookup did not find a match; #NAME? can mean Excel does not recognize a function name or text was not quoted correctly; and #VALUE! often signals an incompatible input type. An error does not always mean the formula syntax is wrong: the referenced data may be missing, stored as text, or outside the expected range.

When copying formulas, check whether references should move. A relative reference such as B2 adjusts as the formula is filled down, while $B$2 stays fixed. Mixed references such as $B2 or B$2 lock only the column or row. Use the F4 key while editing a reference in the formula bar to cycle through reference types on Windows; on many Mac keyboards, use Command+T while editing, though keyboard settings can affect the shortcut. You can also type the dollar signs directly.

To inspect a complicated formula, select its cell and review the formula bar. On Windows, Formulas > Evaluate Formula can step through parts of a formula; availability and exact ribbon layout can vary slightly by update. For simpler diagnosis, calculate pieces in separate cells, confirm the source ranges, and check for stray spaces or numbers stored as text. These checks are often more useful than wrapping every formula in IFERROR, which can hide the original cause.

Frequently Asked Questions

What is the most commonly used Excel formula?
SUM is one of the most common functions because it adds values across a range. It is useful for totals, but the right formula depends on the task: AVERAGE, COUNTIF, IF, and lookups solve different problems.

How do I show a formula instead of its result?
Select the cell to see its formula in the formula bar. To display formulas in worksheet cells, use Formulas > Show Formulas on Windows. The Ctrl+` shortcut toggles this view on many Windows keyboards; Mac shortcut behavior can vary by keyboard and Excel settings.

Why does Excel show the formula rather than calculate it?
The cell may be formatted as Text, the formula may start with an apostrophe, or Show Formulas may be enabled. Set the cell to General, re-enter the formula if needed, and check the Show Formulas setting.

Can I use Excel formulas in Google Sheets?
Many widely used formulas, including SUM and IF, exist in both products, but function availability, syntax details, and behavior can differ. Check the documentation for the spreadsheet app and test a workbook before relying on less common or newer functions.

Do I need to memorize every Excel function?
No. Learn the functions that match your recurring tasks, and use Excel’s function suggestions and help to check syntax. A short, well-labeled formula is usually easier to verify than memorizing a large catalog.

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.