How to Calculate a Decrease in Percentage Value Using ExcelDifferent methods explained in a Step by Step Guide with Screenshots

  |
Written By:
Adiste Mae

Many individuals who utilize Excel for numerical tasks also encounter percentages within the program.

A regular task includes subtracting percentage values in Excel.

A frequent scenario is when one needs to subtract a commission, often represented as a percentage of the total sales, in order to determine the final sales figures.

This guide will demonstrate all the necessary steps for understanding how to subtract percentages in Excel.

How to Use a Formula for Deducting a Percentage from a Number in Excel

One of the simplest methods for subtracting a percentage value from a number is through the use of a basic subtraction formula.

In the following data set, column A represents the Product Name, B represents the sales amount, and C shows the commission percentage.

Let’s compute the final sale value after the commission percentage has been subtracted from the sales value.

The formula that can be utilized to deduct the percentage value from the sales value is as follows:

=B2*(1-C2)

100% is equivalent to 1, so to deduct a specific percentage value from a number, we first compute the percentage value to be removed by subtracting the percentage value from 1 and then multiplying it with the number.

Alternatively, this formula can also be applied:

=B2-B2*C2

In the formula mentioned above, instead of calculating the percentage value that needs to be multiplied by the number, we use the original sales value (found in cell B2) and subtract the commission sales value (calculated by B2*C2) from it.

Note: It is important to note that the formula has a dynamic outcome, meaning that if either the Sales Value or the Commission Percentage value is altered, the result will automatically update.

If you prefer the result to remain constant, you can convert the formula into a value by removing the formula and only retaining the resulting value.

Transforming a Percentage Back into its Original Value

Occasionally, after subtracting a percentage value from a number, the outcome may not be in your desired format and may appear as a percentage instead of a numerical value.

Although the outcome is accurate, the value may not be in the desired format for the specific cell.

This issue can be easily resolved by adjusting the cell format.

Below are the steps to change the cell format to display the number in a general format:

  1. Select the cells you wish to alter the format.
  2. Navigate to the Home tab.
  3. Within the Number group, click on the formatting drop-down.
  4. From the options that appear, select General.

The steps outlined above will apply the General format to the selected cells. Therefore, any cells displaying a number in percentage format will be converted to a standard numerical format.

Using Paste Special to Deduct Percentage

Although using a formula is the most effective method for subtracting percentage values in Excel, there is another technique that may be beneficial in certain scenarios.

It is important to note that this method is only practical when the goal is to deduct a specific percentage value from all the values in an entire column.

The following data set comprises sales values in column A, and the goal is to determine the values after a 10% commission has been subtracted.

The technique we will utilize is to multiply the sales values by the reduced percentage value.

As an example, if the intent is to reduce a sales value by 10%, it can be achieved by multiplying it by 90% (which is equivalent to subtracting 10% from 100%).

The following are the steps for achieving this:

  1. Copy the sales data and paste it into column C, where the final result will be displayed.
  2. In an empty cell, enter the formula “=100%-10%” (or you can enter 90%), this will deduct 10% of the value. If you want to deduct 20% then use “=100%-20%”.
  3. Copy the cell containing this formula.
  4. Highlight all the cells in column C where you pasted the sales data in step 1.
  5. Right-click, then select ‘Paste Special’.
  6. In the Paste Special dialog box, select the ‘Values’ option.
  7. Under ‘Operation’, choose the ‘Multiply’ option.
  8. Click the OK button.

The above instructions will multiply the copied cell with all the values in column C. And since we selected the ‘Values’ option in step 6, the formatting of the copied cell will not be applied to column C.

This method results in a static outcome, meaning that it will not automatically update if the value in cell E1 is changed.

This technique is useful when the data is in a column or row and the goal is to decrease all values by a specific percentage value.

Subtracting Percentage Value from Another in Excel

Finally, another scenario that may arise is when you need to subtract one percentage value from another in Excel, where you have two cells with percentage values and you want to subtract one cell’s value from the other.

As both values are in percentage format, this task can be completed as a regular subtraction.

The following formula can be used to perform this action:

=C2-B2

After entering the formula in cell D2, it can be applied to the entire column by copying it to the remaining cells.

If you are using dynamic arrays, you can apply the following formula to accomplish this task:

=C2:C6-B2:B6

This guide has demonstrated how to subtract percentages in Excel, which is commonly used when deducing a value by a specific percentage.

A simple formula can be used to accomplish this task.

Additionally, a paste special multiplication method was discussed, which can be applied to subtract a specific percentage value from an entire row or column.

I hope this helps!