Most Excel users, who work with numbers, also have to deal with percentages in Excel.
One of the everyday tasks is to subtract percentage values in Excel.
A common example is when you have the sales numbers and you want to deduct the commission (which is mostly a percentage of the sales value) to get the final sales numbers.
In this short Excel tutorial, I will show you everything you need to know about how to subtract percentages in Excel.
Formula to Subtract Percentage in Excel (Deduct Percentage of a Number)
The easiest way to subtract a percentage value from a number would be by using a simple subtraction formula.
Below I have a data set where I have the product name in column A, the sales value in column B, and the commission percentage value in column C.
I want to get the value of the final sale after the commission percentage value has been deducted from the sales value.
Below is the formula you can use to subtract the percentage value from the sales value:
=B2*(1-C2)
Since 100% is equal to 1, to deduct a specific percentage value from a number, we first calculate the deducted percentage value by subtracting the percentage value from 1, and then multiplying it with the number.
You can also use the below formula:
=B2-B2*C2
In the above formula, instead of calculating the percentage value that we need to multiply with the number, we use the value of the initial sale (in cell B2) and subtract the commission sales value (given by B2*C2) from it.
Note that the result of this formula is dynamic, which means that in case you change the Sales value or the Commission percentage value, it would automatically update. In case you do not want this to happen, you can convert the formula into values (i.e., remove the formula and only keep the values).
Converting Percentage Back to General Values
Sometimes you’ll notice that after you have subtracted a percentage value from a number, the resulting value isn’t in the proper format (sometimes it shows a percentage value instead of a number).
While the resulting value is correct, it’s not in the right format that you want in the cell.
And this has a really easy fix – you just need to change the cell format.
Below are the steps to change the cell format to show the number in General format:
- Select the cells for which you want to change the format
- Click the Home tab
- In the Number group, click on the formatting dropdown
- Select General from the options that show up in the drop-down
The above steps would apply the General format to the cells, so if you have any cells that show the number in percentage, they would be converted to a regular number format.
Also read: How to Add a Percentage to a Number in Excel?
Subtract Percentage Using Paste Special
While using a formula is the best way to subtract percentage values in Excel, let me show you another method that might be useful in your situation.
Note that this method is only useful when you want to deduct a specific percentage value from all the values in the entire column.
Below I have sales values in column A, and I want to know the values after a 10% commission has been deducted.
The method we will use here is to multiply the sales values by the reduced percentage value.
So if I want to reduce a sales value by 10%, I can multiply it by 90% (which is the same as subtracting 10% from it).
Below are the steps to do this:
- Copy the Sales data and paste it into column C (where you want the final result)
- In any empty cell, enter =100%-10% (or you can enter 90%). In case you want to deduct 20%, you need to use =100%-20%
- Copy this cell (which has 90%)
- Select all the cells in column C (where we already have the values that we copy pasted in step 1)
- Right-click and then click on ‘Paste Special’
- In the Paste Special dialog box, click on the ‘Values’ option
- Under ‘Operation’, click on the ‘Multiply’ option
- Click OK
The above steps would multiply the copied cell with all the values in column C.
And since we selected values in step 6, it would not carry over the formatting of the copied cell
The result you get from this method is static, which means that it would not update in case you change the value in cell E1.
This method is useful when you have your data in a column or row, and you want to decrease all the values by a specific percentage value.
Also read: Convert Number to Percentage in Excel
Subtract Percentage Value from Another Percentage Value
And the final scenario is where you may need to subtract percentages when you have two cells with percentage values and you want to subtract one cell from the other.
Since both the values are in percentage, this is like a regular subtraction.
You can use the formula below to do this:
=C2-B2
Once you have the formula in cell D2, you need to apply it for the entire column by copying the formula to the remaining cells.
And in case you’re using dynamic arrays, you can use the formula below:
=C2:C6-B2:B6
In this tutorial, I showed you how to subtract percentages in Excel. The most common use of this is when you need to deduct a value by a certain percentage value.
This can easily be done using a simple formula.
I also covered a paste special multiplication technique that can be used to subtract a specific percentage value from an entire row or column.
Other Excel tutorials you may also find helpful:
