Create Basic Formulas In Excel

Table of Contents

Basic Formulas
Order Of Operations
Cell Referencing
Reveal Formulas
Conclusions

Basic Formulas

All Formulas and Functions begin with =. It is the = sign that tells Excel it is going to be doing something mathematical. You will need to ensure you click in the cell you would like the answer to display in. The difference between Formulas and Functions is Formulas use symbols like on the calculator + – the multiply is the * and the divide is the /.

All Calculations Begin with =

= Cell Ref Operator Cell Ref

Order Of Operations

Formulas are not worked on left to right as you might think. You must follow a specific order, a rule. BODMAS is the acronym for what must be done first, second and third.

B ( )

Exponential eg 3m²

D /

M *

A +

S –

Eg; 5+ 2 * 10 = 25

If you need 70 to be your answer

Add brackets to the formula

(5+2)*10 = 70

Absolute Cell Referencing

When you need to perform a formula that requires you to reference the same cell whilst moving down or across. You will need to lock the cell reference by using $ Dollar signs.

To create Absolute Cell Referenced cells you will need to use the $ or press F4

Mac users press Command T

Depending on where the dollar sign is, depends on which part of a cell reference will be locked (remain constant). See the table below for examples.

When deciding on an Absolute you need to think will you be filling down, across or both, down the row reference changes, across the column reference changes.

In the example below I have revealed the formulas for the entire sheet so you can see the pattern of what happens naturally. You’re looking at Cell C8.

The Formula is =B8*B3

Let’s focus on what’s happening to B3 which is the cell that refers to the 12% in cell B3. Notice 3 is changing to 4 then 5, then 6 then 7. If you find yourself changing the 4 to a 3 manually then you have answered your question which part of the cell needs the dollar sign in front of it. It’s the 3

1. Double click in cell C8
2. Position your mouse pointer on Cell B3
3. Press F4 until the dollar sign is only in front of the 3

Reveal Formulas

Sometimes when we are working in Excel, we want to study the pattern of the formulas. Why does the first function work but not the following. What has changed in the pattern of the function? This is particularly helpful when working with Absolute Cell Referencing. Visit my blog on “How To Keep The Same Cell Reference“. For help on Absolute Cell referencing.

To reveal all formulas created in Excel.
Press Ctrl ~ or go to the Formulas Tab and Select Show Formulas. To display the answers rather than the formulas or functions behind it press the Ctrl ~ key again.

Conclusions

There is a difference between Formulas and Functions and it’s more then just the spelling. Formulas use operators + – * / where as Functions follow a structure =Functions Name ( these three thing in this order is important otherwise you don’t have the start of a Function at all.