How to Format Trendline in Excel

Adding a trendline to an Excel chart takes just two clicks, but turning that default dotted line into a powerful forecasting and analytical tool is where the real value lies. Proper formatting can transform a simple chart from a set of data points into a clear, insightful story. This guide will walk you through every option in the "Format Trendline" pane, from changing colors to displaying forecast models, so you can customize your charts with confidence.

Graphed

Still Building Reports Manually?

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

Watch the Demo

A Quick Refresher: How to Add a Trendline in Excel

Before you can format a trendline, you need to add one. Let's start with a basic chart showing monthly sales figures. If you already have a chart with a trendline, feel free to skip to the next section.

Imagine you have this simple dataset in your spreadsheet:

Month   | Sales
--------|-------
January | $1,500
February| $1,750
March   | $2,100
April   | $2,000
May     | $2,400
June    | $2,800

Once you’ve created a line or bar chart from this data, adding the trendline is simple:

1. Click on your chart to select it. You will see three small buttons appear on the top right side of the chart border.
2. Click the plus sign (+) icon, which is the "Chart Elements" button.
3. In the menu that appears, hover your mouse over "Trendline."
4. Click the small arrow to the right of "Trendline" and select a type, such as "Linear." Excel will immediately add a standard trendline to your data series.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Accessing the "Format Trendline" Pane

All your customization options are located in a single, powerful menu called the "Format Trendline" pane. This menu will pop up on the right side of your Excel window. There are two easy ways to open it:

• Method 1: The Right-Click: Right-click directly on the trendline itself on your chart, then select "Format Trendline..." from the context menu.
• Method 2: The Double-Click: Simply double-click on the trendline. This is often the quickest way to get there.

Once opened, you'll see a panel with several icons at the top. We'll be focusing on the two most important ones: "Fill & Line" (the paint bucket icon) and "Trendline Options" (the bar chart icon).

Customizing the Style and Appearance

The "Fill & Line" section is all about making your trendline look good and easy to read. A poorly formatted line can blend in with your data or make your chart look cluttered. Here’s how to fix that.

Changing the Line Color

Excel's default colors don't always match a company's branding or provide enough contrast. Customizing the line color helps it stand out and tell a clearer story.

• Under the "Line" options, click the dropdown menu next to "Color."
• You can choose theme colors, standard colors, or click "More Colors..." for a precise custom shade. A common best practice is to make the trendline a neutral color like gray or a darker shade of your main data series color.

Adjusting Width and Dash Type

How thick or stylized your line is can improve readability. The key is to make your trendline visually distinct from your actual data line.

• Width: Use the "Width" field to make the line thicker (for emphasis) or thinner (for a more subtle look). A width of around 1.5 pt to 2 pt is usually a good starting point.
• Dash type: This is one of the most effective formatting options. Instead of a solid line, which can be confused with the actual data, change it to a dashed or dotted line. It clearly communicates that this line is a calculated trend, not a real data series.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Choosing the Right Analytical Model: Trendline Options

This is where you move beyond simple styling and start using the trendline as a true analytical tool. Clicking the bar chart icon will open up all the statistical and forecasting options.

Types of Trendlines

Excel offers several models to fit different types of data. Choosing the right one is essential for accurate insights.

• Linear: This is the most common option. It creates a straight line of best fit and is ideal for data that shows a steady, consistent increase or decrease over time. Example: Tracking consistent monthly growth in ad spend.
• Logarithmic: Use this when your data increases or decreases quickly at first, and then begins to level out. Example: The initial signup rate for a new app that slows down as the market becomes saturated.
• Polynomial: Best used for datasets with fluctuations (peaks and valleys). The "Order" setting lets you define how many curves the line has. An order of 2 has one curve, an order of 3 has two, and so on. Example: Analyzing sales data that has a clear seasonal peak in the summer and a dip in the fall.
• Exponential: For data that rises or falls at an increasingly rapid rate. The values change by a proportional rate. Example: Tracking the spread of a viral marketing campaign where views compound over time.
• Moving Average: This isn’t a statistical best-fit line. Instead, it smooths out volatility in your data by averaging data points over a specified interval or "Period," revealing a clearer underlying trend. Example: Using a 7-day moving average to smooth out daily website traffic spikes and see the overall weekly trend.

Forecasting Future Values (and Looking Backwards)

One of the most powerful features of trendlines is forecasting. You can project where your data is headed based on its past performance.

• In the "Forecast" section, enter a number in the "Forward" box to project future periods. For example, if your data is monthly, entering "3" will extend the trendline three months into the future.
• Likewise, entering a number in the "Backward" box will extend the line into the past before your data started. This can be useful for estimating a "starting point" before data was tracked.

Adding the Equation and R-squared Value

For more advanced analysis, Excel can display the two most important statistical figures directly on your chart.

• Display Equation on chart: Checking this box will add a label showing the mathematical equation Excel used to calculate your trendline (e.g., y = 245.14x + 1475.6). This is useful if you need to manually calculate future values.
• Display R-squared value on chart: This is incredibly helpful. The R-squared value is a score from 0 to 1 that measures how well the trendline fits your actual data. The closer a value is to 1, the more reliable your trendline model is. An R-squared of 0.95 means your trendline is a great fit, while a value of 0.25 suggests the model isn’t a good match for your data.

After you check these boxes, a small text box with the values will appear on your chart. You can click and drag this box anywhere you like for better readability. You can also format the text (change font size, make it bold, etc.) just like any other chart text.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Putting It All Together: A Practical Example

Let's format a chart for a fictional sales report to make it presentation-ready.

1. We start with our monthly sales chart and add a Linear trendline.
2. Double-click the trendline to open the formatter.
3. Under "Fill & Line," we change the color to a professional dark gray and select a clean Dash type to distinguish it from a solid blue line of actual sales.
4. Under "Trendline Options," we project sales for the next quarter by entering "3" into the "Forward" forecast box.
5. To add credibility, we check the boxes for "Display Equation on chart" and "Display R-squared value on chart." We see an R-squared value of 0.98, so we know our forecast is based on a very strong trend.
6. Finally, we give our line a meaningful description by selecting "Custom" under "Trendline Name" and typing "Projected Sales Growth".

The result is no longer just a simple chart. It’s a clean-looking and data-backed forecast that clearly communicates past performance and future potential.

Final Thoughts

Formatting an Excel trendline is about turning raw data into a clear story. By moving beyond the defaults, you can customize the appearance, choose the right analytical model, and even forecast future outcomes, making your charts far more professional and insightful for any audience.

Manually creating these reports in Excel works well for one-time projects, but it becomes time-consuming when you're constantly refreshing the data for weekly or monthly meetings. At Graphed, we created a tool to eliminate this repetitive work. Instead of downloading CSVs and rebuilding your charts, you can connect your data sources like Google Analytics, Shopify, or Salesforce just once. Then, you can ask for charts and dashboards in plain English, and they’ll update in real-time. This keeps your analysis current without any of the manual wrangling.