How to Use Excel Trendline for Part of Data (2 Easy Methods)

Often it is essential to create an Excel trendline for part of data only while working in order to concentrate on certain trends, identifying outliers, analyzing different parts, and capturing non-linear relationships.

In this article, we have demonstrated step-by-step procedure for creating an Excel trendline for part of data only.

Along with that, you will get to know how one can add multiple trendlines within the same graph. Also, you will familiarize yourself with how one can create trendlines for multiple data series as well.

Download Practice Workbook

You can download the workbook used to demonstrate methods in this article from here.

What is Trendline in Excel?

A trendline in Excel is a straight or curved line that represents the overall trend of a set of data points in a chart. The trend or pattern within the data is visually represented, making it simpler to spot trends, growth rates, and expected future values.

Excel has different types of trendlines such as linear, exponential, polynomial, logarithmic, power trendline, etc.

2 Methods to Create a Trendline for Part of Data in Excel

We will be using the following dataset to illustrate methods in this article. The dataset is representing the speed record of a particular vehicle.

1. Trendline for Part of Data within the Full Chart

You can create a chart from your whole dataset, then generate a trendline within that chart for some part of data. Let’s get started.

• Select your dataset which is range B5:C12.
• Follow these steps as shown below: Insert tab >> Insert Scatter (X, Y) or Bubble Chart >> Scatter.

• And a scatter chart will be plotted as follows.
• We want to create a trendline just for the first 6 points of the graph. That means we want to create a trendline for the range B5:C10 only within the main graph.
• Select the chart.
• Go to the Chart Design tab.
• Click on Select Data.

• A window titled Select Data Source will appear on your sheet.
• Click on Add.

• Set the Series name with whatever you want. Here, we have set the name as Acceleration.
• Set Series X values as range B5:B10.
• Set Series Y values as range C5:C10.
• Click on OK.

• Again, click on OK.

• And the chart will look as follows.
• The orange points represent the first 6 parts of our dataset and the blue points represent the rest.

• Now, to create a trendline just for the orange points, select any orange point and then click on the plus (+) icon.
• Mark the Trendline box from the list.

• And the result is as follows.
• The trendline is created only for some parts of the data.

2. Trendline for Previously Selected Part of Data

You can select a certain part of your dataset and create a trendline out of it.

• Let’s say, we have selected range B5:C10 here.
• Go to the Insert tab.
• Click on Insert Scatter (X, Y) or Bubble Chart.
• Select Scatter.

• And a scatter chart will be created as follows.
• Select the chart.
• Click on the Plus (+) icon.
• Mark the Trendline box.

• And the output will be as follows.

We get the trendline for our selected dataset which is the part of whole dataset.

How to Add Multiple Trendlines in Excel

Let’s presume, you have made a trendline with some part of your data. Now, you want to add another trendline in the same chart. Here, we will start after the 1st method. We can see the trendline is showing for orange points. Now we will add a trendline for blue points.

• Select the chart.
• Go to the Chart Design tab.
• Click on Select Data.

• Click on Add.

• Set the Series name. Here, we have named the series as Deceleration.
• Set the Series X values as range B11:B14.
• Insert range C11:C14 in the Series Y values.
• Click on OK.

• Click on OK again.

• And you will see that the colors of the rest of the points changed from blue to ash color.

• Now, select any ash colored point.
• Click on the plus (+) icon.
• Mark the Trendline box.

• And as you can see, there are two trendlines now in your graph.

How to Add Trendline For Multiple Series

You can add trendlines for multiple series as well.

Suppose, you have a dataset as follows that illustrates the speed record of two vehicles.

• Go to the Insert tab.
• Click on Insert Scatter (X, Y) or Bubble Chart.
• Select Scatter.

• An empty page will appear on your screen.
• Select that and go to the Chart Design tab.
• Click on Select Data.

• Click on Add.

• Set Series name as Vehicle A.
• Select range B6:B10 for the Series X values.
• Select range C6:C10 for the Series Y values.
• Click on OK.

• Click on Add again.

• Set the Series name as Vehicle B.
• Set the Series X values with range E6:E10.
• Select range F6:F10 for Series Y values.
• Click on OK.

• Click on OK again.

• And the chart will be created as follows. You can see blue and orange points.

• Select any blue point.
• Click on the plus (+) icon.
• Mark the Trendline box.

• Select any orange point.
• Click on the plus (+) icon.
• Mark the Trendline box.

• And the final output is as follows.

Frequently Asked Questions

1. How do I add a trendline to my Excel chart?
To add a trendline in Excel chart follow this:

• Select your Excel chart.
• Click the Chart Elements symbol (the plus (+) sign) that shows when you click on the chart.
• Then select Trendline from the menu to add a trendline.

2. Can I customize the trendline in Excel?
Yes, Excel allows you to customize the trendline. A trendline’s appearance (color, line style), type (line, exponential, polynomial, etc.), and equation and R-squared value can all be customized.

3. Can I remove or edit the trendline later?
Yes, you may customize or remove the trendline at a later time. To edit a trendline, simply select it on the chart, click, and then use the Format Trendline choices to replace or remove it.

4. How can I interpret the trendline equation and R-squared value?
The trendline equation, which illustrates the line’s mathematical equation, can be used to forecast values that go beyond the data at hand. How well the trendline fits the data is determined by its R-squared value; a number near to 1.0 indicates a good fit, while one closer to 0.0 indicates a bad fit.

5. Does Excel support other types of trendlines besides linear?
Yes, Excel does support a variety of trendlines, including moving averages, exponentials, polynomials, exponentials, logarithms, and more.

Conclusion

This article illustrates the easiest ways of creating an Excel trendline for part of data only. Also with the help of this article, one can easily add multiple trendlines within the same graph and add trendlines for multiple data series as well. Hope this helps with your work. Visit our site ExcelDemy for more relevant articles.