How to Calculate in Excel (5 Useful Techniques)

The article elaborates the addition, subtraction, multiplication, division, exponentiation, and so on for how to calculate in Excel. The application of the AutoSum command is concisely illustrated. Further, we explain the sequence of formula calculation and changing its mode.

We must be familiar with the mathematical operation while performing real-life arithmetic or algebraic pertinent problems. Nowadays many of us do paperless jobs. Therefore dealing with spreadsheet applications is inevitable. Nevertheless, the Excel Spreadsheet operations as well as be calculations may inscrutable to a neophyte.

Download Practice Workbook

To practice, please download the Excel Workbook file from the link below.

How to Calculate in Excel: 5 Useful Techniques

Here we are using a dataset containing Container, No of particles, Mass, and Speed. To illustrate the approaches, we will further calculate momentum, root mean square of particle, the compressibility of containers, and so on.

1. Perform Basic Calculations in Excel

In this section, we delineate some basic operations that frequently take place in Excel. Needless to say, these operations are our daily routine while performing calculations via Excel spreadsheet.

1.1 Add Two or Multiple Values

By adding the range C5:C15 with plus sign (+), one can find the sum of the total particles in the C16 cell.

1.2 Subtract One from Another

Users can also navigate the difference between containers 1 and 2 by subtracting the C6 cell from the C5 cell and obtaining 6 in the E17 cell.

1.3 Multiply Two or Multiple Data

We all know that momentum is the product of mass and speed. Therefore, by multiplying D5 and E5, we get 190 mg.m/s in the F5 cell.

1.4 Divide a Certain Value

Speed is the ratio of Momentum and mass. Therefore, by dividing the D5 from the E5, we achieve 10 m/s in the F5 cell.

1.5 Utilize Percentage

Suppose a portion of the mass decayed. Let’s say 90% remaining. Therefore, applying the following formula containing percentage results in 17.1 in the F5 cell.

1.6 Perform Exponentiation

To show exponential growth, one must insert the exponentiation (^) symbol in Excel. For instance, the square of a certain value denotes the double product of that value. Therefore, using the formula as follows we get 100 in the F5 cell.

1.7 Apply Square Root

Often, we require to apply a square root in the formula derivation, performing calculations. The SQRT function is the dedication function to apply a square root on a number. Implementing the formula below, we obtain 11.26 in the F11 cell.

1.8 Perform Nth root

When the value of n is 2, the application is termed square root. Nevertheless, it can be any natural number i.e. n = 1,2,3,4,5,6….. Assuming n=3, we obtain 2.154 in the F5 cell which is the cubic root of speed.

1.9 Find Reciprocal of Number

An excellent and relevant example of the reciprocal of a number is Compressibility which is inversely proportional to the Volume expansion of a body. The number is called the reciprocal of a number If that value is inversed directly. Therefore we obtain a compressibility of 4830.92 °C in the F5 cell.

2. Calculate Using Excel AutoSum Command

One can use the AutoSum command to calculate sum, average, count numbers, maximum, minimum, and so on. To find the sum of range C5:C15, select the C5:C15 range >> then go to the Formulas tab >> select the Sum option from the AutoSum command. Thus we obtain a total of 182 particles in the C16 cell.

3. Apply Fill Handle Tool to Ignore Re-writing Formula

Writing formulas repeatedly is quite tedious. Therefore the use of the Fill Handle tool can be your savior to get rid of monotonous tasks.

• Suppose, the following formula is applied in the F5 Therefore, by dragging the Excel Fill Handle tool, you can call relative references and conduct calculations accordingly.

• Therefore, we obtain momentum for each container in the F5:F15 range.

4. Calculate Complex Formula Containing Excel Functions

Sometimes we require adding Excel functions to the formula. In this case, we must know the how formula works if its function exists. Here, we are using the IF function that results based on a logical expression. The following formula illustrates, if the momentum (product of mass and speed) is over 100, the category is A; Otherwise, it is B in the Category column.

5. Use VBA Macro to Calculate in Excel

By applying a simple VBA code in the module or worksheet event we can calculate in Excel.

Initially, write the following VBA code in the Module >> Then, hit the Run button or the F5 key to measure the momentum and category for each container. Meanwhile, we obtain the result in the F5:F15 range.

For your convenience, learn to write a VBA code in the Module.

Sub Calculate_in_Excel()
Dim i As Integer
Dim rng As Range
Set rng = Range("B5:F15")     ‘assigning the range
For i = 1 To 11            ‘Loop for repetitive calculation
    If (rng.Cells(i, 3) * rng.Cells(i, 4)) > 100 Then       
        rng.Cells(i, 5) = "A"         ‘logical expression if the momentum is over 100 the category is A
    Else: rng.Cells(i, 5) = "B"    ‘If fails to meet logic, the category is B
    End If
Next i   ‘Moving to the next iteration
End Sub

What Is Sequence to Calculate Excel Formula

It is needless to say that mathematical calculations share a common pattern. Since Excel works based on mathematical modeling, therefore it also has a pattern to calculate formulas. If two or multiple operations appear in a formula or equation, the sequence of performing calculations is as follows.

How to Change Calculation Mode in Excel

Often, you have noticed that the Calculation options are in Manual mode. Therefore, you are applying Flash Fill or Fill Handle tool, in contrast, it is not working. In this scenario, we must enable the automatic option from the Calculation Options command.

In the beginning, go to the Formulas tab >> Then expand the Calculation Options command >> Finally, check the Automatic option.

Frequently Asked Questions

Q1. How to convert a Number into percentages in Excel?

Excel has a dedicated command to convert a number into a percentage. First, go to the Home tab >> Then select the Percent Style from the Number group.

Q2. How to do cubic root of a value in Excel?

By inserting the formula =(number, or cell reference)^(1/3) users can perform the cubic root of a value in Excel

Q3. How to do calculations for entire Excel column?

Doing calculations in the first cell of a column and after that, using the Fill Handle tool can perform calculations for the Entire Excel column.

Conclusion

Concisely, we delineate how to calculate in Excel. To expose the entire concept we explain the approaches to add, subtract, multiply, divide, percentage, exponential, and so on. Also, we demonstrate the application of the Fill handle tool. Hope you enjoyed your learning. Don’t forget to leave your thoughts in the comment section. In the meantime, any queries regarding the content are appreciated. For new learning and better understanding, don’t forget to visit us.

How to Calculate in Excel: Knowledge Hub

<< Go Back to Learn Excel