![How to Calculate Percentage Change in Excel [Formula]](https://orgcreativecommons.org/wp-content/uploads/2022/08/Saadan-beregnes-procentaendring-i-Excel-Formula.png)
If you work with numbers a lot in your daily work, you will often need to calculate percentages. Excel makes this really easy by helping you calculate different types of percentages using various formulas and methods. Calculating percentages in an Excel sheet is similar to calculating in school math papers, it is very easy to understand and use.
One of the most common percentage calculations that people do in Excel is calculating the percentage change between two values over a period of time. A percentage change or percentage change is used to show the rate of change (growth or decrease) between two values from one period to another. It can be used for financial, statistical and many other purposes.
For example, if you want to find the difference between last year’s sales and this year’s sales, you can calculate it with percentage change. In this tutorial, we will cover how to calculate the percentage change as well as a percentage increase and decrease between two numbers in Excel.
Calculating the percentage change between two values is an easy task. You just need to find the difference between two numbers and divide the result with the original number.
There are two different formulas you can use to calculate the percentage change. Its syntax is as follows:
Let’s take a look at this list of hypothetical fruit orders with column B containing the number of fruit ordered last month and column C containing the number of fruit ordered this month:
For example, you ordered a certain amount of fruit last month, and this month you ordered more than you ordered last month, you can enter the following formula to find the percentage difference between the two orders:
=(C2-B2)/B2
The above formula will be entered into cell D2 to find the percentage change between C2 (new_value) and B2 (original_value). After you type the formula in a cell, press Enter to run the formula. Gets the percentage change in decimal values as shown below.
The result is not yet formatted as a percentage. To apply percentage formatting to a cell (or a range of cells), select the cell(s), then click the ‘Percent Style’ button in the Number group on the ‘Home’ tab.
The decimal value will be converted to a percentage.
Calculate the percentage change between two columns of numbers
Now that we know the percentage change between two apple orders, let’s find the percentage change for all items.
To calculate the percentage change between two columns, you need to enter the formula in the first cell of the result column and AutoFill the formula in the entire column.
To do that, click the fill handle (the little green square in the bottom right corner of the cell) of the formula cell and drag it out to the rest of the cells.
You now have the percentage change values for two columns.
When the new value is greater than the original value, the result percentage will be positive. If the new value is less than the original value, then the result would be negative.
As you can see, the percentage change for ‘Apples’ increases by 50%, so the value is positive. The percentage of ‘Avocodo’ is reduced by 14%, so the result is negative (-14%).
You can also use a custom format to highlight negative percentages in red so they can be easily identified when you search for them.
To format cells, select the cells you want to format, right click and select the ‘Format Cells’ option.
In the Format Cells window that appears, click ‘Custom’ at the bottom of the left menu and type the following code into the ‘Type:’ text box:
0.00%;[Red]-0.00%
Then click the ‘OK’ button to apply the format.
Negative results will now be highlighted in red. Also, this format increases the number of decimal places to display accurate percentages.
You can also use another formula to calculate the percentage change between two values (Last Month’s Order and This Month’s Order):
=(C2/B2)-1
This formula divides the new value (C2) by the original value (B2) and subtracts ‘1’ from the result to find the percentage change.
Calculation of percentage change over time
You can calculate the percentage change from one period to another (change from month to month) to understand the rate of growth or decrease from one period to the next (for each month). This is very useful when you want to know the percentage change for each month or year over a specific period of time.
In the following example, we have the price of the fruits for the month of March in column B and the price for the month of July, 5 months later.
Use this generic formula to find the percentage change over time:
=((Current_value/Original_value)/Original_value)*N
Here, N represents the number of periods (years, months, etc.) between the two values of start and current.
For example, if you want to understand the rate of inflation or deflation in prices for each month for more than 5 months in the example above, you can use the following formula:
=((C2-B2)/B2)/5
This formula is similar to the formula we used before, except we need to divide the percentage change value by the number of months by two months (5).
Calculation of percentage growth/change between rows
Suppose you have a column of numbers that lists the monthly price of gasoline for more than a year.
Now, if you want to calculate the percentage of growth or decrease between rows so that you can understand the price changes from month to month, try the following formula:
=(B3-B2)/B2
Since we need to find the percent growth for February (B3) from the price in January (B2), we need to leave the first row blank because we are not comparing January to the previous month. Enter the formula in cell C3 and press Enter.
Then copy the formula to the rest of the cells to determine the percentage difference between each month.
You can also calculate the percentage change for each month compared to January (B2). To do this, you must make that cell an absolute reference by adding the $ sign to the cell reference, for example, $C$2. So the formula would look like this:
=(B3-$B$2)/$B$2
As before, we skip the first cell and enter the formula in cell C3. When you copy the formula to the rest of the cells, the absolute reference ($B$2) doesn’t change, while the relative reference (B3) will change to B4, B5, etc.
You will get a percentage rate of inflation or deflation for each month compared to January.
Calculation of percentage increase in Excel
Calculating percentage increase is similar to calculating percentage change in Excel. The percent increase is the rate of increase from the initial value. You will need two numbers to calculate the percentage increase, the original number and the New_number.
Syntax:
Percentage Increase = (New_number - Original_number) / Original_number
All you have to do is subtract the original (first) number from the new (second) number and divide the result by the original number.
For example, you have a list of invoices and their amounts for two months (April and May). If your bills for the month of April increase in the month of May, what is the percentage increase from April to May? You can use the following formula to find out:
=(C2-B2)/B2
Here, we subtract the electricity bill for the month of May (C2) from the bill for the month of April (B2) and divide the result by the bill for the month of April. Then copy the formula to other cells using the fill handle.
If you get a positive result ($24.00%), the percentage increases between the two months, and if you get a negative result (eg -$13.33%), then the percentage actually decreases rather than increases.
Calculation of percentage decrease in Excel
Now, let’s see how to calculate the percentage decrease between numbers. The percentage decrease calculation is very similar to the percentage increase calculation. The only difference is that the new number will be smaller than the original number.
The formula is almost identical to the percentage increase calculation, except that it subtracts the original value (first number) from the new value (second number) before dividing the result by the original value.
Syntax:
Percentage Decrease = (Original_number - New_number) / Original_number
Let’s say you have this list of storage devices and their prices for two years (2018 and 2020).
For example, prices for storage devices were higher in the year 2018 and prices decreased in the year 2020. What is the percentage decrease in prices for 2020 compared to 2018? You can use this formula to find out:
=(B2-C2)/B2
Here, we subtract the 2018 price (B2) from the 2020 price (C2) and divide the sum by the 2018 price. We then copy the formula to other cells to find the percent decrease for other devices.
If you get a positive result ($20.00%), the percentage decreased between the two years, and if you get a negative result (eg -$13.33%), then the percentage actually increases rather than decreases.
Common errors you get when using the above formulas
When using the formulas above, you may occasionally come across this list of common errors:
- #DIV/0!: This error occurs when you try to divide a number by zero (0) or by a cell that is empty. For example, the value B6 in the formula ‘=(B6-C6)/B6’ is equal to zero, hence the #DIV/0. mistake.
When you enter ‘zero’ in currency format, it will be replaced by a hyphen (-) as shown above.
- #VALUE: Excel throws this error when you enter a value that is not a supported type or when cells are left blank. This typically occurs when an input value contains spaces, characters, or text instead of numbers.
- NUM!: This error occurs when a formula contains an invalid number that results in a number that is too large or too small to display in Excel.
Convert an error to zero
But there is a way we can get rid of these errors and display ‘0%’ instead when the error occurs. To do this, you must use the ‘IF.ERROR’ function, which returns a custom result when a formula fails.
Syntax of IFERROR function:
=IFERROR(value, value_if_error)
Where,
- value is the value, reference, or formula to check.
- value_if_error is the value we want to display if the formula returns an error value.
Let’s apply this formula to one of the error examples (error #DIV/0!):
=IFERROR((B6-C6)/B6,0%)
In this formula, the ‘value’ argument is the percentage change formula and the ‘value_if_error’ argument is ‘0%’. When the percentage change formula encounters an error (#DIV/0! error), the IFERROR function would return ‘0%’ as shown below.
That’s all you need to know about how to calculate percentage change in Excel.
