A box-and-whisker chart is a visualization of groups of numerical data and their quartiles in the data set. Originally named boxplot chart, this visualization gets its name from a box that represents the lower and upper quartiles, and lines extending from the box (whisker). In this guide, we’re going to show you how to create a box-and-whisker chart in Excel.
A box-and-whisker chart displays data based on a five-number summary:
Minimum: the minimum data point.
Maximum: the maximum data point.
Median (Q2 / 50th percentile): the middle value of the dataset.
First (Lower) quartile (Q1 / 25th percentile): the median of the lower half of the dataset.
Third (Upper) quartile (Q3 / 75th percentile): the median of the upper half of the dataset.
Box-and-whisker Chart Basics
A box-and-whisker chart consists of 5 sections:
- Plot Area: This is where the graphic representation takes place.
- Chart Title: Title of the chart. We recommend keeping it informative and concise.
- Vertical Axis: The axis representing the measured values, also known as the y-axis.
- Horizontal Axis: The axis that includes the categories of the data, also known as the x-axis.
- Legend: The legend is an indicator that helps distinguish data series from one another.
Insert a box-and-whisker chart in Excel
Start by selecting your data in Excel. Include the data label to selection so that it can be recognized automatically by Excel, and it will be easier to modify and visualize the data.
Activate the Insert tab in the Ribbon and click on the Insert Statistics Chart icon to see the chart types under category. Click the Box and Whisker chart to create your chart.
Clicking the icon inserts the default version of the chart. Let's now take a closer look at customization options.
Customizing Box-and-whisker Chart in Excel
Double-Clicking
Double-clicking on any item pops up the side panel where you can find more options for the selected element. Please keep in mind that once the side panel is open, you don't need to double-click again, selecting the chart element you want to configure next will switch the menu to that item.
Side panel includes element specific options as well as generic ones, like coloring and effects.
Right-Click (Context) Menu
Right-clicking an element displays the context menu. You can modify basic styling properties like the chart colors, and activate the side panel for more options. To display the side panel, choose the option that starts with Format string. For example; Format Plot Area… in the following image.
Chart Shortcuts (Plus and Styles Buttons)
In Excel 2013 or newer, charts come with shortcut buttons. You can add/remove elements, apply predefined styles and color sets, and filter values with a few clicks.
Another neat feature is that you can see the effects of your changes on the fly, before actually applying them. For example; in the following image, the cursor is on the Axis Titles item and you can see the labels on the chart.
Ribbon (Chart Tools)
Whenever you activate a special object, Excel adds new tab(s) into the Ribbon. Charts follow the same idea. You can see these chart specific tabs under the Design and Format menus. While the Design tab contains options for adding other chart elements, applying styles, modifying data, and the chart itself; the Format tab, on the other hand, provides more generic options that are common with some of the other objects.
Briefly; the chart tabs in the Ribbon is the only place where you can find all options in one place.
Customizing Tips
Preset Layouts and Styles
Try preset layouts or styles to improve visualization of your chart. Instead of dealing with all elements by yourself give a chance to Excel.
You can find styling options in the Design tab or in brush icon of Chart Shortcuts. Here are some examples:
Applying a Quick Layout:
Changing colors:
Update Chart Style:
Changing chart type
You can change the type of your chart any time by Change Chart Type dialog. Although, most of the chart types have multiple variations, Excel provides only a single type for Box-and-whisker chart.
To change the type of your chart click on the Change Chart Type items from the Right-Click (Context) Menu or Design tab.
In the Change Chart Type dialog, you can see the options for all chart types with preview of your chart. Unfortunately, you don't have any other options for a Box-and-whisker chart.
Switch Row/Column
Excel assumes that vertical labels as categories, and horizontal labels are data series by default. If your data is designed in reverse order, you can easily shift labels with a single click. However, this is not a case for Box-and-whisker charts. Switch Row/Column button in Chart Design tab is disabled.
Move a chart to another worksheet
Charts are created in a worksheet where the selected data is by default. If you need to move your chart into a new or existing sheet, you can use the Move Chart dialog. To open the Move Chart dialog you can click its icon in the Design tab or the right-click menu for the chart itself. Please keep in mind you need to right-click in an empty place in chart area to see the option; right-clicking a chart element won't work.
In the Move Chart dialog, you have 2 options:
- New sheet: Select this option and enter a name to create a new sheet, and place the chart in it.
- Object in: Select this option and select a name of an existing sheet from the dropdown to move your chart into that sheet.
