Box and Whisker Plots Excel

by

What Is a Box‑and‑Whisker Plot?

A box‑and‑whisker plot (also called a box plot) visualises a five‑number summary: minimum, first quartile (Q1), median (Q2), third quartile (Q3) and maximum. The “box” contains Q1, Q2 and Q3, while the “whiskers” extend to the minimum and maximum values. Outliers appear as individual points beyond the whiskers.

Why Use Box‑and‑Whisker Plots in Excel?

  • Quickly compare distributions across several categories.
  • Identify outliers and data spread without calculating standard deviation.
  • Ideal for finance, operations, HR and marketing dashboards.

Step‑by‑Step: Create a Box‑and‑Whisker Plot in Modern Excel (Office 365/2019)

  1. Prepare your data. Arrange each series in a separate column. Include a header for each series.
  2. Select the range. Click any cell inside the table and press Ctrl+A to select the whole block.
  3. Insert the chart. Go to Insert ► Charts ► Statistics ► Box and Whisker.
    • If you cannot see the Statistics group, click Insert ► Recommended Charts ► All Charts ► Box and Whisker.
  4. Fine‑tune the design. With the chart selected, use the Chart Tools ► Design and Format tabs to:
    • Change colors or fill patterns.
    • Add data labels for the median.
    • Show or hide outlier points.
  5. Adjust chart options. Right‑click a box, choose Format Data Series, then explore the Series Options pane:
    • Gap Width – controls spacing between boxes.
    • Show inner points – displays points between the whiskers.
    • Show outlier points – highlights values outside the whiskers.
    • Show mean markers / line – adds a visual reference for the mean.
    • Quartile calculation – choose Inclusive (median included) or Exclusive (median excluded).

Common Mistakes & Tips

  • Empty cells – Excel treats blanks as zero. Replace them with #N/A to exclude.
  • Too many outliers – Consider trimming extreme values or using a log scale.
  • Series order – Use Format Data Series ► Series Order to reorder categories.
  • Label clarity – Add a legend and a concise chart title that includes the metric and time period.

Industry‑Specific Examples

Finance: Quarterly Revenue Distribution

Place each region’s quarterly revenue in separate columns. The box plot instantly shows which region has the widest revenue spread and identifies periods with unusually low or high performance.

For You:

Boost Profits with Activity-Based Costing

Discover hidden costs and optimize profitability

Learn More

Human Resources: Employee Tenure

Use one column per department to visualise tenure. Outliers may reveal employees nearing retirement or very recent hires.

Marketing: Campaign Conversion Rates

Group conversion percentages by channel (Email, Social, Paid Search). Boxes highlight which channel delivers consistent results and where volatility exists.

Quick‑Reference Checklist

Task Done?
Data organised in columns with headers ☐
No blank cells (use #N/A if needed) ☐
Insert Box‑and‑Whisker chart via Insert ► Statistics ☐
Adjust Gap Width and show/hide outliers ☐
Add a clear title and axis labels ☐
Apply corporate colour palette (optional) ☐

Next Steps – Automate Your Reporting

Once you master box‑and‑whisker plots, embed them in automated dashboards. Our automated Excel reporting template lets you refresh data with a single click, update all charts, and share a polished PDF report in seconds.

Get More Excel Resources

Explore our library of Excel tools to boost productivity:

For You:

Download Excel & Financial Templates

Automated reports, dashboards, and financial planning tools

Learn More
Related: