Being able to create basic calculations in Excel is fundamental to being able to use Excel well.
Writing Excel formulas is much the same as writing an equation. With just a little bit of instruction you will be creating basic calculations in no time! And of course, you will be able to confidently read other people’s calculations which is awesome when you want to work out what the calculation is actually doing.
Understanding how Excel treats different data types
Before you start your first calculation understand that only some types of data can be used in a calculation. Excel is constantly checking if a number, a date or text has been entered into a cell. Basic calculations can only be performed using cells that contain numbers or dates.
Checking the alignment of a cell’s content is a good way of checking if a cell can be used in a calculation.
When you enter a text into a cell, it will automatically align to the left of the cell, as shown in column A below.
When a number or a date is entered into a cell the alignment will automatically be aligned to the right, as shown in column C. Data that is a mixture of alpha and numbers is treated as text, as shown in column B.
Writing Excel formulas is much the same as writing an equation.
A formula will display the calculated result on the worksheet, and the formula used to find the result in the Formula Bar. Excel calculates formulas and displays the result.
A formula can include numbers and cell references. For example:
=840*10% or =C4*D4
The example below shows how in an Excel worksheet the figures 840 and 10% will be entered in separate cells. The result of a formula is stored in a separate cell, Cell E4.
840 has been entered in Cell C4 and 10% has been entered in Cell D4. The formula can be written as 840*10% or C4*D4.
Both of these calculations would produce a result of 84. However, using real numbers rather than cell references can be dangerous.
If Cell C4 updates from 840 to 940, the calculation using real numbers will not update to 94 unless you re-enter the formula as 940*10%.
Choosing to write a formula using cell references rather than actual numbers is far more practical. Any number entered in cell C4 automatically is part of the calculation. Therefore, if the calculation is written using cell references instead of real numbers, updating Cell E4 to 940 will automatically update the result to 94.
Note: when entering cell references, the column number precedes the row number in the cell address. Column references can be entered in upper or lowercase.
Creating calculations using formulas
Formulas start with an equals sign (=) and can be made up of values, cell references, mathematical operators and Excel functions. Formulas are entered manually into a cell.
To start entering a formula:
1. Click the cell that will hold the calculation result.
2. Press the equals key (=) and type the formula. All formulas must start with an equals (=) sign. This indicates to Excel that you are creating a formula. Without the equals sign the formula is seen as normal data and is entered into the cell as such.
3. Press ENTER to perform the calculation.
This method is most useful when you are using just one standard mathematical operators such as plus, minus, multiplication and divide.
The table below shows the standard operators that are used in Excel calculations and where to find them on your keyboard.
Tip: most of the operators can also be found on the numeric pad on your keyboard.
|Brackets||( ) parentheses||SHIFT + 9
SHIFT + 0
|Exponent (to the power of)||^ (caret)||SHIFT + 6|
|Division||/ (forward slash)||/ (near your right SHIFT key)|
|Multiplication||* (asterisk a.k.a. ‘star’)||SHIFT + 8|
|Addition||+ (plus sign)||SHIFT + 8|
|Subtraction||– (minus sign)||– (next to the = key)|
Creating calculations using multiple operators
Take a look at the table above again. If you look at the first letter of each of the operator names you will notice it creates the acronym BEDMAS.
You need to be aware that Excel calculates all formulas according to the BEDMAS (also known as BODMAS) hierarchical structure – the order in which to calculate mathematical expressions. When Excel calculates a formula it checks from the top to the bottom of this list and performs the calculations according to where they are placed in the hierarchy.
So when you start to create formulas that have more than one operator, the BEDMAS rule kicks in.
For example: If you wanted to calculate 2 plus (+) 3 multiplied (*) by 5 you would presume you would end up with 25, however the following happens:
Your formula would look like this = 2+3*5 result 17
17 is not the answer you would have expected. Excel calculated the formula according to the built-in hierarchical structure where multiplication is performed before addition. Therefore Excel multiplied 3 by 5 first, and then added the 2.
To overcome this problem you would need to enter the formula as follows:
Your formula would look like this = (2+3)*5 result 25
You have now instructed Excel to calculate the addition of 2 plus 3 first by placing brackets around the calculation. Excel now calculates this part of the equation first as bracketed formulas will always be the first calculations performed, as per the hierarchical structure.
Super tip: always use brackets when creating formula with multiple operators.
Creating a formula – step-by-step
In the following example we will create a formula to calculate the contents of cell C4 multiplied by D4.
1. Click the cell that will hold the calculation result, in this case E4 and then press the = key.
2. Select cell C4. You can do this either by clicking the cell, using your arrow keys to move the the cell or by typing the cell reference directly into the calculation. You will notice “marching ants” around cell C4 indicating that you have selected it.
3. Press the multiplication key on the keyboard (*).
4. Click cell D4. The “marching ants” will highlight D4.
5. Press ENTER. The calculation result will be shown in the cell while the formula used to create the result will be displayed in the Formula Bar.
If the numbers are updated in C4 or D4, the formula in E4 will automatically display the updated result.
Now that you know how to write a formula in Excel check out my post on How to calculate GST at 15% using Excel formulas. You will now be able to read the calculations and recreate the formulas for yourself.
If you found this post helpful please ‘Like’ us!
If you enjoyed this you may also enjoy
- How to total a range of cells in Excel
- Excel keyboard shortcuts for quickly moving and selecting in a worksheet
- Excel Control key shortcut tips to save you time
- How to calculate GST at 15% using Excel formulas