Excel Formulas

A formula is an instruction that tells Excel to calculate something. It always starts with =.

Arithmetic Operators

Operator	Meaning	Example	Result
`+`	Add	`=20+5`	25
`-`	Subtract	`=20-5`	15
`*`	Multiply	`=20*5`	100
`/`	Divide	`=20/5`	4
`^`	Power	`=2^3`	8
`%`	Percent	`=20%`	0.2
`&`	Join text	`="Ali"&" Khan"`	Ali Khan

Most formulas use cell addresses. Ayesha is making a bill:

In D2, type and fill down to D3:

text

=B2*C2

Then the total, discount and final bill:

text

D4:  =D2+D3
D5:  =D4*10%
D6:  =D4-D5

Excel does not always calculate from left to right. It follows this order:

Compare these two formulas:

text

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

Formula	Result	Why
`=10+5*2`	20	5*2 is done first, then 10 is added
`=(10+5)*2`	30	The brackets are done first

When in doubt, add brackets. They make the formula clearer and remove mistakes.

text

=(B2+C2+D2)/3

Without brackets, =B2+C2+D2/3 would divide only D2 by 3, giving a wrong answer.

Click the cell and edit in the formula bar, or press F2
Excel colours each reference and draws a box of the same colour around the cell it uses
Copy a formula down with the fill handle, or double-click the fill handle to fill to the end of the data
Press Ctrl + ` (the key above Tab) to show all formulas instead of results, and press it again to go back

Error	Meaning	Typical cause
`#DIV/0!`	Division by zero	Dividing by an empty cell
`#NAME?`	Excel does not know a name	A misspelled function, like `=SUMM(A1:A3)`
`#VALUE!`	Wrong type of value	Adding text to a number
`#REF!`	Invalid reference	A cell used in the formula was deleted
`#N/A`	Value not available	A lookup did not find a match
`####`	Not an error	The column is too narrow; make it wider

For example, if B2 is empty:

text

=A2/B2

	A	B	C
2	500		#DIV/0!

You will learn how to handle errors like this with IFERROR in the IF chapter.

What is the result of =4+6/2 in Excel?

57102