The box and whisker plot, or boxplot, is a simple and powerful tool to visualize the spread and extreme values of your data. Whether you are analyzing test results, monthly sales, or experimental measurements, this type of representation highlights the median, quartiles, and any outliers. In this article, I guide you step by step to generate and customize a boxplot in Excel, complementing each step with practical tips and internal resources to go further.
1. Understanding the structure of a boxplot
1.1 The key elements
A boxplot consists of four main elements:
- The box which extends from the first quartile (Q1) to the third quartile (Q3).
- The median line which represents the median (Q2) within the box.
- The whiskers which extend to the minimum and maximum values, excluding outliers.
- The isolated points indicating outliers, that is, values considered very distant from the rest of the data.
This visualization allows you not only to judge the symmetry of the distribution but also to immediately identify any outliers.
1.2 Why use a boxplot?
Unlike a histogram or a scatter plot, the boxplot concentrates the essential information at a glance. It is particularly suited:
- to comparing multiple data series on the same chart;
- to quickly detecting skewness and extreme values;
- to communicating statistical results within non-specialized teams.
In a professional environment, this visual finds its place in quality reports, financial statements, or performance analyses.
2. Preparing your data in Excel
2.1 Organization and calculation of quartiles
Before generating a boxplot, make sure the data is arranged in columns, without empty rows or integrated totals. You can calculate the quartiles using built-in formulas:
- =QUARTILE.INC(range,1) for Q1
- =MEDIAN(range) for the median
- =QUARTILE.INC(range,3) for Q3
These formulas allow you to create a summary table in the form:
| Statistic | Excel Formula |
|---|---|
| First quartile (Q1) | QUARTILE.INC(range,1) |
| Median (Q2) | MEDIAN(range) |
| Third quartile (Q3) | QUARTILE.INC(range,3) |
| Min and Max | MIN(range) and MAX(range) |
You can then calculate the interquartile range (IQR): =Q3-Q1, and determine the whisker limits according to the 1.5 IQR rule.
2.2 Cleaning and filtering the data
Careful cleaning is necessary, especially if you have merged cells for the title or empty boxes. If needed, consult the method for Merging and splitting cells correctly in Excel to avoid any misinterpretation. You can also apply an automatic filter to exclude null or incomplete values.
3. Generate a boxplot in Excel
3.1 Recent versions of Excel (2016 and later)
Since Excel 2016, the “box and whisker” type is directly available:
- Select your quartile and extreme values table.
- In the Insert tab, click on the Insert Statistical Chart icon and choose Box and Whisker.
- The chart appears automatically, using your input data.
A simple double-click on one of the boxes will open the formatting pane.
3.2 Earlier versions of Excel (before 2016)
If you are using an older version, it requires a bit more work:
- Build a stacked histogram from the quartile and deviation matrix.
- Convert each series into a stacked bar chart.
- Convert the secondary vertical axis to align the bars and add the median points.
This approach, although a bit more laborious, results in the same visual outcome.
4. Customize your boxplot
4.1 Adjust colors and transparency
To make the boxplot more readable or compliant with your graphic charter, select the box, then:
- Format tab → Fill to change the color.
- Apply a slight transparency (10–20%) if you overlay multiple series.
- Change the color and thickness of the outline to highlight the median.
4.2 Refine axes and titles
Don’t forget to:
- Clearly rename the vertical axis (label with units).
- Adjust the minimum and maximum axis values according to the calculated limits.
- Use a readable font and a size consistent with your other charts.
If you work with dates, consider formatting the horizontal axis as “DD” or “YYYYMM” using text functions (see our article on text functions in Excel).
5. Use a boxplot in a report or dashboard
5.1 Integrate into an interactive report
To make your chart more dynamic, you can link it to a pivot table (PT) and add slicers. You will find the method to make your pivot tables interactive with slicers, which will allow the reader to filter in real time by category, month, or region.
5.2 Additional calculations
If you want to add conditional totals or count outliers, the functions SUMIF and COUNTIFS will be your allies. For example, to count values greater than Q3+1.5×IQR:
=NB.SI.ENS(plage;">Q3+1.5*IQR")
6. Summary table of key functions
| Function | Usage |
|---|---|
| QUARTILE.INC | Calculate quartiles. |
| MEDIAN | Obtain the median of a range. |
| MIN / MAX | Identify extreme values. |
| SUMIF | Sum values under condition. |
| COUNTIFS | Count occurrences according to multiple criteria. |
FAQ on the boxplot in Excel
Q: Can more than two series be compared in the same boxplot?
A: Yes, simply place each series side by side and select all to insert a boxplot. Be sure to distinguish each series by color or fill.
Q: How to handle missing values?
A: Excel automatically ignores empty cells in quartile and median calculations. However, you can filter or replace empty cells with a specific indicator before calculation.
Q: My Excel does not have the “box and whisker” type?
A: You are probably using a version prior to 2016. In this case, create a stacked histogram then adjust the bars and median points to simulate a boxplot.
By following these steps, you will have a clear and effective boxplot for your distribution analyses. Feel free to experiment with colors, add interactive segments, or enhance your calculations with advanced functions.
{
“@context”: “https://schema.org”,
“@type”: “WebPage”,
“about”: {
“@type”: “Thing”,
“name”: “Boxplot dans Excel”
},
“keywords”: [“quartiles”,”boxplot”,”Excel”,”graphique”,”statistiques”]
}