An Excel formula list is a reference to the calculations and functions you can use in a worksheet. Common examples include SUM for totals, AVERAGE for a mean, IF for a condition, SUMIF for conditional totals, and XLOOKUP for finding a matching value; the right choice depends on the question your data needs to answer.
What an Excel formula list includes
Excel has hundreds of built-in functions, so a useful formula list is usually organized by task rather than treated as one short, complete catalogue. A formula is an instruction that begins with an equals sign. It can contain numbers, cell references, operators such as plus and minus, and functions such as SUM. For example, a formula can add two cells directly or ask Excel to total a whole range.
Here are common function families and the questions they help answer:
- Math and statistics: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, and ROUND.
- Logical tests: IF, AND, OR, and IFERROR.
- Conditional calculations: SUMIF, SUMIFS, COUNTIF, and COUNTIFS.
- Lookup and reference: XLOOKUP, INDEX, and MATCH.
- Text handling: LEFT, RIGHT, MID, TRIM, TEXTJOIN, and CONCAT.
- Dates and time: DATE, TODAY, YEAR, MONTH, and NETWORKDAYS.
- Dynamic arrays: FILTER, SORT, and UNIQUE.
This is a practical starter list, not every function Excel provides. Some functions overlap, but they are not interchangeable in every situation. SUM adds values, for instance, while COUNT counts numeric cells. Learning a small set of versatile functions and understanding their inputs is generally more useful than memorizing a long catalogue.
How formulas work in a worksheet
In Excel for Microsoft 365 on Windows, select a cell, type an equals sign followed by the calculation, and press Enter. A cell reference tells Excel where to read a value. A range uses a colon between its first and last cells, such as B2:B5. Function arguments—the inputs separated by commas in the examples here—go inside parentheses.
For example, with 120, 80, 50, and 150 in cells B2 through B5, the formula in B6 =SUM(B2:B5) returns 400. The formula refers to the cells rather than typing the four values into the calculation, so the result updates if one of those cells changes. This is one reason formulas are useful in working spreadsheets rather than only for one-time arithmetic.
Cell references can be relative, absolute, or mixed. A relative reference such as B2 adjusts when a formula is copied to another location. An absolute reference such as $B$2 stays fixed when copied. Mixed references fix just a column or row, as in $B2 or B$2. In Windows, F4 cycles through reference styles while editing a reference in a formula; on many Mac keyboards, use Command+T instead, though the function-key setting can affect the shortcut. You can also type the dollar signs yourself.
Excel recalculates formulas when their referenced values change, subject to workbook calculation settings. If a result does not update, check Formulas > Calculation Options and confirm that Automatic is selected. Excel for the web has a similar formula experience, but ribbon layout and keyboard shortcuts can differ slightly from the Windows desktop app.
Math, totals, and statistics
SUM and AVERAGE are among the most frequently used functions. With 120, 80, 50, and 150 in B2:B5, =AVERAGE(B2:B5) returns 100, because the total of 400 is divided by four numbers. MIN and MAX return the smallest and largest numeric values in a range. COUNT counts numeric entries, whereas COUNTA counts cells that are not empty; that distinction matters when a column mixes numbers and text.
ROUND can make a displayed or calculated value easier to read, but it changes the result returned by the formula, not just its appearance. With 3.146 in A2, =ROUND(A2,2) returns 3.15. If the goal is only to show two decimal places while retaining the underlying value, use number formatting instead: select the cells and use Home > Number to choose or adjust a number format.
Other useful functions include ABS for a number’s absolute value, PRODUCT for multiplying values, and SUBTOTAL for calculations on a list that may be filtered. SUBTOTAL can exclude rows hidden by a filter, which makes it useful for visible-row summaries. Choose a function based on what the result should mean, and check whether blank cells, text, or filtered rows should be included.
Logic and conditional calculations
IF returns one result when a test is true and another when it is false. Suppose C2 contains a score of 72 and the rule is that scores of 60 or higher pass. In D2, =IF(C2>=60,"Pass","Review") returns Pass. The comparison operators include greater than (>), less than (<), equal to (=), and their combined forms such as >= and <> for “not equal to.”
AND and OR let you combine tests: AND is true only when all its tests are true, while OR is true when at least one is true. IFERROR can provide a chosen fallback when a calculation returns an error. If B2 contains 20 and C2 contains 0, =IFERROR(B2/C2,0) returns 0 instead of displaying the division-by-zero error. Use a fallback thoughtfully: replacing every error with zero can conceal a data or formula problem, so a message such as “Check input” may be clearer in some workbooks.
Conditional functions summarize rows that meet criteria. If A2:A5 contains Tea, Coffee, Tea, Tea and the corresponding sales values in B2:B5 are 10, 8, 12, and 5, =SUMIF(A2:A5,"Tea",B2:B5) returns 27. With scores 72, 55, 60, 49, and 88 in C2:C6, =COUNTIF(C2:C6,">=60") returns 3. SUMIFS and COUNTIFS handle multiple conditions, such as totaling sales for one product in one region.
Lookups and dynamic-array functions
A lookup finds a value in one place and returns related information from another. With product codes in A2:A4 (P01, P02, P03), product names in B2:B4 (Notebook, Pen, Folder), and P02 in E2, =XLOOKUP(E2,A2:A4,B2:B4,"Not found") returns Pen. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It can search one range and return a corresponding result from another, and its optional not-found argument makes missing matches easier to handle.
Older workbooks may use INDEX with MATCH, which can also find a value and return a corresponding item. When sharing a workbook with people using older Excel versions, check whether the functions in it are supported on their machines. A newer function can show a compatibility error in an older release even when the formula is otherwise correct.
FILTER, SORT, and UNIQUE can return arrays of results that spill into nearby empty cells. For example, with names in A2:A5 and quantities in B2:B5 (120, 80, 150, 60), =FILTER(A2:B5,B2:B5>=100,"No matches") returns the rows for the first and third entries. These functions are available in Microsoft 365 and Excel 2021 or later. If cells in the spill area are occupied, Excel can return a spill error; clear the obstructing cells before using the result.
Text and date functions
Text functions help clean, combine, or extract parts of values. TRIM removes extra ordinary spaces from text, while LEFT, RIGHT, and MID return characters from specified positions. If A2 contains the text “North-482” and you want the first five characters, =LEFT(A2,5) returns North. These functions are useful for imported lists, but they do not automatically understand every data format or remove every invisible character.
To join names in A2:A4 containing Ada, Bo, and Chen with comma separators, =TEXTJOIN(", ",TRUE,A2:A4) returns Ada, Bo, Chen. TEXTJOIN is available in Microsoft 365 and Excel 2019 or later. CONCAT is another option for joining text, while the ampersand operator can combine two values directly. TEXT can format a number or date as text, but the result is text rather than a numeric value, which can affect later calculations.
Excel stores dates as serial numbers, then displays them according to the cell’s number format. DATE builds a date from year, month, and day inputs; YEAR and MONTH extract parts of a date; TODAY returns the current date and changes as the workbook recalculates. NETWORKDAYS counts working days between dates, with an optional holiday range. If a date appears as a number, apply a date format; if it is stored as text, changing the display format alone may not convert it into a real Excel date.
How to choose the right formula
Start by describing the result you need in plain language. “Add every sale” suggests SUM; “add sales for Tea only” suggests SUMIF; “count scores above a threshold” suggests COUNTIF. For a matching item, use a lookup function; for a decision based on a test, use IF. The data’s shape matters too: a single condition and a range of criteria call for different functions than multiple conditions or a returned list.
Before building a longer formula, check these practical points:
- Confirm which cells contain the input values and which cells should show the result.
- Check whether the data is numeric, text, or a genuine date; values that look alike can have different underlying types.
- Decide how blanks, duplicate matches, missing values, and errors should be handled.
- Use absolute references when a copied formula must keep pointing to a fixed input, such as a tax-rate assumption.
- Test a formula on a small example where you can verify the expected answer by hand.
- Consider compatibility if the workbook will be opened in older Excel versions or another spreadsheet app.
When a formula becomes difficult to read, split the calculation into helper columns or use named ranges. Microsoft 365 also supports LET, which can assign names to intermediate calculations inside a formula; LAMBDA can define reusable custom calculations. These features are available in Microsoft 365, while some newer text and array functions, including TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK, require Microsoft 365 or Excel 2024. A workbook intended for broad sharing may be easier to maintain if it uses widely supported functions.
Common formula problems and fixes
A missing equals sign means Excel may treat an entry as plain text instead of calculating it. A misspelled function name or unmatched parenthesis can produce a formula error. If Excel displays the formula itself rather than its result, check whether the cell is formatted as Text; change it to General, then edit and re-enter the formula. Also check whether Show Formulas is enabled on the Formulas tab.
The #N/A error often means a lookup did not find a match, while #DIV/0! indicates division by zero. #VALUE! commonly points to an unexpected value type, and #REF! means a formula refers to a cell reference that is no longer valid. Before masking an error with IFERROR, inspect the source data and the formula’s ranges. A missing lookup key and a wrongly selected return range need different fixes.
Another common issue is a result that changes unexpectedly after copying. Relative references move with the formula; absolute references do not. A conditional formula can also fail because criteria are written as text incorrectly or because numbers are stored as text. If a formula uses commas but Excel expects semicolons because of regional settings, follow the separators Excel accepts on that computer. The examples in this guide use comma separators as requested, which is standard in many English-language installations.
Frequently Asked Questions
What is the most useful Excel formula?
There is no single best formula for every worksheet. SUM is a good first function for totals, but IF, COUNTIF, and lookup functions can be more useful when the task involves rules, counting matching records, or retrieving related information.
How do I see a list of Excel functions?
Select a cell and use the Insert Function button next to the formula bar, or open the Formulas tab and browse its function categories. The function search can help you find a function by describing what you want to calculate. Available functions depend on your Excel version.
Do Excel formulas always start with an equals sign?
Yes. A calculation entered in a worksheet cell starts with an equals sign. Without it, Excel may interpret the entry as text or as a value rather than a formula.
Why does my Excel formula show an error?
Check the function spelling, parentheses, referenced cells, and input types first. Then identify the error displayed, such as #N/A for a missing lookup match or #DIV/0! for division by zero. Fix the underlying cause before deciding whether an error-handling function is appropriate.
Can I use these formulas in Google Sheets?
Many common functions, including SUM, IF, and COUNTIF, also exist in Google Sheets, but function availability, behavior, separators, and menu options can differ. Check the Google Sheets documentation before relying on a newer Excel-specific function or sharing a workbook across spreadsheet apps.
Related
- Excel Direct Access vs Link: What’s the Difference?
- Excel Desktop Keyboard Shortcuts: A Practical Guide
- Excel Dependent Drop-Down Lists in Microsoft 365
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula Not Calculating Until You Press Enter?If an Excel formula updates only after you press Enter, first check whether calculation is set to Automatic:…
- Using VLOOKUP to Compare Two Sheets in Excel Step by StepTo compare records on two Excel sheets, use VLOOKUP with an exact match and test whether it finds…
- Excel Sum Multiple Cells: Formulas, Ranges, and TipsTo sum multiple cells in Excel, use the SUM function: enter =SUM(A1:A5) to add the values in cells…
- SUMPRODUCT Excel: Calculate a Weighted AverageIn Excel, calculate a weighted average with =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5), assuming A2:A5 contains the values and B2:B5 contains their weights.…
- Excel SUBSTITUTE Double Quote: Formulas and ExamplesTo remove straight double quotation marks from a cell in Excel, use =SUBSTITUTE(A2,CHAR(34),"") when the text is in…
- Excel Formulas to Find Matching Data in Your ListsTo check whether a value appears in another Excel list, use COUNTIF; to return information from the matching…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.