A basic Excel formula list includes SUM for totals, AVERAGE for means, COUNT and COUNTA for counting, MIN and MAX for extremes, and IF for simple decisions. Formulas begin with an equals sign; the examples below explain what each one does and show the cell layout and result so you can adapt them to your worksheet.
What an Excel formula does
A formula tells Excel to calculate or return a value. It can use numbers, cell references, operators such as plus and minus, and functions such as SUM. A formula usually updates when the values in its referenced cells change, which is why a worksheet is more useful than a static list of manually calculated results.
A function is a named operation within a formula. SUM, for example, adds values supplied to it. Its arguments—the values or ranges a function works on—go inside parentheses. You can type a formula directly into a cell or select a cell, type an equals sign, and build the expression by clicking cells and choosing a function. In Excel for Microsoft 365 on Windows, the Formula Bar above the grid also lets you inspect and edit the selected cell’s formula.
The examples here use English function names and commas between arguments, as in the usual Microsoft 365 setup. Depending on Windows regional settings, Excel may require semicolons instead of commas. The same basic functions are available in Excel for Mac and Excel for the web, although menus and some keyboard shortcuts can differ. A formula’s result appears in the cell, while its expression is visible in the formula bar.
Core arithmetic and range formulas
For a small set of cells, you can use arithmetic operators or a function. Functions are especially convenient when a range grows, because you can refer to the whole range instead of writing each cell separately. These are useful starting points:
- SUM: adds numbers.
- AVERAGE: calculates the arithmetic mean of numeric values.
- MIN and MAX: return the smallest and largest numbers in a range.
- COUNT and COUNTA: count numeric entries and nonempty entries, respectively.
With numbers 18, 22, 35, and 45 in B2:B5, entering this in B6 returns 120:
=SUM(B2:B5)
With the same values in B2:B5, this formula in B7 returns 30, their average:
=AVERAGE(B2:B5)
With those same four numeric values in B2:B5, this formula returns 18 and this one returns 45:
=MIN(B2:B5) and =MAX(B2:B5)
With numeric entries in B2:B5 and a text note in B6, this formula returns 4 because it counts numbers, while the next returns 5 because it counts all nonempty cells:
=COUNT(B2:B6) and =COUNTA(B2:B6)
COUNT ignores text and blank cells; COUNTA counts cells containing text, numbers, dates, or formulas that return text or a value. A formula returning an empty text string is still a formula in a nonempty cell, so COUNTA can count it. If the goal is to count cells that meet a rule, COUNTIF is often a better fit.
Conditional formulas for decisions and criteria
IF tests a condition and returns one result when it is true and another when it is false. It is commonly used for labels such as Pass or Review, but can also return numbers or dates. The condition can compare a cell against a value using operators such as greater than, less than, or equal to.
With a score of 72 in B2, put this formula in C2. It returns Pass because 72 is at least 60; a lower score returns Review:
=IF(B2>=60,"Pass","Review")
SUMIF adds values only when corresponding cells meet one condition. In this example, A2:A5 contains Pens, Paper, Pens, and Clips, while B2:B5 contains 3, 5, 4, and 2. The formula returns 7, adding the amounts for the two Pens rows:
=SUMIF(A2:A5,"Pens",B2:B5)
COUNTIF counts cells that match a criterion. With the same category names in A2:A5, this returns 2 because Pens appears twice:
=COUNTIF(A2:A5,"Pens")
Criteria may be text, a number, or a comparison such as greater than a threshold. When a criterion includes a comparison operator and a cell reference, Excel commonly joins the operator and reference with an ampersand. For text criteria, spelling and extra spaces matter; a value that looks identical on screen may not match if one cell contains a hidden leading or trailing space.
Cell references and copying formulas
Cell references are what make a formula reusable. A relative reference such as B2 adjusts when you copy the formula to another row or column. For example, if a row formula refers to B2 and is copied one row down, Excel normally changes the reference to B3. This behavior is useful for applying the same calculation to many records.
An absolute reference, written with dollar signs, stays fixed when copied. Suppose B2 contains a price of 20 and F1 contains a tax rate of 0.1. With the inputs in those cells, enter this in C2; it returns 2, the tax amount:
=B2*$F$1
If you copy that formula down, B2 adjusts to B3 while $F$1 remains fixed. A mixed reference fixes only a row or column: $F1 fixes the column, while F$1 fixes the row. This is handy when copying a calculation across a table in one direction but allowing part of the reference to change in the other. Use absolute references when every row should use the same rate, date, threshold, or other shared input.
How to choose the right basic formula
Start with the question you want answered rather than choosing a function because it looks familiar. For a total, choose SUM; for a typical numeric value, choose AVERAGE; for a count of numbers, choose COUNT; and for a count of filled cells, choose COUNTA. MIN and MAX answer questions about boundaries, while IF returns a decision based on a test. SUMIF and COUNTIF are appropriate when a total or count should include only matching records.
Before using an average, consider whether the data actually represents comparable observations. A blank cell is ignored by AVERAGE, while a zero is included and can lower the result. Similarly, a total may be misleading if some numbers are stored as text and therefore are not included in a calculation. Checking the source data is part of choosing a sound formula, not merely a cleanup task after the fact.
For a list of sales by category, SUMIF can answer a one-condition question such as total sales for one product. If the task involves several conditions, Excel has additional functions, but a beginner can first organize the data clearly and check whether a filter or PivotTable would answer the question more transparently. Formulas are valuable when results need to update in place; a PivotTable can be more convenient for exploring and summarizing a larger table.
Common formula errors and how to fix them
Most beginner formula problems come from references, data types, or syntax. Excel often displays an error code that points toward the problem, but the code does not always identify the exact cause. Check the formula bar and the referenced cells before replacing a formula with a different function.
- #NAME? often means Excel does not recognize a function name or text in a formula. Check spelling and make sure text criteria are enclosed in straight double quotation marks.
- #VALUE! can occur when a calculation receives an incompatible type of value, such as text where a number is expected.
- #DIV/0! means a formula divides by zero or by a blank cell treated as zero.
- Unexpected totals: check whether some apparent numbers are stored as text, whether the range includes the intended rows, and whether values have been omitted.
- Formula shown instead of result: check that the cell is not formatted as Text, and that the entry begins with an equals sign. Re-entering the formula after changing the format may be necessary.
A range written with a colon includes the cells from its first endpoint through its last endpoint. If a new record is added just below a referenced range, it may not be included automatically when the range is an ordinary fixed reference. Excel Tables can help with expanding data: when a range is formatted as a table, formulas and references can use the table’s column names and generally adjust as table rows are added. For repeatable reports, converting a well-structured data range to a table can reduce missed-row errors.
Building a reliable formula workflow
A good formula is easier to maintain when the worksheet is organized. Keep one row for each record, use clear column headers, and avoid inserting explanatory notes or blank rows in the middle of the data. Store rates and assumptions in clearly labeled cells rather than scattering the same number through many formulas. That way, a future change can be made once and checked easily.
- Write down the result you need, such as a total, count, average, or decision.
- Identify the input cells and confirm that numbers are actually numeric and categories are consistent.
- Choose the simplest function that answers the question, then enter it in a result cell.
- Check the result with a small hand-calculated example or a second method where practical.
- Copy the formula only after confirming whether its references should move or stay fixed.
Use readable labels and, when formulas become long, consider breaking the work into helper columns. Several short, auditable steps are often easier to troubleshoot than one dense expression. A formula should make the logic clear to the next person who opens the workbook, not just produce a number.
Frequently Asked Questions
What are the most basic Excel formulas?
Common first formulas use SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, and IF. SUM adds values, COUNT counts numbers, and IF returns one of two outcomes according to a condition. The best starting set depends on the questions your worksheet needs to answer.
How do I start a formula in Excel?
Select a cell, type an equals sign, and then enter the calculation or function. You can type cell references or select cells while building the formula. Press Enter to calculate it; select the cell later to see the expression in the formula bar.
What is the difference between COUNT and COUNTA?
COUNT counts cells containing numbers, including numeric dates, but does not count text or blanks. COUNTA counts cells that are not empty, including text. Choose according to whether you need a count of numeric values or all populated entries.
Why does my Excel formula show as text?
The cell may be formatted as Text, or the entry may not begin with an equals sign. Change the cell format to General and re-enter the formula. Also check for an apostrophe before the equals sign, which can make Excel treat the entry as text.
Do Excel formulas work on Mac and in the browser?
Core functions such as SUM, AVERAGE, COUNT, and IF work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon locations and some shortcuts differ between platforms, and regional settings may use a different argument separator.
Related
- Excel Subscript Out of Range: Causes and Fixes
- Excel Engineering Number Format Explained
- Excel Employee Work Schedule: Setup and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter an Excel Sheet by DateTo filter an Excel sheet by date in Microsoft Excel for Microsoft 365 on Windows, select a cell…
- Excel Formula for Last Updated Date in a Specific CellFor a live current date in Excel, use =TODAY(); for the current date and time, use =NOW(). Neither…
- Excel Align Left Shortcut: Windows and Mac GuideIn Excel for Microsoft 365 on Windows, select the cell or range and press Alt, then H, A,…
- Excel Formula to Return Yes If a Value Is in a ListWith the value to check in A2 and the allowed list in D2:D10, enter =IF(COUNTIF($D$2:$D$10,A2)>0,"Yes","No"). It returns Yes…
- SUMIF in Excel: Match Cells That Contain Specific TextYes. SUMIF can add values when a cell contains particular text: with descriptions in A2:A7 and amounts in…
- Conditional Formatting Formula If Statement in ExcelIn Excel, a conditional-formatting formula must evaluate to TRUE or FALSE for the cell or row you want…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.