In Excel for Microsoft 365 on Windows, select the clustered column chart, then choose Chart Design > Add Chart Element > Trendline > More Trendline Options. In the pane, choose the data series and trendline type; Excel adds the line to that series without changing the source data.
What a trendline shows on a clustered column chart
A clustered column chart compares values across categories, placing the series for each category side by side. A trendline overlays one selected series to show its general direction or pattern. For example, a chart of monthly sales for three product lines can have a separate trendline for each line, helping you compare their overall movement despite month-to-month variation.
A trendline is a calculated model, not another set of measured values. It does not add rows to the worksheet or alter the column heights. It is drawn over the chart and represents a fitted relationship between the selected series’ values and their positions along the category axis. Consequently, treat it as a summary of the data, not proof of what caused the pattern or a guarantee of future results.
Add a trendline step by step
First, make sure the chart is a standard two-dimensional clustered column chart and that the source data is arranged with categories in one column or row and each series in its own column or row. Then add the trendline:
- Click the chart to show the chart tabs on the ribbon.
- On Chart Design, choose Add Chart Element > Trendline > More Trendline Options. Alternatively, right-click a column in the series you want to analyze and choose Add Trendline, if that command appears in the context menu.
- In the Format Trendline pane, confirm the selected series and choose a trendline type, such as Linear.
- Close the pane or click elsewhere in the chart. To add another line, select a different series and repeat the steps.
Excel may select the chart area rather than a series when you first click. Click one of the series’ columns once to select the whole series; clicking a column again can select only that individual data point. A trendline belongs to a series, so selecting just one point can prevent the command from behaving as expected. On Excel for Mac, select the chart and use the chart design controls to add a trendline; labels and ribbon placement can differ slightly by release. Excel for the web has fewer chart-formatting controls, so use the desktop app if the trendline options you need are not available.
Choose the series you want to analyze
Each series in a clustered chart has its own values and its own trendline. A trendline attached to the first series does not summarize the other columns, even if the lines appear close together. This is useful when the question is whether one product, region, or team is improving, but it can make a busy chart hard to read.
Before adding lines, check the chart legend and source range so you know which series is selected. If the series names are unclear, edit the source headings rather than relying on generic labels such as Series 1. You can also temporarily select a series through the chart’s Format tab and its Current Selection list, then choose the named series there. If the aim is to show an overall total across groups, calculate that total in the worksheet and chart it as a separate series; a trendline on one group is not a trendline for the combined data.
Pick a trendline type that fits the question
Excel offers several models. The best choice depends on the shape of the data and what you need the line to communicate. Avoid choosing a more complicated model just because it follows every bump in a small sample.
- Linear: A straight line suited to a reasonably steady increase or decrease. For example, monthly service calls rising by roughly the same amount each month may be summarized with a linear trendline.
- Exponential: A curved model for values that change by a roughly consistent proportion over equal steps. It requires positive values; it is not appropriate when the series includes zero or negative values.
- Logarithmic: A curve that changes quickly at first and then levels out. It requires positive x-values, which may be an issue when the chart’s category positions are used as x-values.
- Polynomial: A curve that can bend to reflect rises and falls. Choose its order carefully: higher orders can fit the existing points closely while producing a misleading story between or beyond them.
- Power: A curved relationship often used when change depends on a power of the x-values. It requires positive x- and y-values, so it is not a general-purpose choice for any column chart.
- Moving Average: A smoothed line based on a selected number of neighboring values. A longer period smooths more short-term variation, but can also hide meaningful changes.
For a simple time series, start with a linear line if a steady rate is plausible, or a moving average if the purpose is to reduce short-term noise. Compare the model with the actual data and the question being asked; do not select a type solely because it gives the highest displayed fit statistic.
Set the equation, fit statistic, and forecast
In the Format Trendline pane, Excel provides options for the model and its appearance. For supported trendline types, you can set how far the line extends backward or forward, measured in chart units. A forward extension is a visual projection of the selected model, not a new observation or a confidence interval. If the categories represent months, extending by three units generally means three category steps, but check how the chart’s category axis is configured.
You can also select Display Equation on chart and Display R-squared value on chart. These labels are useful for analysis or a working chart, but often clutter a chart intended for presentation. To format the line, select it and use the formatting controls in the pane to adjust properties such as color, width, or dash style. Make the line visually distinct from the columns without implying that it is actual measured data.
For forecasts, use a sensible horizon. Extending a line one or two categories may help illustrate a short-term direction; extending it far beyond the observed period can make unsupported assumptions look certain. A trendline does not account automatically for seasonality, a change in business conditions, or known future events.
Read the equation and R-squared carefully
The equation displayed by Excel describes the selected fitted model. For a linear line, it has the form y = mx + b: the slope m represents the modeled change for each x-step, and b is the intercept. On a clustered column chart with ordinary category labels, those x-steps usually represent category positions, not the actual text labels. The x-value 1 is the first category, 2 the second, and so on; the equation does not treat labels such as January and April as literal dates.
R-squared describes how closely the model fits the values used to create it, on a scale from 0 to 1 in the usual cases. A higher value is not a promise that the line predicts future values well. A high-order polynomial may fit a short series very closely and still behave implausibly outside it. R-squared also does not establish causation, explain outliers, or show whether a model is appropriate for the subject matter.
Excel rounds the equation displayed on the chart. If you use the printed coefficients for a separate calculation, their rounded display may give a slightly different result from the underlying fitted line. Use the equation as a visual aid, and avoid presenting its coefficients as exact unless you have verified the precision needed for the analysis.
Common problems and how to fix them
- The Trendline command is unavailable: Check that you selected a data series in a supported two-dimensional chart, not the chart background or an individual point. Trendlines are not available for every chart type, including 3-D charts, pie charts, and doughnut charts. A stacked chart is not the same as a clustered chart; change to a supported chart type if necessary.
- The line appears to use the wrong values: Confirm the selected series in the legend or Current Selection list. If the chart has several series, Excel fits a separate line to each one rather than combining them.
- The curve looks convincing but makes little sense: Try a simpler model, inspect the raw values, and avoid an unnecessarily high polynomial order. A fit to a few points is especially easy to overinterpret.
- The dates seem evenly spaced when they are not: A column chart’s category axis may give categories equal spacing even when the time between dates varies. For analysis where elapsed time or numeric x-values matter, use an XY (Scatter) chart with the actual dates or x-values rather than relying on category order.
- The trendline does not reach the future categories you expected: Review the forward forecast setting and the axis. Forecast length is expressed in chart units, and a category-axis chart may not behave like a plot with a continuous numeric x-axis.
When another chart or method is better
A clustered column chart works well when the main purpose is comparing values among discrete categories. If the central question is how a measure changes over time, a line chart often communicates the sequence more clearly, especially with several periods. Trendlines can also be added to supported line charts, but changing chart type does not fix a model that ignores seasonality or uneven time intervals.
Use an XY (Scatter) chart when x-values are genuinely numeric and the distances between them matter—for example, measurements taken at 1, 2, 5, and 10 units. Scatter charts position points according to those values, unlike a typical category axis that spaces labels evenly. For seasonal data, a trendline alone may conceal recurring peaks; chart the actual periods and consider analyzing seasonal patterns separately. The chart should match the structure of the data, not just the appearance you prefer.
Frequently Asked Questions
Can you add a trendline to each series in a clustered column chart?
Yes. Add a trendline to each series separately. Select the relevant series, add its line, and repeat for the other series. Excel does not automatically create a single trendline for all series together.
Can I add a trendline to only one column?
A trendline is fitted to a data series, not normally to one isolated column. If you want to analyze one category across several series, reorganize or chart those values as a separate series, then add the trendline to that series.
Can a trendline predict future values?
You can extend a trendline forward in the Format Trendline pane, but this is a projection of its chosen mathematical model. It is not a guaranteed forecast and does not automatically include uncertainty, seasonal effects, or future changes in conditions.
Why does my trendline equation use x = 1, 2, 3 instead of dates?
On a category-axis chart, Excel commonly fits the values against category positions. The equation therefore uses the first, second, and third category positions. Use an XY (Scatter) chart with actual numeric date values when spacing by elapsed time is important.
Can I show a trendline without showing its equation?
Yes. The equation and R-squared are optional labels in the Format Trendline pane. Leave those boxes unchecked, or clear them if they are already displayed; the trendline itself remains on the chart.
Related
- Comparison Plot in Excel With R²: Steps and Meaning
- Company Invoice Template in Excel: Setup and Tips
- Compare Lists in Excel: Find Matches and Differences
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Is an Excel Dashboard Free? Templates, Costs, and OptionsYes, you can make an Excel dashboard without paying extra for a dashboard tool: the dashboard is a…
- Advanced Gantt Chart Excel Formula: Build a Dynamic PlanAn advanced Gantt chart in Excel is usually built with a date-based conditional-formatting formula, not a single formula…
- How to Update and Refresh a Pivot Table in Google SheetsTo update a pivot table in Google Sheets, change the source data and the pivot table will usually…
- Excel Area Chart Smooth Lines: What Works and What Doesn’tNo: in Excel for Microsoft 365 on Windows, an area chart does not provide a supported Smoothed line…
- Excel Chart: Add a Vertical Line at a DateTo add a vertical line at a date in an Excel chart, create a helper series with the…
- Excel Line Chart Smooth Curve: Setup, Limits, and TipsYes. In Excel for Microsoft 365 on Windows, select a line in the chart, open Format Data Series,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.