How to Remove Outliers in Excel Pivot Table (The Easy Way)

Sharing is caring!

Have you ever created a pivot table in Excel, only to find that outliers are skewing your results? Outliers, which are data points that differ significantly from other values, can make it difficult to accurately analyze and draw insights from your data. Thankfully, Excel provides several methods to identify and remove outliers from your pivot tables. In this article, we’ll walk you through step-by-step how to eliminate outliers and ensure your pivot table analysis is as accurate as possible.

Understanding Outliers in Excel Pivot Tables

Before we learn about removing outliers, it’s important to understand what outliers are and how they can impact your pivot table analysis.

What are Outliers?

Outliers are data points that lie far outside the normal range of values in a dataset. They can be either extremely high or low compared to the other data points. For example, if you’re analyzing sales data and most transactions fall between $50-$200, but you have one sale for $10,000, that $10,000 data point would be considered an outlier.

Outliers can occur for various reasons:

• Data entry errors
• Unusual but legitimate data points
• Anomalies or rare events

How Outliers Affect Pivot Tables

When you create a pivot table to summarize and analyze your data, outliers can significantly skew the results. They can impact key metrics like averages, totals, and percentages, painting an inaccurate picture of your data.

For instance, let’s say you have a dataset of employee salaries:

If you create a pivot table to calculate the average salary, the outlier value of $500,000 will dramatically inflate the result, even though it’s not representative of the typical salary range.

Average Salary: $164,250

Removing the outlier gives a more accurate representation:

Average Salary (excluding outlier): $52,333

As you can see, outliers can lead to misleading conclusions if not addressed. Next, we’ll look at how to identify and remove them from your pivot tables.

Method 1: Filtering Out Outliers

One of the simplest ways to remove outliers from a pivot table is by using Excel’s filtering functionality. This method works well if you have a clear threshold for what constitutes an outlier in your dataset.

Step 1: Create Your Pivot Table

First, create your pivot table as usual. For this example, we’ll use a dataset of product sales. Our data has fields for Product, Sales Region, Units Sold, and Revenue.

Step 2: Identify the Outlier Threshold

Determine the threshold for what you consider an outlier in your dataset. You might decide that any Revenue value above $10,000 is an outlier.

Step 3: Apply a Filter

• Click on the arrow next to the Revenue field in your pivot table to open the filter menu.
• Uncheck the box next to “Select All” to deselect all values.
• Check the boxes next to the values you want to include (in this case, values <= $10,000).
• Click OK to apply the filter.

Your pivot table will now exclude any rows where Revenue exceeds $10,000, effectively removing those outliers from your analysis.

Pros and Cons

Filtering is a quick and easy method to remove outliers, but it has some limitations:

Method 2: Using Statistical Techniques

For a more automated and statistically rigorous approach to outlier removal, you can use techniques like the interquartile range (IQR) method. This identifies outliers based on the spread of your data, rather than fixed thresholds.

Step 1: Calculate Q1 and Q3

• First, calculate the first quartile (Q1) and third quartile (Q3) of your data.
• In Excel, you can use the QUARTILE.EXC function.
• For Q1: =QUARTILE.EXC(data_range, 1)
• For Q3: =QUARTILE.EXC(data_range, 3)

Step 2: Calculate the IQR

The IQR is the difference between Q3 and Q1. In Excel:

=Q3-Q1

Step 3: Define Outlier Boundaries

Outliers are typically defined as values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR.

In Excel:

• Lower boundary: =Q1-1.5*IQR
• Upper boundary: =Q3+1.5*IQR

Step 4: Identify Outliers

Use conditional formatting to highlight values outside the outlier boundaries. You can then filter them out or adjust as needed before creating your pivot table.

Pros and Cons

The IQR method is more statistically robust than simple filtering, but requires more setup:

Method 3: Manually Adjusting Outliers

In some cases, you may want to keep outliers in your dataset but adjust their values to lessen their impact. This is a good approach when outliers represent legitimate but extreme data points.

Step 1: Identify Outliers

Use one of the methods above (filtering or IQR) to identify which values are outliers in your dataset.

Step 2: Decide on Adjustment Method

There are a few ways you can adjust outliers:

• Set them to the upper or lower boundary value (e.g. Q3 + 1.5*IQR)
• Set them to a fixed percentile (e.g. 99th percentile)
• Adjust them by a fixed amount or percentage

Step 3: Adjust Outlier Values

Create a new column in your data and use formulas to adjust the outlier values based on your chosen method. For example:

=IF(value>upper_boundary,upper_boundary,value)

This replaces any values above the upper boundary with the boundary value itself.

Step 4: Create Pivot Table

Create your pivot table using the adjusted values column instead of the original values. The outliers will now have less impact on your summarized data.

Pros and Cons

Manually adjusting outliers lets you keep extreme but valid data points while still lessening their impact:

Summary

Outliers can significantly skew the results of your Excel pivot tables, leading to inaccurate conclusions. Fortunately, Excel provides several methods to identify and remove or adjust outliers:

1. Filtering: Manually filter out values above or below set thresholds. Easy to apply but requires knowing what defines an outlier.
2. Statistical techniques: Use methods like interquartile range (IQR) to automatically identify outliers based on data distribution. More robust but complex.
3. Manual adjustment: Keep outliers but adjust their values to lessen impact. Retains valid data points but requires subjective decisions.

By using these techniques to handle outliers, you can ensure your pivot table analyses are accurate and insightful. Choose the method that best suits your data and analytical needs. With clean, reliable data, you’ll be well-equipped to make data-driven decisions with confidence!

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.