Box and Whisker Plots Excel

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)

Prepare your data. Arrange each series in a separate column. Include a header for each series.
Select the range. Click any cell inside the table and press Ctrl+A to select the whole block.
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.
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.
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.

Financial modeling errors cost companies millions annually

Financial Mastery Bundle includes error-proof templates and valuation models used by JPMorgan and Netflix analysts

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.