Growth as a percentage – formula in Excel

Percentages are one of the most popular ways to work with Excel. In particular, many users want to learn how to calculate the increase in a certain indicator as a percentage. So, it is useful for analyzing currency quotes or changes in prices for certain goods compared to the previous reporting period.

How to Calculate Growth Rate and Growth Rate in Excel

To determine the growth and growth rate in Excel, you must first define what each of these concepts is. The growth rate means the ratio between the value generated during this reporting period and the same parameter for the previous one. This indicator is defined as a percentage. If there is no growth compared to the previous reporting period, then the value is 100%.

If the growth rate is more than 100 percent, this indicates that a certain indicator has grown over the last reporting period (or several). If less, then, accordingly, fell. The general formula is similar to the standard formula for obtaining a percentage, where the divisor is the value to be compared, and the denominator is the indicator with which to compare.

In turn, the definition of growth rates is carried out in a slightly different way. First, the growth rate is calculated, after which we subtract a hundred from the resulting value. What remains is the percentage by which an increase or decrease in the key indicator occurred. Which indicator to use? It all depends on which form of representation is more convenient in a particular situation. If it is necessary to show an absolute increase or decrease, then growth rates are used; if relative, growth rates are used.

Growth and growth rates are divided into two types: chain and basic. The first is the ratio of the current value to the previous one. The baseline growth and growth does not take the previous value as a basis for comparison, but some kind of base value. For example, the first in the sequence.

What is considered the basic and previous value? If we are talking about a starting indicator, for example, the Dow Jones index in January 2020, and measurements are taken in January 2021, then we can say that the base growth rate of the index was so much. Also, as an example of underlying growth or growth, you can compare with the very first value of this index when it was first published. An example of a previous rise or gain is a comparison of the value of this index in December against the background of November of the same year. No matter what type of growth, you need to subtract 100 to get the growth rate from it.

How to calculate percentages in Excel

Calculating percentages in Excel is done elementarily. You need to enter the required numbers once, and then the application will perform all the actions on its own. The standard formula for obtaining interest is fraction of a number/number*100. But if we carry out calculations through Excel, the multiplication is carried out automatically. So what do we need to do to determine the percentage in Excel?

1. First we need to set the percentage format. To do this, right-click on the desired cell, and then select the “Format Cells” option. When the dialog box appears, we should choose the correct format.
2. Also setting the format is possible through the main menu. You need to find the “Home” tab, go to it and find the “Number” group of tools. There is a cell format input field. You need to click on the arrow next to it and select the one you need from the list.

Now let’s demonstrate how this is implemented in practice, using a real example. Let’s say we have a table that contains three columns: product number, planned sales, and actual sales. Our task is to determine the degree of implementation of the plan.

To achieve the goal, you need to perform such actions. We will describe the principle, and you will need to supply the appropriate values ​​for your case.

1. We write down the formula =C2/B2 in cell D2. That is, we need to divide the actual execution of the task in the numerator, and the planned one in the denominator.
2. After that, using the method described earlier, we translate the format into a percentage.
3. Next, we extend the formula to the remaining cells using the autocomplete handle.

After that, all remaining actions will be performed automatically. This is the advantage of Excel compared to the manual method of calculating percentages – you just need to enter the formula once, and then you can copy it as many times as you need, and all the values ​​​​will be calculated by themselves, and correctly.

Percentage of the number

Suppose we know what percentage should be a part of the number. And the task was set to determine how much this part will be in numerical form. To do this, apply the formula = percentage% * number. Suppose, according to the conditions of the problem, it is required to determine how much it will be 7% of seventy. To solve it, you need:

1. Click on the correct cell and enter the following formula there: =7%*70.
2. Press the Enter key and the result will be written in this cell.

It is also possible to point not to a specific number, but to a link. To do this, it is enough to enter the address of the corresponding cell in the B1 format. Make sure it contains numeric data before using it in a formula.

Percentage of the amount

Often, during data processing, the user is tasked with determining the resulting sum of values, and then calculating the percentage of a certain value from the resulting value. There are two available solutions: the result can be written based on one specific cell or distributed across the table. Let’s give an example of solving the first version of the problem:

1. If we need to record the result of calculating the percentage of one specific cell, we need to write an absolute reference in the denominator. To do this, you need to put one dollar sign ($) in front of the address of the row and column.
2. Since our final value is written in cell B10, it is necessary to fix its address so that when the formula spreads to other cells, it does not change. To do this, we write the following formula: =B2/$B$10.
3. Then you need to change the format of all cells in this series to percentages. After that, using the autocomplete marker, drag the formula to all other lines.

We can check the result. Because the reference we used was absolute, the denominator in the formula didn’t change in the other cells. If we did not put a dollar sign, then the address would “slide” down. So, in the next line, the denominator would already have the address B11, then – B12, and so on.

But what to do if the necessary information is distributed throughout the table? To solve this complicated problem, you need to use the function SUMMESLI. It checks the values ​​in the range against the specified criteria, and if they do, sums them up. After that, you need to get the percentage of the resulting value.

The formula itself generally has the following syntax: uXNUMXd SUMIF (criteria range; summation range) / total sum. In the English version of the program, this function is called SUMIF. Let’s explain how the above formula works:

1. In our case, the range of values ​​means the names of products. They are in the first column.
2. The addition range is all the values ​​that are contained in column B. That is, in our case, this is the number of products of each heading. These values ​​must add up.
3. Criterion. In our case, it is the name of the fruit.
4. The result is recorded in cell B10.

If we adapt the above general formula to our example, it will look like this: =СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10. And a screenshot for clarity.

So you can get the results of the calculation for each of the parameters.

How to Calculate Percent Change

And now let’s figure out what needs to be done to determine the increase or decrease in a certain value compared to the same past period. The built-in functionality of Excel quite allows you to carry out such calculations. To do this, you need to use the formula, which in general mathematical form (not adapted for Excel) looks like this: (BA)/A = difference. But how is the percentage change calculated in Excel?

1. Let’s say we have a table in which the first column contains the product that we are analyzing. The second and third columns show its value for August and September, respectively. And in the fourth column, we will calculate the increase or decrease as a percentage.
2. Accordingly, it is necessary in the first cell after the heading in column D to write the formula for calculating the percentage change in the first row. =(C2/B2)/B2.
3. Next, use autocomplete to stretch the formula to the entire column.

If the values ​​that we need to calculate are placed in one column for a certain product for a long time period, then we need to use a slightly different calculation method:

1. The second column contains sales information for each specific month.
2. In the third column, we calculate the percentage change. The formula we use is: =(B3-B2)/B2 .
3. If you want to compare values ​​with a well-defined indicator that is in a particular cell, then we make the link absolute. Let’s say if we need to perform a comparison with January, then our formula will be as follows. You can see it on the screenshot.

The fact that there is an increase, not a fall, we can understand by the absence of a minus sign in front of the number. In turn, negative values ​​indicate a decrease in indicators compared to the base month.

Calculation of value and total amount

Very often, we only know the percentage of a number, and we need to determine the total amount. Excel provides two methods for solving this problem. Let’s say you have a laptop that costs $950. According to the seller’s information, VAT, which is 11%, must also be added to this price. To determine the overall result, you need to perform several elementary calculations in Excel.

1. The general formula that we will use is − Total * % = Value.
2. Place the cursor in cell C2. In it we write down the formula indicated in the screenshot.
3. Thus, the markup caused by the tax would be $104,5. Therefore, the total cost of the laptop will be $1054.

Let’s use another example to demonstrate the second calculation method. Let’s say we buy a $400 laptop and the salesperson says the price already includes a 30% discount. And we are taken by curiosity, but what is the initial price? To do this, you need to follow this algorithm:

1. First, we determine the share that is paid by us. In our case it is 70%.
2. To find the original price, we need to divide the share by the percentage. That is, the formula will be as follows: Part/% = Total amount
3. In our example, the first column contains the cost of the laptop, and the second column contains the final percentage of the original price that we paid. Accordingly, the final result is recorded in the third column, in the first cell after the heading of which we write the formula =A2/B2 and change the cell format to percentage.

Thus, the cost of the laptop without discount was 571,43 dollars.

Changing a value by a percentage

We often have to change a number by a certain percentage. How to do it? The task can be accomplished using the formula =Costs*(1+%). You need to put the appropriate values ​​in the right places, and the goal is achieved.

Percentage Operations in Excel

In fact, percentages are the same numbers as any other, so you can perform all possible arithmetic operations with them, as well as use formulas. Thus, today we figured out the features of working with percentages in Excel. In particular, we have understood how to calculate the increase in percentage, as well as how to increase the number by a certain percentage.