Boxplot Chart in Excel in 5 Steps

Boxplot Chart in Excel in 5 Steps

Boxplot Chart

Sometimes user may in need to judge how well data is distributed across a specific period, entity, category etc. Excel offers enrich features in charting where we can customize standard charts to build solutions which can accomplish our task easily. Boxplot is a graphically representation of numerical data through their quartiles. Box plot represents the minimum, maximum, median, first quartile and third quartile in the data set. Lets see how to plot the Excel Boxplot in this article.

Data source

figure 1.0

Step 1: Calculate followings using given formulas for each category:

  1. Min: “=MIN(Table4[Category- A])”
  2. 1st Quartile: “=QUARTILE.INC(Table4[Category- A],1)”
  3. Median: “=QUARTILE.INC(Table4[Category- A],2)”
  4. 3rd Quartile: “=QUARTILE.INC(Table4[Category- A],3)”
  5. Max: “=MAX(Table4[Category- A])”

Apply above formulas by forming a separate set of table as shown below form the source table (figure 1.0).

figure 1.1

Note: I have used Table (i.e. Table4) object of Excel you can directly refer it as Range in the formula

Step 2: Calculate the difference for each category and create a separate table as given below:

  1. 1st Quartile – Minimum
  2. Median – 1st Quartile
  3. 3rd Quartile – Median
  4. Max – 3rd Quartile
figure 1.2

Step 3: Create chart over calculated table shown in figure 1.2

  • Select Insert Ribbon Tab
  • Click on Recommended Charts under Charts group
  • Select Stacked Chart from Insert Chart dialog (figure 1.3)
  • Click OK
figure 1.3

Upon selecting given chart we have to switch the Rows to Column to form the stack over each category hence, Right Click over the chart and click Data Source, A dialog pops up, click on Switch Row/Column button and say OK as shown below:

figure 1.4

Step 4: Select the Min category which at the bottom in Blue color dialog and do followings:

  1. Select Min box
  2. Right click select Format Data Series
  3. Select Fill & Line tab
  4. Select No fill from Fill Section
  5. Select No line under Border section
figure 1.5

Step 5: Click on Design tab and select Standard Deviation sub menu under Error Bars sub menu under Add Chart Elements as shown below:

figure 1.6

Output

figure 1.7

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.