Why excel crashes or runs extremely slow when I'm trying to plot data and edit series?

[MaximosGatos]

Hi,

It would be very helpful if someone can help me with this.

I have a Dell laptop, i5 4 cores processor, 16GB ram, 250 SSD (with more than 50% free space) and 1TB HDD, Radeon 520 and Intel UHD 620 graphics and I'm running the 64-bit with the latest updates.

Every time I'm trying to plot engineering data in a plot with multiple series excel slows down, takes ages to respond and crashes. Even if I run it by it self with nothing else on, it doesn't change it response time and crashes. 

Can anyone help me with this please? Any suggestions?
Many Thanks
Maximos

[#2]

How many rows of data are you trying to plot?

[#3]

@MaximosGatos 

It depends on the amount of data on the one hand.

On the other hand, an SSD hard drive in such amounts of data cannot do any harm.

Since it is a DELL, I recommend that you carry out all new driver updates and update the BIOS, especially if you are working with Windows 10 Pro.

Also look in the Config.sys whether the RAM is fully available to the system.

Call config, sys:

In the text search, write "msconfig" and enter.

The system configuration window appears.

Go to start Click on Advanced Options ...

Maximum memory: check the box.

Ok and enter System configurator requests a new start.

Perform a restart.

Hope I could help you at least a little with this info’s.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#4]

@Jan Karel Pieterse This is one file and other similar files with that amount of data causes the problem . Is not much for the damage it does. Hope it helps

[#5]

@NikolinoDE  Thanks for the reply

I have all the latest updates for the drivers and graphics and the latest version of the bios. 
I tried what you said, the file i uploaded still crashes. The way it goes it takes ages to open the plot and says excel not responding. If you let it be it comes back to life but if you click something or move the plot or try to edit the series it crashes again. If you wait again it comes back to life but any click anything will trigger it again.  It's not the PC because you can roam through the rest of the apps with ease while excel is not responding. So I don't know what to do 

Thanks for your help btw fellas, appreciate it 

[#6]

@Jan Karel Pieterse Here's a demonstration

[#7]

@MaximosGatos 

If it is only about this file, it is likely corrupt. It could also be that this workbook is from a different region / language than the one in your computer.

If this is the case, and it only occurs on this file, then I would, with your permission, recommend copying the content into a different (new) workbook and then trying again.

But if the same problem occurs in all workbooks, then I would do it with Frameworks 3.5 & 4 plus.

Framework 3.5 is not automatically loaded in Windows 10. But you can install it manually.

Activate the .NET Framework 3.5 in the control panel

You can activate the .NET Framework 3.5 via the control panel.

Press the WINDOWS KEY Screenshot of the Windows key logo. on the keyboard, type Windows Features, and then press ENTER. The Turn Windows Features On or Off dialog box appears.

Select the .NET Framework 3.5 (includes .NET 2.0 and 3.0) check box, click OK, and then restart the computer after the framework is installed when prompted.

* If the “Net. Framework 4.5 Advanced Services “is not checked, please carry out the same procedure.

Here is an alternative address for manual download:

https://dotnet.microsoft.com/download/dotnet-framework/net35-sp1

If none of this helps either, please upload one of the files (please without sensitive data) that have the problem in the forum.

So we could work directly with the file and possibly better discover the error.

Thank you for your patience and time.

Nikolino

I know I don't know anything (Socrates)

[#8]

@MaximosGatos 

You plot scatter chart using entire columns as data, i.e. Sheet1!A:A as X-axis and Sheet1!C:C as Y-axis. That million of points on which Excel shall do million of millions calculation. It's hard to expect such recalculation will be fast, doesn't matter which hardware do you use.

Please use dynamic ranges for the chart. That could be different approaches, in attached file I illustrated one of them on one series:

Using Formula->Name Manager add named formulas which return dynamic ranges

As data for the chart series use =Sheet1!kp10X and =Sheet1!kp10Y for x and y axis's.  When save Excel transforms sheet names to file name since we used workbook scope for named formula, you may ignore that effect

Finally no problems with recalculations. In attached is only one series, but I believe if you add by same way dozens of more that won't affect performance significantly.

[#9]

Sergei is right (https://techcommunity.microsoft.com/t5/excel/why-excel-crashes-or-runs-extremely-slow-when-i-m-tryin...), you must not point a chart's source to entire columns

[#10]

Thanks a lot @Sergei Baklan  and the rest of the lot

I simply plotted the chosen cells using shift and control and it's fine now.

I feel stupid. Sometimes the answer is right beneath our nose. 

Thanks again 

All the best Max

[#11]

@MaximosGatos , glad to help. If you have static data which is practically never changed fixed range works fine.

[#12]

Next time you create a chart, format your source data range as a table. If you later add data tot he table, the chart grows with it automatically. Just as formulas will.