How to Exclude Data Points from Trendline in Excel

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will explain how to exclude data points from trendline in Excel. This article will cover 2 easy ways to do so.

How to Exclude Data Points from Trendline in Excel: 2 Easy Methods

This is the dataset for this article. We have month-wise sales data of an organization. In the month of September, the organization did not operate. So the sales amount is $0. We will exclude this value from the chart in this article.

1. Edit Dataset to Exclude Data Points from Trendline in Excel

This is a line chart using the above dataset.

As we can see, the sales value for September is an outlier and is misrepresenting the trend. So we will now exclude it.

Steps:

First of all, select the data points.
Then, right-click your mouse to bring the context menu.
After that, click Select Data.

A new box will appear. Uncheck September from the Horizontal Axis Labels.
Then, click OK.

Excel will exclude the data point for September from the line chart.

2. Use PivotChart Feature to Exclude Data Points from Trendline in Excel

The next method is the use of the PivotChart feature. This is a more flexible feature because you can filter the dataset here.

Steps:

First of all, select the dataset B4:C16.
Then, go to the Insert tab.
After that, select PivotChart.

Excel will open a box.
Select the location of your PivotChart.
Then, press OK.

Excel will create a PivotChart.
Drag Month in the Categories field and Sales in the Values Excel will by default calculate the Sum of Sales.

Excel will create a column chart.

Now, I will change the chart type.
To do so, select the chart first.
Then, right-click your mouse to bring the context menu.
After that, select Change Chart Type.

After that, select Line
Then, click OK.

Excel will create a line chart now.

Now, I will add a Trendline.
To do so, select the Add Element.
Then, check the box for Trendline.

Excel will add a Trendline.
Then, I will filter the months.
To do so, select the drop-down for Month.

Then, uncheck the box for September.
After that, click OK.

Excel will exclude the data point for September.

Things to Remember

You can also add a Trendline from the Design This will appear once you select the chart.

Download Practice Workbook

Download this workbook and practice while going through the article.

Exclude Data Points from Trendline.xlsx

Conclusion

In this article, I have explained 2 easy ways to exclude data points from trendline in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.

<< Go Back To Trendline in Excel | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!