What are “Formulas”?

We use a “Formula” when we want to perform a calculation in “Excel”.
A “Formula” in “Excel” always starts with an “equal” sign (=).
For example, if you wish to calculate the sum of 2 and 3, you would normally type 2 + 3. In “Excel”, you must type “= 2 +3”

You can enter a “Formula” in a cell or you can type it in the “Formula bar” of the cell. When you start typing with the “equal” sign, Excel knows that you are entering a “Formula” and that it has to calculate whatever is entered after the equal sign and find out the result.

 

Calculation operators and their priority

Calculation Operators such as addition (+), subtraction (-), multiplication (*), etc., are executed in a standard pre-set order

 

To remove all doubts, I have listed them in below table in the order of execution

Operator                                      Function

1 = %                               Percentage calculation

2 = ^                               Exponential

3 = * And /                    Multiplication, and Division

4 = + and –                    Addition and Subtraction

 

When a “Formula” includes operators with the same priority, for example a “Formula” having both a summation operator as well as a subtraction operator, the operation takes place from left to right.

We can influence this order by using parentheses ().

For example: 2 * (3+4) will not give the same result as 2 * 3+ 4, or (2 * 3) 4

But this is more related to a math lesson rather than a lesson for “Excel”.

 

Entering “Formula”(s)

Each “Formula” always begins with the equal sign (=), followed by values and/or calculation operators.

For example, in cell A1 we have typed 100 and in cell B1 we’ve typed 200. If we want to calculate the sum of cells A1 and B1, we can select cell C1 and type =A1+B1 and click Enter. Another way is for you to type the equal sign, select cell A1 with your mouse, add a + sign, then select cell B1 with your mouse and click Enter. This does the same.

If we now change the numbers in cell A1 and/or B1, then the resulting sum in cell C1 is automatically changed as well.

This is one of the best features of “Excel” and one of the reasons we want to minimize entering numbers in “Formula”(s).

 

When we click the Enter key, the result of the “Formula” is seen in the cell, though the “Formula” itself can still be seen in the “Formula bar”.

Below are some examples of “Formula”(s):

“Relative cell reference” and “Absolute cell reference”

As previously mentioned, a cell is the intersection of a column with a row.

Each cell has a cell address. The cell address consists of the letter of the column and number of the row.

The cell address of the selected cell is listed in the “Name box”.

Now that we know what a cell address is , we can refer to cell addresses in a “Formula”, for example = A2*B2.

If we copy Formulas, cell references are adjusted automatically.

For example: If we have a Formula “= A2 * B2” in cell C2, and we are going to copy this to cell C3, then the copied “Formula” will automatically adjust to = A3 *B3. This is called a “Relative cell reference”.

However, if you are referencing a cell in your “Formula” that should remain the same when copying, you should make this cell reference “Absolute”. This is done by adding the dollar sign ($) before the column letter and row number. Even if you copy your “Formula” down, up, left or right, a cell reference with a $ character will always remain the same.

This is called an “Absolute cell reference”.

You can also use a “Semi-absolute cell reference” eg: B$2

A semi-absolute cell reference will make the row remain the same but the column will be adjusted.

For example: When I have a Formula in cell C2 =A2*B$2 and copy it one row down and one column right, I get the following formula in cell D3 =B3*C$2

The first cell reference (A2) is automatically adjusted because it is relative.

In the second cell, the column is adapted (B) because this is also relative but the second part of the formula ($2) is not adjusted because it is specified as absolute.

This can also be reversed, for example $C2.

Then the column is absolute and the row is relative.

 

Another tip: To quickly convert a cell reference from relative to absolute and vice versa, double-click the cell containing the “Formula”, select the cell contents, and click the F4 key on your keyboard.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats