Excel Operators List pdf- Free Download

Including several operators in Excel that allow you to perform mathematical operations, comparisons, and concatenations in your spreadsheet. In this article we will describe the excel operators list and their purposes.

The classification of the most commonly used operators in Excel is given below:

1. Arithmetic Operators in Excel
2. Comparison, Logical, or Conditional  Operators in Excel
3. Text Concentration Operators in Excel
4. Reference Operators in Excel
5. Miscellaneous Operators

Arithmetic Operators in Excel

• Addition (+): Adds two or more values together.
• Subtraction (-): Subtracts one value from another.
• Multiplication (*): Multiplies two or more values together.
• Division (/): Divides one value by another.
• Exponentiation (^): Raises a value to a power.
• Modulus (%): Returns the remainder of a division operation.

Examples: 

=5+3        // Returns 8
=10-5       // Returns 5
=2*5        // Returns 10
=10/2       // Returns 5
=2^3        // Returns 8
=11%*2      // Returns 0.22

Comparison, Logical, or Conditional  Operators in Excel

• Equal to (=): Checks to find if two values are equal by comparing them.
• Not equal to (<>): Compares two values to see if they are not equal.
• Greater than (>): Compares two values to see if the first value is greater than the second.
• Less than (<): Determines whether the first number is less than the second by comparing the two values.
• Greater than or equal to (>=): Compares two values to see if the first value is greater than or equal to the second.
• Less than or equal to (<=): Evaluate whether one value is less than or equal to another by comparing them.

Examples:

=5=5      // Returns TRUE
=5<>3     // Returns TRUE
=5>3      // Returns TRUE
=5<3      // Returns FALSE
=5>=5     // Returns TRUE
=5<=5     // Returns TRUE

Text Concentration Operators in Excel

• Ampersand (&): Joins or concentrates two or more strings together.
• Concatenate Function: Joins two or more strings together.

Examples:

="Hello" & " World!"             // Returns "Hello World!"
=CONCATENATE("Hello", " World!") // Returns "Hello World!"

Reference Operators in Excel

• Colon (:): Creates a range of cells.
• Comma (,): Separates multiple cell references.
• Intersection Operator (Space): The intersection operator selects the common cells between two ranges.

Examples:

=A1:A5         // Selects cells A1 through A5
=SUM(A1,A3,A5) // Adds the values in cells A1, A3, and A5
=B1:B5 B4:E4  //return the cell or cells where the ranges B1:B5 and B4:E4 intersect.

Miscellaneous Symbols:

• Dollar Sign ($): The dollar sign ($) is used in Excel to create absolute references in a formula. An absolute reference refers to a specific cell or range that will not change if the formula is copied to another cell.

Examples: 

=$A$1  //Refers to cell A1 and will not change if the formula is copied to another cell

• At Sign (@): The at sign (@) is used in Excel to indicate that a formula should be evaluated as an array formula. With array formulae, you can perform computations across a number of cells and get various outcomes.

Examples:

=SUM(@C1:@C3)   //returns 4

This formula calculates the sum of the values in cells C1 through C3, in the same row as the output cell.

• Pound Sign (#): The pound sign (#) is used in Excel to indicate error values or insufficient space to display the value entered in any particular cell. For example, if you enter a formula that divides a number by zero, Excel will display the pound sign (#) to indicate that the result is an error.

Examples: 

=5/0   //Returns #DIV/0! error

• Double hyphen (–): The double hyphen (–) is then applied to this array using the “–” operator. This converts the logical values to numerical values, where TRUE is represented as 1 and FALSE is represented as 0. 

Examples:

=SUMPRODUCT(--(ISOWEEKNUM(A1:A100)=27))

The double hyphen (–) is used to convert the logical values returned by the “ISOWEEKNUM” function into numerical values that can be used in the “SUMPRODUCT” function

Which Order of Precedence is Used in MS Excel?

Microsoft Excel evaluates formulas and functions in a specific order of precedence. This means that certain operators and functions are evaluated first, and Excel uses a specific hierarchy to determine which operations to perform first.

Excel follows the following precedence order:

• Parentheses-enclosed operators are evaluated first.
• Following that, exponents (^) are evaluated.
• Before addition (+) and subtraction (-), multiplication (*) and division (/) are evaluated. 
• Excel evaluates multiple operators with the same level of precedence in a formula from left to right.
• In addition to the operator hierarchy described above, Excel has a function hierarchy that governs the order in which nested functions are evaluated. The innermost function is evaluated first, followed by the outermost function.

Example:

=SUM(3+4*2^2)/2. //returns 5.5

Excel would follow the order of precedence as follows:

1. 2^2 = 4 (exponentiation)
2. 4*2 = 8 (multiplication)
3. 3+8 = 11 (addition)
4. 11/2 = 5.5 (division)

For more illustrations, you can see the image below:

It is critical to understand the order of precedence in Excel in order to ensure that your formulas are correctly calculated. If you’re unsure how Excel will evaluate a formula, you can use parentheses to specify the order of operations explicitly.