Excel memory management - Small file taking huge resources

[Meesam]

Guys I got one very annoying problem related to memory management of excel. I have one file of size 4.75 MB and it's a ".xlsm" file meaning it has some very basic general routines related to management of my various sheets. I got 275 sheets in the workbook. Whenever i am working on sheet (saving some data and data entry), sometimes the memory just shoots up and at times it consumes more than 1GB of memory. What is the reason behind this and why it is happening? I have attached screenshot of my "Task Manager" showing memory taken by that file.

Any help and guidance is highly appreciated.

 

[RoryA]

Very hard to diagnose from that information. I think you'll need to narrow down when the memory use increases - probably when one or more of your routines runs.

 

[Meesam]

Rory, Thanks for quick reply. Actually there aren't any routines running in the background like any action related code or anything fancy like that. Its plain sheets but having lot of "Conditional formatting". I shouldn't even mention routines which I have sort of "saved" at the background of sheet as added modules. Routines only get invoked when I need them. I am thinking it might be related to "number of sheets" and in some way related to "conditional formats" which are a lot and are copied with each new addition of sheet.

 

[thomassharp]

I don't know if this will help but I had a similar problem and when I cleared all excess formatting it resolved the issue

 

[Zakkaroo]

Do you have a lot of formulas in those sheets? I may be mistaken, but I believe excel re-calculates all open workbooks & formulas, it will do it very regularly if you have it set to 'automatic' - that could explain spikes in memory usage

 

[Meesam]

I don't have lot of formulae either. I only have lot of conditional formatting. Now I am just having one idea. I should make a MACRO to standardize all the formatting in each sheet and then apply ONE uniform formatting (all conditionals are inter-related) to all the sheets in that particular range. Now just looking for some nice macro and hopefully will come up with something soon and lets hope it WORKS. Will keep posted.

 

[Meesam]

Ok at last I got to know one thing about this conditional formatting that it was itself also taking lot of space. I removed conditional formatting from my 5 columns and now the size of file has reduced from 4.75MB to 3.16MB. Its huge difference considering that it was JUST formatting. My book also seems to be extremely stable now. Now I am looking forward to COPY the format given in one of the sheet to all the sheets in same ranges so that format can be re-applied but hopefully with less severe consequences.