How to Perform Trend Analysis in Excel (With Example)

Trend analysis is used to summarize the historical trend (or “pattern”) of data and forecast future values.

The following step-by-step example shows how to perform trend analysis in Excel.

Step 1: Create the Data

First, let’s create the following dataset in Excel that shows the total sales made by a company during 15 consecutive years:

Step 2: Create a Scatter Plot

Next, we will create a scatter plot to visualize the sales values over the years.

To do so, highlight the cell range A2:B16, then click the Insert tab along the top ribbon, then click the Insert Scatter icon in the Charts group:

The following scatter plot will appear:

The x-axis displays the years and the y-axis displays the sales for each year.

Step 3: Add a Trendline

Next, click anywhere on the scatter plot to make the chart active.

Then click the tiny green plus sign ( + ) in the top right corner of the chart and click the box next to Trendline:

In the Format Trendline panel that appears on the right side of the screen, select the button next to Linear, then check the box at the bottom next to Display Equation on chart:

A linear trendline along with a trendline equation will be displayed on the scatter plot:

Step 4: Interpret the Trendline

We can see that the trendline equation is:

y = 4.9071x + 136.21

This tells us that for each additional year, the expected total sales increases by 4.9071.

Since this value is positive, it tells us that sales (on average) increase over time.

We can also use this equation to predict the total sales for a given year in the future, assuming the trend holds.

For example, we can plug in the value 20 for x in the trendline equation to predict what the sales will be in year 20 for this company:

  • y = 4.9071x + 136.21
  • sales = 4.9071(20) + 136.21
  • sales = 234.352

Using this equation, we predict that the total sales in year 20 will be 234.253 units.

Note: We used a linear trendline for the trend analysis in this example, but you can add a more complex trendline by clicking a different option under Trendline Options within the Format Trendline panel.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: How to Add One Trendline for Multiple Series
Excel: How to Find Trendline Equation Without Chart
Excel: How to Find the Slope of a Trendline

Leave a Reply

Your email address will not be published. Required fields are marked *