How To Create A Box Plot In Excel (Including Outliers)

in this tutorial i'm going to show you how to easily create a box plot otherwise known as a box and whisker plot by using microsoft excel i'll show you how to create a simple box plot with one data set and i'll briefly interpret what you see on the graph including outliers i'll also show you how to plot multiple box plots so you can have two groups side by side as always i'd be really grateful if you can like the video if you find it useful as it really does help support the channel and feel free to drop me a question below if you're stuck so let's jump into excel and get started for this tutorial it's worth noting that i'm using microsoft excel 365 pro plus so i've got some example data already entered into my sheet to create a box plot simply highlight the data then go to insert then in the chart section at the top you should see some different options in excel versions 2016 and above you should see an option to insert a statistic chart if you select this you can then see that there is an option to insert a box and whisker plot i can now see my box plot which happens to have no outliers and i'll discuss outliers later on in this tutorial i'll now briefly interpret what the box plot is presenting box plots typically show a summary of five values the bottom whisker marks the minimum value this is the same as using the min function so in my example the minimum value is 45. the bottom of the box marks the location of the first quartile or 25th percentile by default excel creates a box plot based on quartiles that were calculated with the median excluded otherwise known as exclusion median if you want to calculate this manually then use the quartile.exc function highlight your data and add a comma then add one and for me this comes out to 58. if you prefer to calculate the quartiles with the median included known as inclusion median then you use the quartile.inc function instead the line within the box refers to the median value and you can calculate this with the median function and when i do i get a value of 62. the top of the box marks the location of the third quartile or 75th percentile this is calculated in the same way as the first quartile but this time you add a number three into the second part of the function finally the top whisker signifies the maximum value in the data set and this can be calculated with the max function so mine was 80. excel also adds an x symbol onto the plot by default if you're wondering this marks the value of the mean or average value from the data set to calculate this you use the average function so that's a brief interpretation of a simple box and whisker plot i'll now go on to talking a bit more about outliers to do this i'll add a new value to my data set and this value will be 30. with the chart selected i'll extend the blue box the new data point is included into the plot as you can see this new value has been added as a circle and when you see circles on a box plot these indicate an outlier but how does excel define an outlier before i explain this i will update the first and third quartile values to account for the new data point that has been added to my data set so to determine the outlier we need to know the interquartile range the interquartile range is simply the distance between the third and first quartiles this is easy to calculate because we already know these values so in a new cell i will enter equals i'll then select the cell with the third quartile and then i'll subtract the cell with the first quartile so my interquartile range is 12. in terms of the threshold to determine what is an outlier let me focus on outliers below the box plot basically you take the value of the first quartile and subtract the value that is 1.5 times the interquartile range so it'll look like this in a new cell i will enter equals i'll select the cell containing the first quartile i'll then subtract and include an open bracket i'll click on the cell containing the interquartile range multiply by 1.5 i'll then close the bracket and press enter so in this example any value that is below 40 will be flagged as an outlier and will be shown as a circle to work out the outliers above the pot the process is similar but this time we add the values to the third quartile and if i do this i get a value of 88 so any values that are greater than 88 will be flagged as an outlier above the box plot sometimes you may not want to plot the outliers on the graph if you want to remove these then simply right click on the plot and select format data series and when you do a new sidebar should open to the right then simply uncheck the option to show outlier points you can also remove the x mark that represents the mean value by unchecking the show mean markers option also note here that you can change how the quartiles are calculated so this can be either inclusive or exclusive of the median as i mentioned briefly before just a word of warning if you plot multiple data points as box plots and you want to change these options then you will need to adjust this option for all of the parts on the graph to finish this graph i'll delete the x-axis label by selecting it and pressing delete on my keyboard and i'll also delete the title and finally i'll add a title to my y-axis by going to chart design add chart element axes title primary vertical and i'll rename this score so that's my finished simple box plot before i finish this tutorial let me show you how to plot multiple box plots side by side i have a new sheet here with two sets of data now the process is pretty much the same as the first example i'll highlight all the data and go to insert statistic chart and then select box and whisker and now you can see the two plots are side by side one thing that you may want to do here is to add a figure legend to show which box plots correspond to which group to do this with the graph selected go to chart design add chart element and then select a legend and then from here you can select a desired option i'll just select the first option now a legend has been added so i can easily see the blue plot corresponds to group one and the orange plot corresponds to group 2. i could then select the x-axis label and delete this as it is no longer needed alternatively some people like to have the group names shown on the x-axis instead of having a figure legend personally i don't know of an easy way to do this with box plots in excel but i'll show you one method which i found that does the same thing to be able to show the group names on the x-axis you need to adjust how your data appears in your sheet before creating your plot what i've done here in this new sheet is that in the first column i have added labels to say which group the data point is from either group one or group two then in the second column this is the data values as before and you can see that the data are stacked on top of each other so group two's data is directly below group ones instead of being side by side now if you select all of the data and add a box plot as before you should see that the x-axis labels have been added to signify the group names if you know the better way to do this then please let me know in the comments below as i'm struggling to find a better method and that brings me to the end of this tutorial you now know how to create and interpret a box and whisker plot by using microsoft excel if you found this video useful please leave a like it really does help support the channel if you've got a question pop it down in the comments below also consider subscribing for more weekly tutorials

Channel: Top Tip Bio

Views: 25,259

Rating: 4.9913793 out of 5

Keywords: how to create a box plot in excel, Box and whisker plot in Excel, Plot multiple box plots in Excel, box and whisker, box plot, Box plot in Excel 2016, excel box and whisker plot with outliers, multiple box and whisker plot excel, box plot outliers, box plots explained, box plots, excel box and whisker plot explained, excel box and whisker plot outliers, How to create a boxplot in Excel, Boxplots with outliers in Excel

Id: 39lsUsJsc2c

Length: 8min 0sec (480 seconds)

Published: Tue Mar 30 2021