Comparing data is the best way to visualize the evolution of a business, whether sales data, customers, production, etc.. But do you know how to express those data in percentage form correctly? In this tutorial, we teach you how to calculate the percentage variation in Excel correctly. Easily calculate percentage increases and decreases.
Depending on the calculation you need to make you will have to use one or another formula in Excel. So let’s see different examples.
Percentage variation in Excel between 2 values
This example is most commonly used to compare, for example, sales results in two consecutive years. By calculating the percentage variation in Excel you can show much more clearly and precisely how much sales have decreased or increased compared to the previous year.
The formula for calculating percentage change
The percentage change would be equal to the final value divided by the initial value minus the initial value divided by the initial value.
We will simplify the formula as much as possible to make it easier to enter in Excel.
In our example the initial value would be sales in 2018 and the final value would be sales in 2019. At the end of the post, you can download the exercise template to practice.
The formula for the example
Pressing ENTER will apply the formula and we will see the variation in decimal format. To change it to a percentage format you just have to apply to the cell the Percentage format that you will find in the Number tools of the Excel Home tab.
When the percentage variation decreases the percentage will be negative. For example, -16%.
How to enter percentages correctly in Excel
When working with percentages in Excel it is very important that you know how to enter them correctly. There are several ways that are explained below:
Write the amount followed by the percentage symbol “10%”.
Enter the percentage in the decimal format “0,10”.
First, change the format of the cell to percentage and then type in the value “10” (the % symbol will automatically appear next to the amount entered).
You should never first enter the numeric value in the cell and then change the format to percentage because changing the Excel format will multiply the value by 100. If you wrote a 10 (because you wanted to apply 10%) when changing the format to percentage 1000% will appear and you will have to delete 2 zeros.
Apply percentage increase to an amount
In the example, we have an initial price to which we want to apply a percentage increase. This example can be used to apply VAT and other taxes to prices.
In this case, the formula you must use is equal to Initial Price + Initial Price x %.
Apply percentage decrease or discount
In this case, you have two options, you can use the same formula as in the percentage increase but writing a negative percentage.
You can also vary the formula and keep the percentage positive. In this case, the formula would look like this.