Chart & Visualization Issues

Fix Excel Box Plot Cannot Use More Than 50 Data Points

Ayan Masood Oct 8, 2026 869 views

Question details

The user is unable to generate a box plot in Excel for a dataset of approximately 170 values due to a data point limit restriction.

Fix Excel Box Plot Cannot Use More Than 50 Data Points

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a statistical Box and Whisker chart to visualize the distribution of a complete dataset.
Observed behavior: Excel blocks the chart creation and displays an error stating that the selected range is too large, effectively limiting the box plot to around 50 data points.

Before you start

Before attempting workarounds, ensure your dataset is fully cleaned, removing any blank cells or hidden text values that might be contributing to chart calculation errors.

Solution 1Recommended

Aggregate Data Using a PivotTable

Summarize your large dataset into statistical quartiles and medians using a PivotTable to bypass the raw data point limit.

By manually calculating the five-number summary (minimum, first quartile, median, third quartile, and maximum) with a PivotTable, you can bypass Excel's limit on raw data points for box plots. You can then use these aggregated values to create a custom stacked column chart with error bars.

Insert a PivotTable

Select your entire dataset, navigate to the Insert tab on the Excel ribbon, and click PivotTable to create a new summary table.

Calculate Statistical Values

Use Excel functions like MIN, QUARTILE.INC, MEDIAN, and MAX within your PivotTable or adjacent cells to extract the necessary box plot values.

Build a Custom Chart

Highlight the summarized data, go to Insert > Insert Column or Bar Chart, and select Stacked Column. Then, format the chart and add Error Bars to visually represent the whiskers.

Data Accuracy: This method ensures your final visualization accurately reflects the entire dataset of 170+ values without being truncated by Excel's plotting restrictions.

Solution 2

Split Data into Smaller Groups

Divide your large dataset into multiple smaller groups of 50 or fewer points to plot them as separate series within the same chart.

Free Microsoft Office alternative

Create Advanced Charts Without Limits in WPS Office

If Excel's arbitrary charting limitations are slowing down your data analysis, consider switching to WPS Office. WPS Spreadsheet handles large datasets efficiently and provides a seamless, familiar interface for advanced statistical charting without restrictive data point caps.

1. Download WPS Office: Visit the official WPS website and click 'Download WPS Office Free' to install the software on your device.
2. Open Your Dataset: Launch WPS Spreadsheet and open your existing .xlsx file containing the 170+ data points.
3. Insert Box Plot Directly: Select your entire dataset, navigate to the Insert tab, click Chart, and choose the Box and Whisker chart type to generate your visualization instantly.

Process extensive datasets without encountering 'selected range too large' errorsFully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv)Free, lightweight, and easy-to-use spreadsheet applicationRich built-in charting tools including Box and Whisker, Histograms, and more

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel say my selected range is too large for a box plot?

Certain versions of Excel impose a limitation on the number of raw data points (often around 50-60 per series) that can be processed directly by the built-in Box and Whisker chart engine to prevent performance issues. When you exceed this, Excel throws a 'selected range is too large' error.

Can I use a third-party add-in to create large box plots in Excel?

Yes, many external visualization tools and Excel add-ins bypass Excel's native charting engine. Installing statistical add-ins can allow you to select thousands of data points to generate accurate box plots directly within your spreadsheet.

Does aggregating the data change the accuracy of the box plot?

No. If you properly calculate the exact statistical quartiles, median, minimum, and maximum of the full dataset using formulas or a PivotTable, a custom-built stacked chart using those values will represent the distribution perfectly.

Excel & Data Problems