How to Get a Trend Line in Excel

Adding a trend line to an Excel chart is a powerful way to visualize patterns and predict future outcomes from your data. This simple visual tool, also known as a line of best fit, can quickly show you whether sales are growing, if website traffic is declining, or how two variables relate to each other. In this article, you’ll learn exactly how to add, format, and interpret trend lines for different chart types in Excel.

What is a Trend Line, Anyway?

A trend line is a straight or curved line added to a chart that illustrates the overall direction of the data points. Imagine you've plotted your monthly sales for the past year. Your sales probably fluctuated, with good months and bad months. A trend line cuts through that noise, showing you the general trajectory - is the overall trend going up, down, or staying flat?

Technically, Excel calculates this "line of best fit" using a statistical method called linear regression. It tries to draw a line that comes as close as possible to all the individual data points in your series, minimizing the total distance from each point to the line. But you don't need to be a statistician to use them. For most of us, it’s a quick-and-easy way to spot patterns that aren’t immediately obvious from looking at raw numbers in a spreadsheet.

Common uses for trend lines include:

• Identifying trends: Are your key metrics growing or declining over time?
• Forecasting: Extend the trend line into the future to make educated guesses about what might happen next.
• Finding relationships: On a scatter plot, a trend line can show the correlation between two different variables, like ad spend and revenue.

How to Add a Trend Line in Excel: Step-by-Step

Let's walk through adding a trend line to a typical dataset, like monthly sales figures. Trend lines work best on charts that show data over time or compare two numeric variables, such as line charts, column charts, bar charts, and scatter plots. They cannot be used on chart types like pie charts or 3D charts.

Step 1: Create Your Chart

First, you need data organized in your spreadsheet and a chart to display it. For our example, we have two columns: one for the "Month" and another for "Sales."

1. Select your data range, including the headers (e.g., A1:B13).
2. Go to the Insert tab on the Excel ribbon.
3. In the Charts group, choose a suitable chart. A column chart or line chart is perfect for this type of time-series data. Let's use a column chart. Excel will instantly create the chart on your worksheet.

You should now have a basic chart showing your sales for each month.

Step 2: Add the Trend Line

With your chart created and selected, you have two simple ways to add a trend line.

Method A: Using the "Chart Elements" Shortcut

This is the quick and easy method introduced in modern versions of Excel.

1. Click anywhere on your chart to select it. Three buttons will appear at the top-right corner of the chart.
2. Click the plus sign (+), which is the Chart Elements button.
3. In the menu that appears, simply check the box next to Trendline.

Excel will immediately add a default linear trend line to your chart. To access additional options, you can click the small arrow to the right of the Trendline option in this same menu to choose a different type (like Exponential or Moving Average).

Method B: Using the Chart Design Ribbon

If you prefer using the ribbon menu or have an older version of Excel, this method also works perfectly.

1. Click on your chart to activate the Chart Design and Format tabs in the ribbon.
2. Go to the Chart Design tab.
3. On the far left, click the Add Chart Element dropdown.
4. Hover over Trendline, then select one of the available types (e.g., Linear).

Just like with the first method, a trend line will now appear on your chart, visually representing the general sales trend.

Choosing the Right Type of Trend Line

Excel defaulted to a straight, "Linear" trend line, but that's not always the best fit for every dataset. Some data follows a curve. You can fine-tune this by selecting the right trend line type.

To access these options, double-click on the trend line in your chart. This will open the Format Trendline pane on the right side of your screen. Here, you’ll see several trend line options to choose from.

• Linear: This is the default and most common type. It's a straight line best used for data sets that increase or decrease at a steady rate. Think consistent growth or a steady decline.
• Exponential: Use this when your data values rise or fall at increasingly higher rates. The line is a curve that rises or falls very sharply. This is common when looking at things like compound interest or viral growth.
• Logarithmic: This type is useful for data that increases or decreases quickly at first and then starts to level off. Imagine a learning curve where progress is fast at the beginning but slows down as you approach mastery.
• Polynomial: When your data has fluctuations (hills and valleys), a polynomial trend line can be a good fit. It uses a curved line to follow data that has more than one peak or trough. You can also adjust its "Order" to change the number of bends in the line. An Order 2 has one bend, an Order 3 has two, and so on.
• Power: This is best for datasets that show a symmetrical curve, often seen in scientific data that measures change at a specific rate.
• Moving Average: This type is different. It doesn't draw a single line of best fit. Instead, it smooths out short-term fluctuations to reveal a clearer, more stable trend. You need to specify a "Period" - for example, a 2-period moving average would plot the average of the current and previous data point. It's excellent for "noisy" data like daily stock prices or website traffic, helping you see the pattern beyond daily spikes and dips.

Level Up: Advanced Trend Line Formatting and Analysis

Beyond just adding a line, Excel lets you customize its appearance and pull out some powerful statistical information from it.

Customizing the Appearance

In the Format Trendline pane, click the paint bucket icon (Fill & Line). Here, you can change:

• Color: Make the trend line stand out from your data series.
• Width: Make the line thicker for better visibility.
• Dash type: Using a dotted or dashed line is a common way to distinguish the trend line from actual data lines.

Forecasting Future Values

This is one of the most useful features of a trend line. You can project the trend into the future to estimate what might happen next.

1. In the Format Trendline pane, go back to the chart icon (Trendline Options).
2. At the bottom, you'll find the Forecast section.
3. In the Forward box, enter the number of periods you want to project ahead. For our monthly sales data, entering "3" would extend the trend line three months into the future. You can also project "Backward."

Your trend line will now extend beyond your actual data, giving you a visual forecast. Remember, this is just a projection based on past data - not a guaranteed outcome!

Displaying the Equation and R-squared Value

For a more statistical view, you can display two important numbers right on your chart.

• Display Equation on chart: This checkbox adds the mathematical formula Excel used to plot your line. For a linear trend line, it will look like y = mx + b. It is useful for making manual predictions.
• Display R-squared value on chart: This is incredibly helpful. The R-squared value (or R²) is a number between 0 and 1 that tells you how well the trend line fits your data. A value closer to 1 means a very reliable fit. A low value (e.g., an R² of 0.25) means your data is very scattered and the trend line isn't a strong representation of the data. Checking this can give you confidence in your trend analysis.

After selecting your trend line type, checking the R-squared value is a great way to see if you chose the right one. Try a few different types (Linear, Exponential, etc.) and see which one gives you the highest R-squared value - that one is likely the best statistical fit for your data's pattern.

Common Problems and Quick Fixes

Why Can't I Add a Trend Line? (It's Greyed Out)

If the "Trendline" option is greyed out, it’s almost always for one of two reasons:

1. You're using an incompatible chart type. Trend lines do not work with pie charts, doughnut charts, stacked charts, or any 3D chart formats. Switch to a line, column, bar, or scatter chart.
2. You haven't selected a data series. Make sure you have clicked on the data series itself (e.g., a bar, a line, or a dot) before trying to add the trend line.

How Do I Add a Trend Line for Only One Series?

If your chart has multiple data series (e.g., sales for two different products), you don't have to add trend lines for both. Simply click on the data series you're interested in, then right-click and choose Add Trendline from the context menu. This will add a trend line for that specific series only.

How to Remove a Trend Line?

Getting rid of a trend line is simple. Click on the trend line once to select it (you'll see dots at either end), and then press the Delete key on your keyboard. It's that easy.

Final Thoughts

Mastering trend lines in Excel moves you beyond just presenting data to actively analyzing it for insights and forecasts. It's a simple, powerful visual tool that helps you communicate the hidden story in your numbers, whether you're tracking progress toward a goal or explaining performance to your team. So next time you build a chart, take that extra step to add a trend line and see what patterns you can uncover.

Working in Excel and exporting CSVs is a familiar process, but it can quickly become time-consuming, especially when your data lives across multiple platforms like Google Analytics, Shopify, and your CRM. We built Graphed to solve this challenge. Instead of manually creating charts and wrangling data, you can connect your sources and simply ask in plain English to "create a line chart showing the trend of website traffic and sales over the past quarter." We instantly generate live, interactive dashboards that update automatically, so you can spend less time building reports and more time acting on the insights they provide.