How to Make a Box and Whisker Plot in Excel: Step-by-Step Guide (2025)

Box and whisker plots are powerful tools for visualizing data distribution, identifying outliers, and comparing multiple datasets at a glance. While Excel offers box plot functionality, many users find the process unnecessarily complicated and time-consuming. This comprehensive guide shows you exactly how to make a box and whisker plot in Excel, plus reveals a much easier free alternative that works on any device.

Whether you're a student analyzing research data, an office worker creating reports, or a data analyst visualizing statistics, you'll learn the complete process and discover why thousands of users are switching to simpler online tools.

What You'll Need Before Starting

Before creating your Excel box plot, ensure you have:

Excel Version Requirements:

• Excel 2016 or later (Windows/Mac)
• Excel 365 (subscription version)
• Excel 2019

Important: Excel 2013 and earlier versions do NOT have built-in box and whisker plot functionality. If you're using an older version, skip to the "Free Online Alternative" section below.

Data Format Requirements:

• Data organized in columns or rows
• Numerical values only
• At least 5 data points per dataset (recommended)
• Headers for each dataset (optional but helpful)

Step-by-Step: How to Create a Box Plot in Excel

Step 1: Prepare Your Data

Open Excel and organize your data in columns. Each column represents a different dataset you want to compare.

Example data structure:

Test A    Test B    Test C
65        72        68
78        85        74
82        79        81
90        88        86
75        82        79
88        91        85

Make sure there are no empty cells within your data ranges, as this can cause chart errors.

Step 2: Select Your Data Range

Click and drag to highlight all the data you want to include in your box and whisker plot. Include column headers if you have them - Excel will use these as series names in your chart.

Pro tip: Hold Ctrl (Cmd on Mac) to select non-adjacent columns if your data isn't side-by-side.

Step 3: Insert the Box and Whisker Chart

1. Navigate to the Insert tab in the Excel ribbon
2. Look for the Insert Statistic Chart button (icon shows a histogram)
3. Click the dropdown arrow next to it
4. Select Box and Whisker from the chart options

Screenshot description: The Insert Statistic Chart menu showing Histogram, Pareto, Box and Whisker, Waterfall, Funnel, and Stock chart options.

Excel will generate a basic box plot, but it probably doesn't look perfect yet.

Step 4: Customize Your Box Plot

Excel's default box plot often needs adjustment. Right-click on the chart to access customization options:

Chart Title:

1. Click the chart title
2. Type a descriptive title like "Test Score Distribution Analysis"

Axis Labels:

1. Click the + icon next to your chart
2. Check Axis Titles
3. Edit both horizontal and vertical axis labels
4. Vertical axis: "Score Values"
5. Horizontal axis: "Test Groups"

Color Customization:

1. Right-click any box in the plot
2. Select Format Data Series
3. Choose Fill & Line options
4. Change colors to match your presentation or report theme

Step 5: Adjust Box Plot Settings

Excel offers specific box plot configuration options:

1. Right-click on the boxes (data series)
2. Select Format Data Series
3. In the sidebar, look for Series Options

Key settings to adjust:

Show inner points: Check this to display all individual data points inside the box plot

Show outlier points: Displays data points that fall outside the whiskers (recommended for identifying anomalies)

Show mean markers: Adds a marker showing the mean value (different from median)

Show mean line: Connects mean values across multiple datasets

Quartile calculation: Choose between "Inclusive median" or "Exclusive median" (Excel default is exclusive)

Step 6: Fine-Tune Your Chart

Polish your box and whisker chart with these final adjustments:

Gridlines:

• Click the + icon
• Check/uncheck Gridlines for better readability

Legend:

• Position the legend at top, bottom, left, or right
• Or remove it entirely if column labels are sufficient

Chart Style:

• Use the paintbrush icon to apply pre-designed styles
• Match your corporate branding or report requirements

Understanding Your Excel Box Plot

Once created, your box and whisker plot displays five key statistics (the "five-number summary"):

• Minimum: The lower whisker endpoint (excluding outliers)
• Q1 (First Quartile): Bottom of the box (25th percentile)
• Median (Q2): Line inside the box (50th percentile)
• Q3 (Third Quartile): Top of the box (75th percentile)
• Maximum: Upper whisker endpoint (excluding outliers)
• Outliers: Individual points beyond the whiskers

Want to learn more about interpreting these elements? Check out our comprehensive guide on how to read box plots.

Common Excel Box Plot Problems and Solutions

Problem 1: "Box and Whisker" Option is Grayed Out

Cause: You're using Excel 2013 or earlier, or your data isn't properly selected.

Solution:

• Verify you have Excel 2016 or later
• Ensure you've selected numerical data only
• Remove any text values from selected cells

Problem 2: Chart Looks Weird or Incomplete

Cause: Empty cells, text in data range, or insufficient data points.

Solution:

• Fill or remove empty cells in your dataset
• Ensure all values are numbers, not text
• Have at least 5 data points per series

Problem 3: Can't Customize Specific Elements

Cause: Clicking the wrong chart element.

Solution:

• Click directly on the specific box/whisker you want to format
• Use the Format tab that appears when chart is selected
• Check the dropdown menu to select exact chart elements

Problem 4: Outliers Not Showing

Cause: "Show outlier points" option is disabled.

Solution:

• Right-click the data series
• Format Data Series > Series Options
• Check "Show outlier points"

Problem 5: Box Plot Updates Incorrectly

Cause: Data range is hardcoded, not dynamic.

Solution:

• Right-click chart > Select Data
• Edit data range to include new rows/columns
• Consider using Excel Tables for automatic range expansion

Excel Box Plot Limitations You Should Know

While Excel can create box and whisker plots, it has significant drawbacks:

Time-Consuming Process

Creating a properly formatted box plot in Excel takes 5-10 minutes, even for experienced users. Every customization requires multiple clicks through nested menus.

Limited Customization

• Can't easily adjust box width
• Limited color palette options
• No modern gradient or shadow effects
• Difficult to match specific brand guidelines

Version Restrictions

• Requires Excel 2016 or later (expensive subscription)
• Not available in Excel Online (browser version)
• Charts may break when sharing with users on older Excel versions

Mobile Unfriendly

• Creating box plots on mobile Excel is nearly impossible
• Touchscreen controls make precise editing frustrating
• Charts often don't display correctly on smaller screens

No Export Flexibility

• Limited export formats (Excel, PDF, image)
• Image exports often have quality issues
• Difficult to get transparent backgrounds for presentations

Data Privacy Concerns

• Requires uploading sensitive data to Microsoft cloud
• Files can be accidentally shared via OneDrive
• Enterprise users face compliance challenges

Why Use a Free Online Box Plot Maker Instead?

After walking through Excel's complicated process, you might be wondering: "Isn't there an easier way?"

Yes, there absolutely is.

Free online box plot makers like BoxPlotMaker.online offer a dramatically simpler experience with powerful advantages:

1. Works in 30 Seconds

• Paste your data
• Click "Generate"
• Download your professional box plot

No software installation, no subscription fees, no version compatibility issues.

2. No Excel License Required

Save hundreds of dollars on Excel subscriptions. Our free tool works on any device with a web browser:

• Windows, Mac, Linux computers
• iPhones and Android phones
• Tablets and Chromebooks

3. Better Customization Options

Unlike Excel's limited formatting, you get:

• Advanced color customization
• Professional design templates
• Custom axis labels and titles
• Adjustable box widths and spacing
• Modern visual styles

4. Instant Export Options

Download your box plot as:

• High-resolution PNG images
• Print-ready PDF files
• Transparent backgrounds for presentations
• Web-optimized formats

5. Completely Private

Your data never leaves your browser. Unlike Excel cloud services:

• No account creation required
• No data stored on servers
• GDPR compliant
• Perfect for sensitive datasets

6. Always Up-to-Date

No software updates to install. Every time you visit, you get the latest features and improvements automatically.

7. Mobile-Friendly Interface

Create professional box plots on your phone during fieldwork, at conferences, or anywhere you need quick data visualization.

Excel vs Free Online Tool: Side-by-Side Comparison

Feature	Excel	BoxPlotMaker.online
Time to create	5-10 minutes	30 seconds
Cost	$70-$100/year	Free
Version requirements	2016 or later	Any browser
Mobile support	Poor	Excellent
Customization	Limited	Advanced
Export options	Basic	Multiple formats
Privacy	Cloud-based	Browser-only
Learning curve	Steep	Instant

Alternative Method: Create Your Box Plot Online (Recommended)

Here's how to create a box and whisker plot using our free online tool:

Step 1: Visit BoxPlotMaker.online

Step 2: Enter your data in three simple ways:

• Type or paste values directly
• Upload a CSV file
• Copy from Excel and paste

Step 3: Click "Generate Box Plot"

Step 4: Customize (optional):

• Adjust colors and styling
• Add custom labels
• Configure axis ranges
• Toggle outlier display

Step 5: Download your box plot:

• Click "Download" button
• Choose PNG or PDF format
• Use immediately in reports, presentations, or papers

Total time: Less than 60 seconds from data to finished chart.

When to Use Excel vs Online Tools

Use Excel if:

• You're already working extensively in Excel spreadsheets
• You need to embed live data connections
• Your organization requires all work in Microsoft ecosystem
• You're creating complex dashboards with multiple linked charts

Use a free online tool if:

• You need a box plot quickly
• You don't have Excel 2016 or later
• You're working on a mobile device
• You want better customization options
• You need to share charts outside Excel
• You're creating standalone visualizations
• You value data privacy and browser-based processing

Pro tip: Many professionals use both - Excel for data organization and online tools for creating final publication-quality visualizations.

Advanced Excel Box Plot Tips

If you decide to stick with Excel despite its complexity, here are expert tips:

Tip 1: Use Excel Tables for Dynamic Charts

Convert your data range to an Excel Table (Ctrl + T). When you add new rows, your box plot updates automatically.

Tip 2: Create Templates

Save your formatted box plot as a template (.crtx file) to reuse formatting across multiple projects.

Tip 3: Combine with Other Charts

Use Excel's combo chart feature to overlay box plots with scatter plots or line graphs for deeper analysis.

Tip 4: Macro Automation

Record a macro while creating a box plot to automate repetitive formatting tasks.

Tip 5: Power Query Integration

Use Power Query to clean and transform data before creating box plots, ensuring consistent data quality.

Box Plot Best Practices (Excel or Online)

Regardless of which tool you choose, follow these visualization best practices:

Data Preparation:

• Remove obvious data entry errors before plotting
• Document data sources and collection methods
• Keep raw data separate from calculated values

Visual Design:

• Use distinct colors when comparing multiple groups
• Maintain consistent color schemes across related charts
• Add clear axis labels with units (e.g., "Score (points)")
• Include descriptive chart titles

Statistical Integrity:

• Ensure sufficient sample size (minimum 5-10 points per group)
• Report sample sizes alongside box plots
• Note any data transformations applied
• Consider showing both mean and median when they differ significantly

Accessibility:

• Use colorblind-friendly palettes
• Provide alternative text descriptions
• Include data tables for screen readers
• Avoid relying solely on color to convey information

Frequently Asked Questions

Q: Can I create box plots in Google Sheets? A: Google Sheets doesn't have built-in box plot functionality. You'd need to use add-ons or create them manually using formulas and custom charts. A free online tool is much easier.

Q: How many data points do I need for a box plot? A: Technically 5 minimum (for the five-number summary), but 10-20+ data points provide more meaningful visualizations and reliable quartile calculations.

Q: What's the difference between box plots and histograms? A: Box plots show distribution summary statistics (median, quartiles, outliers), while histograms show frequency distribution of values across bins. Box plots are better for comparing groups; histograms for understanding shape of single distributions.

Q: Can Excel calculate and display the mean on box plots? A: Yes, in Format Data Series > Series Options, check "Show mean markers" to display mean values alongside the median line.

Q: Why do my Excel and online tool box plots look slightly different? A: Different tools may use different quartile calculation methods (inclusive vs. exclusive median). Both are statistically valid; the difference is usually minimal.

Q: Is it better to use horizontal or vertical box plots? A: Vertical is standard for most applications. Use horizontal when you have many groups with long names, making vertical labels difficult to read.

Conclusion: Choose the Right Tool for Your Needs

Creating a box and whisker plot in Excel is certainly possible with Excel 2016 or later, but it requires navigating multiple menus, dealing with limited customization options, and investing 5-10 minutes per chart. For occasional users or those without the latest Excel version, the complexity often outweighs the benefits.

The smarter approach? Use a dedicated free online box plot maker that delivers professional results in seconds, works on any device, and requires zero software installation or subscription fees.

Whether you're analyzing test scores, comparing experimental groups, visualizing sales data, or presenting research findings, the right visualization tool saves time and produces better results.

Ready to create your box plot the easy way?

Visit BoxPlotMaker.online and generate professional box and whisker plots in under 30 seconds. No signup required, completely free, and your data stays private in your browser.

Create Your Box Plot Now - Free →

Still have questions? Check out our complete guide on understanding and interpreting box plots, or explore our statistics tutorial library for more data visualization resources.

---

About the Author: This guide was created by the Box Plot Maker team, dedicated to making statistical visualization accessible to everyone. We believe powerful data analysis tools should be free, fast, and user-friendly.