Excel Boxplot problem

Question

I am trying to design a boxplot to measure the spread of a large set of values (roughly 170 cells) but I get an error message reads "The range you selected is too large." The highest number of cells I can select for this boxplot is 50. I have tried using file types xlsx, xlsm, and csv. Any fixes? I do need to use all of this data.

Answer 1

Hello Nicholas P,

I am an independent advisor, trying to help other users in the Community with my experience in Microsoft products. Please note that I am a user like you and don't work directly for Microsoft.

The error message you're encountering indicates that Excel is limiting the number of cells you can select for creating a box plot. This limitation is common in Excel, especially when dealing with large datasets.

To work around this limitation and create a box plot for your entire dataset, you can try the following approaches:

1. Aggregate Data: Instead of plotting each individual value in your dataset, consider aggregating the data first. For example, you could calculate summary statistics like the mean, median, quartiles, etc., for smaller subsets of your data and then plot these summary statistics in the box plot.
2. Use Pivot Tables: Pivot tables are a powerful tool in Excel for summarizing and analyzing large datasets. You can create a pivot table to aggregate your data and then use the pivot table as the data source for creating the box plot.
3. Use Excel Add-Ins or External Tools: Consider using Excel add-ins or external tools specifically designed for visualizing large datasets. These tools may provide more flexibility and capabilities for handling large amounts of data than Excel's built-in features.
4. Split Data into Smaller Chunks: If your dataset is too large to be processed as a single entity, consider splitting it into smaller chunks and creating separate box plots for each chunk. You can then compare the box plots to analyze the overall spread of your data.

I would appreciate your feedback

Kind Regards, Fatai