In the previous edition of The Dossier, we debunked the Excel Myth: “… to be skilled at using Excel, you must be a math head”.
Now I will share with you, my proven recipe for creating the perfect formula – every time. The recipe works no matter whether the formula is simple or complex.
Secret recipe, DEFCORE:
- Destination Cell
- Equals Sign “=”
- First Cell
- Calculation Sign (, *, /, +, -, ^, )
- Other cell
- Repeat Steps 4-5 until complete
- Enter or Tick
1) Destination Cell
Move the cursor to the destination cell.
I know – this sounds obvious – but I have seen many times where students of Excel (and Pro users too) become so fixated with the formula they are about to create that they don’t even notice where the cursor is when they start typing.
That’s a sure-fire way to over-type something important – if you don’t think about it first.
That’s why I added this step!
2) Equals “=”
Every formula in Excel starts with an “=” sign – no exception! As soon as you type this symbol, Excel immediately knows that what follows is to be calculated.
By typing the “=” symbol, Excel changes from Cell Mode to Edit Mode. This can be identified by the Cross and the Tick symbols which appear on the Formula Bar.
3) First Cell
When creating a formula in Excel, you can either type the cell reference (e.g. “B5”) or you can use the mouse and click on the cell. Using the mouse is called the “Point and Shoot” method – because by clicking on the cell, the cell reference appears in the formula.
The Point and Shoot method is my preference because it removes the line of sight errors when you are working out the cell reference coordinates. It is much safer to click on the cell you want and Excel will insert the reference for you.
4) Calculation Sign
Step 4 is where we tell Excel what type of calculation to perform. The options are:
You can use these calculation symbols as many times as you like in a formula.
In this case, we want to use the “+” calculation. So type the “+” as shown below:
5) Other Cell
In this step, you use the Point and Shoot method to select the next cell in the formula.
6) Repeat Steps 4-5
Depending on the complexity and the length of the formula you may not need this step, and you can proceed straight to Step 7.0. If, however, the formula is longer and involves more calculations or cells, it is simply a matter of repeating Steps 4 and 5 until you have included all calculations and cell references.
Something to note – your formula should always end with a cell reference or a Bracket. Never finish with a Calculation Symbol [+, -, *, /, ^]. Otherwise, Excel will complain about it loudly.
7) Enter or Tick
The final step in creating the formula is to “lock it in” and return to Cell Mode.
Voilà – all done!
That is the DEFCORE secret recipe for creating the perfect formula – every time!
Watch out for our next article as we examine the internal rules that Excel uses to calculate the result of your formula – this may surprise you!