Outliers in the Regression Analysis are a common issue. In most cases, Outliers are the result of experimental errors. Having this kind of Outliers can steer the direction of the analysis and create misconceptions about the outcome. If you are baffled to find out the Outliers from your Regression Analysis results, this article may come in handy for you. Here we discuss how you can find Outliers in Regression Analysis in Excel effectively.
How to Find Outliers in Regression Analysis in Excel: 3 Easy Ways
We are going to use 3 separate ways in order to distinguish the Outliers from the Regression Analysis results in Excel. The methods will involve using various types of functions and charts. For better understanding, prior knowledge of the Regression Analysis and basic charts is recommended.
1. Applying Combined Formula
Using the IF function we will get the Outliers from our dataset. We will also use the SQRT, ABS, and SUM functions. We can conduct the Regression Analysis and extract the Outliers from the output.
Steps
- In the beginning, we need to conduct a Regression Analysis of our dataset.
- In order to do this, go to Data > Data analysis.
- Then in the new dialog box named Data Analysis, select the option Regression.
- Then click OK.
- In the Input Y Range box, select the range of cells C4:C15.
- Then in the Input X Range box, select the range of cells B4:B15.
- Next tick on the Labels box.
- Then in the Output options group, select the Output Range and then select cell G5 in the range box.
- After then, check the Residuals, Standardize Residuals, and Line Fit Plots boxes.
- Click OK after this.
- Then you will notice that the Regression Analysis results are now presented in the Excel sheet starting from the G5 cells.
Now we will need the Squared Sum value of the residuals and then we will use that value to calculate the Standard Deviation of the residuals.
- Select the cell K26, and enter the following formula:
=I26^2- Entering this formula will calculate the square of the residual at cell K26.
- Drag the Fill Handle to cell K36.
- Doing this will fill the range of cells K26:K36 with the squared value of the Residuals.
- Then select the cell K37 and enter the following formula:
=SUM(K26:K36)- Doing this will calculate the sum of the range of cells K26:K36, in other words, we got the Sum of the Squared Residuals(SSE).
- Next, to calculate the Standard Deviation(S), we select the cell K38 and enter the following formula:
=2*SQRT(K37/(H10-2))
- This value is the value of the Standard Deviation of the residuals of the given dataset.
- Now we select the cell L26 and enter the following formula:
=IF(ABS(I26)>$K$38,"Outlier","Not Outlier")
- This formula will check whether the absolute value of the residuals is greater than the Sum of the Squared Residuals(SSE) calculated in the earlier step. If the residual is greater than the SSE value, then the cell will show “Outlier”. Otherwise, it will show “Not Outlier”.
- Drag the Fill Handle to the cell L36, and doing this will indicate whether the cell value is an Outlier or not in the range of cells L26:L36.
- We can observe that the first value of the range of cell L26:L36 is showing Outlier And the rest of them are Not Outliers.
- Next, select the cell D5 and enter the following formula:
=C5+$K$38 
- Then drag the Fill Handle to cell D15, which will fill the range of cell D5:D15 with the upper limit of the casualties Outlier value. Any value above the values will be considered an Outlier.
- Similar way, select cell E5 and enter the following formula:
=C5-$K$38
- Then drag the Fill Handle to cell E5. Doing this will fill the range of cell E5:E15 with the lower limit of the Outlier value of Casualties.
- Then we created the graph with the values we obtained so far. Which constitutes the lower and the upper limit of the Outlier values.
We can see clearly from the graph that the first value is on the verge of the upper limit. So this value is the Outlier here.
Read More: How to Calculate Outliers in Excel
2. Applying Inter-Quartile Range
By calculating the first and the third Quartiles of a dataset and then estimating the Inter-quartile Range between them, we can separate the Outlier from the dataset. We will use the combination of QUARTILE.EXC and IF functions to achieve this.
Steps
- In the beginning, select the cell F5 and enter the following formula:
=QUARTILE.EXC(C5:C11,1)
- In the beginning, select the cell F5 and enter the following formula:
=QUARTILE.EXC(C5:C11,3)
- In the beginning, select the cell F7 and enter the following formula:
=F6-F5
- In the beginning, select the cell F8 and enter the following formula:
=F6+1.5*F7
- In the beginning, select the cell F9 and enter the following formula:
=F5-(F7*1.5)
- Then select the cell D5 and enter the following formula:
=IF(C5<G9,"Outlier","Not Outlier")
- Then drag the Fill Handle to cell D11.
- Doing this will fill the range of cell D5:D11 with text indicating whether the value is an Outlier or not.
When we created a scatter plot of the graph, we can easily distinguish the Outliers from the rest of the dataset in Excel while conducting regression analysis.
Read More: How to Show Outliers in Excel Graph
3. Using Charts to Find Outliers
Using charts is the fastest way to separate Outliers in Regression Analysis results. Although they can’t be 100 percent accurate and can vary from person to person. It can be used as a part of the primary screening process.
Steps
- For this method, we need to create a Scatter chart.
- To do this, go to the Insert tab and then click on the Scatter in the charts group.
- Then right-click on the mouse and click on the Select Data.
- After this, there will be a new dialog box named Select Data Source.
- In that box click on the Add button.
- Then in the Edit Series dialog box, click on the Series X values range box and select the range of cells B5:B11.
- Then click on the Series Y value range box and select the range of cells C5:C11.
- Click OK after this.
- Then you will notice that the chart in the worksheet now has the data plotted.
- In order to plot a Trendline, we need to click on the plus icon on the right side of the chart.
- Then choose and tick the Trendline box.
- There will be a Trendline after then.
- Double click on the Trendline to select it and then right-click on it.
- From the context menu, click on the Format Trendline.
- On the side panel menu, tick the Display Equation on chart and the Display R Squared value boxes.
- The equation and the r-squared value of the equations are now presented in the chart.
- From the chart, it is quite evident the third value in the database is the Outlier, as it is quite a way from the Trendline.
💬 Note
The above method only works when the dataset is small. Where you can scan the value and the chart quickly. In the case of a large dataset, you better opt for the second and the first method.
And this is how we find out the Outliers in regression analysis in Excel using the scatter chart.
Read More: How to Find Outliers with Standard Deviation in Excel
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the question how to find Outliers in Regression Analysis Excel’ is answered here in 3 separate steps with elaborate explanations. For this problem, a workbook is available for download where you can practice these methods.
Related Articles
<< Go Back to Outliers in Excel | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
This is very hard to follow for setting up with your own dataset. The column values are not consistent, so it is hard to know what you should be applying across different columns. The standard deviation calculation references G10 – but there is nowhere where G10 is referenced! The formula is also not the standard deviation formula I am familiar with, which has N on the bottom. Doing the maths to work out what G10 is – it is 9 – but there is NO VALUE 9 anywhere above – so it is very confusing. I’ve tried a number of different ways to work this out, and every time, either all my points are outliers or all are not. The graph it is very easy to see that there are outliers – but this regression method is very complicated to follow!
Hello KAREN W,
First of all, we would like to apologize for the trouble. As you pointed out, there were in fact some issues with the cell referencing in the Regression mathod, luckily they have been updated.
The Exceldemy team is grateful to you for sharing your thoughts and feedback. Hopefully, now you can obtain the desired result.