How do I create Box Plot in Excel?

charts
excel

#1

Hi I am doing an inter-department salary analysis, I am trying to do it using box plots in excel since the entire sheet has lots of formulas and the data also is not very big. Can someone please help me on how to create box plots using excel.


#2

Hi,

Please refer to the following link -

Anyways chandoo.org is the best way to get your excel doubts sorted. Hope this helps.

Regards,
Aayush


How can I create Box Plot Chart in Excel?
#3

Hi,

We can create box plot in excel by using error bars with stacked column chart. Here we need to populate five number summary for both departments first and after that create data set for box plot. Let’s follow the below steps to perform this.

Step - 1 Create Five number summary (Max, Third Quartile, Median, First Quartile, Min) for both departments.

Step - 2 Create Data Source from above table for Box plot.

Step - 3 Select range D10:F12 and insert Stacked column chart.

Step - 4 Select Chart and Switch Row to column

Step - 5 Select “Q1” of the stacked column chart and error bar to it.

After that go to more option of error bar and select minus from direction after that specify values for custom error amount of Q1-Min (E9:F9) under negative error value.

Step - 6 Add error bar for “Q3-Median” and go to more option after that select plus from direction and specify values for custom error amount of “Max-Q3” (E13:F13) under positive error value.

Step - 7 Remove fill color and border line for Q1 and Remove only fill color for Median-Q1 and Q3-Median.

Step - 8 Now, you can remove legend and add level for horizontal axis as Department A and Department B.

Regards,
Sunil


#4

Nice answer @Sunil

Thanks for posting it