Categories
Design

How to work with trendlines in microsoft excel charts

This example teaches you how to add a trendline to a chart in Excel.

1. Select the chart.

2. Click the + button on the right side of the chart, click the arrow next to Trendline and then click More Options.

How to work with trendlines in microsoft excel charts

The Format Trendline pane appears.

3. Choose a Trend/Regression type. Click Linear.

4. Specify the number of periods to include in the forecast. Type 3 in the Forward box.

5. Check “Display Equation on chart” and “Display R-squared value on chart”.

How to work with trendlines in microsoft excel charts

How to work with trendlines in microsoft excel charts

Explanation: Excel uses the method of least squares to find a line that best fits the points. The R-squared value equals 0.9295, which is a good fit. The closer to 1, the better the line fits the data. The trendline predicts 120 sold Wonka bars in period 13. You can verify this by using the equation. y = 7.7515 * 13 + 18.267 = 119.0365.

6. Instead of using this equation, you can use the FORECAST.LINEAR function in Excel. This function predicts the same future values.

How to work with trendlines in microsoft excel charts

7. The FORECAST.ETS function in Excel predicts a future value using Exponential Triple Smoothing, which takes into account seasonality.

How to work with trendlines in microsoft excel charts

Tip: visit our page about forecasting to learn more about these functions.

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel – 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I have monthly historical sales data. I want to predict future sales by month.

Strategy: You can use the least-squares method to fit the sales data to a trendline. Excel offers a function called LINEST that will calculate the formula for the trendline.

How to work with trendlines in microsoft excel charts

    Forecast future data.

You might remember from math class that a trendline is represented by this formula: y = mx + b

In this example, y is the revenue for the month, m is the slope of the line, x is the month number, and b is the y-intercept. If you were to look at the data, you might guess that the prediction for a given month is $10,000 + Month number x $400. In this case, the value for b would be 10,000, and the value for m would be 400. This is just my wild guess; Excel can calculate the number exactly.

LINEST is a very special function. Instead of returning one number, it actually returns two (or more) numbers as the result. If you select a single cell and enter =LINEST(C2:C35), it will return a single number, which is of no help. Entering the formula the wrong way returns a single answer of 204.8133. The first time you do this, you might wonder how the number 204.81 could describe a line.

It turns out that Excel really wants to return two numbers from the function. Here’s the trick:

  1. Select two cells that are side by side.
  2. Type the function in the first cell. After you type the closing parenthesis, press Ctrl+Shift+Enter. Excel returns both the slope and the y-intercept.
  • The results appear in 2 cells.
    1. Add a Prediction column. In column D, enter a formula to calculate the predicted sales trendline. The formula is the intercept in F2 plus the slope in E2 times this row’s month number.

    How to work with trendlines in microsoft excel charts

    Use the results of the ­LINEST to predict sales.

    You will now be able to graph columns B:D to show how well the prediction matches the historical actuals.

    Additional Details: When the data along one axis of your data contains dates, it is best to delete the heading in the upper-left corner of your data set before creating the chart. You clear cell B1, select B1:D47, and select Insert, Line, Line with Markers. As shown below, the resulting chart shows that the predicted trendline comes fairly close to the actuals. You can also see that the formula predicts that you will be selling almost $20,000 per month one year from now.

    How to work with trendlines in microsoft excel charts

    Plot actuals vs. forecast to see if the sales match a trend.

    Gotcha: When you select two cells for the LINEST function, they must be side by side. If you try to select two cells that are one above the other, you will just get two copies of the slope.

    Alternate Strategy: A different method is to use the INDEX function to pluck a specific answer from the array.

    =INDEX(LINEST(C2:C35),1,1) will return the first element from the array. This is the slope.

    =INDEX(LINEST(C2:C35),1,2) will return the second element from the array. This is the y-intercept.

    By How to work with trendlines in microsoft excel chartsMadhuri Thakur

    How to work with trendlines in microsoft excel charts

    Excel Trendline (Table of Contents)

    Trendline in Excel

    The trendline in Excel is the part of all the Charts available in the Charts section under the Insert menu tab, which is used to see the trend in the plotted data over any chart. This helps us to see whether there is an increase or decrease in data values. This is also helpful in seeing at which point the data is going up or down. It can be used in any type of chart (Except Pie, Donut). To apply trendline, first create a chart using the option available in the Charts section, then click right on any data on the chart and select Add Trendline. We can also insert Trendlines using the Chart Elements list, which can be accessed from the plus sign at the right top corner or any chart.

    Excel functions, formula, charts, formatting creating excel dashboard & others

    Types of Trendline in Excel

    There is various type of trendlines in excel:

    1. Exponential trendline
    2. Linear trendline
    3. Logarithmic trendline
    4. Polynomial trendline
    5. Power trendline
    6. Moving Average trendline

    Now we will explain one by one the types of Trendline in excel.

    Exponential:

    This trendline is mostly used where the data rise or falls constantly. Avoid this trendline if the data contains zero or negative values.

    Linear:

    This trendline is useful for creating a straight line to show the increasing or decreasing of data values at a straight line.

    Logarithmic:

    This trendline is useful where the data suddenly increases or decreases and then becomes stable at some point. In this trendline, you can include the negative values in the dataset.

    Polynomial:

    This trendline is useful where you see gain or loss in the business. The degree of this trendline shows the number of fluctuations in the data.

    Power:

    This trendline is useful where the dataset is used to compare results that increase at a fixed rate. Avoid this trendline if your dataset contains Zero or negative values.

    Alan Murray has worked as an Excel trainer and consultant for twenty years. On most days, he can be found teaching Excel in a classroom or seminar. Alan gets a buzz from helping people improve their productivity and working lives with Excel. Read more.

    How to work with trendlines in microsoft excel charts

    You can add a trendline to a chart in Excel to show the general pattern of data over time. You can also extend trendlines to forecast future data. Excel makes it easy to do all of this.

    A trendline (or line of best fit) is a straight or curved line which visualizes the general direction of the values. They’re typically used to show a trend over time.

    In this article, we’ll cover how to add different trendlines, format them, and extend them for future data.

    How to work with trendlines in microsoft excel charts

    Add a Trendline

    You can add a trendline to an Excel chart in just a few clicks. Let’s add a trendline to a line graph.

    Select the chart, click the “Chart Elements” button, and then click the “Trendline” checkbox.

    How to work with trendlines in microsoft excel charts

    This adds the default Linear trendline to the chart.

    There are different trendlines available, so it’s a good idea to choose the one that works best with the pattern of your data.

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

    How to work with trendlines in microsoft excel charts

    Some of the key trendline types include:

    • Linear: A straight line used to show a steady rate of 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 trendline.
    • Logarithmic: This type is best used when the data increases or decreases quickly, and then levels out.
    • Moving Average: To smooth out the fluctuations in your data and show a trend more clearly, use this type of trendline. It uses a specified number of data points (two is the default), averages them, and then uses this value as a point in the trendline.

    To see the full complement of options, click “More Options.”

    How to work with trendlines in microsoft excel charts

    The Format Trendline pane opens and presents all trendline types and further options. We’ll explore more of these later in this article.

    How to work with trendlines in microsoft excel charts

    Choose the trendline you want to use from the list, and it will be added to your chart.

    Add Trendlines to Multiple Data Series

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

    If you want to apply a trendline to only one of the data series, right-click on the desired item. Next, select “Add Trendline” from the menu.

    How to work with trendlines in microsoft excel charts

    The Format Trendline pane opens so you can select the trendline you want.

    In this example, a Moving Average trendline has been added to the charts Tea data series.

    How to work with trendlines in microsoft excel charts

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

    How to work with trendlines in microsoft excel charts

    You can add a trendline to multiple data series.

    In the following image, a trendline has been added to the Tea and Coffee data series.

    How to work with trendlines in microsoft excel charts

    You can also add different trendlines to the same data series.

    In this example, Linear and Moving Average trendlines have been added to the chart.

    How to work with trendlines in microsoft excel charts

    Format Your Trendlines

    Trendlines are added as a dashed line and match the color of the data series to which they’re assigned. You might want to format the trendline differently—especially if you have multiple trendlines on a chart.

    Open the Format Trendline pane by either double-clicking the trendline you want to format or by right-clicking and selecting “Format Trendline.”

    How to work with trendlines in microsoft excel charts

    Click the Fill & Line category, and then you can select a different line color, width, dash type, and more for your trendline.

    In the following example, I changed the color to orange, so it’s different from the column color. I also increased the width to 2 pts and changed the dash type.

    How to work with trendlines in microsoft excel charts

    Extend a Trendline to Forecast Future Values

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

    From the Format Trendline pane, click the Trendline Options category, and then type a value in the “Forward” box under “Forecast.”

    How to work with trendlines in microsoft excel charts

    Display the R-Squared Value

    The R-squared value is a number that indicates how well your trendline corresponds to your data. The closer the R-squared value is to 1, the better the fit of the trendline.

    From the Format Trendline pane, click the “Trendline Options” category, and then check the “Display R-squared value on chart” checkbox.

    How to work with trendlines in microsoft excel charts

    A value of 0.81 is shown. This is a reasonable fit, as a value over 0.75 is generally considered a decent one—the closer to 1, the better.

    If the R-squared value is low, you can try other trendline types to see if they’re a better fit for your data.

    How to work with trendlines in microsoft excel charts

    You can add a trendline to a chart in Excel to show the general pattern of data over time. You can also extend trendlines to forecast future data. Excel makes it easy to do all of this.

    A trendline (or line of best fit) is a straight or curved line which visualizes the general direction of the values. They’re typically used to show a trend over time.

    In this article, we’ll cover how to add different trendlines, format them, and extend them for future data.

    How to work with trendlines in microsoft excel charts

    Add a Trendline

    You can add a trendline to an Excel chart in just a few clicks. Let’s add a trendline to a line graph.

    Select the chart, click the “Chart Elements” button, and then click the “Trendline” checkbox.

    How to work with trendlines in microsoft excel charts

    This adds the default Linear trendline to the chart.

    There are different trendlines available, so it’s a good idea to choose the one that works best with the pattern of your data.

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

    How to work with trendlines in microsoft excel charts

    Some of the key trendline types include:

    • Linear: A straight line used to show a steady rate of 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 trendline.
    • Logarithmic: This type is best used when the data increases or decreases quickly, and then levels out.
    • Moving Average: To smooth out the fluctuations in your data and show a trend more clearly, use this type of trendline. It uses a specified number of data points (two is the default), averages them, and then uses this value as a point in the trendline.

    To see the full complement of options, click “More Options.”

    How to work with trendlines in microsoft excel charts

    The Format Trendline pane opens and presents all trendline types and further options. We’ll explore more of these later in this article.

    How to work with trendlines in microsoft excel charts

    Choose the trendline you want to use from the list, and it will be added to your chart.

    Add Trendlines to Multiple Data Series

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

    If you want to apply a trendline to only one of the data series, right-click on the desired item. Next, select “Add Trendline” from the menu.

    How to work with trendlines in microsoft excel charts

    The Format Trendline pane opens so you can select the trendline you want.

    In this example, a Moving Average trendline has been added to the charts Tea data series.

    How to work with trendlines in microsoft excel charts

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

    How to work with trendlines in microsoft excel charts

    You can add a trendline to multiple data series.

    In the following image, a trendline has been added to the Tea and Coffee data series.

    How to work with trendlines in microsoft excel charts

    You can also add different trendlines to the same data series.

    In this example, Linear and Moving Average trendlines have been added to the chart.

    How to work with trendlines in microsoft excel charts

    Format Your Trendlines

    Trendlines are added as a dashed line and match the color of the data series to which they’re assigned. You might want to format the trendline differently—especially if you have multiple trendlines on a chart.

    Open the Format Trendline pane by either double-clicking the trendline you want to format or by right-clicking and selecting “Format Trendline.”

    How to work with trendlines in microsoft excel charts

    Click the Fill & Line category, and then you can select a different line color, width, dash type, and more for your trendline.

    In the following example, I changed the color to orange, so it’s different from the column color. I also increased the width to 2 pts and changed the dash type.

    How to work with trendlines in microsoft excel charts

    Extend a Trendline to Forecast Future Values

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

    From the Format Trendline pane, click the Trendline Options category, and then type a value in the “Forward” box under “Forecast.”

    How to work with trendlines in microsoft excel charts

    Display the R-Squared Value

    The R-squared value is a number that indicates how well your trendline corresponds to your data. The closer the R-squared value is to 1, the better the fit of the trendline.

    From the Format Trendline pane, click the “Trendline Options” category, and then check the “Display R-squared value on chart” checkbox.

    How to work with trendlines in microsoft excel charts

    A value of 0.81 is shown. This is a reasonable fit, as a value over 0.75 is generally considered a decent one—the closer to 1, the better.

    If the R-squared value is low, you can try other trendline types to see if they’re a better fit for your data.

    This topic covers the different trendline options that are available in Office.

    Use this type of trendline to create a best-fit straight line for simple linear data sets. Your data is linear if the pattern in its data points looks like a line. A linear trendline usually shows that something is increasing or decreasing at a steady rate.

    A linear trendline uses this equation to calculate the least squares fit for a line:

    where m is the slope and b is the intercept.

    The following linear trendline shows that refrigerator sales have consistently increased over an 8-year period. Notice that the R-squared value (a number from 0 to 1 that reveals how closely the estimated values for the trendline correspond to your actual data) is 0.9792, which is a good fit of the line to the data.

    How to work with trendlines in microsoft excel charts

    Showing a best-fit curved line, this trendline is useful when the rate of change in the data increases or decreases quickly and then levels out. A logarithmic trendline can use negative and positive values.

    A logarithmic trendline uses this equation to calculate the least squares fit through points:

    where c and b are constants and ln is the natural logarithm function.

    The following logarithmic trendline shows predicted population growth of animals in a fixed-space area, where population leveled out as space for the animals decreased. Note that the R-squared value is 0.933, which is a relatively good fit of the line to the data.

    How to work with trendlines in microsoft excel charts

    This trendline is useful when your data fluctuates. For example, when you analyze gains and losses over a large data set. The order of the polynomial can be determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve. Typically, an Order 2 polynomial trendline has only one hill or valley, an Order 3 has one or two hills or valleys, and an Order 4 has up to three hills or valleys.

    A polynomial or curvilinear trendline uses this equation to calculate the least squares fit through points:

    where b and are constants.

    The following Order 2 polynomial trendline (one hill) shows the relationship between driving speed and fuel consumption. Notice that the R-squared value is 0.979, which is close to 1 so the line’s a good fit to the data.

    How to work with trendlines in microsoft excel charts

    Showing a curved line, this trendline is useful for data sets that compare measurements that increase at a specific rate. For example, the acceleration of a race car at 1-second intervals. You cannot create a power trendline if your data contains zero or negative values.

    A power trendline uses this equation to calculate the least squares fit through points:

    where c and b are constants.

    Note: This option is not available when your data includes negative or zero values.

    The following distance measurement chart shows distance in meters by seconds. The power trendline clearly demonstrates the increasing acceleration. Note that the R-squared value is 0.986, which is an almost perfect fit of the line to the data.

    How to work with trendlines in microsoft excel charts

    Showing a curved line, this trendline is useful when data values rise or fall at constantly increasing rates. You cannot create an exponential trendline if your data contains zero or negative values.

    An exponential trendline uses this equation to calculate the least squares fit through points:

    where c and b are constants and e is the base of the natural logarithm.

    The following exponential trendline is shows the decreasing amount of carbon 14 in an object as it ages. Note that the R-squared value is 0.990, which means the line fits the data almost perfectly.

    How to work with trendlines in microsoft excel charts

    This trendline evens out fluctuations in data to show a pattern or trend more clearly. A moving average uses a specific number of data points (set by the Period option), averages them, and uses the average value as a point in the line. For example, if Period is set to 2, the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, etc.

    A moving average trendline uses this equation:

    The number of points in a moving average trendline equals the total number of points in the series, minus the number you specify for the period.

    In a scatter chart, the trendline is based on the order of the x values in the chart. For a better result, sort the x values before you add a moving average.

    The following moving average trendline shows a pattern in the number of homes sold over a 26-week period.

    How to work with trendlines in microsoft excel charts

    Important: Beginning with Excel version 2005, Excel adjusted the way it calculates the R 2 value for linear trendlines on charts where the trendline intercept is set to zero (0). This adjustment corrects calculations that yielded incorrect R 2 values and aligns the R 2 calculation with the LINEST function. As a result, you may see different R 2 values displayed on charts previously created in prior Excel versions. For more information, see Changes to internal calculations of linear trendlines in a chart.

    Need more help?

    You can always ask an expert in the Excel Tech Community or get support in the Answers community.

    Written by co-founder Kasper Langmann, Microsoft Office Specialist.

    Excel trendline is a great way to visualize the general trend in your data.

    In total, there are 6 different types of trendlines in Excel you can use depending on the type of data you have .

    Furthermore, you can add more than 1 type of trendline!

    Fortunately, it’s quite easy to do!

    In this article, we’ll show you how you can add a trendline in Excel. Let’s get it started!

    *This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

    Table of Content

    Trendlines in Excel

    A trendline, also called “a line of best fit” , is an analytical tool that is used to visualize and represent the behavior of a data set to see if there’s a pattern .

    In Excel, most charts allow trendlines except for pie, radar, 3-D, stacked charts and other similar ones.

    As stated earlier, there are 6 different types of trendlines:

    1. Linear: From the name itself, this is a best-fit straight line usually used with simple linear data sets. Results are easy to interpret as they’re either increasing or decreasing at a steady rate.
    2. Logarithmic: This one is commonly used when there are quick changes in the data which levels out. It’s a best-fit curved line and uses a negative and/or positive values.
    3. Polynomial: A polynomial trendline is also a curved line used to visualize data fluctuations. The fluctuations are seen as a hill or valley in the trendline.
    4. Power: A power trendline is another curve line used with data sets comparing values that increase at a specific rate. This trendline is only possible for positive values.
    5. Exponential: An exponential trendline is also a curve line that’s useful for data sets with values that rise or fall at increasingly higher rates. Like a power trendline, you can’t use an exponential trendline with negative or zero values.
    6. Moving Average : This trendline is useful in forecasting through moving average. This shows a clear pattern or trend.

    eBook includes PDF, ePub and Kindle version

    In order to read or download Disegnare Con La Parte Destra Del Cervello Book Mediafile Free File Sharing ebook, you need to create a FREE account.

    Download Now!

    eBook includes PDF, ePub and Kindle version

    We have made it easy for you to find a PDF Ebooks without any digging. And by having access to our ebooks online or by storing it on your computer, you have convenient answers with Excel Manual Trendline . To get started finding Excel Manual Trendline , you are right to find our website which has a comprehensive collection of manuals listed.
    Our library is the biggest of these that have literally hundreds of thousands of different products represented.

    Finally I get this ebook, thanks for all these Excel Manual Trendline I can get now!

    cooool I am so happy xD

    I did not think that this would work, my best friend showed me this website, and it does! I get my most wanted eBook

    wtf this great ebook for free?!

    My friends are so mad that they do not know how I have all the high quality ebook which they do not!

    It’s very easy to get quality ebooks 😉

    so many fake sites. this is the first one which worked! Many thanks

    wtffff i do not understand this!

    Just select your click then download button, and complete an offer to start downloading the ebook. If there is a survey it only takes 5 minutes, try any survey which works for you.

    lol it did not even take me 5 minutes at all! XD

    eBook includes PDF, ePub and Kindle version

    In order to read or download Disegnare Con La Parte Destra Del Cervello Book Mediafile Free File Sharing ebook, you need to create a FREE account.

    Download Now!

    eBook includes PDF, ePub and Kindle version

    We have made it easy for you to find a PDF Ebooks without any digging. And by having access to our ebooks online or by storing it on your computer, you have convenient answers with Excel Manual Trendline . To get started finding Excel Manual Trendline , you are right to find our website which has a comprehensive collection of manuals listed.
    Our library is the biggest of these that have literally hundreds of thousands of different products represented.

    Finally I get this ebook, thanks for all these Excel Manual Trendline I can get now!

    cooool I am so happy xD

    I did not think that this would work, my best friend showed me this website, and it does! I get my most wanted eBook

    wtf this great ebook for free?!

    My friends are so mad that they do not know how I have all the high quality ebook which they do not!

    It’s very easy to get quality ebooks 😉

    so many fake sites. this is the first one which worked! Many thanks

    wtffff i do not understand this!

    Just select your click then download button, and complete an offer to start downloading the ebook. If there is a survey it only takes 5 minutes, try any survey which works for you.

    lol it did not even take me 5 minutes at all! XD