Excel (2016) - side-by-side boxplot

Instructional video showing how to create a side-by-side boxplot with Excel 2016.
Note: this will not work with earlier versions of Excel, see peltiertech.co... for a nice explanation on how to do this for earlier versions.
Companion website at PeterStatistic...
Donation welcome at Patreon: www.patreon.co...

[@stevenson720]

after about 2 hours of looking I eventually found this and its perfect. Thank you

[@stikpet]

thanks, glad it helped. If you let me know you're search terms, I could add some tags to the video for others to find it a bit faster....

[@myha2491]

That's exactly what I'm looking for. Short and straight to the point. Thanks a bunch!

[@stikpet]

Glad it was helpful.

[@guiguitoLemon]

That's exactly what I was looking for! Perfect tutorial!

[@stevenson720]

exactly the same. This guy is great.

[@skayam3539]

Legend. Great tutorial

[@kamalnikhilgokavarapu6006]

Thanks Man! You saved my day

[@stockstradr]

Great tutorial !

[@skayam3539]

nice video

[@stikpet]

Thanks

[@InboardPG]

Sometimes when I do this the box plot shows up completely empty. Completely confused...

[@stikpet]

hard to tell what causes this, without being able to replicate the problem. It could be you did one step incorrect, it could be that Excel made a mistake, it could be a specific problem with the data. One reason for a box-plot to appear with missing boxes, is if one or more quartiles are the same. The box plot shows a box whisker from the minimum to the first quartile, a box from the first quartile to the median, another box from the median to the 3rd quartile, and another whisker from the 3rd quartile to the maximum. If a quartile is equal to the median, no box will show. Hope this still helped though.

[@anonymousjanitor270]

Thank you!

[@stikpet]

You're welcome! Glad it helped.

[@skayam3539]

sympa

[@yashajathan8032]

Very Useful Video.Thanks a lot :)

[@stikpet]

glad it helped.

[@yashajathan8032]

@@stikpet Could you please let me know how to get data points on the plots? Thanks

[@stikpet]

@@yashajathan8032 right-click on one of the boxes, select ' Format Data Series' then click the 'Series options' icon (looks like a small bar chart), and click and check the 'Show inner points' . Hope this is what you meant.

[@misavo4566]

Thank you so much!

[@stikpet]

You're welcome!

[@ik5377]

Hi, i tried this method but it says that the data range is too large. Any advice?

[@stikpet]

difficult to say without being able to re-produce the error you get.

[@idiebholah7388]

Hi,is there a maximum number of boxes taht we can create.? I have created a bix plot but not all boxes are showing.Thanks

[@stikpet]

I don't think there is a maximum. Perhaps one category has no data? Hard to say what the problem is, if I cannot replicate it :-(

[@alexandergangl2556]

Thanks for this tutorial. It worked perfectly in the past. Unfortunatly now I've got a problem. After selecting the diagram type it is not empty anymore, but there are already filled with a gird. If I contiune the procedure no box plots are shown in the diagram. Maybe Execl 2019 causes this problems? Hopefully you can help me. Kind regards... Alexander

[@stikpet]

Make sure to select an empty cell when you start, this usually then gives an empty diagram. I noticed that for me with Excel 2019 it no longer worked because it kept saying the range is too large. I solved it by ordering the data on location, then created one series for each location. Hope this helps.

[@alexandergangl2556]

@@stikpet Perhaps you can be so kind and upload another tutorial regarding this issue.... Kind regards

[@stikpet]

will do, hopefully this weekend.

[@alexandergangl2556]

@@stikpet thx... 👍

[@stikpet]

Hope this works: kzbin.info/www/bejne/fn-oopx7arl8iac

[@sapatoization]

Do you know if it is possible to remove the whiskers from the boxplot? That is, only display the 25 - 75th percentiles (the boxes)?

[@stikpet]

Not to my knowledge. I tried removing the borders, the whiskers than disappear but you can also not see the difference anymore between the two quartile boxes. It might be possible perhaps if you follow an older approach described at support.office.com/en-us/article/create-a-box-plot-10204530-8cdf-40fe-a711-2eb9785e510f Hope this helps.

[@katharinaschuler8689]

Do you know if there is a function to change the distance between the box plots?

[@stikpet]

If you RIGHT-click on any of the boxes and then select 'Format Data Series' there should be a 'gap width' that you can adjust.

[@Mist_R]

poggers

[@orxanibayev340]

Just one question: could we make each box in different color?

[@stikpet]

good question, there doesn't seem to be one easy way to do this. A simple cheat would be to copy the chart to MS Paint and then fill them in. One way would be to sort the data so that you have all scores of each category together, then create a separate entry for each category. The categories will then be shown in the legend and not the horizontal axis. Hope this helps.

[@AG-xs1kw]

great video! However I need to have each boxes in different color. How do I do that?

[@stikpet]

Apologies for the late reply, has been busy with the holidays. Unfortunately I also haven't figured out a way to get the boxes in a different color in one go. One thing you can do is to sort the data on the category (in my example the location) and then add each category as a series. Then each series gets it's own color.

[@AG-xs1kw]

@@stikpet Yes it works! Thank you so much for the reply :) I have one more question... After the whisker plot is made, if I sort any of the rows (by any column), the shapes of the boxes change. Is this expected behavior? Sorry for asking this... I'm a total noob in statistics :p

[@stikpet]

The shapes should not change, since the data itself is not changed. Most likely if you sort after you created the box-plot the references will be messed up. Perhaps simply create a copy of the data on a new sheet just for the box-plot, so on that sheet you don't have to sort it back again?

[@arijitgupta5773]

@@stikpet yes that makes sense. However, I'm not sure why the shape changes? Although, the data is sorted in different order but mean median and range still remains same

[@stikpet]

Not sure what you mean. If I have three categories A, B and C, and for A the numbers 1, 2, 5, for B 10, 22, 28, for C 200, 356, 2345, 6543. Then if I sort the data and my references in the diagram get mixed up the mean, median etc. are bound to change, which is why the diagram will then look different.

[@BanQasimBanaHikmat]

Nice but please how to label each box plot?

[@stikpet]

At 1:27 I add the axis labels (the name for each box plot). Not sure if that is what you refer to. Otherwise, which label would you like to add?

[@Lucasss267]

excel 2016 too easy

[@skayam3539]

That's exactly what I was looking for! Perfect tutorial!

[@ahnafrr]

Legend. Great tutorial

[@stikpet]

Thanks. Glad it helped. Good luck with your analysis.