How to Exclude Data Points from Trendline in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

dataset for how to exclude data points from trendline in excel


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.

Editing dataset to exclude data points from trendline in excel

  • 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.

Editing dataset to exclude data points from trendline in excel

Read More: How to Create Equation from Data Points in Excel


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.

PivotChart to exclude data points from trendline in excel

  • 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.

PivotChart to exclude data points from trendline in excel

  • 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.

PivotChart to exclude data points from trendline in excel

  • 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.

PivotChart to exclude data points from trendline in excel

  • 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.

PivotChart to exclude data points from trendline in excel

  • 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.

PivotChart to exclude data points from trendline in excel


Download Practice Workbook

Download this workbook and practice while going through the article.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo