Chart & Visualization Issues

How to Arrange Excel Box-and-Whisker Plots in Paired Groups

Chanuka GeekiyanageChanuka Geekiyanage Nov 3, 2026 871 views

Question details

The user needs to display Box-and-Whisker plots in paired groups so that treatment and control datasets appear next to each other for each specific data column.

How to Arrange Excel Box-and-Whisker Plots in Paired Groups
Product
Excel
Device & OS
not provided
Scenario
Creating a statistical Box-and-Whisker chart to compare paired data categories (e.g., treatment versus control) side by side.
Observed behavior
Excel defaults to grouping all treatment data plots together and all control data plots together, rather than pairing them by their respective categories.
Before you start

Ensure your dataset is complete and organized, with clear headers defining your treatment and control groups before attempting to generate the chart.

Solution 1Recommended

Restructure Source Data and Adjust Chart Settings

By reorganizing your source data to alternate between treatment and control for each measurement category, Excel can correctly pair the Box-and-Whisker plots side by side.

Box-and-Whisker charts in Excel rely heavily on the physical layout of the source data in the worksheet. To achieve paired groupings, the data sets must be positioned adjacently in the source table.

1
Reorganize the source data

Restructure your worksheet so that each measurement category has adjacent treatment and control series side by side.

2
Format category labels

Ensure that the category labels are placed together in the first column of your newly organized data range.

3
Insert the Box and Whisker chart

Highlight the reorganized data range, navigate to the Insert tab on the ribbon, click on the Statistic Chart icon, and select Box and Whisker.

4
Switch Row/Column if necessary

If Excel still assigns the groups incorrectly, select the chart, go to the Chart Design tab, and click 'Switch Row/Column' to fix the pairing. Verify that the category axis labels are now correct.

Restructure Source Data and Adjust Chart Settings
Data Layout is Key: The chart groups data based on adjacent columns. If your treatment data and control data are stored in separated blocks (e.g., starting at row 300), you must combine them into alternating columns to achieve the paired visual effect.
Create Professional Charts Easily

Use WPS Spreadsheet to Create Paired Box-and-Whisker Charts

WPS Spreadsheet offers powerful, intuitive charting tools that are fully compatible with Microsoft Excel formats, allowing you to easily build paired Box-and-Whisker plots for complex data comparison without the hassle.

  1. 1. Open your data in WPS: Launch WPS Spreadsheet and open your dataset containing the treatment and control values.
  2. 2. Arrange your columns: Restructure your columns so the paired data (treatment and control) sit side by side next to their respective category labels.
  3. 3. Insert the chart: Highlight the data, go to the Insert tab, click Chart, and choose the Box and Whisker option.
  4. 4. Adjust the grouping: If the pairs are not side by side, use the 'Switch Row/Column' tool located in the Chart Tools tab to instantly fix the visual pairing.
100% format compatibility with Microsoft Excel (.xlsx) filesIntuitive chart insertion and formatting interface for statistical dataFree and lightweight alternative for all your spreadsheet needs
microsoft office alternative - wps office

Frequently Asked Questions

Why are my Excel Box-and-Whisker plots grouped by series instead of category?

Excel groups chart data based on the layout of your source table. If your data separates all treatment metrics into one block and control metrics into another, the chart will reflect that separation. You must interleave the data columns to fix this.

Can I change the colors of the paired Box-and-Whisker plots to distinguish them?

Yes. Once the chart is generated, click on an individual box plot series in the chart to select it, go to the Format tab, and select Shape Fill to apply distinct colors to your treatment and control groups.

What should I do if the Switch Row/Column button is grayed out?

This typically occurs if the selected data range contains blank headers, merged cells, or spans non-contiguous ranges. Ensure your source table has a simple, uniform structure with clear column headers before inserting the chart.