How to Exclude Data Points from Trendline in Excel

Creating a trendline in Excel is a powerful way to visualize your data's direction, but a single outlier can skew the entire result, giving you a misleading forecast. If you've ever had a one-off sales spike or a random data error throw off your analysis, this guide is for you. We'll walk through a few practical methods to exclude specific data points from your trendline calculation without having to delete them from your chart.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Are Outliers and Why Do They Matter?

An outlier is a data point that is significantly different from other observations. Think of tracking monthly website traffic, and one month a viral blog post causes a massive, unrepeatable spike. Or perhaps a piece of equipment goes offline for a day, causing your daily production numbers to hit zero.

If you include these unusual events in your trendline calculation, the line will be pulled artificially up or down. Your trendline is supposed to represent the typical pattern in your data. By excluding these outliers from the calculation, you get a much more accurate and reliable picture of your real, underlying trend.

The goal isn't to pretend bad (or unusually good) data didn't happen. It’s to isolate unusual events so they don’t distort your understanding of normal operations. In many cases, you still want to see the outlier on the chart for context, but you just don’t want it influencing the trendline itself.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 1: The Quick & Easy Fix (Hiding the Data)

The simplest way to remove a data point from a trendline is to hide the source data row in your worksheet. This method is fast but comes with a major caveat: it removes the data point from the chart completely, which might not be what you want.

Here’s how it works:

Step-by-Step Instructions

1. Find the outlier data: In your Excel sheet, locate the row containing the data point you want to exclude. Let’s say in our example of monthly sales, July had an abnormally low value due to a known issue.
2. Hide the row: Right-click on the row number on the far left side of the worksheet (e.g., click on the "8" for row 8).
3. Select 'Hide' from the context menu. The row will disappear from your data table, and you'll see your chart update automatically. The outlier point will be gone, and the trendline will immediately recalculate based on the remaining visible data points.

In our "Monthly Sales" example, the trendline, once dragged down by July's low number, would now tilt upwards more accurately to reflect the true growth trend of the other months.

The Important Caveat: Checking Chart Settings

This method only works if Excel is configured to ignore data in hidden rows. This is the default setting, but it's good to know where to find it in case your chart behaves differently.

1. Right-click on your chart and choose Select Data.
2. In the "Select Data Source" dialog box, click the button at the bottom-left corner that says Hidden and Empty Cells.
3. Make sure the option Show data in hidden rows and columns is unchecked. If it’s checked, Excel will continue to include the hidden data in its trendline.

Pros and Cons

• Pros: Extremely fast and simple. Non-destructive, as you can easily unhide the row at any time by right-clicking the surrounding row numbers and choosing "Unhide."
• Cons: Removes the data point from the chart visually. This isn't ideal if you need to show the anomaly for context or explain why it has been excluded.

Method 2: The Best & Most Flexible Method (Using an #N/A Formula)

This is the preferred technique because it allows you to exclude the data point from the trendline calculation while keeping it visible on the chart. It takes a few more steps, but the result gives you complete control and maintains the full story of your data. The magic here relies on a quirk of Excel: it ignores #N/A errors when plotting a trendline.

We do this by creating two new "helper" columns and adding a second data series to our chart.

Original Setup Example

Let's assume your data is in columns A and B. Column A has the month (Jan, Feb, Mar...) and Column B has the sales figures. Our outlier is in cell B8 (the value for July).

Your chart is a scatter or line chart based on A2:B13 and has a skewed trendline.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 1: Create a Helper Column for the Trendline

First, we need to create a data series that contains all our values except the outlier.

1. In a blank column (let's use Column C), give it a header like "Sales for Trendline."
2. In cell C2, enter a formula that checks for the outlier condition. For this example, we want to exclude the data for July, which is in row 8 (cell A8). The formula will be:

=IF(A2="July", NA(), B2)

Let's break this down:

• IF(A2="July", ... ): It checks if the month in column A is "July". You can adjust this condition to whatever identifies your outlier. For instance, if you wanted to exclude a value greater than 5000, your condition could be IF(B2>5000, ...).
• NA(): If the condition is true (if it's July), the formula returns an #N/A error. This is exactly what we want.
• B2: If the condition is false, it simply returns the original sales value from column B.

Drag this formula down for all your data. Your new Column C will now be identical to Column B, except cell C8 will show #N/A.

Step 2: Create a Helper Column for the Outlier Point

Next, we need to create a column that contains only the outlier value. This allows us to plot it separately as its own dot on the chart.

1. In another blank column (like Column D), give it a header like "Outlier Data."
2. In cell D2, enter the inverse formula of the one we just wrote:

=IF(A2="July", B2, NA())

This formula does the opposite: if the month is "July," it returns the original sales value from B2. For every other month, it returns an #N/A error.

When you drag this down, Column D will be filled with #N/A errors, with the single outlier value appearing in cell D8.

Step 3: Update and Rebuild Your Chart

Now, we put it all together by adjusting the data sources in our chart.

1. Change the primary series: Right-click your chart and go to Select Data. Select your original data series (probably called "Sales") and click Edit.
2. Change the Series values field to reference your new "Sales for Trendline" column (C2:C13). Leave the X-axis values (A2:A13) as they are. Click OK. Your chart will update, and the trendline will now be corrected! However, the dot for July has vanished. Let's add it back.
3. Add the outlier series: While still in the "Select Data Source" window, click Add to create a new series.

Click OK. You will now see a second data point on your chart - an orange (or other default color) dot representing the outlier.

1. Format the new series: The final step is to make the new dot look like the original series so the chart appears seamless. Right-click the new outlier dot on the chart and choose Format Data Series. In the "Fill & Line" panel on the right, under "Marker," adjust the Marker Options to match the style, color, and size of your original blue dots.

You’re done! Your chart now displays a clean, accurate trendline reflecting your core data, while also showing the excluded outlier for complete transparency.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Pros and Cons

• Pros: This is the most professional and flexible solution. The outlier remains visible for context, and the trendline is accurate. It’s dynamic, so if your source data changes, the formulas will update automatically.
• Cons: Involves several steps and the use of helper columns, which can feel a little complex at first.

Method 3: Manually Splitting the Data into Two Series

If formulas aren't your thing, you can achieve the same result as Method 2 with a more manual, copy-and-paste approach. This can be faster for a one-off chart with a single outlier but becomes tedious if you need to do it often or with multiple exclusions.

1. Copy your entire data set (e.g., columns A and B) and paste it into two new columns (e.g., C and D). Column C is your X-axis labels ("Months"), and Column D contains your Y-axis values ("Sales").
2. Create a third set of columns (e.g., E and F). Copy and paste your data here too. At this point, you have two identical copies of your original data.
3. In your first copied data set (Column D), find the outlier value and manually delete it from the cell, leaving it blank. This set will be used to draw the trendline.
4. In your second copied data set (Column F), delete all values except for the outlier. This set will be used to plot the outlier dot.
5. Delete the original series from your chart.
6. Add a new series, using your first copied data set (Columns C and D) as the source. Add a trendline to this series.
7. Add another new series, using your second copied data set (Columns E and F).
8. Format the outlier dot to match the main series, just as in the final step of Method 2.

Pros and Cons

• Pros: No formulas required, which some users might find less intimidating.
• Cons: Entirely manual. If the original data changes, you have to repeat the entire process. It's not a practical solution for datasets that are updated regularly.

Final Thoughts

Excluding outliers is a critical step for accurate trend analysis in Excel. For a quick and simple exclusion where the point can disappear, hiding the row is effective. But for a more robust and professional chart that preserves the full visual context, using the #N/A formula technique to separate the main data from the outlier is by far the best approach.

Adjusting charts with helper columns and custom data series in Excel gets the job done, but it can quickly become cumbersome when you’re dealing with constantly updating data. We created Graphed to eliminate this kind of manual work. You can connect all your data sources, and our AI will build powerful, real-time visualizations for you. Need to exclude an outlier? Just ask in simple language, like "show me our Q3 revenue trend but ignore the data from the July outage.” Graphed lets a simple conversation do what used to take multiple formulas and a dozen clicks, delivering accurate insights in seconds.