Back to Excel Formulas menu


The very basics of formulas


Overview:
Every formula in Excel starts with the equal sign (“=”).

This sign is very meaningful and tells the computer:
“What’s written next to the Equal sign is not simple text. I want to calculate something.
If a combination of a letter and a number appears next to it (eg. B6, G78, D13) they refer to addresses of cells in the worksheet.
If arithmetic signs appear (signs like + - * / ) they refer to the operations they mean (eg. adding, subtracting etc.)”


Creating a formula step by step
1. Type the = sign.
2. Type the address of the cell to include in the formula.
-or-
2. Click with the mouse on the cell which you want to include in the formula (the cell’s address will automatically appear in the formula, no need to type it).
3. Type an arithmetic sign (e.g. +, -, *, /, see list below)
4. Continue with adding your desired elements to the formula (it could be a number, or another cell’s address, or a function).
5. End by pressing the [Enter] key.


Examples of formulas:
=B4
In words: Show me the value of the cell in address B4.

=B4*7+3
In words: Multiply the value of the cell B4 with 7, and add 3 to it.

=(B4+B5+B6)*D6
In words: add up the values of the cells B4, B5 and B6, and multiply their sum by the value of cell D6.

You can include functions in your formulas:
=SUM(B4:B6)*D6
This does exactly the same as the last formula from above.

=B4*MIN(C1:C10)+7
In words: Multiply the value of cell B4 with the smallest number in the range of cells C1:C10, and add 7 to it.


Using the percentage sign (%):
Excel understands the % sign when attached to numbers inside a formula.

=A5*20%
In words: Multiply the value of cell A5 by 0.2 (or: give me 20 percent of A5).

=A5*115%
In words: Show me the value of cell A5 plus 15 percent of it.

=A5*(1+B5)
Let’s assume the value 15% is written inside cell B5, then the formula in words will be:
Show me the value of A5 plus 15 percentage of it. That seems the same as the last formula, but the difference is now you can easily change the percentage, just type a new value in cell B5 (eg. 20%) and the formula gives a new updated result.


List of arithmetic signs (called also: “operators”):
+ plus
- minus
* multiplication
/ division

There is also:
^ exponential
for example =2^4 (will give the value of 16)


One note:
You should take into consideration the order in which arithmetics are calculated, consider the following two formulas:
=1+2*3+4
=(1+2)*3+4
They are not equal, the first one gives the value of 11 (multiplication is calculated first), and the second gives the value of 13.