How to Create a Trend Line Graph in Excel

A trend line graph in Excel can instantly reveal the bigger picture hidden within your data, showing you whether your sales are growing, your website traffic is dipping, or your key metrics are holding steady. It cuts through the noise of daily or weekly fluctuations to show you the overall direction. This guide will walk you through exactly how to create, customize, and interpret a trend line graph in Excel, step-by-step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Trend Line and Why is it Useful?

In simple terms, a trend line (often called a "line of best fit") is a straight or curved line through a set of data points on a graph that shows the general direction or pattern of that data. It essentially smooths out the ups and downs to give you a clearer view of the long-term story.

Imagine you're looking at your monthly sales figures. Some months are great, and others are a bit lower. By plotting this on a chart and adding a trend line, you can instantly see if your sales are generally:

• Trending Upwards: The line points up, indicating overall growth.
• Trending Downwards: The line points down, highlighting a potential decline you need to investigate.
• Staying Flat: The line is mostly horizontal, suggesting stability.

This is incredibly valuable for business analysis. Are the leads from your new marketing campaign trending up over the past quarter? Is your customer churn rate decreasing? A trend line makes answering these questions at a glance much simpler.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Get Your Data Ready for Excel

Before you can make a chart, you need clean, well-organized data. For a trend line that shows a pattern over time, you’ll typically need two columns:

1. A time period column (e.g., dates, months, quarters, years). This will be your horizontal axis (X-axis).
2. A data value column (e.g., sales, website sessions, number of new customers). This will be your vertical axis (Y-axis).

Your data should be arranged in a simple table like this:

Pro Tip: Ensure your dates or time periods are formatted correctly in Excel (e.g., as 'Date' format, not just 'General' text). This helps Excel properly order and plot the information on your chart.

Step 2: Create a Basic Line or Scatter Chart

Trend lines are added to existing charts, not created from scratch. The best chart types for trend analysis are line charts and scatter plots.

1. Select your data. Click and drag your cursor to highlight all the cells containing your data, including the headers (e.g., "Month" and "Revenue").
2. Insert the chart. Go to the Insert tab on Excel’s top ribbon. In the "Charts" section, you’ll see icons for different chart types. A Line Chart or Scatter (X, Y) Chart works perfectly. For data over evenly spaced time intervals like months, a line chart is very clear.

Excel will automatically generate a chart on your worksheet that plots your data points. Now, you have the foundation needed to add your trend line.

Step 3: Add the Trend Line to Your Chart

This is where the magic happens. Adding a trend line is just a few clicks away. Here are two easy ways to do it:

Method 1: The 'Chart Elements' Button

1. Select your chart. Simply click anywhere inside your newly created chart.
2. Click the green '+' icon. This icon for "Chart Elements" will appear in the top-right corner of your chart.
3. Check 'Trendline'. In the menu that appears, simply tick the checkbox next to Trendline. Excel will immediately add a default linear trend line to your chart.

Method 2: Right-Click on the Data Series

1. Select the data series. Right-click directly on the line or one of the data points in your chart.
2. Choose 'Add Trendline...'. Select this option from the context menu that pops up.

Whichever method you choose, a trend line will appear on your chart, and the Format Trendline pane will open on the right side of your screen. This is your command center for customizing the trend line.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Customize and Interpret Your Trend Line Graph

A default trend line is good, but a customized one is even better. The "Format Trendline" pane gives you powerful tools to refine your analysis.

Choosing the Right Trend Line Type

Excel offers several types of trend lines. Don't be intimidated by the names, for most business uses, you'll only need to know a couple of them.

• Linear: This is the default and most common type. It's a straight line that's best used when your data is increasing or decreasing at a relatively steady rate. Think consistent sales growth or a gradual decline in user engagement.
• Exponential: This is a curved line used when data values are rising or falling at an increasingly rapid rate. Classic examples include viral growth in social media followers or the initial adoption curve for a new product.
• Moving Average: This isn't a true mathematical 'best fit' line, but it’s fantastic for smoothing out highly volatile or "noisy" data. For example, if you're looking at daily website traffic, a 7-day moving average will smooth out the weekend dips and give you a clearer view of the week-over-week trend. You can set the 'Period' to specify how many data points it should average at a time.

Forecasting Future Trends

One of the most powerful features here is the ability to project your trend into the future. In the "Format Trendline" pane, you’ll see a Forecast section.

You can enter a number in the Forward box to extend the trend line beyond your existing data. For example, if your chart shows 12 months, entering '3' in the box will forecast the trend for the next three months.

Important Note: This is a simple projection based on past data. It’s a helpful guide but should not be treated as a guaranteed prediction of the future, as it doesn't account for market changes, seasonality, or other external factors.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Check How Well Your Trend Line Fits (R-squared Value)

How do you know if your trend line is actually a good representation of your data? That’s where the R-squared value comes in.

In the "Format Trendline" options, check the box for Display R-squared value on chart. An "R²" number will appear on your chart. In plain English, R-squared is a score from 0 to 1 that measures how closely the trend line matches your actual data points.

• An R² value close to 1 means the line is an excellent fit (e.g., 0.95).
• An R² value close to 0 means the line is a very poor fit (e.g., 0.15).

This helps you trust your analysis. If your linear R² value is low, you might try a different trend type (like Exponential) to see if it provides a better fit.

Final Thoughts

Creating a trend line graph in Excel is a straightforward process that transforms raw data into a clear, actionable visual story. By following these simple steps, you can quickly identify the patterns in your business metrics, make simple forecasts, and get a better handle on your performance over time.

While whipping up trend lines in Excel is perfect for single datasets, this manual process of exporting and updating reports becomes a time sink when your data is scattered across platforms like Google Analytics, Shopify, and your CRM. That's why we built Graphed, I wanted a way to automatically see these trends from all my live data sources in one place. Instead of spending my Monday mornings wrangling spreadsheets, I can simply ask in plain English, "Show me a trend line of revenue from Facebook Ads and Google Ads this quarter," and get an always-updated dashboard in seconds. You can try it out for free and see how easily you can get answers from all your data. Graphed