MO-211 Expert Prep
Module 4 · Lesson 4.10

Trendlines and Forecasting on Charts

Edit on GitHub

A trendline is a regression line overlaid on a chart series — it summarizes the direction of the data and, optionally, projects it forward (or backward) by N periods. On the MO-211 exam you should be able to add a trendline of any type, configure its forecast horizon, and display its equation and R-squared value.

Part 1: Inserting a Trendline

  1. Click the chart, then click the specific series you want to analyze.
  2. Click the Chart Elements (+) icon to the right of the chart.
  3. Tick the Trendline checkbox.
  4. Click the arrow next to Trendline > More Options… to open the Format Trendline pane.
NOTE
If the chart contains multiple series, Excel may prompt you to pick which series to fit. Trendlines belong to one series at a time — add a second trendline if you need to fit another.

Part 2: Trendline Types

Pick the type that matches the shape of your data, not the one that gives the highest R-squared.

Type Shape Use When Restrictions
Linear Straight line. Constant rate of change (most common default). None.
Exponential Curve that accelerates. Compound growth/decay (population, viral spread). Source values must be strictly positive — no zeros or negatives.
Logarithmic Rapid change that levels off. Diminishing returns (learning curves, saturation). None on the Y values; X values must be positive.
Polynomial Curve with peaks/valleys. Data that rises and falls (seasonal, parabolic). Pick Order 2-6; higher order = more wiggles, more overfit risk.
Power Curve passing through the origin. Physics/engineering ratios, area-vs-volume relationships. All X and Y values must be positive.
Moving Average Smoothed line lagging the data. Reducing noise to see the underlying trend. Pick Period 2-N; not a regression — no equation/R² available.
TIP
Polynomial **Order 2** is a parabola; **Order 3** is an S-curve; orders 4-6 chase noise. If the prompt says "fit a curve to seasonal data," start at Order 2 and only raise it if explicitly asked.

Part 3: Format Trendline Pane

The Format Trendline pane (right-click the trendline > Format Trendline) is where every adjustment happens.

Trendline Name

  • Automatic — Excel labels it "Linear (Series 1)" etc.
  • Custom — type your own legend label (e.g., Q1-Q4 Forecast).

Forecast

  • Forward: project the trendline N periods beyond the last data point. Excel extends the line into empty plot area.
  • Backward: project the trendline N periods before the first data point.
Forward: 3   →   trendline extends 3 periods past the last bar/point
Backward: 1  →   trendline extends 1 period before the first bar/point

Set Intercept

Force the trendline to pass through a specific Y value at X = 0. Common uses: * Set Intercept = 0 for a Linear or Polynomial fit through the origin (e.g., regression where you know zero input = zero output). * Disabled for Logarithmic, Power, Exponential, and Moving Average (mathematically inapplicable).

Display Equation on Chart

Tick to print the regression equation directly on the plot, e.g., y = 0.42x + 18.6. Useful when the prompt asks "show the trendline formula."

Display R-Squared Value on Chart

Tick to print the coefficient of determination (R²), a 0-1 number measuring how well the line fits the data. Closer to 1 = tighter fit. Not available for Moving Average.

TIP
"Display Equation" and "Display R-squared" are independent checkboxes — tick both for the full statistical readout the exam often asks for.

Part 4: When NOT to Use a Trendline

Trendlines are powerful, but they are easy to misuse:

  • Categorical X-axis (e.g., Region: North/South/East/West) — there is no meaningful "next" category to project to.
  • Very few data points (3-4) — any line will fit, but the R² is meaningless.
  • Irregular time gaps — trendlines assume even spacing on the X axis. Daily data with random missing days will project incorrectly.
  • Mixed regimes — if the data clearly changed behavior partway through (a price increase, a launch event), one trendline across both regimes hides the real story. Split into two series instead.

Part 5: Trendlines vs. Forecast Sheet

A chart trendline is a visual approximation. For real forecasting on a time-series, pair it with a Forecast Sheet (covered in lesson 3.10):

Chart Trendline Forecast Sheet
Drawn on top of an existing chart. Generates a new sheet with formulas + chart.
One regression curve, no confidence interval. Uses AAA-ETS algorithm with confidence bounds.
Static — does not update if you change source. Built on FORECAST.ETS formulas that recalc.
Good for visual narrative. Good for actual numeric forecasts you'll cite.
NOTE
The exam may ask for either. "Add a trendline" = chart workflow above. "Create a forecast" = `Data` tab > **Forecast Sheet**.

Technical Checklist for the Exam

Task Location
Add a trendline Chart > Chart Elements (+) > tick Trendline
Pick type Chart Elements (+) > Trendline arrow > More Options > pick type
Forward forecast Format Trendline > Forecast > Forward = N
Show equation Format Trendline > tick Display Equation on chart
Show R² Format Trendline > tick Display R-squared value on chart
Polynomial order Format Trendline > Polynomial > Order = 2-6
Moving Average period Format Trendline > Moving Average > Period = N
Set intercept Format Trendline > tick Set Intercept > type Y value

Official Resources: