[RESOLVED, for the time being] Excel File Size

[Siddharth Rout]

Hi

My job requires me to create new trackers every month. To name a few : Attendance tracker, Payroll Tracker, Quality Tracker etc...

I have been trying to find a workaround but haven't been successful.
Basically there are approx 500 employees in my process so I have to use lots of validation-drop down lists, colors, formulas, tables, charts etc...

The problem is the file size. No matter what i do the file size of an average file is minimum 2 mb!!! the files are on CITRIX so it takes more time to open large files...

I tried using vba where ever possible but the size refuses to reduce....

Unfortunately we have to use excel... I tried convincing the management to use vb as frontend and access to store data but they are not ready for it yet...

Is there any other way I can reduce the file size....

Any help will be appreciated...

[kjeldc]

Fire half the employees

[Siddharth Rout]

ARRRRRGHHHHHH!!!!!!!!!

And I thought I was the only one with sAd hUmOr.

OH GoD, either call me or call kjeldc near you!!!

hUmOr Is NoT WhAt It UsEd To Be.....

Ok, on a serious note... Can we do something about it ? I mean these files take ages to open....

[zaza]

If you can just keep a list of employees on one sheet and use lookup tables, you might be better off, i.e. rather than repeating lots of data on several sheets, just put it in once on one sheet.


But I suspect that won't do a great deal to reduce the file size, unless you are repeating a lot of data.


Another alternative might be to store ALL your data in one Excel sheet and then access it like a database from other Excel sessions...

Otherwise, not much.


zaza

[Siddharth Rout]

Hi Zaza

Thanks for the reply...

I already have a master sheet which I use for vlookup. I am not repeating data anywhere.... the main problem is using validation, formula and colors, it really makes my file heavy....

[KGComputers]

Hi,

In access theres a command for compression...I don't know if theres a tool(third party) in excel that would compress an excel file/database...


greg

[Siddharth Rout]

Hi,

In access theres a command for compression...I don't know if theres a tool(third party) in excel that would compress an excel file/database...

That would be a problem again because every time (which is very often) someone wants to look at it, he or she would have to uncompress it again which would be a tedious job...

[KGComputers]

Hi,

I mean Compact...
Sorry...


Greg

[Siddharth Rout]

hmm... that could work i.e if there is any such utility which can be integrated with excel....

[si_the_geek]

A database would definitely be the best option, but sometimes management refuse to see sense, thinking that extending/fixing what you have must be easier that starting again in the proper way! (I once spent a month "fixing" a system, when it would have taken a week to re-write it!)


In terms of colours, would you be able to colour items appropriately using Conditional Formatting? If so that might reduce the file size (it depends on exactly how Excel deals with it tho!).

For the formulas, do you have something like all items in column having the same formula? If so you might be able to use a macro instead (which would be called from Worksheet_Change, and possibly Workbook_Open too). If the formulas are large, this should make a big difference to the file size.

Validation could possibly be done in a similar way to the formula suggestion.

[Mitch_s_s]

i use some attendance sheet in our work
but i split ours down into teams

then have a "master" will pulls in all the sick etc in and gives us the %'ages for our weekly reports

[Mitch_s_s]

access databases arent supported by our systems administrators either
which is a pain

SQL is though, but one step at a time i think ha

[Siddharth Rout]

which would be called from Worksheet_Change, and possibly Workbook_Open too). If the formulas are large, this should make a big difference to the file size.

Thanx Si

I have already tried that but the problem is the files are on citrix and every time the file opens, it takes hell lot of time to open and update. The second major problem is that the people are not that into excel so if the file is taking hell lot of time then they "end-task" the application which results in your vba application being lost!!!. I have lost lot of files recently and i had to remake it....

[si_the_geek]

You wouldn't necessarily need to run anything in Workbook_Open, if all of your users have macros enabled then there would be no need (as the required changes would have been done by Worksheet_Change).

A good way to force people to only use it when macros are enabled is to have a dummy sheet, which simply contains text saying that they need to re-open it with macros enabled. This sheet would be hidden by Workbook_Open (and all other sheets shown), and it would be re-shown by Workbook_Save/Close (and the others hidden).

[Siddharth Rout]

Okay here is an example...

let's say i have master file which has the Employee ID's of 500 people, their 1st names and their last names.

now I want to create a Quality tracker which should have the following details

1) 5 different sheets. 4 for 4 weeks data and 1 for summary
2) each weeks data will have the following column heading
a) employee ID, Employee 1st name, 2nd name, Reports to, total score, and sixteen other columns for sub scores.

Now these 16 columns are the most irritating ones cause they all have validations set. so in all I am talking about

1) (500people * 16 cells of validations)* 4 sheets
2) A grand summary using subtotals to pick up data from all the 4 weeks
3) different colors to 'beautify' the sheet (as per management's requirements)
4) 4 different types of charts based on the summary excluding a chart which does a comparison of last month's data... phew!!!!

Hope u understand...

[si_the_geek]

That's 42000 cells (without the summary sheet), so the file will be large whatever you do (if possible, ensure the relevant cells are formatted as numbers).

There are also 32000 validations.. or are there? Are the validations per cell, or per column, or for the entire 16 columns?

I haven't used validations before, but just recorded a macro and it seems that they apply to a range - so by applying them to a range of cells (if you haven't got it like that already) could save some size.

[Siddharth Rout]

I haven't used validations before, but just recorded a macro and it seems that they apply to a range - so by applying them to a range of cells (if you haven't got it like that already) could save some size.

I tried that, managed to bring down the size by 376kb...

anymore such tips ?

[si_the_geek]

Nice, that was lucky guesswork!!

My next thought would be the colours.. but how (or if) you would alter them depends on how they are set up, and it may not make any difference to file size anyway.

It would also be worth checking out the charts.. I don't know if they take much space in the file, but if they do it may be worth removing them when saving, and rebuilding them when you open it again.

[Siddharth Rout]

My next thought would be the colours.. but how (or if) you would alter them depends on how they are set up, and it may not make any difference to file size anyway.

Trust me, colors do make a difference... at least to the file and my management

Rgd rebuilding the charts, I don't want to do it cause it'll take ages to open in Citrix...

That leaves us with formulas...

[si_the_geek]

I don't know what to suggest really.. can you upload a sample file (with names removed/altered to protect the innocent!)?

[Torc]

What do the users do with the files, do they make changes, or just view the results?

[PabloSan]

If management is only using the spreadsheet as a report viewer, then you could "flatten" the file by doing all the work from your master file, and only saving the values and colors to the file on citrix.

Then each time you open a data file, you can open the file quickly (because there are not formulas, conditions, etc...), then recreate the formulas and conditions through macros on the master, and all the processing is done locally on your machine. When you have made your adjustments, save the values instead of the formulas, and change the colors of the cells or text to match your conditions, then delete the conditional statements.

You would take a big hit performance wise, but the other users would not.

[Siddharth Rout]

What do the users do with the files, do they make changes, or just view the results?

@Torc/pablo : Thanks for your replies...Yes they need to make changes as well.

[PabloSan]

This may be a real stretch. If the calculation mode was set to manual, would the spreadsheet load into the local computer's memory with no calculations, then set the calculation mode to Automatic once it is in memory (Workbook_Open).

[Siddharth Rout]

@Si : I am sorry I am not supposed to even load a blank format

anyways, if the company is not ready for changes so be it....

But yes!!! We managed to bring down the size by 376kb...

I am closing this thread for the time being. However if anyone has amazing solutions then please feel free to post your views here...

Thanks to every one once again...

[Siddharth Rout]

This may be a real stretch. If the calculation mode was set to manual, would the spreadsheet load into the local computer's memory with no calculations, then set the calculation mode to Automatic once it is in memory (Workbook_Open).

Though I haven't tried that, but my guess is it will take the same time to recalculate the cells... I might be wrong...

Anyways, I'll give it a shot...

Thanks for your views

[Foxer]

I found a big impact is the number of worksheets you use.

You can do a quick test yourself in about 60 seconds.

I've found, from a files size PoV, you are better to put all your data on one worksheet (spread it out and hide rows/columns as required) than you are spreading your data out over multiple worksheets.

You mentioned you had 5 worksheets? Try moving/copying 4 weeks data into one sheet and then off to the side, put your summary on the same sheet. You can then delete any unused sheets - including blank sheets, and you'll notice a difference.

If you try this - let us know how much you save. I work with 7 meg files and I'm considering this same approach.

[Siddharth Rout]

Hi Gary

Thanks for the input...

But I have already convinced the management to use Visual Basic 2005. The very fact that Visual Basic Express Edition is free was an added advantage.

I have already started making a software which has multiple logins and which can take care of all these trackers

Coindidently I launched the basic version of it today just need to build on it every day now...