In many statistical calculations, you may need to find out the outliers in a dataset. In Microsoft Excel, you can show these outliers in a graph from a large dataset within seconds. This article demonstrates how to show outliers in Excel Graph with two different methods.
What Are Outliers in Excel Graph?
An outlier is a value in a dataset that is far away from the other values in the dataset. In other words, you can say that an outlier is a value that is much higher or lower than the mean and median of the dataset. In an Excel graph, you can consider the data points which remain far away from the other data points as outliers.
For example, consider a scenario where you have total points scored by 5 different players in an NBA season. The scored points are 1671, 1476,1898, 3041, and 1576. Now, if you plot a graph in excel you will see that point 3041 lies far away from the others. So, in this case, points 3041 is the outlier.
How to Show Outliers in Excel Graph: 2 Methods
Let’s say, you have a dataset where you have the Brand value over the twelve Months in a year. Now, you need to show the Outliers in an Excel graph. In this case, I will show you two methods to do so.
1. Showing Outliers in Box and Whiskers Chart
The Box and Whiskers Chart is one of the most convenient charts to show outliers. Now, if you want to show outliers in Box and Whiskers Chart, you can follow the steps below.
Steps:
- First, select range C5:C16.
Here, C5 and C16 are the first and last cells of the Brand Value column respectively.
- Next, click on the Insert tab at the top of the page.
- After that, go to the Insert Statistic Chart and then click on Box and Whiskers.
- At this point, you will get your Box and Whiskers Chart for the respective Brand Values.
- Now, in the graph, you will see two points indicating Brand Values $79.60 and $68.57 are not in the box, rather they are far away from the box in the graph.
- So, the Brand Values of $79.60 and $68.57 are outliers in this case.
💡Notes:
In case, you can not see the Outliers after plotting the Box and Whiskers Chart, you can use the following steps to solve your problem.
Steps:
- First, Right-Click on the plot area.
- Then, select Format Data Series and a box will pop up on the right side of the screen.
- At this point, first go to Series Options and then make sure you have the Show outlier points box checked like in the following screenshot.
2. Using Scatter Chart to Show Outliers in Excel Graph
You can also show the Outliers using the Scatter Chart. Now, if you want to use the Scatter Chart to show the Outliers, you can follow the following steps.
Steps:Â
- At the very beginning, select range C5:C16.
- Next, select the Insert tab from the top of the page.
- After that, click on Insert Scatter (X, Y) or Bubble Chart and a drop-down box will appear.
- At this point, select Scatter.
- Now, you will get a Scatter Chart. In this chart, the points located far away from the other points are the outliers. In this case, data points indicating Brand Values of $79.60 and $68.57 are the outliers.
Read More: How to Remove Outliers in Excel Scatter Plot
Download Practice Workbook
You can download the practice workbook from the link below.
Conclusion
Last but not the least, I hope you found what you were looking for from this article. Here I have described 2 methods to show outliers in Excel Graph. If you have any queries, please drop a comment below.