Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2013 Excel for iPad Excel Web App Excel for iPhone Excel for Android tablets Excel for Android phones Excel for Windows Phone 10 Excel Mobile More...Less
Operators specify the type of calculation that you want to perform on elements in a formula—such asaddition, subtraction, multiplication, or division. In this article, you'll learn the default order in which operators act upon the elements in a calculation. You'll also learn that how to change this order by using parentheses.
Types of operators
There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference.
To perform basic mathematical operations such as addition, subtraction, or multiplication—or to combine numbers—and produce numeric results, use the arithmetic operators in this table.
Arithmetic operator | Meaning | Example |
---|---|---|
+ (plus sign) | Addition | =3+3 |
– (minus sign) | Subtraction | =3–1 |
* (asterisk) | Multiplication | =3*3 |
/ (forward slash) | Division | =3/3 |
% (percent sign) | Percent | =20% |
^ (caret) | Exponentiation | =2^3 |
With the operators in the table below, you can compare two values. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE.
Comparison operator | Meaning | Example |
---|---|---|
= (equal sign) | Equal to | =A1=B1 |
> (greater than sign) | Greater than | =A1>B1 |
< (less than sign) | Less than | =A1<B1 |
>= (greater than or equal to sign) | Greater than or equal to | =A1>=B1 |
<= (less than or equal to sign) | Less than or equal to | =A1<=B1 |
<> (not equal to sign) | Not equal to | =A1<>B1 |
Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.
Text operator | Meaning | Example |
---|---|---|
& (ampersand) | Connects, or concatenates, two values to produce one continuous text value. | ="North"&"wind" |
Combine ranges of cells for calculations with theseoperators.
Reference operator | Meaning | Example |
---|---|---|
: (colon) | Range operator, which produces one reference to all the cells between two references, including the two references. | =SUM(B5:B15) |
, (comma) | Union operator, which combines multiple references into one reference. | =SUM(B5:B15,D5:D15) |
(space) | Intersection operator, which produces a reference to cells common to the two references. | =SUM(B7:D7 C6:C8) |
# (pound) | The # symbol is used in several contexts:
|
|
@ (at) | Reference operator, which is used to indicateimplicit intersection in a formula. | =@A1:A10 =SUM(Table1[@[January]:[December]]) |
The order in which Excel performs operations in formulas
In some cases, the order in which calculation is performed can affect the return value of the formula, so it's important to understand the order— and how you can change the order to obtain the results you expect to see.
Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=). The equal sign tells Excel that the characters that follow constitute a formula. After this equal sign, there can be a series of elements to be calculated (the operands), which are separated by calculation operators. Excel calculates the formula from left to right, according to a specific order for each operator in the formula.
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication and division operator — Excel evaluates the operators from left to right.
Operator | Description |
---|---|
: (colon) (single space) , (comma) | Reference operators |
– | Negation (as in –1) |
% | Percent |
^ | Exponentiation |
* and / | Multiplication and division |
+ and – | Addition and subtraction |
& | Connects two strings of text (concatenation) |
= | Comparison |
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula results in the value of 11, because Excel calculates multiplication before addition. The formula first multiplies 2 by 3, and then adds 5 to the result.
=5+2*3
By contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3
In the example below, the parentheses that enclose the first part of the formula will force Excel to calculate B4+25 first, and then divide the result by the sum of the values in cells D5, E5, and F5.
=(B4+25)/SUM(D5:F5)
Watch this video on Operator order in Excel to learn more.
How Excel converts values in formulas
When you enter a formula, Excel expects specific types of values for each operator. If you enter a different kind of value than is expected, Excel may convert the value.
The formula | Produces | Explanation |
= "1"+"2" | 3 | When you use a plus sign (+), Excel expects numbers in the formula. Even though the quotation marks mean that "1" and "2" are text values, Excel automatically converts the text values to numbers. |
= 1+"$4.00" | 5 | When a formula expects a number, Excel converts text if it is in a format that would usually be accepted for a number. |
= "6/1/2001"-"5/1/2001" | 31 | Excel interprets the text as a date in the mm/dd/yyyy format, converts the dates to serial numbers, and then calculates the difference between them. |
=SQRT ("8+1") | #VALUE! | Excel cannot convert the text to a number because the text "8+1" cannot be converted to a number. You can use "9" or "8"+"1" instead of "8+1" to convert the text to a number and return the result of 3. |
= "A"&TRUE | ATRUE | When text is expected, Excel converts numbers and logical values such as TRUE and FALSE to text. |
Need more help?
You can always ask an expert in the Excel Tech Communityor get support inCommunities.
See Also
-
Basic Math in Excel
-
Use Excel as your calculator
-
Overview of formulas in Excel
-
How to avoid broken formulas
-
Find and correct errors in formulas
-
Excel keyboard shortcuts and function keys
-
Excel functions (alphabetical)
-
Excel functions (by category)
Need more help?
Want more options?
Discover Community
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Microsoft 365 subscription benefits
Microsoft 365 training
Microsoft security
Accessibility center
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.