How to Calculate Trend Analysis in Excel – 3 Easy Methods

 

 

The following dataset contains three columns: Month, Cost, and Sales.

How to Calculate Trend Analysis in Excel


Method 1. Using the TREND Function to Calculate Trend Analysis in Excel

This is the sample data. There are two columns: Month and Sales.

Steps:

  • Select a different cell (D5, here) to calculate the Trend analysis.
  • Enter this formula.
=TREND(C5:C10,B5:B10)

How to Calculate Trend Analysis in Excel

TREND will return a value in a linear way with the given points using the least square method. In this function,

    • C5:C10 is the known dependent variable, y.
    • B5:B10 is the known independent variable, x.
  • Press ENTER or CTRL+SHIFT+ENTER.

This is the output.

To forecast Sales for the Months of July and August:

  • Select a different cell (D11, here) to calculate the Trend analysis.
  • Enter this formula.
=TREND(C5:C10,B5:B10,B11:B12)

How to Calculate Trend Analysis in Excel

Here:

    • C5:C10 is the known dependent variable, y.
    • B5:B10 is the known independent variable, x.
    • B11:B12 is the new independent variable, x.
  • Press ENTER.

This is the output.

Read More: How to Add Trendline in Excel Online


Method 2 – Using the Excel Charts Group

Steps:

  • Select the data. Here, C4:D10.
  • Go to the Insert tab.

  • Select the Charts group and click 2-D Line >>Select a feature (Line with Markers, here).

How to Calculate Trend Analysis in Excel

  • Select the chart.
  • In Chart Design >> choose Select Data.

In the Select Data Source dialog box:

  • Select Edit to include Axis Labels.

How to Calculate Trend Analysis in Excel

In the Axis Labels dialog box:

  • Select the Axis label range. Here, B5:B10.
  • Click OK.

  • Click OK on the Select Data Source box.

The data chart will be displayed.

How to Calculate Trend Analysis in Excel

  • Click the + icon.
  • In Trendline >> select Linear Forecast.

In the Add Trendline dialog box:

  • Select Cost.
  • Click OK.

How to Calculate Trend Analysis in Excel

The forecast Trendline for Cost is displayed.

Apply the same process to find the Trendline for Sales.

  • Click the + icon.
  • In Trendline >> select Linear Forecast.

How to Calculate Trend Analysis in Excel

In the Add Trendline dialog box.

  • Select Sales.
  • Click OK.

The forecast Trendline for Sales is displayed.

How to Calculate Trend Analysis in Excel

 

  • Click the Trendline you want to format.
  • Choose Trendline Options. Here, line width was changed.

This is the output.

How to Calculate Trend Analysis in Excel


Method 3 – Applying a Generic Formula to Calculate Trend Analysis

The following dataset contains two columns: Year and Sales.

Steps:

  • Select a different cell (D6, here) to calculate the Change in Amount.
  • Enter this formula.
=C6-C5

How to Calculate Trend Analysis in Excel

In this formula a simple subtraction (current year 2013- previous year 2012) is applied to get the Change in Amount.

  • Press ENTER to see the value in the Change in Amount column.
  • Drag the Fill Handle to AutoFill the rest of the cells (D7:D10).

This is the output.

How to Calculate Trend Analysis in Excel

To see the Percentage Change:

  • Select a different cell (E6, here) to calculate the Percentage Change.
  • Enter this formula.
=D6/C5

In this formula, a simple division is applied (Change in Amount by the previous year 2012) to get the Percentage Change.

  • Press ENTER to see the value in the Percentage Change column.
  • Drag the Fill Handle to AutoFill the rest of the cells (E7:E10).

How to Calculate Trend Analysis in Excel

This is the output.

  • Select the range E5:E10.
  • In the Home tab >> select Number and choose Percentage %.

How to Calculate Trend Analysis in Excel

The result is displayed in Percentage.


Calculating Trend Analysis for Multiple Sets of Variables

Steps:

  • Select a different cell (E5, here) to calculate the Trend analysis.
  • Enter this formula.
=TREND(D5:D10,B5:C10)

How to Calculate Trend Analysis in Excel

TREND will return a value in a linear way with the given points using the least square method. In this function,

    • D5:D10 is the known dependent variable, y.
    • B5:C10 is the known independent variable, x.
  • Press ENTER.

This is the output.

To forecast the Sales for the months of July and August:

  • Select a different cell (E11, here) to calculate the Trend analysis of the forecast value.
  • Enter this formula.
=TREND(D5:D10,B5:C10,B11:C12)

How to Calculate Trend Analysis in Excel

    • D5:D10 is the known dependent variable, y.
    • B5:C10 is the known independent variable, x.
    • B11:C12 is the new independent variable, x.
  • Press ENTER.

This is the output.


Practice Section

Now, you can practice.

How to Calculate Trend Analysis in Excel


Download Practice Workbook

You can download the practice workbook here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo