Percentages in Excel are like a compass in a sea of data: they transform figures with no context into a means of comparison and understanding of a specific situation. Percentages offer a straightforward method for illustrating relationships between numbers, useful for tasks like calculating price fluctuations, determining proportions, analyzing financial outcomes, or evaluating performance. This clear approach helps present numerical connections effectively.
By mastering the calculation of a percentage within your data tables, you enhance your understanding of data and your efficiency. In this article, discover the complete process of calculating a percentage in Excel.
What is the formula for calculating a percentage?
As a reminder, a percentage is a proportion expressed on a basis of 100 units. The formula to determine a percentage is as follows: (Partial Value/Total Value)×100. This formula converts the ratio of the partial value over the total value into a percentage, offering a clear perspective on the relative importance of that partial value.
Example of percentage calculation
Let’s take a concrete example to illustrate what a percentage is, using the formula seen above: (partial value/total value) x 100.
Imagine you want to calculate the proportion of girls in a second-grade primary class. In this class of 31 students, 17 are girls.
The partial value is then the number of girls, which is 17.
The total value corresponds to the number of students, which is 31.
By applying the formula, we get:
→ (17/31) x 100 = 54.8 %
This means that 54.8 % of the students in the class are girls. This proportion then allows you to compare with other classes that have more or less students. Indeed, 17 girls in a class of 26 students results in a higher percentage (65.4 %).
Calculating a percentage in Excel
Let’s go back to our previous example to illustrate the application of the percentage formula in Excel. We assume then that you want to calculate the percentage of girls with respect to the total class of the second grade primary. The formula in Excel would be something like this:
- Enter the partial value in a cell (for example, B2 = 17).
- Enter the total value in another cell (for example, B3 = 31).
- In a new cell, apply the percentage formula: =(B2/B3)*100. You can also format this cell as “Percentage” so that Excel automatically displays the result in percentage form without needing to multiply by 100.
The displayed result will be 54.8%, indicating that girls represent 54.8% of the second-grade primary class. If you wish, you can create a chart to make the result more visual.
Calculation if you know the partial value and the percentage
If you know the number of girls in the class and the percentage of girls, but you want to know the total number of students, the calculation is as follows:
- Enter the partial value in a cell (for example, B2 = 17)
- Enter the percentage in another cell (for example, B3 = 54.8%). Here, make sure to format the cell as a percentage. Otherwise, Excel will understand that your data is the number 54.8.
- In a new cell, enter the formula: =(B2/B3). The format of the result should be numeric since you are looking for the total number of students.
The number displayed will then be 31 students.
If you know the total value and the percentage but want to know the partial value (the number of girls in the class), enter the total value in cell B2. The formula will then be a multiplication: =(B2*B3). The result will be 17.
Calculating a rate of change in Excel
Now that we’ve seen how to calculate a simple percentage in Excel, it’s time to consider rates of change. Indeed, percentages are particularly relevant for testifying to an evolution, whether upward or downward. For the number of employees, turnover, costs, or even the evolution of your salary, the rate of change is an important calculation to know. And Excel simplifies the process.
Calculation of a percentage increase
To calculate the increase in percentage between two values, you can use the following formula: =((Final Value – Initial Value) / Initial Value) * 100.
Example: imagine you want to calculate the percentage increase in sales from one year to the next, going from 1000 units to 1200 units.
- Initial value: enter the initial value in a cell, say A1 (1000).
- Final value: enter the final value in the following cell, A2 (1200).
- In a new cell, enter the formula =((A2-A1)/A1)*100.
- Excel will display the increase in percentage. In this example, the result is 20%.
Calculation of a percentage decrease
To calculate a decrease in percentage, a similar logic is followed, using the formula =((Initial Value – Final Value) / Initial Value) * 100.
Example: to calculate the percentage decrease of the costs of an initial project of 5000 euros that has been reduced to 4500 euros.
- Initial value: enter 5000 in a cell, for instance B1.
- Final value: then enter 4500 in cell B2.
- In the cell where you want to see the result, enter =((B1-B2)/B1)*100.
- Excel will show the reduction in percentage, here 10%.
Before applying these formulas, make sure that the result cell is formatted as a percentage to facilitate the reading and interpretation of the results. Your result might appear as: -0.01000. To change this, click on “%” in the “Home” tab. Also remember that a positive value indicates an increase, while a negative value means a reduction. These small details are important for the correct analysis of data.
Summarizing percentage calculation in Excel
The basic formula (Partial Value/Total Value)×100 enables the calculation of simple percentages. For increases and decreases, we use the rate of change formula: ((Final Value – Initial Value) / Initial Value) * 100. To learn to create interactive dashboards and offer yourself new career opportunities, follow our Power BI course.
