Excel Formulas and Functions: A Beginner's Guide

An Excel formula is an instruction that calculates a result. A function is a predefined calculation that you can use inside a formula. For example, =B2*C2 is a formula built with cell references and an operator, while =SUM(B2:B10) is a formula that uses the SUM function.

That distinction matters less than knowing how the pieces fit together. Once you understand the equal sign, references, operators, functions, and arguments, you can read unfamiliar formulas instead of treating them as mysterious strings of symbols.

Formula or function: what is the difference?

Every worksheet formula begins with an equal sign. Without it, Excel normally treats what you typed as a value or text rather than as a calculation.

Read a formula one part at a time

Consider this formula:

=SUM(E2:E8)

• = tells Excel that a formula follows.
• SUM is the function name.
• The parentheses enclose the function’s arguments.
• E2:E8 is a range reference supplied as the argument.

A formula can also contain constants and operators. In =B2*C2*(1-D2), the references are B2, C2, and D2; the constant is 1; and the multiplication and subtraction signs are operators.

Function arguments are commonly separated with commas, but Excel may use semicolons when required by your operating-system locale and Excel settings. Follow the separator shown by Formula AutoComplete on your computer.

Build a useful formula from worksheet data

Suppose an order sheet uses column B for quantity, column C for unit price, column D for the discount rate, and column E for the final line amount.

1. Select cell E2, where the result should appear.
2. Type =.
3. Select B2, type *, and select C2.
4. Type *(1-D2).
5. Press Enter.

The completed formula is:

=B2*C2*(1-D2)

This formula multiplies quantity by unit price and then applies the discount to return the final amount.

If B2 contains 4, C2 contains 25, and D2 contains 10%, the formula returns 90. Excel first calculates the quantity times the price, then multiplies that amount by the portion remaining after the discount.

Using cell references instead of typing all three values directly into the formula makes the result respond when the worksheet data changes.

Use functions for common calculations

Functions save you from spelling out every operation. These five cover many everyday summaries:

The SUM function adds the sales values in cells B2 through B4 and returns the total in B5.

Let Excel help with the syntax

As you type a function name after the equal sign, Formula AutoComplete displays matching functions. After you choose one, Excel shows the expected arguments. You can type references or select cells directly on the worksheet.

For a quick total, select the cell below a column of numbers and choose Home > AutoSum. Because AutoSum detects a nearby range for you, check the highlighted cells before pressing Enter.

If you do not know the function name, choose Formulas > Insert Function or select the Insert Function button beside the formula bar. You can search by task, choose a function, and fill in its arguments.

These steps show how to insert the AVERAGE function, enter its range, and return the final result in the worksheet.

Microsoft’s formula overview provides more examples of functions, references, constants, and operators.

Control the order of calculation

Excel does not simply calculate every formula from left to right. It follows operator precedence. Parentheses override the default order by forcing the enclosed calculation to happen first. Without parentheses, Excel uses this order:

1. Reference operators, including the colon in a range such as B2:B8
2. Negation, as in -1
3. Percent
4. Exponentiation
5. Multiplication and division
6. Addition and subtraction
7. Text concatenation with &
8. Comparisons such as =, >, <, and <>

For example, the two formulas below do not return the same result:

=10+5*2
=(10+5)*2

The first returns 20 because multiplication occurs before addition. The second returns 30 because the parentheses make Excel add first.

Parentheses change the calculation order in Excel, so the same numbers can return different results.

Even when parentheses are not strictly required, they can make the intended logic easier to see. Use them when a reader might otherwise have to remember the precedence rules.

Copy formulas without breaking references

New references are relative by default. If the formula =B2*C2 is copied from row 2 to row 3, Excel changes it to =B3*C3. This is usually what you want when the same calculation applies to every record.

When you copy a formula in Excel, relative references adjust automatically to match the new row or column.

Use dollar signs when part of the reference must stay fixed. If cell H2 contains a tax rate, this formula keeps referring to that cell as it is copied down:

=E2*(1+$H$2)

An absolute reference such as $H$2 stays fixed when you copy a formula, while the other cell references adjust normally.

While editing a reference in the formula bar, press F4 to cycle through relative, absolute, and mixed forms.

Combine functions when one is not enough

A function can be used as an argument inside another function. This is called nesting. The following formula calculates an average and then rounds the result to two decimal places:

=ROUND(AVERAGE(E2:E8),2)

Read nested formulas from the inside out. Here, AVERAGE produces a value first, and ROUND uses that value. Nest only when it keeps the logic readable; helper cells can be clearer than one long formula.

Diagnose common formula problems

Show every formula on the worksheet

Press Ctrl+` to switch between displayed results and displayed formulas. You can also choose Formulas > Show Formulas. This view makes inconsistent references easier to spot across a row or column.

Show Formulas displays the formulas in worksheet cells instead of their calculated results.

Do not wrap a formula in IFERROR until you understand the original error. IFERROR catches every error type, so it can hide a broken reference or an invalid input as easily as an expected error.

Microsoft’s guide to avoiding broken formulas covers separators, parentheses, missing arguments, and common error values in more detail.

Habits that make formulas easier to maintain

• Keep inputs in cells instead of burying changeable values inside formulas.
• Use parentheses to make the intended calculation order obvious.
• Use absolute references only for inputs that must remain fixed.
• Check the first copied formula before filling an entire column.
• Break a long calculation into helper cells when that makes the logic easier to audit.
• Fix the cause of an error before deciding whether its display should be suppressed.

Excel formula questions

Start with formulas you can explain

A reliable formula is not merely one that returns a result; it is one you can read and verify. Begin with the equal sign, identify each reference and operator, and check the function arguments before copying the formula elsewhere. That approach scales from a two-cell calculation to far more capable worksheets.