Often you may want to add one trendline to a chart in Excel to summarize the trends of multiple series.
For example, you may want to create the following plot with one trendline that summarizes the overall trend of two different product sales over time:
The following step-by-step example shows how to create this exact plot.
Step 1: Create the Data
First, let’s create the following dataset that shows the total sales of two different products during 10 consecutive years:
Step 2: Insert the Scatter Plot
Next, highlight the cell range A1:C11, then click the Insert tab along the top ribbon, then click the Scatter icon within the Charts group:
The following scatter plot will automatically appear:
The blue dots represent the sales each year for Product A and the orange dots represent the sales for Product B each year.
Step 3: Add the Overall Series
Next, right click anywhere on the chart and then click Select Data:
In the new window that appears, click the Add button under Legend Entries (Series):
Then type the following into each box:
- Series name: All Data (or whatever name you’d like)
- Series X values: =Sheet1!$A$2:$A$11, Sheet1!$A$2:$A$11
- Series Y values: =Sheet1!$B$2:$B$11, Sheet1!$C$2:$C$11
Then click OK.
This “All Data” series will now be added to the scatter plot and it will overlap all of the existing points on the plot:
Step 4: Add the Overall Trendline
Next, click on any of the grey dots in the plot.
Then click the tiny green plus sign that appears in the top right corner of the chart, then check the box next to Trendline.
A trendline that summarizes all of the series will automatically appear:
Step 5: Hide the Overall Data Series
Lastly, we need to hide the grey overall data series in the chart.
To do so, double click on any of the grey dots in the chart.
In the Format Data Series panel that appears, click the button next to None under Marker Options, then click the button next to No fill under Fill:
The overall data series will be now be hidden:
Lastly, click on the trendline to make it darker and easier to read, then click on the individual legend elements called All Data and Linear (All Data) and delete them.
Feel free to add a chart title and axis labels as well.
The final chart will look like this:
The one trendline in the chart now summarizes the overall trend for both product sales.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Find Trendline Equation Without Chart in Excel
How to Find the Slope of a Trendline in Excel
How to Add a Quadratic Trendline in Excel
