Chart Point Limits in Excel 2010
by Jon Peltier
Thursday, August 13th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
I heard a rumor that Excle 2010 had expanded limits to how many points you could plot in a chart. From Excel 97 through 2007, you were limited to 32,000 points per chart series, and 256,000 points per chart. As far as I was ever concerned, this was more than enough points to make a chart unreadable. Most charts have a plot area less than 250,000 pixels. The plot area in the following chart, which I’ve shaded yellow, is rather large, 605 x 423 pixels, or 255,915 total pixels. If every point is one pixel in size, and no points overlap, you couldn’t fit them all into this chart.
Never mind now long it takes to redraw.
Number of Points in a Series
Despite the limited utility of cramming data points on top of each other, I thought I’d investigate the rumored new limits. Here’s an Excel 2010 chart with 100 points; I’ve used the row number for X and 1/(row number) for Y.
Let’s skip 1000 points, here’s a chart with 10,000 points
Keep piling on. Here’s a chart with 32,000 points, the maximum allowed in a series in earlier Excel versions.
100,000 points, anyone?
Okay, I’ll bite. Here’s a chart with all of column A as X values and all of column B as Y values. 1,048,576 points. And it didn’t choke. It took a while to redraw, of course.
Check out this unique chart series formula. It uses the entire columns in the definitions of X and Y.
Number of Series in a Chart
I tried the same thing with the number of chart series. This is what 100 series look like in a chart. The X values are 1 through 25. Series 1 uses Y values 2 to 26, Series 2 uses 3 to 27, and so forth.
Here’s the same chart with 255 series, the maximum number of series in a chart in prior versions of Excel.
I tried adding more, and learned I’d exceeded the limit.
Summary
The number of series allowed in a chart in Excel 2010 has remained the same as in earlier versions, 255 series, but the number of points in a series has increased dramatically, from 32,000 to 1,048,576, so that entire columns in the larger grid of Excel 2007 & 2010 can be used as the source data of a chart series.
The total number of points allowed in a chart has obviously increased, since the old limit of 256,000 points is smaller than the new limit on points per series. Since drawing one series with a million points took over a minute, I did not have time to explore this limit. (Hmm, I also didn’t check whether the help files have yet been updated with the new limits.)
Related Posts:
- Chart Redrawing Performance of Excel 2003 and 2010
- Stacked Area Chart Challenge
- Introducing Control Charts (Run Charts)
- Polynomial Fit vs. Statistical Process Control
- Physics Lesson
- Pie Chart Plotting Deficiency
- Prepare Your Data
- Marimekko Replacement – 2 by 2 Panel
- Chart Busters – Compare Employee Sales
- Marimekko Replacement – Overlapping Bars (Easy)
Posted: Thursday, August 13th, 2009 under Excel 2010.
Comments: 7
Comments
Comment from AdamV
Time: Thursday, August 13, 2009, 9:42 am
No, the online and offline help for Excel 2010 both have the old limits at the moment. But then the offline help is also labelled as being for 2007, so this looks like they simply have not replaced those files yet.
You are absolutely right – this does seem to be raising or removing a limit for some pretty arbitrary reasoning, beyond the usual level of resolution available, particularly for on-screen use. I guess you could have much larger charts which you would have to show full screen or possibly scroll around to see anything worthwhile – or use some kind of “deep zoom” facility to move in and see more detail.
Comment from derek
Time: Thursday, August 13, 2009, 10:05 am
I have a little private challenge to myself called the Million Point Challenge, inspired by your remarks on large data sets being hard to graph usefully. The idea is to create a small multiple of at least four scatter graphs, having a total of a million data points, printed on a page no larger than A3, without the picture being a mess.
(it has to be at least four, because Excel before 2007 allows at most a quarter million points)
I’ve made good progress with the techniques necessary, but I’m now stalled by a different problem: just how often do any of us ever see a genuinely interesting data set that has a million points? So now I’m looking out for data I can use to demo the techniques. It should ideally have at least two two quantitative dimensions and at least one category dimension (or a quantitative dimension that can be binned into categories) and have interesting structure.
Comment from Bob
Time: Friday, August 21, 2009, 9:01 am
In my old job in a laboratory, I was able to produce very high resolution charts with excel of gamma ray spectra of Uranium powders.
Getting the data into excel was tedious, but the quality of the chart was great. I used the thinnest line possible. This was back in the days of 20 Mhz 386 machines being “state of the art”.
Spectral analysis may be a venue for these huge data sets.
Comment from Peder Schmedling
Time: Wednesday, August 26, 2009, 3:35 am
Interesting post. According to the below blog-entry on the Microsoft Excel Team blog, the number of points per data series is limited by the available memory on the machine..
http://blogs.msdn.com/excel/archive/2009/08/25/more-charting-enhancements-in-excel-2010.aspx
What setup did you test this on?
(OS, 32/64bit, RAM installed?)
A 64bit setup should be able to address an enormous amount of memory, but I don’t know what the memory manager in post-2003 versions of Excel can cope with..
Comment from Jon Peltier
Time: Thursday, August 27, 2009, 1:29 pm
I have used 32 bit versions of 2010 TP and 2003 SP3 on a laptop with around 1.5MB of RAM running 32 bit Windows XP SP3.
Comment from Jim
Time: Saturday, October 31, 2009, 1:58 pm
Where does one obtain Excel 2010? Please email me if you know. aka_jim@ymail.com.
THANKS !!
Comment from Jon Peltier
Time: Saturday, October 31, 2009, 2:58 pm
Excel 2010 is only available now to beta testers and reviewers.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.