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 RefOperatorCell 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 3m2
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.
Column and Row
When drag across or down both column and row are constant
When drag across column is constant
When drag down Row is constant
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
Double click in cell C8
Position your mouse pointer on Cell B3
Press F4 until the dollar sign is only in front of the 3
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.
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.
Microsoft Office Small Group Training Sessions
AZ Solutions delivers customized training courses in Sydney – Australia. We come to you. All you need is a board room, PC’s for each student and a TV/ Projector with a HDMI connection cable. Virtual Training sessional also available.
In our training sessions you are welcomed to bring examples of your work to class. We prefer it.
Analir Pisani has been delivering Microsoft Office training courses since 1995. Her post come from questions participants commonly ask. She loves to inspire others to explore Microsoft Office Suite of Applications.