If you remember your school math, the process for calculating percentages in Excel is pretty similar. Here’s how to use Excel to calculate percentage increases and decreases. And perform other percentage calculations like percentages of a number.
Calculating Percentage Increase in Excel
Percentage increases involve two numbers. The basic mathematical approach for calculating a percentage increase is subtracting the second number from the first number. Using the sum of this figure, divide this remaining figure by the original number. To give you an example, the cost of a household bill costs $100 in September, but $125 in October. To calculate this difference, you could use the excel formula =SUM(NEW-OLD)/OLD or, for this example, =SUM(125-100)/100 in Excel. If your figures are in separate cells, you can replace numbers for cell references in your formula. For example, if September’s bill amount is in cell B4 and October’s bill amount is in cell B5, your alternative Excel formula would be =SUM(B5-B4)/B4.
The percentage increase between September and October is 25%, with this figure shown as a decimal number (0.25) by default in Excel using the formula above. If you want to display this figure as a percentage in Excel, you’ll need to replace the formatting for your cell. Select your cell, then click the Percentage Style button in the Home tab, under the Number category.
You can also right-click your cell, click Format Cells, then select Percentages from the Category > Number menu to achieve the same effect.
Calculating Percentage Decrease in Excel
To calculate the percentage decrease between two numbers, you’ll use an identical calculation to the percentage increase. You subtract the second number from the first, then divide it by the first number. The only difference is that the first number will be smaller than the second number. Continuing the above example, if a household bill is $125 in October, but it returns to $100 in November, you would use the excel formula =SUM(NEW-OLD)/OLD or, in this example, =SUM(100-125)/125. Using cell references, if October’s bill amount of $125 is in cell B4 and November’s bill amount of $100 is in cell B5, your Excel formula for a percentage decrease would be =SUM(B5-B4)/B4.
The difference between October and November’s figures is 20%. Excel displays this as a negative decimal number (-0.2) in cells B7 and B8 above. Setting the cell number type to Percentages using the Home > Percentage Styles button will change the decimal figure (-0.2) to a percentage (-20%).
Calculating a Percentage as a Proportion of a Number
Excel can also help you calculate a percentage as a proportion. This is the difference between one number, as your complete figure, and a smaller number. This requires an even simpler mathematical calculation than a percentage change. To give you an example, if you have a debt of $100, and you’ve already paid $50, then the proportion of the debt you’ve paid (and coincidentally still owe) is 50%. To calculate this, you divide 50 by 100. In Excel, the formula to calculate this example would be =50/100. Using cell references, where $100 is in cell B3 and $50 is in cell B4, the excel formula required is =B4/B3.
This uses only a basic division operator to give you the decimal number (0.5). Converting this cell number type to Percentages by clicking Home > Percentage Style button will show the correct percentage figure of 50%.
How to Calculate Percentages of a Number
Calculating the percentage of a number is something that you’ll encounter in day-to-day life. A good example would be an item for sale, where a discount of 20% is being applied to the original price of $200. A store employee would need to know what 20% of $200 was. They could then subtract this number from the original price to provide the discounted price. This requires another simple mathematical calculation in Excel. Only the multiplication operator () and percentage sign (%) are used here. To calculate what 20% of the original $200 price is, you can use either =20%200 or =0.2200 to calculate in Excel. To use cell references, where 20% is in cell B4 and the original price of $200 is in cell B5, you could use the formula =B4B5 instead.
The result is the same, whether you use 20%, 0.2, or separate cell references in your formula. 20% of $200 equals $40, as shown in cells B6 to B8 above.
Using Excel for Complex Calculations
As this guide shows, Excel is great for simple calculations, but it handles more complex ones, too. Calculations using functions like the VLOOKUP function are made easy, thanks to the built-in function search tool. If you’re new to Excel, take advantage of some Excel tips every user should know to improve your productivity further.
