**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.

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.*

**Table of Content**

**4: How to**__add__**Conclusion: Wrapping things up**

**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!*

**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.

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

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

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.**

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.

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.**

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:

You can also use cell references instead of literal numbers.

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

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.

**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!

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.

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.

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.**

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.**

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

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

**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:

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.

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

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

**Simply separate your ranges with a comma.**

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

**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.**

The same concept holds true when you create a formula for 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.

The same holds true for 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.

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

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.

You can also use cell reference when multiplying.

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

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.

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

**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 (/).

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.

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

**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.

**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.**

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.

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.

**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 (^).

**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.

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

**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…

**P**arentheses**E**xponents**M**ultiplication and**D**ivision**A**ddition and**S**ubtraction

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.

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

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 productsecond, and the sumlast.

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.

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

This returns a different result…

**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.

**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.

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!