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.


Download Practice Workbook

You can download the practice workbook from here:


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.


3 Methods to Calculate Trend Analysis in Excel

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.

How to Calculate Trend Analysis in Excel


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)

How to Calculate Trend Analysis in Excel

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)

How to Calculate Trend Analysis in Excel

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.

Read More: How to Create Monthly Trend Chart in Excel (4 Easy Ways)


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.

How to Calculate Trend Analysis in Excel

  • 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.

How to Calculate Trend Analysis in Excel

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.

How to Calculate Trend Analysis in Excel

  • 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.

How to Calculate Trend Analysis in Excel

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.

How to Calculate Trend Analysis in Excel

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.

How to Calculate Trend Analysis in Excel

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.

How to Calculate Trend Analysis in Excel

Read More: How to Add Trendline in Excel Online (with Easy Steps)


Similar Readings


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

How to Calculate Trend Analysis in Excel

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.

How to Calculate Trend Analysis in Excel

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.

How to Calculate Trend Analysis in Excel

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.

How to Calculate Trend Analysis in Excel

Finally, you will get the result in Percentage form.

Read More: How to Calculate Trend Percentage in Excel (With Easy Steps)


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)

How to Calculate Trend Analysis in Excel

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 Month 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)

How to Calculate Trend Analysis in Excel

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.

Read More: How to Add Multiple Trendlines in Excel (With Quick Steps)


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 2nd method.

Practice Section

Now, you can practice the explained method by yourself.

How to Calculate Trend Analysis in Excel


Conclusion

I hope you found this article helpful. Here, I have explained 3 methods of How to Calculate Trend Analysis in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo