spread sheet huge size not able to work
Dear Experts:
We are having a spread sheet microsoft excel of file size 35mb and this to be used on daily basis by the team we are in progress of implementing CRM however this will take few months for completion but until then we have to manage using this spreadsheet, when opened the spread sheet this takes more time to open and also more time for any work to be done. this spread sheet consists of multiple sheets and also many formulas.
can anybody suggest the best method to handle this situation,
We are having a spread sheet microsoft excel of file size 35mb and this to be used on daily basis by the team we are in progress of implementing CRM however this will take few months for completion but until then we have to manage using this spreadsheet, when opened the spread sheet this takes more time to open and also more time for any work to be done. this spread sheet consists of multiple sheets and also many formulas.
can anybody suggest the best method to handle this situation,
andrew_man
Do you think you can put all of your data to a mdb?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi thanks for the reply, are you recommending to go for microsoft access. if yes can you recommend the steps like first procure license and then should we have to hire or outsource this migration from excel to ms access. please suggest
just use the mdb file to store all the data! Append, update and delete are using excel.
ASKER
can you please help in little more detail , should we have to convert present xls file to mdb and work on it, please explain
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
On each sheet, pressing "End + Home" or "Ctrl + End" should take the cursor to the last used column and row, ie bottom right of your data.
If the cursor goes way beyond the bottom right of the data it is possible that the sheet's used range area has been corrupted in some way. It could just be that there is some formatting in cells beyond the end of the data that Excel is recognising as being used.
To get rid of excessive formatting, manually go to the known bottom right of the data and delete columns to the right and rows below. Physically delete the columns/rows not just the Clear Contents or Clear All.
Do that on each sheet and see if that reduces the file size.
One burning question - is the file using Shared Mode? If so that is probably the problem; the Excel Shared mode facility is notoriously unreliable and cause corruption in various forms, file size bloat being one of them!
Thanks
Rob H
If the cursor goes way beyond the bottom right of the data it is possible that the sheet's used range area has been corrupted in some way. It could just be that there is some formatting in cells beyond the end of the data that Excel is recognising as being used.
To get rid of excessive formatting, manually go to the known bottom right of the data and delete columns to the right and rows below. Physically delete the columns/rows not just the Clear Contents or Clear All.
Do that on each sheet and see if that reduces the file size.
One burning question - is the file using Shared Mode? If so that is probably the problem; the Excel Shared mode facility is notoriously unreliable and cause corruption in various forms, file size bloat being one of them!
Thanks
Rob H
It is the best way to Implement an Application Like CRM to overcome this problem and use team member skills in an effective way. Let me know of you need CRM Consultancy.
The best way to use excel in your scenario is to create multiple workbooks all linked as per your business flow and every individual use a separate excel file which pull or submit data in a single repository ( through linking) to a big excel file.
Most of the business use this type of system to overcome such type of issues.
If possible please share few sample of your Excel file, to share a better solution.
Thanks,
The best way to use excel in your scenario is to create multiple workbooks all linked as per your business flow and every individual use a separate excel file which pull or submit data in a single repository ( through linking) to a big excel file.
Most of the business use this type of system to overcome such type of issues.
If possible please share few sample of your Excel file, to share a better solution.
Thanks,
What version of Excel are you using? In one of your previous posts you mentioned the .XLS file. If you are using Excel 2007 or 2010 or 2013, the new file formats (*.XLSX and *.XLSB)greatly reduce the size of an Excel file. As an example, a 25meg XLS file I have on my PC, saves to the XLSX format at 14meg and saves to the XLSB format at 5meg. Just use File SAVE AS and select one of the newer formats. If you are using Excel 2003 or earlier this will not be an option for you.
Assuming you are sharing the file from a single system you may want to make sure it is not being scanned by your anti-virus.
There are many things that can make it slow.
How much ram in the systems. What Operating System. What kind of network, peer to peer or server. How powerful are the systems. What is the speed of the network. Etc.
There are many things that can make it slow.
How much ram in the systems. What Operating System. What kind of network, peer to peer or server. How powerful are the systems. What is the speed of the network. Etc.
Another option - Is this the Office File Validation update?
Check this MS Link
http://support.microsoft.com/kb/2570623/en-us
Thanks
Rob H
Check this MS Link
http://support.microsoft.com/kb/2570623/en-us
Thanks
Rob H