How to Calculate Trend Analysis in Excel (3 Easy Methods)

Sometimes you may need to calculate Trend analysis in Excel to analyze the future situation. In this article, I’m going to explain how to calculate Trend analysis in Excel.

What Is Trend Analysis?

Trend analysis is an analysis for the prediction of future situations. In addition, with this analysis, you can forecast the stocks, demands, prices, and so on. Moreover, sometimes Trend analysis is represented with a curve or line. The Trend line denotes the prevailing direction of something.

How to Calculate Trend Analysis in Excel: 3 Simple Methods

Here, I will describe three methods and an example to calculate Trend analysis in Excel. For your better understanding, I’m going to use an example of the following dataset. Which contains three columns. These are Month, Cost, and Sales.

1. Use of TREND Function to Calculate Trend Analysis in Excel

You can use the TREND function to calculate Trend analysis in Excel. The TREND function is a built-in feature in Excel. Furthermore, for this function, you have to use numerical values. That is why I will use the Month values numerically. The sample data is given below. Which has two columns. They are Month and Sales.

Steps:

• Firstly, select a different cell D5 where you want to calculate the Trend analysis.
• Secondly, use the corresponding formula in the D5 cell.

• Now, you must press ENTER to get the result.

In the case of using the TREND function, if your Excel is an older version than MS Office 365 then you must use the following key instead of using ENTER to get the result.

“CTRL+SHIFT+ENTER”

Finally, you will see the following result.

Furthermore, to forecast the Sales for the Month July and August, you have to follow the following steps.

• Firstly, select a different cell D11 where you want to calculate the Trend analysis of the forecast value.
• Secondly, use the corresponding formula in the D11 cell.

• Now, you must press ENTER to get the result.

Finally, you will see the following result.

Read More: How to Add Trendline in Excel Online

2. Using Excel Charts Group

There is a built-in process in Excel for making charts under the Charts group Feature. Furthermore, there is a feature in that chart which is Trendline. You can use the Trendline feature to calculate Trend analysis in Excel. The steps are given below.

Steps:

• Firstly, you have to select the data. Here, I have selected the range C4:D10.
• Secondly, you have to go Insert tab.

• Now, from the Charts group section you have to choose 2-D Line >> then choose Line with Markers.

In addition, there are 6 features under the 2-D Line. Along with that, you can choose according to your preference. Here, I have used Line with Markers.

• Now, you must select the chart.
• Then, from Chart Design >> choose Select Data.

Subsequently, a dialog box of Select Data Source will appear.

• Now, you must select Edit to include the Axis Labels from the following box.

At this time, a dialog box named Axis Labels will appear.

• Then, you have to select the Axis label range. Here, I have selected the range from B5:B10.
• Now, you need to press OK to make the changes.

• After this, press OK on the Select Data Source box.

At this time, you will see the corresponding data chart. Where I have changed the Chart Title to Trend Analysis.

• Now, from the chart, you have to click on the + icon.
• Then, from the Trendline >> you must select Linear Forecast.

At this time, the following dialog box named Add Trendline will appear.

• From the box, you may select Cost.
• Then, you need to press OK.

Now, you can see the forecasted Trendline for the Cost values.

Similarly, you have to follow the process to find out the Trendline of Sales.

• At first, from the chart, you have to click on the + icon.
• Then, from the Trendline >> you must select Linear Forecast.

At this time, the following dialog box named Add Trendline will appear.

• From the box, you may select Sales.
• Then, you need to press OK.

Now, you can see the forecasted Trendline for the Sales values.

In addition, you can format the Trendline.

• Firstly, you have to click on the Trendline. Which you want to format.
• Secondly, you must choose Trendline Options according to your preference. Here, I have only changed the width of the line.

At last, the final result is given below.

3. Applying Generic Formula to Calculate Trend Analysis

You can apply a generic formula to calculate the Trend analysis. For this, I will use the following dataset. Which contains two columns. They are Year and Sales.

Steps:

• Firstly, select a different cell D6 where you want to calculate the Change in Amount.
• Secondly, use the corresponding formula in the D6 cell.

Here, in this formula, I have done a simple subtraction from the current year 2013 to the previous year 2012 to get the Change in Amount.

• Then, you have to press ENTER to get the value in the Change in Amount column.
• Subsequently, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D7:D10.

After that, you can see the following result.

Now, to get the Percentage Change you have to do further calculations.

• Firstly, select a different cell E6 where you want to calculate the Percentage Change.
• Secondly, use the corresponding formula in the E6 cell.

Here, in this formula, I have done a simple division (Change in Amount by the previous year 2012) to get the Percentage Change.

• Then, you have to press ENTER to get the value in the Percentage Change column.
• Subsequently, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E7:E10.

At this time, you will see the following result.

• Now, you need to select the values E5:E10.
• Then, from the Home tab >> you must choose the Percentage % option under the Number section.

Finally, you will get the result in Percentage form.

Calculating Trend Analysis for Multiple Sets of Variables

Here, I will show you an example with multiple sets of variables. Moreover, I will use the TREND function again.

Steps:

• Firstly, select a different cell E5 where you want to calculate the Trend analysis.
• Secondly, use the corresponding formula in the E5 cell.

• Now, you must press ENTER to get the result.

At this time, you will see the following result.

Furthermore, to forecast the Sales for the months of July and August, you have to follow the following steps.

• Firstly, select a different cell E11 where you want to calculate the Trend analysis of the forecast value.
• Secondly, use the corresponding formula in the E11 cell.

• Now, you must press ENTER to get the result.

Finally, you will see the following result.

Read More: How to Add Multiple Trendlines in Excel

Things to Remember

• In the case of using the TREND function, if your Excel is an older version than MS Office 365 then you must use the following key instead of using ENTER to get the result.

“CTRL+SHIFT+ENTER”

• In addition, the 1st method is the best option for calculating the Trend analysis.
• Furthermore, when you want to see the visual representation of your forecasted data, then you can use the 2ndmethod.

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

You can download the practice workbook from here:

Conclusion

I hope you found this article helpful. Here, I have explained 3 methods of How to Calculate Trend Analysis in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Make a Polynomial Trendline in Excel
• How to Draw Best Fit Line in Excel
• How to Plot Quadratic Line of Best Fit in Excel
• How to Insert Trendline in an Excel Cell
• How to Create Trend Chart in Excel
• How to Create Monthly Trend Chart in Excel
• How to Calculate Trend Percentage in Excel

<< Go Back To Trendline in Excel | Excel Charts | Learn Excel