• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Maximum Limit of Chart Points in Excel 2010

nagovind

nagovind

Member
There are more than 50,000 points X-Y axis to be ploted in the log-log sheet using XY scatter chart.


If it is plotted with less no. of points say 10,000 points there is no issues but if the same plot is plotted with 50k points of xy coordinate last part is not shown/ truncated in the chart


Please provide a soultion


Thanks
 
Nagovind


It may be worth reading this:

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx


This says that the Charting No of Points is limited by available memory


So I figured I'd better test that out


I setup 2 columns for X and Y data

in Column A I put the Row No

In Column B I put Randbetween(1,100)

I copied these down to Row 1048576


I then setup two named formulas with

X: =Offset(Sheet1!$A$1,,,Sheet1!$C$1,1)

Y: =Offset(Sheet1!$BA$1,,,Sheet1!$C$1,1)


This allows me to put a value in C1 and chart it


I then setup a scatter chart with 1 Series

X Series: =Sheet1!X

Y Series: =Sheet1!Y

I changed the scatter chart so the line style was set to None (No line)


I then changed C1


I was able to plot the full 1048576 points

Although it did take a few seconds to redraw once it got past 200,000 points

I should note that I do have 16GB of RAM in my PC.


You may want to simplify the chart as much as possible by

- Using simple colors

- Remove shadows and other effects

- Do you need lines to join data points

etc


You may also want to consider is having all those points adding value?

Most screens max out at 1920 pixels wide and so having column Charts with more than 1900 columns is superfluous

True Scatter charts may be able to display 20-50,000 points, but even past that they start to loose impact as a lot of the areas are just solid color


Is some type of Contour or Heat Map style chart a better style to display your data?


Have a play with my test file if you want:

https://www.dropbox.com/s/jgn10mv94ab2mml/Chart%20Memory.xlsx
 
You must log in or register to reply here.
Back
Top