How to Add a Trendline to a Chart in Microsoft Excel 2007

How to Add a Trendline to a Chart in Microsoft Excel 2007

Trendlines in Excel 2007

A trendline is a graphical object used to represent the general trend of a set of data. Despite the name, a trendline is not necessarily a straight line. It may also take the form of an exponential, logarithmic, polynomial, or other type of curve.

Not only can a trendline depict the historical trend of data on a chart, but it can also be used to forecast future data based on past history. In the instructions that follow, we’ll look at how to use a trendline in an Excel chart to do these two things.

How to Add a Trendline to a Chart in Excel 2007

In these instructions, we will be using the column chart created in Part 2 of this series.

Step 1: Open the Excel spreadsheet containing the chart to which you would like to add a trendline. Click on the chart to select it.

Step 2: Open the Layout tab under Chart Tools on the Excel ribbon. Click on the Trendline button in the Analysis section of the ribbon to expand the box containing options for the trendline. (Click the image for a larger view.)

If you don’t see the type of trendline you are looking for here, you can choose More Trendline Options for a broader selection. For now, we’ll pick the standard Linear Trendline.

Step 3: If you have more than one data series represented in your chart, a new window entitled Add Trendline will appear on your screen. Here, you’ll be asked to specify the data series to which you want to add a trendline. In this example, we’ll select Countrywide.

The screenshot below shows how the trendline would initially appear on the chart.

Step 4 (optional): Although it’s not necessary, many times you’ll want to adjust the style and appearance of the trendline. For instance, we’d like the trendline in this chart to be a little wider. To modify this, right-click on the trendline and select Format Trendline.

In the Format Trendline window that appears, select Line Style. The width of the line can be adjusted either by manually typing in a new value or by using the arrows on the right side of the box next to Width.

This wider trendline can be seen in the screenshot below.

Adding a Forecast Trendline

If you want your trendline to also include predictions for future values based on historical data, you can choose to create a forecast trendline instead. The steps for doing this are identical to the ones described above except that, in Step 2, you would choose Linear Forecast Trendline instead of Linear Trendline.

This would yield the following results in the chart.

By default, this type of trendline will forecast two periods into the future. If you want to change the number of forecast periods and/or include “backward” periods, right-click on the trendline and select Format Trendline. When the Format Trendline window appears, click on Trendline Options. Near the bottom of this window (see the screenshot below) is an area where you can adjust the number of forecast periods.

Other aspects of the forecast trendline can be formatted in the same manner as any other trendline.

Looking for more tips and tricks? Be sure to browse through the other Microsoft Excel chart and graph tutorials found on Bright Hub’s Windows Channel. More are being added all the time, so bookmark us and check back often!

This post is part of the series: Bar and Column Charts in Microsoft Excel 2007

This series includes tips and tricks for working with bar and column charts in Microsoft Excel 2007. We’ll explain what they are, when to use them, and how to create them.