Md. Abdur Rahim Rasel
Looking for ways how to calculate percentage decrease in Excel? Then this is the right place for you.
While working with large Microsoft Excel, sometimes we need to calculate percentages. Some percentages are positive whereas some are negative. We can denote the negative percentage as the percentage decreases.
We can easily do that by using Excel formulas and VBA Macros also. This is an easy and time-saving task also.
Today, in this article, we’ll learn 3 quick and suitable ways how to calculate percentage decrease in Excel effectively with appropriate illustrations.
Introduction to Percentage Decrease
While subtracting the second value from the first value, you will get the change between these two values. If the first value is less than the second value, you will get a decreased value.
Decrease = First value – Second ValueThen divide this decreased value by the first value and multiply by 100, and you will get the percentage decreased.
Percentage Decreased = (Decrease / First value)*100%Before diving into the big picture, let’s get to know about today’s Excel sheet first. This dataset contains 4 columns. They are Brand, Device, Old Price, and New Price. There are the products and prices respectively. Here, you can notice that the New Prices are lower than the Old Prices.
Now for different products, we will show you how to calculate the percentage decrease in price in Excel.
1. Using Excel Formula to Calculate Percentage Decrease
We can easily calculate the percentage decrease by using the mathematical formula in Excel. This is a time-saving way also. From our dataset, we will calculate the percentage decrease. Let’s follow the instructions below to calculate the percentage decrease!
- Select the cell where you want to calculate the percentage decrease and write the following formula.
=(E5-D5)/D5- After that, press Enter and drag the Fill Handle to copy the formula to the other cells.
- Finally, you can see that we have copied the formula to the other cells and got the percentage decrease.
- You may find the results in decimal. To change that, select the cells where you have got the results in decimal.
- Go to the Home tab >> click on the drop-down menu from the Number group >> select Percentage.
- Finally, you will see that the results are shown in percentages.
2. Calculating Percentage Decrease from Specific Value in Excel
Suppose you want to calculate the percentage decrease from a specific value. In this case, you will have to use an absolute cell reference.
Here, in cell C18 we have the Maximum Price according to the Old Prices and we want to calculate the percentage decrease of New Prices from this value. Here are the steps to do that.
- Select cell F5 and insert the following formula.
=(E5-$C$18)/$C$18- Press Enter and drag the Fill Handle to copy the formula to the other cells.
Read More: How to Calculate Percentage Increase in Excel (Examples with All Criteria)
Similar Readings
- How to Calculate Price Increase Percentage in Excel (3 Easy Ways)
- Show Percentage Change in Excel Graph (2 Ways)
- How to Calculate Percentage Change with Negative Numbers in Excel
- Calculate Percentage Increase from Zero in Excel (4 Methods)
- How to Calculate Average Percentage Increase for Marks in Excel Formula
3. Running VBA Macro to Calculate Percentage Decrease
Now we’ll show how to calculate the percentage decrease in Excel by using a simple VBA code. It’s very helpful for some moments and a time-saving way also. From our dataset, we will calculate the percentage decrease.
- Go to the Developer tab >> click on Visual Basic.
- Now, the Microsoft Visual Basic Editor will open. To add a module, click on Insert >> select Module.
- Copy the following code in your module and go back to your worksheet.
Code Breakdown
- In the above code, we declared lastRow and i as Long and oldPrice, newPrice and percentageDecrease as Double.
- We calculated the number of rows using the Count method and assigned that value as lastRow.
- Then, we assigned the values of column D as oldPrice and column E as newPrice starting from the 5th row.
- We looped through each row and calculated the percentage decrease between the oldPrice and the newPrice using the formula:
percentageDecrease = (oldPrice - newPrice) / oldPrice * 100
- Finally, we inserted the results in column F using the Format function to display the percentage with two decimal places and a “-“ sign to indicate a decrease.
- Then, go to the Developer tab >> click on Macros.
- Select the Percentage_Decrease named macro and click on Run button.
- Now, you will be able to calculate all the values of the percentage decrease in New Prices which has been given in the below screenshot.
Read More: How do you Calculate Percentage Increase or Decrease in Excel
4. Determining Percentage Decrease for Negative Values
In this section, we will explain how to calculate the percentage increase or decrease in Excel for negative values. We will explain 2 different situations here.
Here, we have a dataset containing some values of City, State, Old Temperature and New Temperature. Now, we will calculate the Percentage increase or decrease in Temperature of each city.
4.1. Both Values Are Negative
In this example both the old value and the new value are negative. For this type of situation, the formula for percentage change is,
Percentage Change = (Old Value – New Value)/Old ValueSuppose you have a dataset that contains the Old Temp and the New Temp. We will show you how you can calculate the Percentage decrease. Let’s see the steps.
- Select the cell where you want to calculate the Percentage Change and write the following formula.
=(D5-E5)/D5- Press Enter and drag the Fill Handle down to copy the formula to the other cells.
- You can see that I have copied the formula to the other cells and got the Percentage Change.
4.2. New Value Is Negative and Old Value Is Positive
For this example, we have taken a dataset where the new value is negative and the old value is positive. For this situation, the formula for Percentage Change is,
Percentage Change = (New Value – Old Value)/Old ValueLet me show you the steps.
- Select the cell where you want to calculate the Percentage Change and insert the following formula.
=(E5-D5)/D5- Press Enter and drag the Fill Handle down to copy the formula to the other cells.
- Lastly, you can see that we have copied the formula to the other cells and got the values of percentage decrease.
How to Calculate Percentage Increase in Excel
You can calculate the percentage increase using a similar mathematical formula in Excel. Here, in the dataset given below you can notice that the New Prices are higher than the Old Prices. So, to calculate the percentage increase in New Prices follow the steps given below.
- Select the cell where you want to calculate the percentage increase and write the following formula.
=(E5-D5)/D5- Press Enter and drag the Fill Handle to copy the formula to the other cells and you will get all the values percentage decrease.
How to Determine Values after Percentage Decrease in Excel
In this example, we will show you how you can calculate the values after a percentage decrease in Excel. Suppose you have a Product list, their Old Price, and Percentage Change. Here, the negative sign before the percentage change indicates the percentage decrease in the prices.
Now, we will show how you can calculate the New Price from this dataset. Let’s see the steps.
- Select the cell where you want to calculate the New Price and insert the following formula in the selected cell.
=D5*(1+E5)- Press Enter and copy the formula to the other cells by dragging the Fill Handle down. Thus, you will get all the values of the New Price.
How to Use Specific Percentage Decrease to Calculate Values in Excel
Now you may need to calculate values on the basis of a given specific percentage decrease.
Here, we have a Discount value in percentage. In this example, we will use this specific value (12%) as a percentage decrease to calculate values in Excel.
Using the following formula you can calculate the New Value using a specific percentage decrease (Discount) in one go.
New Value = Old Value * (1 - Percentage Increase)When you are told that the price will be decreased by 12%, your updated value will be (100% – 12%) of the present price. 1 is the decimal equivalent of 100%. When you are adding 12% to 1, it will add the decimal equivalent of 12%(0.12) to 1.
- Select the cell where you want to calculate the New Price and insert the following formula in the selected cell.
=D7*(1-$C$4)- Press Enter and you will get the result. Next, drag the Fill Handle to copy the formula. In the end, you can see that I have copied the formula to the other cells.
Common Errors While Calculating Percentage Increase or Decrease Using Formulas in Excel
You may run into different types of errors while using formulas in Excel. Here I have listed some of them with their possible reasons.
- #DIV/0: It may occur if you try to divide any number by zero. To solve this error you may use the IFERROR function for datasets that contain zero.
- #Value: It may occur when you leave cells blank or insert different types of data than you should have. Such as you are supposed to insert a number in the formula, but you have inserted a text value.
- Num!: It may occur when you insert an invalid numeric value into the formula.
- #Name?: It occurs when you misspell any function name.
- #REF!: It may occur when you refer invalid cell or any cell that has been deleted in a formula.
Things to Remember
- If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to File >> Option >> Customize Ribbon.
- You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
We hope all of the suitable methods mentioned above on how to calculate percentage decrease will now help you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
