How to Calculate Trend Percentage in Excel: A Quick Guide

The trend percentage is an ambiguous term often used to describe the change of a value relative to a base term. To calculate the trend percentage in Excel, we need to use the mathematical formula.

In financial analysis, the trend percentage is the ratio of a value to a base value by which everything is measured. It is measured in percentages. It is used to track growth and evaluate performance.

In this tutorial, we’ll cover how to use the trend percentage formula and calculate it in Excel.

Understanding the Formula for Trend Percentage

In financial analysis, the trend percentage of a value is the ratio of it to the base value. Since it is calculated in percentages, the basic formula for a trend percentage is:

The base value is typically the first value in a period. It can also be some other values in the data range.

However, the value has to be consistent throughout all the calculations. Because this is regarded as a benchmark, all values need to be compared with one. Otherwise, the representation of growth or decline won’t be as accurate.

How to Calculate Trend Percentage in Excel

Since the trend percentage is a simple ratio, we can use the mathematical formula to calculate the trend percentage in Excel. The fixed base value comes from using absolute references.

Absolute referencing is the cell reference technique that does not change if a formula is copied or moved to other cells.

Let’s say we have the following sales values for each year.

If we consider the first value as the base value, the method to calculate the trend percentage would be:

➤ Select the output cell (C2) and use the following formula:
=(B2/$B$2)*100

The $ sign indicates the B2 cell is fixed and doesn’t change when we copy the formula to other cells. Select the cell and press  F4  on the keyboard to change reference types.

➤ Fill down the rest of the cells with the same formula.

That’s the simple procedure to calculate trend percentage in Excel.

Interpreting the Result from Trend Percentage

The trend percentage of the base value will always be 100% by the nature of the equation.

Our second value (for the year 2021) has a trend percentage of 120. This indicates the sales increased by 120-100=20%. Similarly, it is 50% for the third when compared with the base value.

The trend percentage amount indicates the following:

➤ Greater than 100% indicates an increase relative to the base value.
➤ 100% values indicate no change.
➤ Lower than 100% says there is a decrease compared to the base value.

FAQ

How do you calculate trend in Excel?

To calculate the trend in Excel, you need to use the TREND function. Use the following equation:

=TREND(y-values,x-values)

You can use new x-values for which you want to calculate y-values. The output of this formula will return the trend values of the existing y values.

How to calculate growth percentage in Excel?

The formula for growth percentage is (New value – Old value)/Old value.

Use it as a mathematical formula in Excel. Either use the values directly or put the cell references in these values to get the growth percentage.

How to calculate percentage change?

Percentage change is the measurement of growth, but in a percentage. So, the formula is (New value – Old value)/Old value * 100

In Excel, either directly use these new and old values or the cell references in the mathematical formula to calculate the percentage change.

Conclusion

In this tutorial, we have covered how to calculate the trend percentage in Excel. The idea is to use the mathematical formula of trend percentage as an Excel equation to find the value. As the base value is fixed in the trend percentage formula, we have used absolute references so that we can copy the formula later.

Feel free to download the practice file and let us know about your feedback.