How to Create and Customise Box and Whisker Charts in Microsoft Excel

Box and Whisker charts (also known as box plots) are powerful statistical visualisations designed to depict data distributions, central tendencies, and statistical outliers across multiple datasets. In Microsoft Excel, generating a native Box and Whisker chart requires no manual quartile math or complex formula arrays. Built directly into modern Excel versions across Windows, macOS, and Microsoft 365, this chart type automatically calculates minimums, first quartiles, medians, third quartiles, maximums, and outliers.

Anatomy of an Excel Box and Whisker Plot

Understanding each visual component helps you interpret and explain statistical findings clearly to stakeholders:

• The Box: Represents the interquartile range (IQR), spanning from the first quartile (25th percentile) to the third quartile (75th percentile). Fifty percent of the dataset’s observations reside inside this boundary.
• The Median Line: A horizontal line drawn through the box indicating the 50th percentile (the central value of the distribution).
• The Mean Marker: Marked by a small “x” cross inside the box indicating the arithmetic mean of the dataset.
• The Whiskers: Vertical lines extending from each edge of the box toward minimum and maximum values within 1.5 times the IQR.
• Outlier Dots: Individual points displayed beyond the whiskers representing anomalous data points.

Structuring Your Source Data Correctly

For Excel to generate accurate box plots automatically, arrange your dataset in contiguous columns or rows with distinct category headers. For example:

| Team Alpha | Team Beta | Team Gamma |
|------------|-----------|------------|
| 45         | 62        | 38         |
| 52         | 58        | 42         |
| 61         | 70        | 55         |
| 39         | 65        | 49         |
| 88         | 61        | 51         |

If you have uneven sample sizes across groups, leave blank cells at the end of shorter columns; Excel handles unbalanced datasets without distorting quartile calculations.

Inserting a Box and Whisker Chart in Excel

Follow these steps to generate the chart from your data range:

1. Highlight your entire dataset, including the column headers.
2. Click the Insert tab on the Excel ribbon.
3. In the Charts section, click the Insert Statistic Chart dropdown icon (resembling a blue histogram).
4. Under the Statistical category, click Box and Whisker.

Excel instantly places a dynamic Box and Whisker chart into your active worksheet, plotting distinct boxes for each column category.

Customising Box Plot Options and Formatting

To refine how statistical markers and quartiles are presented, open the dedicated series formatting pane:

1. Right-click any of the boxes on the chart and select Format Data Series.
2. In the side pane, ensure the Series Options tab (the column bar icon) is selected.
3. Tailor the following crucial settings:
   • Show inner points: Enable this checkbox to plot every individual data point as a small dot along the whisker line, providing deeper density context.
   • Show outlier points: Keep this enabled to flag extreme outliers outside the 1.5 IQR boundary.
   • Show mean markers: Toggles the display of the mean “x” marker inside each box.
   • Show mean line: Draws a continuous line connecting the means across multiple category boxes (useful for spotting categorical trends).
   • Quartile calculation: Choose between Exclusive median (recommended for larger datasets following NIST guidelines) and Inclusive median (commonly taught in secondary school statistics).

Refining Colours, Titles, and Chart Presentation

To prepare your chart for executive reporting or academic publishing, implement clean aesthetic touches:

• Informative Titles: Double-click the chart title placeholder to assign a clear descriptive label detailing the measured variable and sample cohort.
• Custom Fill Colours: Select individual series boxes and apply distinct palette fills under the Fill & Line bucket tab to colour-code cohorts intuitively.
• Data Labels: Click the green + Chart Elements button and check Data Labels to overlay exact numeric values for quartiles and median lines directly onto the chart.

Utilising Excel’s built-in Box and Whisker charts empowers data analysts to communicate complex distribution metrics with clarity, rigor, and immediate visual impact.