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.


Download Practice Workbook

Download this workbook and practice while going through the article.


2 Easy Methods to Exclude Data Points from Trendline in Excel

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 Edit Chart Data in Excel (5 Suitable Examples)


Similar Readings


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

Read More: How to Import and Use Data into Power Pivot in Excel


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


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. Please visit Exceldemy for more useful articles like this.


Related Articles

Akib Bin Rashid
Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo