The very basics of Excel formulas
Please be honest. Can formulas be easy to learn?
Yes! Have a look at the video above, it can't get any easier than that. You'll be amazed how simple the concept of formulas really is.
After that, open a blank worksheet and try it on your own.
Formulas - The basic concept
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.
2. Click with the mouse on the cell which you want to include in the formula (this way the cell’s address will automatically appear in the formula, no need to type it).
3. Type an arithmetic sign (such as plus +, minus - , multiplication * , or other, see the 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 simple 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.
Excel understands the percentage sign (%) when attached to numbers inside a formula. Here are a few examples:=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 that inside cell B5 you have written 15%, then the formula in words will be:
Show me the value of A5 plus 15 percentage of it. That seems the same as the formula before it, but the difference is that 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.
Formulas with functions:(You can follow this link to learn how to use simple functions.)
This does exactly the same as one of the examples from above. It sums up the cells from B4 to B6 and multiply it by D6.=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.
List of arithmetic signs (called also: “operators”):
There is also:
for example =2^4 (will give the value of 16)
One important note:
Don't forget the order of operations rules!
You should take into consideration the order in which arithmetics are calculated, consider the following two formulas:
They are not equal, the first one gives the value of 11 (multiplication is calculated first), and the second gives the value of 13.
Excel knows the order of operations too!