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

Get FREE Advanced Excel Exercises with Solutions!

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.
=TREND(C5:C10,B5:B10) Here, TREND will return a value in a linear way with the given points using the least square method. In this function,

• C5:C10 denotes the known dependent variable, y.
• B5:B10 denotes the known independent variable, x.
• 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.
=TREND(C5:C10,B5:B10,B11:B12) Here, in this function,

• C5:C10 denotes the known dependent variable, y.
• B5:B10 denotes the known independent variable, x.
• B11:B12 denotes the new independent variable, x.
• Now, you must press ENTER to get the result.

Finally, you will see the following result. ### 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.
=C6-C5 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.
=D6/C5 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.
=TREND(D5:D10,B5:C10) Here, TREND will return a value in a linear way with the given points using the least square method. In this function,

• D5:D10 denotes the known dependent variable, y.
• B5:C10 denotes the known independent variable, x.
• 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.
=TREND(D5:D10,B5:C10,B11:C12) Here, in this function,

• D5:D10 denotes the known dependent variable, y.
• B5:C10 denotes the known independent variable, x.
• B11:C12 denotes the new independent variable, x.
• Now, you must press ENTER to get the result.

Finally, you will see the following result. ## 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. ## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Musiha Mahfuza Mukta

Musiha Mahfuza Mukta, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has been working with the ExcelDemy project for 1.5 years. She has written over 97+ articles for ExcelDemy. Currently, she is working as the Excel and VBA Content Developer, creates contents, solves user problems, writes articles etc. Her work and learning interest varies from Microsoft Office Suites, and Excel to Data Analysis, and developing Excel Applications with VBA codes.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  