Understanding Complex Formulas in Excel

You may have worked with simple formulas before, like 7+9. But sometimes, formulas have more than one operator, like 5+2*8. When this happens, Excel follows specific rules to decide which operation to do first. This is called the order of operations.

*Download our practice document.

To get the correct answer in Excel, you need to understand these rules.

The Order of Operations in Excel

Excel follows this order when solving formulas:

  1. Parentheses – ( )
  2. Exponents – ^ (Example: 3^2 = 9)
  3. Multiplication and Division – * and / (whichever comes first, from left to right)
  4. Addition and Subtraction – + and - (whichever comes first, from left to right)

A helpful way to remember this is PEMDAS:
Please Excuse My Dear Aunt Sally.

While this formula may look complicated we can use the order of operations step by step to find the right answer.

First, we'll start by calculating anything inside parentheses. In this case, there's only one thing we need to calculate:6-3=3.

As you can see, the formula already looks simpler. Next, we'll look to see if there are any exponents. There is one:2^2=4.

Next, we'll solve any multiplication and division, working from left to right. Because the division operation comes before the multiplication, it's calculated first: 3/4=0.75

Now, we'll solve our remaining multiplication operation:0.75*4=3.

Next, we'll calculate any addition or subtraction, again working from left to right, Addition cames first:10+3=13.

Finally, we have one remaining subtraction operation:13-1=12.

Now we have our answer: 12. And this is the exact same result you would get if you entered the formula into Excel.

Example of Order of Operations

Let's solve 10+(6-3)/2^2*4-1 step by step:

  1. Parentheses: (6-3) → 3
    → Now the formula is 10+3/2^2*4-1
  2. Exponents: 2^2 → 4
    → Now the formula is 10+3/4*4-1
  3. Division (first from left to right): 3/4 → 0.75
    → Now the formula is 10+0.75*4-1
  4. Multiplication: 0.75*4 → 3
    → Now the formula is 10+3-1
  5. Addition: 10+3 → 13
  6. Subtraction: 13-1 → 12

Final Answer: 12

If you enter this into Excel, you will get the same result.

Creating Complex Formulas

Let's say we need to calculate sales tax for a catering bill. The formula is:

*=(D3+D4+D5)0.075

  • First, Excel adds the values inside the parentheses:
    (45.80+68.70+159.60) = 274.10
  • Then, it multiplies by 7.5% (0.075):
    274.10 * 0.075 = 20.56


So, the sales tax is $20.56.

If we forget the parentheses, Excel will multiply first, and the result will be wrong. Parentheses help ensure the formula works correctly.



Example Using Cell References

We can also use cell references in a formula. Look at this example:

=B3C3+B4C4

Excel follows the order of operations:


  • B3*C3 → 2.79 * 35 = 97.65
  • B4*C4 → 2.29 * 20 = 45.80
  • Then, it adds both results:


    97.65 + 45.80 = 143.45

The correct subtotal is $143.45.

To make formulas easier to read, we can add parentheses:
=(B3C3) + (B4C4)


This does not change the result, but it makes the formula clearer.

Final Tip

Excel does not always warn you about errors. Always double-check your formulas before pressing Enter.