How do you divide and multiply in Excel one formula?

Howto Create a Formulain Excel:Add, Subtract, Multiply, and Divide(& more)Written by co-founder Kasper Langmann, Microsoft Office Specialist.Learning how to create a formula in

How do you divide and multiply in Excel one formula?

Howto Create a Formulain 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.

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Table of Content

Bonus: Free exercise file (download)

  • 1: What is a formula?
  • 2: How to create a formula?
  • 3: The difference between a formula and function
  • 4: How to add
  • 5: How to subtract
  • 5: How to multiply
  • 6: How to divide
  • 7: How to use roots
  • 8: How to use exponents
  • 9: The order of operations

Conclusion: Wrapping things up

Get your FREE exercise file

Before you start:

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

Ive included one for you (for free).

Click below and well send it right over!cloud-download

BONUS:Download theFormula 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 lets 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 lets see what happens when we click away from the cell containing the (partial) formula.

Kasper Langmann, Co-founder of Spreadsheeto

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

How to create a formula

Now lets 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 wont need to double click into the cell to actually see the formula.

Kasper Langmann, Co-founder of Spreadsheeto

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 lets 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 totype 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 lets 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.

Kasper Langmann, Co-founder of Spreadsheeto

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 thenthe 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

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 lets look at how to write a formula to subtract in Excel.

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

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 numbersin 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

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, lets look at multiplication.

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

So, lets start by clickingintoa cell any typing an equal sign to begin to create our formula. Then lets write out the multiplication of 3 times 2.

Kasper Langmann, Co-founder of Spreadsheeto

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 thissection,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.

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.

Kasper Langmann, Co-founder of Spreadsheeto

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. Heres what each letter stands for

Kasper Langmann, Co-founder of Spreadsheeto

  • Parentheses
  • Exponents
  • Multiplication andDivision
  • Addition andSubtraction

In short: PEMDAS.

So, lets break this down a bit with some examples.

Lets say you have a formula like =5+3*2.

Multiplication of the 3 by 2 takes place beforethe 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 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.

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

This returns adifferent result

Everything withinthe 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. Yetthere 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

Video liên quan