Chart with Big Data set

[Krishnan1989]

Hi,

I have an Excel file which contains 3 tabs such as "A", "B" and "C". The "A" tab has a data table where 16k rows of data are added programmatically, with 7 legends I have created a combo chart in tab "C" by using the table in "A" tab. Everything is working fine, the problem was, if I click one of the chart series from the chart and suddenly click another series from the same chart then the Excel getting stuck/hang for a few seconds and then recovered back. Please advise anyone to overcome/fix this problem.

Note - With 7 Legends - 4 are Primary and 3 are Secondary

Thanks.

[#2]

That is really hard to answer as there are many things it could be. Simply having 7 plots each with 16k data points is a starting point. Do you have any custom formatting on the chart? You may also have other parts of the workbook causing issues. If you have 365 you can try opening using the online version and under the 'Review' tab is a new feature called 'Check Performance' which might help find areas to improve performance.

[#3]

Hi @mtarler, 
Thank you for the reply. I have attached a sample Excel file, as I mentioned in the above discussion play with multiple clicks on the chart (click on the chart's plot area/click on the points) then move into a different file such as My Computer or any other program in the PC/Laptop then come back and see the excel may hang. Also, see the performance of the Task Manager. it goes high. Please let me know if you want any further clarification.

Thanks. 

[#4]

it is big data that you make many cells use random function and your chart wil every click make the big date do random calculations so you can use from tab formulas -calculations option -manula so you contron the update of chart when you want by clculate now

[#5]

How to do that? Any sample or any examples?

[#6]

@Krishnan1989  so I don't see that behavior but will try more later.  Also, I don't know if this file uses all those RAND functions just to create a sample or if your actual file has lots of RAND() but those are volatile functions meaning excel re-calculates them and every cell dependent on them on every calculation iteration which will cause huge hits to performance ( you can search for excel volatile functions for more information).  I believe that is what is being noted in the response above and that you can also turn off automatic calculation updates under Formula menu and selecting 'Manual' instead:

this does mean when you make changes and you actually want everything to update you need to hit the calculate now button.

[#7]

@mtarler Random function is used for sample data. For me when I clicked the chart drawn area multiple times, I'm getting the above mentioned issue.

[#8]

I'm just not getting the same behavior. I even changed the rand() function to create separation so i could easily click between the series (i.e. =RANDBETWEEN(50*column(), 50*column()+100)
but I'm not getting any lag or issues even with autocalc set on. In you device manager how much memory is used? If you are getting close to full memory usage then that could be the issue.

[#9]

@Krishnan1989