How to Create a Formula in Excel

Posted March 12, 2022

Being able to create a formula in Excel is fundamental to being able to use Excel well.

Writing an Excel formula is much the same as writing an equation.

With just a little bit of instruction you will be creating Excel formulas in no time! You definitely need this skill in your toolbox if you need to calculate GST or multiply and subtract using Excel formulas.

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.

In this blog I cover:

Data types that can be used to create a formula in Excel

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.

Excel formulas 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 to check 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. These data types cannot be used in a formula.

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. This data can be used in a formula.

Data that is a mixture of alpha and numbers is treated as text, as shown in column B. Alphnumeric data cannot be used in a formula.

Excel worksheet with column A and B text aligning to the left and column C and D to the right

How to create a formula in Excel using cell references

Writing Excel formulas is much the same as writing an equation. However, in Excel we begin a formula with the equals sign.

A formula can include numbers and cell references.

For example: =840*10% or =C4*D4

The formula example below shows the figures 840 and 10% will be entered in separate cells.

The result of a formula is stored in a separate cell, Cell E4.

Excel multiply formula

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.

How to write a formula in Excel

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. In our example above this would be cell E4.

2. Press the equals key (=). In our example above this would be =C4*D4. Note, 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.

Formula is shown 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



Brackets or parentheses

(  ) parentheses


Exponent (to the power of)

^ (caret)



/ (forward slash)

/ (near your right SHIFT key)


* (asterisk a.k.a. 'star')



+ (plus sign)



- (minus sign)

- (next to the = key)

How to create basic Excel formulas Mobile Table

How to create a formula in Excel for multiple cells

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, PODMAS, and PEDMAS) 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.

How to create a formula in Excel example with download file

Check out my video below on how to subtract the right way. Click here to download the exercise file to follow along with me and try out creating formulas to subtract, add and multiply.

For a deeper dive into creating formulas in Excel you can check out the full blog here How to subtract in Excel (minus formula).

How to create a formula in Excel 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.

Amount ex GST with figure 840 in cell below, Discount with percentage 10% in cell below and Total discount with = in the cell below

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.

Cell below Amount ex GST is selected and cell below Total Discount has =C4 in it

3. Press the multiplication key on the keyboard (*).

4. Click cell D4. The “marching ants” will highlight D4.

Cell below Amount ex GST is boarded with blue rectangle, cell below Discount is highlighted and cell below Total Discount shows =C4*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.

Cell below Amount ex GST showing 840, cell below Discount showing 10% and cell below Total Discount highlighted with 84 in it and the formula =C4*D4 in the formula bar

If the numbers are updated in C4 or D4, the formula in E4 will automatically display the updated result.

Was this post helpful? Let us know in the Comments below.

If you enjoyed this post check out the related posts below.

Elevate your Excel game and become a pro with our exclusive Insider Group

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus, your list of Essential Excel Skills to become proficient in Excel!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}