In excel there are over 300 pre-defined formulas…these are formulas which comes pre-packaged in your Excel software. These are called: Functions
Formulas in Excel are those, which are entered in by the user.
Some Examples of pre-defined formulas are VLOOKUP, INDEX, HLOOKUP, SUM, AVERAGE, etc.
Functions and Formulas in Excel helps you to quickly perform calculations.
All Formulas consists of:
- And equal sign: =
- Operands – Are the data Excel uses to perform calculations. This could be a number (1,2,3,4,5…) or a cell reference e.g. B1, F6, C7 and so on.
- Operators – These are symbols which are used to combine operands e.g. +, – , * , /
Here is an example of a Formula in Excel:
= 5 + 4
See diagram below for more explanation
Some of the Standard Operators Excel uses are:
Addition (+) = 10 + 4
Subtraction ( – ) = 7 – 3
Multiplication ( * ) = 5 * 5
Division ( / ) = 8 / 4
Exponents ( ˆ ) = 5 ˆ 5 – This means 5 raised to the power of 5 or 5 multiplied by itself 5 times.
How to create a Basic Formula
1. Click the cell you want to build a formula in
2. Type an equal ( = ) sign in the selected cell
3. Type in your operand/s. You can also click on a reference cell – We clicked on B1.
4. Enter in your operator. We entered in the Additional operator.
5. Type in your second operand or click on the second reference cell you want in you formula. We clicked on A4.
6. Press the ‘Enter’ key on your computer keyboard.
Excel automatically added the cells which were referenced in the formula – Cell B1 + A4, and produced the answer 14. See Diagram below.
To create multiplication, Division, Subtraction or Exponential Formulas just follow the same steps above, but you will just need to change the Operator in the formula to perform the specific formula you require.
Lesson 46: How to use Functions in Excel