Formulas are a set equations that perform a certain calculations on values in your worksheet. Proper use of Formulas in Excel can make your life really easier. In this lesson we will discuss about 5 elements that can be used to create a formula in Microsoft Excel.
Before we start here are few things that you may already know but still requires some attention.
- A formula always starts with an equal sign (=).
- A formula can also contain any or all of the following five elements: Constants, References, Operators, Functions and Parentheses.
- A formula in a cell can contain maximum 8000 characters, if you need more than you have to break it in multiple formulas.
Formula Elements or Parts of Formula in MS Excel
There are Five formula elements in Microsoft Excel and a formula in any Cell of a worksheet can contain all of them. These elements are: Constants, References, Operators, Functions and Parentheses.
See this example formula below
Here we are calculating the perimeter of a circle with a radius of the value of the cell A1. Lets divide this formula in five elements.
- Constants: are Values or Strings directly entered in to a formula. Constants can be anything like Numbers or Texts that is not calculated and entered directly/manually in a formula.
Numeric values like 10 or 50, texts like Result or Annual Interest and even Dates like 10/9/2008 are all Constants. With constance the result will only change if you modify the formula yourself
The numeric value 2 is a constant value in the above formula.
- References: are used to refer to a cell or range in a Excel. A reference tells Excel where look for values that is required in a formula. In Excel a cell(s) or range(s) can be refereed as many times as you need in as many formulas as you need.
You can refer for values in a formula to a cell(s) or range(s) in the current worksheet, in another worksheet in the same workbook, or even in a worksheet in another workbook. References to cells in other workbooks are called links or external references.
A1 is the cell reference here.
- Operators: are special symbols that perform specific operations between operands, and then return a result.
See more on Different Types and Usages of Operators in Excel Formulas.
Multiple signs ( * ) are operators in the given example.
- Functions: are inbuilt (block of)codes that performs a certain calculation in a particular order using values called arguments. Though Excel comes with hundreds of inbuilt functions and you can also create your own functions using VBA.
Here we have used the function PI() that returns the value of pi or 22/7.
- Parentheses: control the order in which expressions within a formula are evaluated. Just like simple mathematics use parentheses to define the order of an equation.
For example if you enter a formula like =2+3*5, then the result will be 17 as it will calculated as 2+(3*5) but the result will change to 25 if we write like =(2+3)*5.
Using the first parentheses () in above example makes it a block of equation that is evaluated within itself before evaluating others if we add some more equation on the above example.
Thats it! Those are the five elements that we have and can use in Excel to create formulas and calculate our equations. Hope you like it.
That was the first part of Formulas in Microsoft Excel. In the next part we will go through Different ways to Enter formulas In Excel.