Outliers in Excel: Definition, Tips and How To Calculate Them

Excel is a spreadsheet creation application that allows users to create basic or complex data storage, analysis and visualization reports. When entering, analyzing and interpreting data, outliers can cause a significant change that impacts the accuracy of a report. Understanding these outliers can help you identify them and minimize the potential discrepancies they might cause. In this article, we discuss what outliers in Excel are, explain how to calculate them and provide some tips to guide you.

Related: Basic Excel Formulas and How To Use Them

What are outliers in Excel?

An outlier in Excel is a data point or set of values that are significantly different from the average or expected range in a statistical sample or division. These outlying values may be unnecessary anomalies for the purposes of your data analysis or spreadsheet needs. Outliers may influence data interpretations and cause inaccurate results or forecasts through their notable difference from the rest of the presented data. To ensure the accuracy of your data reports, you may want to identify, calculate and minimize these outliers in your Excel spreadsheet.

Related: 5 Ways To Find Outliers in Statistics (With Examples)

How to calculate outliers in Excel

Consider these steps to calculate outliers in Excel:

1. Review your entered data

The process of data entry can cause manual or automatic transferring errors, which may result in outlying values. Make sure to review and check the data entered in your spreadsheet to find and fix typos or other errors that create inaccuracies. You may use automated tools to review your data entries or manually verify each cell's values.

Related: How To Find Outliers

2. Sort your data values

Select the range of your data set by clicking on the first cell and dragging the box in the bottom right corner through to the last cell. In the top function ribbon of Excel, click on the "Home" tab to access the "Sort & Filter" tool and select the "Custom Sort" option. Under the "Order" category drop-down menu, choose to order your data set from "Smallest to Largest" and click "OK" to implement your changes.

3. Analyze your values

After sorting the values in your data set, you can manually identify large data discrepancies and outliers to eliminate. You can delete them from your data set or further calculate to remove only statistical anomalies. For smaller data sets, you can right-click on the row or column of your outlying value and choose to delete it from your spreadsheet.

4. Identify your data quartiles

To calculate the outliers in your data set, calculate your quartiles using Excel's automated quartile formula beginning with "=QUARTILE(" in an empty cell. After the left parenthesis, specify the first and last cells in your data range separated by a colon and followed by a comma and the quartile you want to define. Your formula may look similar to "=QUARTILE(A5:A50, 1)" or "=QUARTILE(B2:B200, 3)."

5. Define the interquartile range

The interquartile represents the expected average range of your data set without outlying values. You can calculate the interquartile range by subtracting the first quartile from the third quartile. In an empty cell, indicate the cell with your third quartile formula, a minus sign and the cell with your first quartile formula to input something like "C2-C1" and press enter for Excel to calculate it.

6. Calculate the upper and lower bounds

Defining the upper and lower bounds of your data set allows you to identify values large or smaller than each, respectively, to find the outliers. To find the upper bound of your data range, multiply the interquartile range by 1.5 and add it to your third quartile value to create a formula such as "=C2+(1.5*C3)." To find the lower bound of your data range, multiply the interquartile range by 1.5 and subtract it to from your first quartile value to create a formula such as "=C1-(1.5*C3)."

7. Remove your outliers

After defining the upper and lower bounds of your data set, review your values to find those that are higher than the upper bound or lower than the lower bound. You can consider these values as statistical outliers to remove from your data set for more accurate analysis or visualization reports. Eliminate them from your spreadsheet by right clicking the cell or cells of the outlying values and choosing to delete them.

Related: 10 Common Excel Formula Errors (And How To Fix Them)

Tips for calculating outliers in Excel

Here are a few tips to help you calculate outliers in Excel:

Adjust your outlying values

Depending on the purpose of your spreadsheet report, you might consider adjusting the values of your outliers rather than calculating them to simplify your analysis process. You can identify distinct value discrepancies and adjust them to match the highest value in the average range. This allows you to maintain the number of data points you've gathered while reflecting the relevant values you need for your report.

Look at data visualizations

Through Excel's set of tools and features, you can create data visualizations, such as plots and graphs. You can create an automated visualization based on your entered data range to see the average trend of your data points, along with the outlying data points that are separate from the average range. Data visualizations can help you identify significant discrepancies to remove or eliminate them from your report results.

Trim your data range

You can use Excel's automated data range trimming function to eliminate a specified percentage of values from the highest and lowest parts of your data set. To use this function, type "=TRIMMEAN(" in an empty cell to begin your formula. After the left parenthesis, indicate the first and last cells in your range separated by a colon, followed by the percentage you want to trim and a right parenthesis to create a formula similar to "=TRIMMEAN(A5:A50, 0.25)."

Please note that none of the companies or products mentioned in this article are affiliated with Indeed.