Being able to create basic formulas in Excel is fundamental to being able to use Excel well.
Writing basic Excel formulas is much the same as writing an equation.
With just a little bit of instruction you will be creating basic Excel formulas in no time!
And of course, you will be able to confidently read other people's Excel formulas which is awesome when you want to work out what the formula is actually doing.
How to create basic Excel formulas
Before you start your first formula understand that only some types of data can be used in a formula.
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. These type of cells are commonly referred to as 'values'.
Checking the alignment of a cell’s content is a good way of checking if a cell can be used in a formula.
When you enter text into a cell, Excel will automatically align the text to the left of the cell, as shown in column A in the example below.
When a number or a date (a value) 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.
Creating basic Excel formulas
Writing Excel formulas is much the same as writing an equation.
A formula can include numbers and cell references. For example:
=840*10% or =C4*D4
The formula 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 Excel 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 basic Excel 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.
Excel will now display the calculated result on the worksheet, and the formula used to find the result is displayed in the Formula Bar.
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.
Operator Name | Operator | Press |
---|---|---|
Brackets | ( ) parentheses | SHIFT + 9 |
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 basic Excel formulas 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 7 plus (+) 3 multiplied (*) by 10 you would presume you would end up with 100, however the following happens:
Your formula would look like this = 7+3*10 result 37
37 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 10 first, and then added the 7.
To overcome this problem you would need to enter the formula as follows:
Your formula would look like this = (7+3)*10 result 100
You have now instructed Excel to calculate the addition of 7 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 basic Excel formula - step-by-step
In the following example we will create a basic Excel formula to calculate the contents of cell C4 multiplied by D4.
1. Click the cell that will hold the formula 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.
Was this post helpful? Let us know in the Comments below.
If you enjoyed this post check out the related posts below.