Excel's Box & whisker charts interactive slicer dashboards with outliers

People often equate averages with typical, but that is often very far from the reality. If you think you want to look at averages, you should probably be using a box & whisker chart, often with an associated pareto chart for counts. Box & whiskers show means, medians, spreads, outliers in a clean succint way, and since 2016 they are built into Excel, Word and PowerPoint. Here I will explain exactly what they are, how to create them and the options available
We will also go through how to create an interactive clickable slicer based dashboard with a box & whisker & other visuals too.
Microsoft's documentation explains box & whiskers & Paretos etc. here: www.microsoft.com/en-us/microsoft-365/blog/2015/08/18/visualize-statistics-with-histogram-pareto-and-box-and-whisker-charts/
Table of Contents:
00:00 - Why averages suck
02:26 - What is a box & whisker
03:13 - Create a box & whisker
04:19 - Multi colour plots
04:48 - Likert scales
05:52 - Set up the data
08:13 - Create count chart through Pareto Chart
09:30 - Make charts not disappear with filters
10:32 - Box & Whisker options
11:18 - Outliers explained
14:04 - Inclusive vs exclusive median
14:55 - Pivots & labels
15:23 - PowerPoint makes starting easier

[@izzatafandi9298]

Great video, thank you! btw is it possible if the box chart to be viewed horizontally? and i tried using the slicer, but the chart just doesnt seem to respond when i filter out using the slicer, what seems to be the issue?

[@learnspreadsheets]

Ah no sadly box & whisker charts are only vertical in Ecvel

[@catherinebingham1498]

Hi, I really like this thanks. Is there a way to plot a data point over the box and whisker i.e if behind vietnam was a distinct list of subjects, to have on the overall chart a point highlighting the result for 'history' against all?

[@learnspreadsheets]

Only if you make the object semi transparent & put another object behind

[@jjsambac]

Is there a way to do this but with a larger dataset? Tried your way but my file keeps crashing

[@learnspreadsheets]

Sorry I’m not sure maybe try 64 by excel

[@MedschoolConcepts]

How can you add standard deviation or standard error of mean bars to a box plot

[@learnspreadsheets]

Good question but I am pretty sure you cannot, the box and whisker charts already show spread (albeit in a different way), you can add that to other charts though

[@MedschoolConcepts]

@@learnspreadsheets thank you !

[@learnspreadsheets]

No worries