First formulas and operators — Excel

In this lesson you will learn

Write simple formulas with cell references
Use operators and the order of operations
Recognise the #DIV/0!, #VALUE!, #NAME? and #REF! errors

Murad is buying school supplies: 3 notebooks, 2 pens and 5 pencils. Working out the total once on a calculator is easy — but what if the notebook price changes? You would have to count everything again. In Excel you write a formula once, and the result updates by itself whenever a price or quantity changes.

What is a formula?

Definition

Formula

An expression in a cell that starts with =. Excel calculates it and shows the result in the cell and the formula itself in the formula bar.

Type =2+3 and the cell shows 5. It is far more useful, though, to use cell references instead of numbers: =B2*C2 means “multiply the price in B2 by the quantity in C2”. As soon as B2 or C2 changes, so does the result.

Operator	Meaning	Example
+	addition	`=B2+C2`
-	subtraction	`=B2-C2`
*	multiplication	`=B2*C2`
/	division	`=B2/4`
^	power	`=A2^2`
&	joining text	`=A2&B2`
= <> > < >= <=	comparison: the result is TRUE or FALSE	`=B2>=50`

Order of operations

Excel follows the rule from maths: brackets first, then powers (^), then multiplication and division, and finally addition and subtraction; operations of the same level go from left to right. That is why =2+3*4 gives 14 while =(2+3)*4 gives 20. When in doubt, add brackets — extra brackets do no harm.

A purchase with a discount

The notebook price is in B2 (1.50 ₼) and the quantity in C2 (3); the pen price is in B3 (0.60 ₼) and the quantity in C3 (2). The shop gives 10% off the whole purchase, and the discount is typed in F1 as 10%. Which formula calculates the amount to pay?

Show solution

First the brackets give the purchase total: (B2*C2+B3*C3) = 4.50 + 1.20 = 5.70 ₼.
After the discount, 90% of the price remains: (1-F1) = 0.9.
Formula: =(B2*C2+B3*C3)*(1-F1) = 5.70 × 0.9 = 5.13 ₼.
Without the outer brackets Excel would first work out B3*C3*(1-F1) and the result would be wrong.

Enter a formula by pointing

1
Select the result cell and type =
Click cell D2 and type the equals sign.
2
Click the cells
Click B2, type *, then click C2. Excel types the addresses for you and outlines the cells in colour.
3
Press Enter
The cell shows the result, and the formula bar shows =B2*C2.
4
Copy the formula down
Drag D2's fill handle down. In each row the formula adapts to its own cells: =B3*C3, =B4*C4… You will learn why in the next lesson.

Interactive

Loading simulation…

A stationery purchase: price × quantity in each row.

Error values

Error	Cause
#DIV/0!	Division by zero or by an empty cell
#VALUE!	Wrong type, e.g. multiplying text by a number
#NAME?	Excel doesn't recognise a name: a misspelt function or text without quotes
#REF!	The formula refers to a cell that was deleted
#####	Not an error: the column is just too narrow

Edit a formula — the cells it refers to are shown in colourF2

Show or hide formulas instead of results on the sheetCtrl+`

Cancel typing a formulaEsc

Key points

Every formula starts with =; the cell shows the result, the formula bar shows the formula.
Operators: + - * / ^, & for text and = <> > < >= <= for comparisons.
Order: brackets → powers → multiply/divide → add/subtract.
Use cell references instead of numbers so results update automatically.
#DIV/0! — division by zero, #VALUE! — wrong type, #NAME? — unknown name, #REF! — deleted reference.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

Which of these is a correct Excel formula?