Akib Bin Rashid
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.
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.
Read More: How to Edit Chart Data in Excel (5 Suitable Examples)
Similar Readings
- How to Plot Time over Multiple Days in Excel (With Easy Steps)
- How to Sort Data in Excel Chart (5 Easy Methods)
- Selecting Data in Different Columns for an Excel Chart
- How to Add Data Points to an Existing Graph in Excel (3 Ways)
- How to Format Data Series in Excel (with Easy Steps)
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.
- 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.
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.
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
- How to Create a Scatter Chart in Excel (with Easy Steps)
- How to Add Data Table in an Excel Chart (4 Quick Methods)
- Excel Chart by Month and Year (2 Suitable Examples)
- How to Group Data in Excel Chart (2 Suitable Methods)
- How to Change Chart Data Range Automatically in Excel (2 Easy Ways)
- Excel Chart Not Updating with New Data (2 Suitable Solutions)
