Learn How to Create Box and Whisker Plots in Excel

Understanding Box and Whisker Plots: What They Show and Why They Matter

A box and whisker plot, sometimes called a box plot, is a visual way to display how data is distributed. Instead of showing every single data point, a box plot summarizes the information into a simple diagram that reveals patterns and outliers at a glance. This type of chart has been used in statistics since John Tukey developed it in 1977, and it remains one of the most useful tools for comparing data sets across different categories or time periods.

The basic structure of a box and whisker plot includes several key components. The "box" in the middle contains the middle 50 percent of your data, while the "whiskers" are the lines extending from the box that show the range of the data. A line inside the box indicates the median value, which is the middle point when all numbers are arranged in order. Understanding these components helps you read what the data is actually telling you, rather than just looking at numbers in a spreadsheet.

Box plots work particularly well when you want to compare multiple groups of data. For example, if you have sales figures from four different regions across twelve months, a box plot for each region lets you see at a glance which region has the most consistent sales and which has the most variation. This visual comparison would take much longer if you were just staring at rows and columns of numbers.

The real strength of box plots lies in their ability to show outliers—those unusual values that don't fit the normal pattern. If a company's production line normally makes between 95 and 105 items per hour, but one hour produced only 60 items, a box plot would flag that immediately. This makes box plots valuable in quality control, scientific research, financial analysis, and any field where understanding the spread and consistency of data matters.

Practical Takeaway: Box plots summarize data distribution in a way that makes it easy to spot patterns, compare groups, and identify unusual values without needing statistical training to interpret the results.

Setting Up Your Data in Excel Before Creating a Box Plot

Before you can create a box plot in Excel, your data needs to be organized in a way that the software can understand. Unlike some charts that automatically work with any data arrangement, box plots require careful setup. The good news is that the process is straightforward once you understand the basic rules.

Start by organizing your data into columns. Each column should represent either a category or a data set you want to compare. If you're tracking test scores for three different classrooms, you might put Classroom A scores in column A, Classroom B scores in column B, and Classroom C scores in column C. Each cell in that column contains one score. Make sure all the data within a column relates to the same category—mixing different types of measurements in one column will create a confusing and incorrect chart.

Your data should be arranged vertically, with values stacked from top to bottom. Excel can process data arranged horizontally (left to right), but most users find vertical arrangement more intuitive. If you have headers—like "Q1 Sales," "Q2 Sales," "Q3 Sales"—place these in the first row. Excel will recognize these as labels for your data categories.

Here's an important consideration: Excel's built-in box plot feature (available in Excel 2016 and later versions) works best with data that's already been organized for analysis. If you're using an older version of Excel or prefer a different approach, you may need to use alternative methods like creating a box plot through the chart tools or using statistical add-ins. Check your Excel version by clicking "File" then "Account" to see which version you're running.

Clean your data before creating the chart. This means removing any blank cells within your data range, correcting obvious errors, and deciding how to handle missing information. If a data point is genuinely missing—say, a store was closed on a particular day—you might delete that cell or note it separately rather than leaving a blank space, which can confuse the chart creation process.

Practical Takeaway: Organize data into clean, vertical columns with clear headers, ensuring each column contains only values from one category or group that you want to compare.

Creating a Box Plot in Excel 2016 and Later Versions

Excel 2016 introduced a built-in box plot feature, making it significantly easier to create these charts compared to earlier versions. If you have Excel 2016, 2019, Office 365, or a more recent version, you have access to this tool. The process involves just a few clicks once your data is properly organized.

First, select all your data including headers. Click on the first cell of your data and drag to select the entire range, or click the first cell, hold Shift, and click the last cell. You can also click on a cell within your data and use the keyboard shortcut Ctrl+A to select the current data region. The selected data should appear highlighted in blue.

Next, navigate to the Insert tab in the ribbon menu at the top of Excel. Look for the Charts section. In newer versions, you'll see a button labeled "Insert Statistic Chart" or simply a dropdown arrow next to chart icons. Click this dropdown to reveal options for different statistical charts. Box and Whisker Plot should appear in this list. Click on it, and Excel will immediately create a box plot based on your data.

The chart will appear as an embedded object on your worksheet. It shows a box for each column of data you selected. If you selected three columns, you'll see three boxes aligned horizontally. Each box displays the distribution of that column's data. The chart will have default formatting, but you can customize it by right-clicking on various elements.

If you want to move the chart to a different location on your worksheet, click on it and drag it. To resize it, click and drag the handles (small squares) on the corners and edges. To adjust the data the chart uses, right-click on the chart and select "Select Data" to open the data range dialog, where you can add or remove data series.

Excel may also show you options for how to calculate the quartiles (the sections that divide the box). Some versions offer different calculation methods. The default setting works well for most purposes, but if you're following specific statistical protocols, you may need to check these settings through the Format Axis options.

Practical Takeaway: Select your data, go to Insert > Insert Statistic Chart > Box and Whisker Plot, and Excel creates the chart automatically. You can then move, resize, and customize it as needed.

Understanding the Components of Your Box Plot Chart

Once your box plot appears on your screen, you should understand what each visual element represents. This knowledge transforms the chart from a confusing image into a meaningful summary of your data. Each part of a box plot tells a specific story about your numbers.

The box itself represents the interquartile range, which contains the middle 50 percent of your data. If you arranged all your numbers in order from smallest to largest, the box starts at the 25th percentile (where 25 percent of the data falls below) and ends at the 75th percentile (where 75 percent of the data falls below). For a dataset of 100 test scores, the box would contain scores ranked from position 25 to position 75. The width of the box shows you how spread out that middle half is—a narrow box means values cluster closely together, while a wide box indicates greater variation.

The line inside the box shows the median—the exact middle value. If you listed all your numbers in order, the median is the value in the middle. For example, in the numbers 10, 15, 20, 25, 30, the median is 20. This line's position within the box tells you something important: if it's off-center, your data is skewed. A median closer to the bottom of the box indicates that values tend to be higher; a median closer to the top indicates values tend to be lower.

The whiskers are the lines extending from the top and bottom of the box. These lines typically extend to 1.5 times the interquartile range distance from the box, or to the furthest data point within that range. The whiskers show you the range of typical values. For instance, if you're looking at daily temperatures, the whiskers might extend from 42 degrees to 88 degrees, representing the normal variation you might expect.

Points that appear beyond the whiskers are outliers—values that fall far outside the typical pattern.