Box and whisker plots display data distribution through quartiles and outliers. Creating one manually in Excel requires sorting data, calculating quartile values, and inserting a stacked column chart, which is time-consuming. Copilot in Excel can automate this entire process by generating the chart structure from your raw data. This article explains how to use Copilot to create box and whisker plots in Excel, including the required data format and the exact prompts to use.
Key Takeaways: Generating Box and Whisker Plots with Copilot
- Data must be in a single column per dataset: Each category or group you want to plot must occupy its own column with no gaps or text headers in the data rows.
- Copilot pane > Prompt box: Type a natural language prompt such as “Create a box and whisker plot for the data in columns A through D” to start the chart.
- Chart formatting with Copilot: After the plot appears, you can ask Copilot to adjust colors, labels, or axis ranges by typing follow-up prompts.
How Copilot Handles Box and Whisker Plot Generation
Copilot in Excel uses the built-in Box and Whisker chart type, which was introduced in Excel 2016. When you give a prompt to create this chart, Copilot reads the selected data range or the data you reference in your prompt. It then calculates the five-number summary for each dataset: minimum, first quartile, median, third quartile, and maximum. Copilot also identifies outliers using the interquartile range rule. The chart is inserted as a native Excel chart object, which you can resize, move, or format like any other chart.
To use Copilot for this task, you need a Microsoft 365 subscription that includes Copilot for Microsoft 365. The feature is available in Excel for Windows version 2401 or later, Excel for Mac version 16.81 or later, and Excel for the web. Your workbook must be saved to OneDrive or SharePoint for Copilot to access it. Data should be clean: no merged cells, no blank rows within the data range, and each column should represent a separate category or group.
Data Layout Requirements
Box and whisker plots compare distributions across groups. Your data must be arranged in a specific way for Copilot to interpret it correctly. Each group you want to compare must be in its own column. For example, if you are comparing test scores from three classes, place Class A scores in column A, Class B scores in column B, and Class C scores in column C. Do not stack all values in a single column with a group identifier in another column. Copilot does not support the stacked data format for box plots. Include a header row with group names, but ensure no blank cells exist within the data range.
Steps to Create a Box and Whisker Plot with Copilot
- Open your workbook and select the data range
Open the Excel workbook saved to OneDrive or SharePoint. Highlight the cells that contain your data, including the header row. Make sure there are no empty columns or rows inside the selected range. If your data has empty cells, fill them or remove the rows and columns that contain them. - Open the Copilot pane
Click the Copilot icon on the Home tab of the ribbon. The Copilot pane opens on the right side of the Excel window. If you do not see the icon, your account may not have the Copilot license assigned. Contact your Microsoft 365 administrator. - Type a prompt to create the chart
In the Copilot pane, type a prompt such as “Create a box and whisker plot for the selected data” or “Insert a box plot for columns A through D.” Copilot processes the request and inserts the chart on the current worksheet. The chart appears as a floating object near your data. - Adjust the chart position and size
Click the chart to select it. Drag the chart to a location where it does not overlap your data. Use the corner handles to resize the chart if needed. The chart updates automatically when you change the source data. - Format the chart using Copilot
With the chart selected, type a follow-up prompt in the Copilot pane. For example, “Change the chart colors to blue and orange” or “Add data labels showing the median value.” Copilot applies the formatting changes directly to the chart. You can also use the Chart Design tab on the ribbon for manual formatting. - Verify outliers and quartile calculations
Hover over any data point in the chart to see its value. Copilot uses Excel’s standard quartile calculation method, which is QUARTILE.INC. Outliers appear as dots beyond the whiskers. If your data has extreme outliers, the whiskers extend to the highest and lowest values within 1.5 times the interquartile range.
Common Issues with Copilot and Box Plots
Copilot Does Not Recognize the Data Range
If Copilot responds with “I cannot find a suitable data range,” the data layout is likely incorrect. Ensure each group is in its own column. Do not include summary rows like averages or totals in the selection. Also verify that the workbook is saved to OneDrive or SharePoint. Copilot cannot read data from locally stored files.
The Chart Shows All Data in One Box
This happens when all data is in a single column. Copilot treats one column as one group and creates a single box plot. To compare multiple groups, each group must be in a separate column. If your data is stacked, you need to unstack it using a PivotTable or Power Query before prompting Copilot.
Copilot Creates a Different Chart Type
Sometimes Copilot misinterprets the prompt and inserts a column chart or line chart instead. Rephrase your prompt to be more specific. Use the exact phrase “box and whisker plot” or “box plot.” If the wrong chart still appears, delete it and type a new prompt referencing the data range explicitly, such as “Create a box plot for columns A, B, and C.”
Outliers Are Not Displayed
By default, Excel’s box and whisker chart shows outliers. If outliers are missing, the data may not have any values beyond 1.5 times the interquartile range. To verify, calculate the interquartile range manually using the QUARTILE.INC function. If outliers exist but are not shown, check that the chart is not filtered or that the data does not contain errors.
Copilot Box Plot vs Manual Box Plot Creation
| Item | Copilot Method | Manual Method |
|---|---|---|
| Time required | Under 1 minute | 5 to 15 minutes |
| Steps involved | One prompt plus optional formatting prompts | Calculate quartiles, create stacked column chart, format series |
| Error risk | Low, if data layout is correct | Moderate, from miscalculated quartiles or incorrect chart type |
| Formatting flexibility | Limited to Copilot’s formatting capabilities | Full control over every chart element |
| Data layout required | One column per group | Can use stacked or unstacked data with extra steps |
The manual method gives you more control over chart elements like whisker calculation method and outlier display. However, for quick exploratory analysis, Copilot is significantly faster. You can always switch to manual formatting after Copilot creates the initial chart.
You can now use Copilot to generate box and whisker plots in Excel without calculating quartiles manually. Start with clean, unstacked data and a specific prompt. After the chart appears, refine colors, labels, and axis ranges with follow-up prompts. For datasets with many groups, consider using Copilot to create the initial plot and then manually adjust the chart layout for readability.