How to Speed up Excel Processing

[sheltton]

HI' Guys,

How to speed up excel calculation's ?

If thats the question we need answers too. lets discuss it here.

Couple of tips and tricks from my side. That I use to speed up exel

Tip 1

Like it's been repeated a million times on millions of forums accross millions of website.

Change the settings to manual,

How to do that is simple.

A:Tools > Option > Calculation > Check on Manual
Con: Everytime you put in a formula in one cell and drag it down you will not get an instant update of your formulas in the adjacent cells.
but when your ready to update your sheet and your done with all the calculations Just Press F9 for updation However make sure you do it in parts so that it seems it will work faster.... (It doesnt actually make it faster it just seems it is faster) actually if we do things manually it just takes the same time )

Pro : to it is that you can finish all your work and then press F9 for updation. otherwise it's a pain to wait for the moment you can put the next formula isn't it ?

B: Calculation = RAW Processor speed.

Note for dummies
Most people wonder what that means.

People might be surprised with this but Excel doesnt process from thru RAM. It Dumps the File there but doesnt actually do anythign else other than that.
Which means even though you have a 2-4-8 GB Ram, people have a 1 MB file that takes longer than 5 Hours to calculate. Thats nothing wrong with Excel, or your formulas or your VBA Code, Note that if you copy paste a file that goes thru the RAM as a BUFFER, so dont mistake copy-Paste as processing. It's completely different. Processing take up your entire Processing CHIP Which can be your Intel Celeron - Intel Dual Core or your nre i7 for that matter for example If I take a.XLS file and run the processing on a celeron it will be slower, that the i7 ofcource, but it still do the same thing, that it will always do. Process at the given rate.
Input ----Process----Output.ie 1 thing at one time. no matter what you do. It's basically like a vba script that will process in stages or better yet First cum First serve bases in MACDonals,

Follow the red Line.

The Pro side to it is, If your only using excel and it's really that important to processor Just your file, look down there to the right corner of your screen those small icons are programs right click on the ones that are not required and exit all the ones that are not required ! YEs that means the WInamp Player too . and

NOTE: This example is only for files that are bigger than 5-50MB and like me if you have a quad core or a better processor. There are consequences to everything.

NewBies: Dont try this at all. You'll just get confused. and ask questions for no reason.

-----------------------------------------------

1 : Now Open your excel sheet in Manual Update mode
2 : Select everything you want to update(Dont Press F9 yet)
3 : Hold Ctrl + Alt + Del and go to task Bar
4 : Click on Processes
5 : Search for Excel.EXE
6 : Right Click on Excel.EXE and either choose "Set Priority" > Above Normal (For Dual Processors) and for Quad Core Processors try ( High )

What this does it hangs up your computer from all tasks and process's EXCEL.EXE at Full Speed (Like Cutting the Line in MAcdonals and being treated as A VVIP , However the processing will be faster. Than Normal
7 : Go Back to Your File and Press F9

The Good part is that your work will be done faster.

The Bad Part is that nothing else will work till Excel Processing is not over, since the CPU it buzy processing at full power for Excel.

Note:- Go Have a cup of cofee.

-------------
C : Costly but Buy yourself Fast Excel.

How does fast excel actually work.

Well the it's a magic trick actually.

Rem the copy paste technicq
Well it's kind of like that.

For example.

Hypothetical situations.

Let assume your Your file is filled with formula's to process.
I have
List of names in A,
List of Surnames in B
and Formulas in C all the way down to the last cell that is C1:C65536 (Hypothetically Speaking)
The time taken to process this will be assumed at 5 Min's

It's Like drying your laundry in the sun.

If you put a pice at a time you to dry it will take more time but if your break that piece into 10 times it will take lesser time.

What the file does is Breaks the data into 5-10 parts (again hypothetically speaking ) Like this

Data from A1:C3000 = New Document
Data from A3001:C6000 = New Document 2
etc etc etc.

Now the processing starts as normal however here data is processed for each file symanteniously.

Making your dry your clothes all at once. The Sun is reaching all the clothes at onces to dry them

Get my Point.

Well the Jack who made the Program was a Genius dont you think ?
No wonder you have to Pay for it.
God I hate the corrupt world. solution Welcome to Linux : Complete Opensorce Which means free energy.

Any way Back to the Topic.

We'll I'm not a programer but I'm sure with all the Brains out there some1 could come up with a buffering macro similar to that and help rid of all the speeding issues.

We have the Man POwer all we need is the leadership < Count me as man power not LEadership material.

This is my Sales Pitch for a better White Hat excel Society ?

The Dorrs are officially Open for Discussion.

D: Build your self a grid computer with 8 PS3's and get a 50 MB xls file to calculate in seconds rather than hours.

IF you have those kinda a files every day i'm sure the cost wont be a problem

Sheltton.

 

[NdNoviceHlp]

Thanks Shelton for your interesting post. It seems that one should simply focus on keeping the XL file size to a minimum thus reducing the RAM time to process? If that is the case, it seems like alot of effort should be given to "dumping" non essential data into retrievable storage (ie. Word/Access) and leaving the calculation stuff to XL (ie. if your XL file size gets too large, you're not doing the right stuff with XL... it's not a data base for "large" amounts of data.) My 2 cents are up. Dave

 

[sheltton]

Not sure but yeah kinda of, but then there are exceptions, you can have a data of 50MB xls File and yet not have any formulas in it, and retireve data from another smaller KB file it might help doing processing that way, except if your VB code is Being specific. only then will it work. I've seen posts where people write codes so complex that they just want to retrieve column A:A and end up making a script that searches and reads the whole data from the sheet.
Which's kind of is dumb.

Sometimes it help to update posts here and ask seniors for help, trimming the macro's , and sometimes even they are as lost as we are. !

Yesterday I learnt yet another trick to help speed up processing.

if you go to options and click on calculations, you have 2 options below,

1 is iteration : set that to half of whatever it is in my case it was 100 i set it to 25 since my calculation is not required to recheck more than recalculate more than that just not humane.

the the other one is another option with values set at 0.001, the lower the value the faster the calculation I tried value 0, which the least count but it doesn't actually work, then I set it at 0.000001 I think it works, but not really all that difference in speed up.

though it was meant to do that.

Try it and let me know if this works for you guys. Do a speed test.

 

[danielphayward]

As an update to this in Windows 10, the easiest way to set something to priority is:
1. Right click the Windows Logo in the bottom left hand of the screen.
2. Go to Task Manager.
3. Right click Excel and go to details.
4. Then right click again and set to priority once you're in the details tab of task manager.