Technology

A Comprehensive Guide to Using Subtraction in Excel with Illustrative Examples

Subtraction is a widely-used mathematical operation for computing numbers. However, you may still struggle and encounter difficulties when using subtraction in Excel, and you want to find a way to use it quickly and efficiently. Join FPT Shop in exploring the answer to subtraction through the following article.

Administrator

16 February, 2024

134

Subtraction is a commonly used operation not only in academics but also widely applied in daily life. So how is subtraction performed in Excel, is it difficult? Let’s find out about the various ways to use this operation in the Excel software in a detailed and easy-to-understand manner, with the help of FPT Shop!

An overview of subtraction in Excel

In reality, in the Excel software, there is no specific function for subtraction. However, users can still perform subtraction in Excel quickly and simply. In that case, users need to use the “-” symbol or the Sum function to perform the calculation.

There are four common syntaxes used to perform subtraction in Excel, as follows:

= (Subtracting number – Subtrahend).

= (Subtracting number – Subtrahend 1 – Subtrahend 2 – … – Subtrahend n).

= Sum(Subtracting number, -Subtrahend 1, …). Since the Sum function actually performs addition, when used to subtract, users need to add the negative “-” sign in front of the subtracted numbers.

= Subtracting number – Sum(Subtrahend 1: Subtrahend n). In this syntax, you can understand the Sum(Subtrahend 1: Subtrahend n) part as the sum of the subtrahends from the first subtrahend to the last subtrahend.

In addition, users should also note that the separator between numbers in the syntax can be either “,” or “;” depending on the settings or version of Excel installed on the computer. You should check the settings to avoid incorrect syntax and error messages that may occur, and to ensure that the results are displayed correctly.

Instructions for using subtraction in Excel

There are many ways to perform subtraction in Excel, but let’s take a look at the four most common methods. Each method includes detailed examples.

Subtracting multiple cells in Excel

When subtracting multiple cells in the same row in Excel, you just need to use the value of the referencing cell as the Subtracting number, and then subtract the values of the referencing cells from the Subtrahends. Then press the Enter key to get the result.

Illustration: Find the number of weak students in class 1A when given the total and the number of students for each academic achievement level (excellent, good, average) respectively in the referencing cells B1, B2, B3, and B4 as shown in the image:

Step 1: To find the number of weak students in class 1A, the user needs to enter the formula: =B1-B2-B3-B4.

Step 2: Press the Enter key, and the result of 10 will be displayed.

Subtraction combined with the Sum function in Excel

In addition to using the “-” symbol between the values to be subtracted, you can also combine subtraction with the Sum function. This function is commonly used to calculate sums quickly and conveniently.

Method 1: Use the Subtracting number with the Sum function.

Step 1: Still using the example from above, enter the formula: =B1-Sum(B2:B4), with the Sum(B2:B4) function calculating the sum of the values from B2 to B4.

Step 2: Press the Enter key, and the result will appear.

Method 2: Use the Sum function to add negative Subtrahends.

Step 1: Continue using the example above, enter the formula: =Sum(B1,-B2,-B3,-B4). In this case, -B2, -B3, -B4 are the subtracted numbers with added negative “-” signs at the front. So when using the Sum function to calculate the sum of the Subtracting number and the negative Subtrahends, we get the equivalent result of subtraction in Excel.

Step 2: Press the Enter key, and the result of the formula will be displayed.

Subtracting percentage values in Excel

Subtracting percentage values in Excel is also simple and does not require any special formatting. What you need to do is simply add the “%” sign (by pressing the Shift key and the number 5 key on the keyboard).

When subtracting percentage values in Excel, you can perform the calculation directly by adding the “%” sign (by pressing the Shift key and the number 5 key on the keyboard) without changing the format. If the data is already given in percentage form, you can choose either Method 1 or Method 2 below to perform the calculation.

Illustration: Find the percentage of weak students in class 1A when given the percentage values respectively in the referencing cells B1, B2, B3, and B4. Calculate the percentage as follows:

Method 1: Use the negation sign as in regular subtraction in Excel.

Enter the formula: =B1-B2-B3-B4 and press Enter to get the result.

Method 2: Use the Sum function to subtract the percentage values.

In this method, subtract the Subtracting number from the Sum function, which calculates the sum of the Subtrahends.

Step 1: Enter the formula: =B1-Sum(B2:B4), with Sum(B2:B4) being the function that calculates the sum of the percentage values from B2 to B4.

Step 2: Press the Enter key, and the result will be displayed.

In addition, you can also use the Sum function with negative Subtrahends to perform percentage subtraction. To ensure that the calculation is performed correctly, if the given values do not include the percentage sign, you need to add the “%” sign to each value, such as B1%, B2%, B3%, and B4%.

Step 1: Enter the formula: =Sum(B1,-B2,-B3,-B4). When using this method, you need to add the negative sign in front of the Subtrahends to convert them into negative numbers.

Step 2: Press the Enter key, and the result of the formula will be displayed.

Conclusion

In conclusion, the above article has shared with you the common ways to use subtraction in Excel, with each step illustrated in a detailed and easy-to-understand manner. Hopefully, through this article, you will be able to successfully apply and use this calculation in the Excel software!