Microsoft Excel provides different types of Operators to evaluate Arithmetic Expressions. Arithmetic Operators in Excel deal with ONLY Numerical data. In this article, I am going to explain what are Arithmetic Operators and their usage in Microsoft Excel.
Arithmetic Operators in Excel
These are the Arithmetic Operators, to work on Numerical data. If you attempt to apply these operators to non-numeric data; Excel will through “#VALUE!” Error.
Remember that, when you want to Apply the Operators in Excel; the Operators must be in the Formula. The formulas in Excel always start with an equal sign (“=”).
Addition operator in Excel (+ Operator – Plus sign)
This Operator is used to perform an addition operation on the given operands. You can give direct numerical value(s) or reference to the cell(s). It also acts as a unary operator; to mention the sign of the value; “+2”.
For example, below is the usage of this operator: Observe that, we can use values or cell references with this operator. We can use cell references for all these operators.
| Example | Cell Reference | Result |
| =2+3 | =A1+A2 | 5 |
| =2+3+4+5 | =A1+A2+A3+A4 | 14 |
Subtraction in Excel (- Operator – Minus sign)
This operator is used to subtract the values; you can give numeric values or Cell references. This is also used as a Unary operator to indicate the sign of the number; negative numbers are shown with the “-” symbol; “-2”.
For example, below is the usage of this operator:
| Example | Cell Reference | Result |
| =5-4 | =A4-A3 | 1 |
| =10-5-2-1 | =A9-A4-A1-1 | 2 |
Multiplication operator in Excel (* Operator – Asterisk)
Multiplication of two or more operands can be done through the “*” operator. Here is an example:
| Example | Cell Reference | Result |
| =5*4 | =A4*A3 | 20 |
| =5*4*3*2*1 | =A4*A3*A2*A1*1 | 120 |
Division operator in Excel (/ Operator – Forward slash)
The division of two or more operands can be done through this operator. Below is the example: observe the second example; (10/2/2), does it do (2/2) first or (10/2) first? There is a difference if it considers (2/2) first; because it produces the result as (10/1) which equals 10. The below results show 2.5 as the result. That means Excel considers (10/2) first; which means (10/2) is equal to 5 and then (5/2) which equals 2.5. How does it happen? This is because of the order of the precedence; which tells, which order has to consider; either left to right OR right to left. Especially this is useful; when we apply the operator to multiple operands. Most of the time, the order of precedence is left to right.
| Example | Cell Reference | Result |
| =5/2 | =A4/A1 | 2.5 |
| =10/2/2 | =A9/A1/A1 | 2.5 |
Percent operator in Excel (% Operator – Percent sign)
This gives the percentage of the given operand. From below, 20% means, 20/100; produces 0.2 as the result.
| Example | Cell Reference | Result |
| =20% | =A19% | 0.2 |
Exponentiation operator in Excel (^ Operator – Caret sign)
This operator is used to find the exponentiation; raising one quantity to the power of another. From the below example, 2 ^ 4 means; it multiplies 2; 4 times; that means, 2 * 2 * 2 * 2; produces the result 16. How about the second one? Here also the order of the operation plays a major role, and it is from left to right; which is (2 ^ 4) executes first, results in 16, and then applies (16 ^ 16), resulting in 256.
| Example | Cell Reference | Result |
| =2^4 | =A1^A3 | 16 |
| =2^4^2 | =A1^A3^A1 | 256 |
Combining Operators in Excel
We can combine the operators to produce the required results. When we combine the operators; WE MUST KNOW how the Order of Precedence and Operator Precedence works.
Order of Operations AND Operator Precedence in Excel
Order of Operations tells whether the formula is evaluating from left to right OR right to left. In Excel, formulas are evaluated from left to right.
Operator Precedence tells, which operator has the highest priority when evaluating the formula. In Arithmetic operations, below is the precedence of Arithmetic Operators:
- Percent operator (%) will evaluate first.
- Next is the (^) exponentiation operator.
- Multiplication (*) and Division (/) has the same precedence.
- Similarly, Addition (+) and Subtraction (-) have the same precedence.
This doesn’t mean that; always the evaluation will happen in the above order. It happens, ONLY if there is any conflict when evaluating the expression. For example, in the expression “=2+3+4*5”; 2+3 will evaluate first as there is NO CONFLICT with the evaluation. Then from “=5+4*5”; 4*5 will evaluate first because there is a conflict with the evaluation. As per the Operator Precedence, the “*” operator has the highest priority over the “+” operator; hence it evaluates 4*5 first and then, will evaluate 5+20; finally Resulting in 25.
Lets’ take another Example: This is a little bit complex and confusing.
"=2+3*4/5%^6-7"
Here is the order of evaluation:
- First, it evaluates 3*4. Results, “=2+12/5%^6-7″
- Then, 5%; which Results, in “=2+12/0.05^6-7″
- Next, it evaluates 0.05^6; which results, “=2+12/0.000000015625-7″
- After that, it evaluates 12/0.000000015625; which Results, in “=2+768000000-7″
- It evaluates, 2+768000000; which Results, “=768000002-7″
- Finally, it evaluates the rest of the expression and produces the result 767999995.
It’s’ so confusing, right? To make it simple, it recommends grouping the expressions within the parentheses to manage the expressions easily and for readability.
Grouping of Expressions
Excel supports the grouping of expressions using parentheses. When you group the expression, the order of the evaluation will change. For example, “=2+3*4” results, 14. Because “*” has the highest precedence than “+”; (3*4) evaluates first and then added the result to 2. If you want to evaluate 2+3 first; you can group 2+3 as (2+3). The expression would be “=(2+3)*4”. The result now would be 20.
Will discuss more Excel features and their functions in my upcoming Articles.
🙂 Sahida
One thought on “Microsoft Excel – Arithmetic Operators”