Trend lines and regression: Finding the story hidden in your data
Trend Lines: Finding the Story in Your Data
Learn to draw and interpret trend lines that reveal the pattern behind the numbers
Sarah pulls up a scatter plot showing each weekend's sales for the past 3 months. The dots look scattered - some high, some low - but she notices a general tendency for the dots to creep upward over time. She draws a straight line through the middle that represents this general tendency. This is a trend line.
Trend Line Definition
A trend line is a straight line drawn through a scatter plot that represents the general direction data is moving. It summarizes "on average" where the data points fall.
The key insight: trend lines don't tell you what will happen - they tell you what has been happening. If the pattern holds, you can use it to make reasonable predictions.
Sarah looks at her trend line and notices it's not flat - it tilts upward. The slopeof a trend line tells you how fast the data is changing.
Positive Slope
Line goes up from left to right. As time increases, sales increase.
Café example: Each month, average sales go up by about $400
→ Month 1: $8,000
→ Month 2: $8,400
→ Month 3: $8,800
Negative Slope
Line goes down from left to right. As time increases, sales decrease.
Café example: Each month, average sales drop by about $200
→ January: $10,000
→ February: $9,800
→ March: $9,600
Zero Slope
Line is perfectly flat. No clear increase or decrease over time.
Café example: Sales stay around $9,000 every month
→ January: $9,000
→ February: $9,100
→ March: $8,900
What Slope Tells You
Slope is the rate of change. For each unit of time (month, week), the outcome changes by this amount.
Business use: "If this pattern continues, sales will increase/decrease by [slope] each [time unit]"
Sarah notices that in some months, the dots are very close to her trend line. In other months, the dots are scattered far from the line. This is fit - how closely the data follows the pattern.
High Fit (Close to 1.0)
Data points cluster tightly around the trend line. The pattern is consistent.
Café example: Sales vary predictably by about $200-300 each month
Predictions are more reliable when fit is high
Low Fit (Close to 0)
Data points are scattered widely around the trend line. The pattern is weak.
Café example: Sales jump from $8,000 to $12,000 to $7,500 - no clear pattern
Predictions are less certain when fit is low
Critical Understanding: Fit ≠ Quality
A low R-squared doesn't mean something is "wrong" with your data. It means the pattern is less consistent. A sales figure that varies wildly from month to month might still be a perfectly normal business - it just has more variability that can't be explained by time alone.
Fit tells you how much you can trust the prediction, not whether the business is good or bad.
Sarah wants to use her trend line to predict sales for next year. But here's the critical rule: predictions become less reliable the further you go from your known data.
The Danger Zone Rule
- Within data range: Most reliable - predicting for times similar to your data
- Slightly outside: Reasonable - a few months beyond the data
- Far outside: Unreliable - predicting years into the future
Reliable Prediction
Using 12 months of data to predict the next 1-2 months
"We have summer data, let's plan for late summer"
Unreliable Prediction
Using 12 months of data to predict 3+ years ahead
"Let's plan our 2028 budget based on 2024 sales"
Sarah can now read a trend line like a pro. When she looks at any scatter plot with a trend line, she asks three questions:
Sarah's Trend Line Checklist
1. Direction (Positive or Negative?)
Is sales going up or down over time?
2. Speed (What is the slope?)
How fast is it changing per time period?
3. Consistency (What is R-squared?)
How tightly do points cluster around the line?
With this checklist, Sarah can interpret any trend line and understand what it does - and doesn't - tell her about the business.
Why This Matters
Before building Excel forecasting tools, you need to understand what they're doing. A trend line is simply a visual summary of the pattern in your data. When you add Excel's FORECAST or TREND functions, they're doing the same math automatically. Understanding the logic first makes you a better analyst - you won't trust the numbers more than you should, and you'll know when to question the predictions.