How to Create a Box Plot in Excel 2013

If you are performing statistical analysis, you may want to create a standard box plot to show the distribution of a set of data. In a box plot, numerical data is divided into quartiles. A box is drawn between the first and third quartiles, with an additional horizontal line drawn along the second quartile to mark the median value. In many box plots, the minimums and maximums outside the first and third quartiles are depicted with lines, which are traditionally referred to as whiskers.

While Microsoft Excel 2013 does not feature a native built-in chart template specifically for a box plot, you can easily build your own custom, professional box plots by leveraging a stacked column chart. The process involves four primary phases:

1. Calculate quartile values from your raw source data set.
2. Calculate the specific quartile differences (differentials).
3. Generate a baseline stacked column chart type from those calculated ranges.
4. Convert and format the stacked column chart into a clean box plot style.

In our practical example, the source data set contains three distinct columns representing annual records. Each column contains 30 sequential data entries generated from the following ranges:

• Column 1 (2013): Data values ranging from 100 to 200
• Column 2 (2014): Data values ranging from 120 to 200
• Column 3 (2015): Data values ranging from 100 to 180

Step 1: Calculate the Quartile Values

To build the foundation of your chart, you must first calculate the minimum, maximum, and median values, along with the exact first and third quartiles from your raw source data.

1. Create a secondary data calculation table within your worksheet and populate it using the following Excel formulas:
   | Statistical Value | Excel Formula Syntax |
   | — | — |
   | Minimum Value | =MIN(cell range) |
   | First Quartile (Q1) | =QUARTILE.INC(cell range, 1) |
   | Median Value (Q2) | =QUARTILE.INC(cell range, 2) |
   | Third Quartile (Q3) | =QUARTILE.INC(cell range, 3) |
   | Maximum Value | =MAX(cell range) |

2. Once entering these formulas, you will generate an intermediate calculation table containing your exact dataset values. The following output values are derived directly from our provided sample data set:

Step 2: Calculate Quartile Differences

Next, you need to calculate the precise differences or differentials between each statistical phase. To map out the structured segments of a stacked chart, you must calculate the exact differences between:

• First quartile and the minimum value
• Median and the first quartile
• Third quartile and the median
• Maximum value and the third quartile

1. Begin by creating a third data table structure. Copy the calculated minimum values from your previous step directly into the first row of this new table.
2. Use the standard Excel subtraction formula (=cell1 - cell2) to compute the remaining differentials, populating the rest of the table segments accordingly.

Step 3: Create a Stacked Column Chart

The data structured inside the third table is perfectly formatted for constructing a box plot. We will begin by inserting a standard stacked column chart, which we will then modify.

1. Select the entire calculated dataset within your third table, then navigate to the Excel ribbon and click Insert > Insert Column Chart > Stacked Column.
   Initially, this generated chart will not resemble a standard box plot. This occurs because Excel plots stacked columns from horizontal data sets by default instead of vertical configurations.
2. To reverse the data visualization layout, right-click anywhere on the chart canvas and click Select Data.
3. In the dialog box, click Switch Row/Column.
   Professional Formatting Tips:

• To quickly rename your data columns: On the Horizontal (Category) Axis Labels section, click Edit, select the specific cell range in your third table containing your category names, and click OK.

• To clean up your legend labels: On the Legend Entries (Series) side, click Edit and manually enter your preferred series names.

4. Click OK to apply the changes.

Your updated graph will now properly display the columns as shown below. In this specific workflow example, the chart title has been updated, and the legend has been hidden to clean up the workspace layout.

Step 4: Convert the Stacked Column Chart to the Box Plot Style

Hide the Bottom Data Series

To transform the structural stacked column graph into a recognizable box plot, you must begin by hiding the baseline foundational data series:

1. Left-click to select the lowest data series block of the columns.
   Note: Clicking a single column block automatically selects all matching series instances across the entire chart.
2. Navigate to the top ribbon and click Format > Current Selection > Format Selection. The advanced Format Data Series panel will open on the right side of your screen.
3. Inside the Fill & Line options tab within the properties panel, choose No Fill. This will make the baseline segment invisible.
   The bottom data series are now completely hidden from sight within your chart canvas.

Create Whiskers for the Box Plot

The next step requires replacing both the topmost and the second-from-bottom data series blocks with clean lines, or whiskers.

1. Select the highest visible data series block at the top of your columns.
2. Under the Fill & Line options tab in the properties panel, select No Fill to hide the block.
3. Go to the ribbon menus and click Design > Add Chart Element > Error Bars > Standard Deviation.
4. Click directly on one of the newly drawn error bars to select them.
5. Open the Error Bar Options tab inside the formatting sidebar panel and configure these specific settings:

• Set Direction to Minus.
• Set End Style to No Cap.
• For the Error Amount, check the Percentage option and set it to 100%.

6. Repeat these exact sub-steps for the second-from-bottom data series block to create the lower whisker element.
   Your customized stacked column chart will now clearly look like a standard statistical box plot.

Color the Middle Box Areas

Standard box plots are typically styled with a single matching fill color paired with a subtle, clean border outline. Follow these closing steps to finalize your presentation layout:

1. Click to select the primary top remaining box area of your data series.
2. Inside the Fill & Line properties sidebar tab, click on Solid Fill.
3. Select your preferred background fill color from the color palette dropdown.
4. Expand the Border menu options on that same tab and select Solid Line.
5. Choose a clean matching outline color and specify your preferred border stroke line Width (e.g., 1.5 pt).
6. Apply these exact same style, border, and color values to the adjacent middle sections of your box plot.

Your final chart output will render as a highly polished, presentation-ready statistical box plot.