قالب وردپرس درنا توس
Home / Tips and Tricks / Working with trendlines in Microsoft Excel charts

Working with trendlines in Microsoft Excel charts



  Micorsoft Excel logo.

You can add a trend line to a chart in Excel to see the overall pattern of the data over time. You can also extend trendlines to predict future data. All this is easy to do in Excel.

A trend line (or best fit line) is a straight or curved line that illustrates the general direction of the values. They are usually used to indicate a trend over time.

This article explains how to add, format, and expand various trendlines for future data.

 Excel chart with a linear representation Add trendline

Add a trendline

With a few clicks, you can add a trendline to an Excel spreadsheet. Let's add a trend line to a line chart.

Select the chart, click the Chart Items button, and then click the Trend Line check box.

 Click

By default, linear trend line to the graph.

There are several trendlines available. Therefore, it is best to choose the one that best fits the pattern of your data.

Click the arrow next to the "Trendline" option to use other trendlines, including Exponential or Moving Average.

 Click the arrow for more trendline options.

Major trendline types include:

  • Linear: A straight line that is used for show a steady increase or decrease in values.
  • Exponential: This trendline visualizes an increase or decrease in values ​​at an increasingly higher rate. The line is more curved than a linear trend line.
  • Logarithmic: This type is best used when the data increases or decreases rapidly and then equalizes.
  • Moving Average: Compensating Fluctuations Use this type of trend line in your data to make a trend clearer. It uses a fixed number of data points (two by default), averages them, and then uses that value as a point in the trend line.

To see the full range of options, click More Options.

 Click

The Format Trendline window opens, displaying all trendline types and other options. We will examine more of this later in this article.

 Full Excel chart

Select the trend line to use from the list and it will be added to your chart.

Adding Trendlines to Multiple Data Series

In the first example, the line chart contained only one data series, but the following column chart contained two.

If you want to apply a trend line to only one of the data series, this applies to the desired item. Then select "Add Trendline" from the menu.

 Select

. The Format Trendline window opens so you can select the trend line you want.

In this example, a trend line with moving average has been added to the charts. Tea data series.

 Exponential trend line for chart data series.

If you click the Chart Items button to add a trendline without first selecting a series, Excel asks you to which series you want to add the trendline.

 Specify to which data series the trend line should be added.

You can add a trend line to multiple data series.

In The following figure adds a trend line to the Tea and Coffee data series.

 Several trend lines in a diagram.

You can also add different trend lines to the same data series.

In this example, linear and moving average trend lines have been added to the chart.

[1 9459038] Linear and moving average trend lines in one chart. "width =" 650 "height =" 399 "src =" /pagespeed_static/1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "/>

Format your trendlines.

Trendlines are added as a dashed line and match the color of the series they are assigned to. You may want to format the trendline differently, especially if you have multiple Have trendlines in a chart.

Open the Format Trendline window by either double-clicking the trendline you want to format or right-clicking and choosing Format Trendline.

 Select [19659043ClickontheFillandLinecategoryandthenselectadifferentlinecolorwidthlinestyleandmoreforyourtrendline</p>
<p>  In the example below, I has the color in changed to orange, so it differs from the column color. I also increased the width to 2 points and changed the stroke type. </p>
<p>  <img class=

Extend a Trendline to Predicted Future Values ​​

A very cool feature of trendlines in Excel is the option to extend it to the future. This gives us an idea of ​​what future values ​​might be based on the current data trend.

In the Format Trendline area, click the Trendline Options category and enter a value in the Next field under Forecast. [19659003]   Click

Show R-Square Value

The R-squared value is a number that indicates how well your trend line matches your data. The closer the R-squared value is to 1, the better the trendline fits.

In the Format Trendline area, click the Trendline Options category and select the Show R Square Value in Diagram check box.

 Click

. A value of 0.81 is displayed. This is a reasonable match, as a value above 0.75 is generally considered decent – the closer to 1, the better.

If the R-squared value is low, you can try other trendline types to see if this is the case to better fit your data.


Source link