What does Excel do when you go over the limit for total data point

  • Thread starter Thread starter Carl
  • Start date Start date
C

Carl

The maximum number of total data points in a plot is 256,000. However, I
have setup plots with > 1.5 million data points. Excel does not complain
about this, but I'm guessing it is still only showing 256,000 points. The
thing is, I can't figure out which 256,000 it is showing. I don't think it's
just the first 256,000 because the data is sorted and it would look abruptly
cutoff after the 256,000th point.

I'm using a scatterplot if that helps. The plot looks correct, with
gradually decreasing density towards the outer edges of the plot. But if
it's only showing 17% of the data points, is Excel intelligently filtering
the data evenly?
 
Nevermind. I thought I had the data sorted, but I didn't. I can clearly see
it is cutting it off now, so I will have to filter the data in my application
first.

Please delete/lock this thread.
 
Usually I ask what's the use of showing more points in a chart than there
are pixels. But his time I thought I'd check it out.

I filled A1:L32001 (12 columns) with data. I tried to make a series with
A1:A32001, and Excel warned me that the limit was 32000 points per series.

I made a chart using A1:H32000 (8*32000=256000), which supposedly reaches
the maximum number of points in the chart. Fine, glad I'm using Excel 2003
and not 2007, because in the latter it would take a long time to redraw.
Then I extended the data range to I32000. No warning, and the new series was
added as if there was nothing wrong. I extended the range to column L, so I
have 12 series in the chart, each with 32000 points, which exceeds the limit
by 50%.

In VBA I ran command this from the Immediate window, with the following
results:

For Each s in ActiveChart.SeriesCollection : p = p + s.Points.Count : ?
s.Name, s.Points.Count, p : Next
Series1 32000 32000
Series2 32000 64000
Series3 32000 96000
Series4 32000 128000
Series5 32000 160000
Series6 32000 192000
Series7 32000 224000
Series8 32000 256000
Series9 32000 288000
Series10 32000 320000
Series11 32000 352000
Series12 32000 384000

So Excel thinks there are 384k points and is continuing happily along. I
don't know of any feasible way to tell which points are showing and which
(if any) have been filtered out.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______
 
Thanks for the help, Jon. I would totally agree that it's pointless to plot
more points than can be viewed on the screen, but you don't always know how
many pixels the user's monitor displays. Plus, they may want to zoom in on a
specific area.

Also, it would be much easier to let Excel figure out how to plot it all
rather than writing my own code to filter the data before giving it to Excel.
Or at least it would be if I could be sure Excel was displaying it properly.
 
Back
Top