How to Create a Formula in Excel

Add, subtract, multiply, and divide (+more)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Learning how to create a formula in Excel is easy.

If you have never known how to write a formula in your Excel spreadsheets, you are in the right place.

This guide will walk you through each step of how to write a formula in Excel and how to understand them.

Kasper Langmann, Co-founder of Spreadsheeto

By the time you get through this guide, you will know what a formula is and how to create your own.

*We will perform all the examples in this guide using Microsoft Excel 2016 for Windows.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Click below and we’ll send it right over!

cloud-download

BONUS: Download the Formula Exercise File (with 12 pre-loaded exercises) to go along with this post.

What is a “Formula”?

So, what exactly is a formula?

It is simply a statement made up of ‘operands’ and ‘operators’.

Say you want to calculate the 15% discount off of $20.

In this statement, there are two ‘operands’: price ($20) and discount percent (15%).

The operator is multiply (*).

‘Discount’ is the result.

Discount = $20 (price) * 15% (discount percent)

One fundamental thing to note about formulas is that they must always begin with an equal sign (=). If you do not begin your formulas with an equal sign, Excel will treat it as a string data type.

Kasper Langmann, Co-founder of Spreadsheeto

Now let’s see what our discount formula looks like in Excel.

Example of formula

Once you press enter, the formula will calculate the formula and show the result.

Result of formula

Note the formula shown in the box above the spreadsheet. This is the ‘formula bar’. More on that in a bit.

Now, look at the result if you input your formula without the equal sign.

Formula without equal sign

Nothing seems to be any different aside from the missing equals sign in front of the actual formula.

But now let’s see what happens when we click away from the cell containing the (partial) formula.

Kasper Langmann, Co-founder of Spreadsheeto
Result of formula without equal sign

Without the equal sign, it is just a string of text or numbers according to Excel.

How to create a formula

Now let’s move on to creating your own formulas.

The first step to creating a formula is to type an equal sign into the cell where you will be placing it.

Equal sign in formula

Now you are ready to begin the input of your actual formula.

If we wanted to divide 6 by 3, we would input the following:

6 divided by 3

You can also use cell references instead of literal numbers.

Example of cell references

In fact, you can mix cell references and literal numbers in your formulas.

Cell reference mixed with literal number

Note, your formula is also shown in the ‘formula bar’ just above the ‘C’ column of the worksheet.

You can always view the full formula and even edit it from the ‘formula bar’.

If you click on a cell containing a formula, it will appear in the ‘formula bar’. This means you won’t need to double click into the cell to actually see the formula.

Kasper Langmann, Co-founder of Spreadsheeto
Double click to view formula

What is the difference between
a “Formula” and a “Function”?

In contrast to formulas, functions are pre-built formulas that Excel offers.

For example, the ‘SUM’ function in Excel simplifies the addition of two or more numbers.

Instead of creating your own formula to add two number values, use the ‘SUM’ function!

Result of SUM function

While functions are pre-built formulas themselves, they still need to begin with an equal sign.

You can combine more than one function in a formula.

How to add in Excel

Now let’s look at the specifics of how to add in Excel.

As you have already seen, this is a pretty simple task. But we will now look at some specific examples of how to add number values in Excel.

Kasper Langmann, Co-founder of Spreadsheeto

The most elementary way to do this is to type in the mathematical expression for adding two numbers in a cell.

For example, just type ‘=2 + 2’ in a cell of your choice and Excel will perform the requested calculation.

Simple addition in Excel

Note the formula in the ‘formula bar’ while the result we would expect is in cell B2.

You can also use cell references as the terms of your equation instead of literal number values.

Adding cell references

Once you type the equal sign, you can either type in the cell reference manually or click on the cell. Either method will do.

Now let’s look at mixing both literal number values and cell references in our formula.

Adding cell references and literal number

What if you need to add more than two numbers? Well, you could always keep adding more numbers to your formula.

Kasper Langmann, Co-founder of Spreadsheeto
Adding numbers to a formula

You can even continue to add cell references to the formula this way…

Adding multiple cell references together

But there is an easier way that we already touched on earlier… The ‘SUM’ function!

So, for adding many number values, you can do the following:

Adding numbers using SUM

You just type ‘=SUM’ and then the numbers between parentheses and separated by commas.

This is how to make a formula in Excel to add multiple numbers (see the ‘formula bar’ in the figure above).

Excel makes things much easier when using the ‘SUM’ function with many cell references. Instead of using individual cell references, ‘SUM’ allows you to select and entire range.

Kasper Langmann, Co-founder of Spreadsheeto
SUM and ranges

You can also type the first and last cell of the range separated by a colon (:) and do the same thing.

SUM and example of range

You can also add more than one range of cell references using the ‘SUM’ function.

Simply separate your ranges with a comma.

SUM of separate ranges

Remember, you can always type the ranges manually. Or you can highlight different ranges separated by commas.

SUM of manual entered ranges

How to subtract in Excel

Now let’s look at how to write a formula to subtract in Excel.

Since you already know how to add in Excel, learning to subtract is easy.

You can subtract the same way you can add by just changing the plus sign to a minus sign in a simple formula.

How to subtract in Excel

The same concept holds true when you create a formula for subtracting cell references.

Subtracting cell references

You can even use the ‘SUM’ function to create a formula for subtracting numbers in Excel.

This is simple.

You just need to make sure you change signs for the numbers or values in cell references to do this.

For example, if we want to use the ‘SUM’ function to subtract 2 from 4, we need to write the formula as follows.

Kasper Langmann, Co-founder of Spreadsheeto
Using SUM to subtract

The same holds true for cell references.

Using SUM to subtract cell references

You can change the sign of the value that you want to subtract from another value in the cell.

Then select the range like we did before using the ‘SUM’ function when adding.

Another example of using SUM to subtract

Once you change the sign for the appropriate value, you can just select the range for your ‘SUM’ formula.

Change sign to appropriate value

While not as simple, this is still an effective way to subtract in Excel.

How to multiply in Excel

Now that you have seen how to write a formula in Excel to add and subtract, let’s look at multiplication.

In Excel, you have to use the asterisk (*) to multiply numbers.

So, let’s start by clicking into a cell any typing an equal sign to begin to create our formula. Then let’s write out the multiplication of 3 times 2.

Kasper Langmann, Co-founder of Spreadsheeto
Example of multiplication

You can also use cell reference when multiplying.

Cell reference and multiplication

The concept is simple enough: The ‘PRODUCT’ function allows you to multiply more than two numbers at the same time.

PRODUCT function

As you begin to type ‘PRODUCT’, the function shows itself and the explanation of it in the tooltip box.

Now we select the range of cells from A2 through A8.

PRODUCT function and ranges

Now press ‘Enter’ and the formula will calculate the product of all the values in the range ‘A2:A8’.

Result of PRODUCT function

How to divide in Excel

In this section, we are going to look at how to make a formula in Excel to divide.

In the case of creating a formula to divide in Excel, we will use the forward slash (/).

Kasper Langmann, Co-founder of Spreadsheeto

This separates the dividend and the divisor.

The dividend will be to the left of the forward slash whereas the divisor will be on the right.

As with the other formulas, we start our division formula with and equals sign.

Then we type our dividend, the forward slash, and finally the divisor.

Example of division

When we hit enter, we get the result of our formula.

Result of division

One special note about dividing in Excel is that you cannot divide any number by zero.

If you attempt to do so in Excel, you will get the following error.

Division error

How to use roots in Excel

Excel has one built in function that allows you to find the square root of a number.

It is ‘SQRT’ and it only requires one argument: the number you want to find the square root of.

Function SQRT

This is as straightforward as it gets.

But remember that you cannot calculate the square root of a negative number – and neither can Excel. If you attempt to do so, you will get the #NUM! error.
Kasper Langmann, Co-founder of Spreadsheeto
Error from dividing with zero

Excel does have another built in function that can help with this issue. The ‘ABS’ function returns the absolute value of a number.

You can nest the ‘ABS’ formula inside your ‘SQRT’ formula (like shown below).

This will help you avoid the ‘#NUM!’ caused by a negative number.

Function ABS nested in SQRT

How to use exponents in Excel

You can also write formulas in Excel to raise a number to some specified power.

There are two ways to do this:

The first is by using the caret operator (^).

Example of exponent

Excel also has a built-in function that you can use called the ‘POWER’ function.

The first of the two arguments is the base and the second argument is the exponent.

POWER function

The ‘POWER’ function calculates the same result as the formula using the caret.

Example of POWER function

The order of operations

What is the order of operations?

It is the mathematical protocol that dictates the mathematical order in which operations occur.

The logic follows the acronym PEMDAS. Here’s what each letter stands for…

Kasper Langmann, Co-founder of Spreadsheeto
  • Parentheses
  • Exponents
  • Multiplication and Division
  • Addition and Subtraction

In short: PEMDAS.

So, let’s break this down a bit with some examples.

Let’s say you have a formula like ‘=5+3*2’.

Multiplication of the 3 by 2 takes place before the addition operation.

The final result is 11.

Example of order of operations

If you place the addition operation within parentheses, the order of operations changes.

Impact of order of operations

Note that the 5 gets added to the 3 and its result of 8 then gets multiplied by the 2 for a final result of 16.

This is quite a contrast from the result of the previous example.

Look at the following formula and consider the following example. This formula will calculate the exponent first, the product second, and the sum last.

Kasper Langmann, Co-founder of Spreadsheeto

The result of the exponent part is 25 while the ‘PRODUCT’ result is 12.

These two terms are then added together for the result of 37.

Exponent first, product second, sum last

If we enclose a part of the formula in parentheses, the order of operations changes.

This returns a different result…

How a parenthesis changes the order of operations

Everything within the parentheses is calculated first.

Simplified, that is 12 + 5.

The resultant 17 is then raised to the power of 2, giving us the final result of 289.

Because of the order of operations, we see how dramatic the differences in the results. Knowing this protocol is instrumental in being able to effectively troubleshoot formulas in Excel.

Kasper Langmann, Co-founder of Spreadsheeto

Conclusion

This wraps up our guide on how to create a formula in Excel.

As you have seen, the concepts can be pretty simple. Yet there are different approaches to formulas like using literal values and/or cell references.

Then you have some options like using some of the pre-built functions that Excel offers.

It is worth reiterating that any formula requires the equal sign for Excel to recognize it as such. Otherwise, you are just typing in numbers and strings.

Kasper Langmann, Co-founder of Spreadsheeto

As you start to combine operations in your formulas, you must be aware of the ‘Order of Operations’.

This will allow you to confirm the results of your formulas and help you create them more effectively.

These concepts take repetition and continued use to really sink in. So, get going and practice the different methods of creating formulas in Excel!

Kasper Langmann, Co-founder of Spreadsheeto

BONUS: Download the Formula Exercise File Workbook (with 12 pre-loaded exercises) to go along with this post.

2018-12-11T19:39:03+00:00