How to Use Arithmetic Operators in Excel (Detailed Guide)

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will learn everything you need to know about how to use arithmetic operators in Excel. The main application of Excel requires mathematical calculations. So having the knowledge of the arithmetic operators is essential to work with Excel.

The article will cover the application of these operators with necessary examples. The application of arithmetic operators is common to us. But while working in Excel, we need to apply some modifications in formulas.

Please stay tuned to the article to understand the use of arithmetic operators in Excel.


Download Practice Workbook


Types of Operators in Excel

There are four kinds of operators for calculation. They are:

  1. Arithmetic
  2. Comparison
  3. Text concatenation
  4. Reference
  • Arithmetic Operators:

We use the arithmetic operators to execute fundamental mathematical operations including addition, subtraction, multiplication, and division as well as to combine numbers and obtain numerical outputs. We covered them in the later sections of this article.

  • Comparison Operators:

You can compare two values using the comparison operators. These operators compare two values, and the outcome is a logical value that can be either TRUE or FALSE. Comparison operators are: Equal symbol (=)Greater than symbol (>), Less than symbol (<), Greater than or equal to symbol (>=), Less than or equal to symbol (<=) and Not equal to symbol (<>).

  • Text Concatenation Operators:

To combine or concatenate two or more text strings into a single piece of text, use the ampersand (&) operator.

  • Reference Operators:

We can combine ranges of cells for calculations with these operators.

The reference operators are: Colon (:), Comma (,), Space, Pound (#) and At (@).


Arithmetic Operators in Excel

There are 6 basic arithmetic operators in Excel. They are:

  1. + (Addition Operator)
  2. – (Minus Operator)
  3. * (Multiplication Operator)
  4. / (Division Operator)
  5. ^ (Exponent Operator)
  6. % (Percentage Operator)

In the following sections of this article, we will show you some examples with these arithmetic operators in Excel. The following image shows the arithmetic operators with short descriptions and applications.

Overview of Using Arithmetic Operators in Excel


How to Use Arithmetic Operators in Excel

1. Plus (+) Operator

The plus operator adds two or more numbers. However, whenever you enter a formula in Excel, you can’t just type 2+2 in a cell to get the summation result between 2 and 2. You have to put an Equal (=) symbol at the beginning of the formula.

The following image shows how you can add two numbers using the plus operator in Excel.

Summing Numbers by Plus Arithmetic Operator in Excel

In the image above, you can see that we can add two numbers either by hard coding or referencing cells that contain numbers. Here, the numbers are in the C4 and D4 cells. So adding the cell references C4 and D4 will return the sum value.

Moreover, you can use the plus operator multiple times if you want to add multiple numbers.


2. Minus (-) Operator

The use of the minus operator is similar to the plus operator. Follow the image below.

Subtracting Numbers by Minus Arithmetic Operator in Excel

You can also use an algebraic expression to subtract a number from another in Excel. Here is the image below.

Subtracting Numbers by Algebraic Expression


3. Multiplication (*) Operator (Asterisk)

The following picture shows the way of applying the asterisk (*) symbol to multiply two numbers.

Multiplying Numbers by Multiplication Arithmetic Operator in Excel

Like the addition and subtraction procedures, you can either use the numbers directly or reference the cells for multiplication.


4. Division (/) Operator

In the next picture, you will see how division works in an Excel workbook.

Dividing Numbers by Division Operator


5. Exponent Or Circumflex (^) Operator

The exponent operator shows the power of a number. Say, we want to find out the cube value of 8 or 8 to the power 3. Follow the image below to see how we can calculate this value.Finding Power by Exponent Operator


6. Percentage (%) Operator

The percentage operator is used to show a number as a fraction of 100. However, if you use the percentage operator with a number, you will see a decimal number. You need to change the number format to Percentage to show the number in percentage.Use of Percentage Operator

Note: All the operators mentioned with their applications can also be done by Excel’s mathematical functions. If your dataset consists of a huge set of numbers, working with mathematical functions will be a lot easier. If you want to know more about mathematical functions, please go through the article 44 Mathematical Functions in Excel.

VBA Arithmetic Operators in Excel

1. Operators for Mathematical Calculations

In VBA, there are arithmetic operators too that we can use in codes. The common operators are +,, *, and /. We have seen their applications in the Excel sheet. Now, we shall see their applications in VBA. There’s another arithmetic operator that will be discussed. Its name is Mod that calculates the remainder of a division operation. Please go through the next sections so you can understand them better.

  • Here, we will show you some mathematical calculations of two numbers. We created some buttons to calculate addition, subtraction, multiplication etc. See the image below.

Initiating Arithmetic Calculation of Two Numbers by VBA

  • To create the buttons, select Developer >> Insert >> Button from the Form Controls Give suitable names to the buttons.

Creation of Buttons to Assign Macros

  • Next, press Alt + F11 to open the VBA editor window.
  • Later, select Insert >> Module to open a Module.

Opening a VBA Module

  • After that, copy the code below in the VBA Module.
Sub AddNumbers()
Dim K As Double
K = Sheets("vba calculation").Range("C4").Value + _
Sheets("vba calculation").Range("D4").Value
Sheets("vba calculation").Range("C6").Value = K
End Sub
Sub SubtractNumbers()
Dim K As Double
K = Sheets("vba calculation").Range("C4").Value - _
Sheets("vba calculation").Range("D4").Value
Sheets("vba calculation").Range("C7").Value = K
End Sub
Sub MultiplyNumbers()
Dim K As Double
K = Sheets("vba calculation").Range("C4").Value * _
Sheets("vba calculation").Range("D4").Value
Sheets("vba calculation").Range("C8").Value = K
End Sub
Sub DivideNumbers()
Dim K As Double
K = Sheets("vba calculation").Range("C4").Value / _
Sheets("vba calculation").Range("D4").Value
Sheets("vba calculation").Range("C9").Value = K
End Sub
Sub CalculateRemainder()
Dim K As Double
K = Sheets("vba calculation").Range("C4").Value Mod _
Sheets("vba calculation").Range("D4").Value
Sheets("vba calculation").Range("C10").Value = K
End Sub
Sub ClearCells()
Sheets("vba calculation").Range("C6:C10").Value = ""
End Sub

Macros for Arithmetic Calculations

In this Module, there are several Macros to calculate Addition, Subtraction, Multiplication, Division and Remainder respectively. There is an additional Macro to clear the cells after calculation too.

The understanding of this code isn’t that complex although it looks a bit large. You can see that all the Macros are similar except their arithmetic operators. The AddNumbers Macro has the Plus (+) operator to add numbers in the cells of C4 and D4 of the vba calculation sheet. Similarly the following Macros contain Minus (-), Multiplication (*), Division (/) and Remainder (Mod) operators. The ClearCells Macro removes the data range C6:C10 while executed.

  • Now, we will be assigning the Macros to the corresponding buttons. For this reason, right-click on the button and select Assign Macro from the Context Menu. Here, we are assigning a Macro to the Addition button.

Assigning Macro to Addition Button

  • Next, select the desired Macro (AddNumbers) in the Assign Macro window and click OK.

Macro Assigned to Addition Button

Similarly, assign the other Macros to the corresponding buttons. After that, click on the buttons to find out their arithmetic operation results.

Follow the video below and you can see the results just by clicking the buttons.

VBA arithmetic operators are useful for automatic calculations. We can store or clear the results automatically, while we had to do this manually using Excel formulas.


2. Bit-Shift Operators

Bit-Shift operators are basically the left-shift and right-shift operators. In VBA, they are noted as Bitlshift and Bitrshift, respectively. As noted, the left-shift operator repeatedly shifts bits to the left.

For instance, the binary form of 32 is 100000. If we shift its bits 3 times to the left, the binary format becomes 100000000 and the decimal value of it is 28 or 256. Like the left-shift operator, the right-shift operator repeatedly shifts bits to the right. In that case, 100000 will be reduced to 100 and it’s value will be 22 or 4.

Below here, you will find two Macros to show Right Bit-shift and Left Bit-shift respectively.

Sub RightShift()
Dim m As Double
m = 32
p = Application.WorksheetFunction.Bitrshift(m, 3)
MsgBox p
End Sub
Sub LeftShift()
Dim m As Double
m = 32
q = Application.WorksheetFunction.Bitlshift(m, 3)
MsgBox q
End Sub

Macro to Operate BitShift Operators

The following MsgBox shows the right shifted value if the RightShift Macro is executed.

Returning Value of Right Shift Operator

And this one shows the left shifted value if the LeftShift Macro is executed.

Returning Value of Left Shift Operator


Combining Arithmetic Operators in Excel

We can combine arithmetic operators in Excel to get a simplified value from several data. For example, we know that 3+2*4 equals 11. If we operate this calculation manually, we need to calculate 2*4 first and then add it to 3. This would be a time costly procedure. Instead, we can use the operators combined to get the result at once.

In the following image, some combined arithmetic expressions are illustrated along with their corresponding values.

Combining Arithmetic Operators


Things to Remember

  • Always use the equal symbol at the beginning of a formula. Otherwise you will get a text value only.
  • Keep the numeric data in proper format. It may lead to an error if the formattings are not maintained.

Frequently Asked Questions

1. What is the order of operations in Excel, and how does it affect my calculations?

Answer: Excel uses the PEMDAS/BODMAS standard order of operations: parentheses, exponents, multiplication and division (from left to right), and addition and subtraction (from left to right). To ensure the correct order of calculations, use parentheses as necessary.

2. What are circular references, and why should I avoid them?

Answer: Circular references arise when a cell’s formula directly or indirectly refers to itself. They can result in inaccurate calculations or infinite loops. To ensure correct calculations, avoid using circular references.

3. Can I use built-in functions instead of arithmetic operators for standard calculations?

Answer: Yes, Excel has many built-in functions for performing common calculations, such as SUM, PRODUCT, AVERAGE, and so on. These functions offer more precise and convenient methods of doing numerous activities.


Conclusion

My goal was to provide necessary ideas on how to use arithmetic operators in Excel in this article. The use of these operators not only includes numeric calculations but also in other text or logic formulas, which is another topic of extensive discussion. As our concern was arithmetic calculation, we limited our topic to mathematics. If you have any questions or ideas regarding this article, please share them in the comment section. This will help me develop newer ideas for Excel applications.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo