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,
D_wathiAsked:
Who is Participating?
 
Danny ChildConnect With a Mentor IT ManagerCommented:
Sheets of that size are normally down to some sheet corruption.  I'd suggest making a temporary copy, deleting half the tabs and see if the size drops dramatically.  If it doesn't, go back to the copy, delete the other half and see if that works.  Keep repeating this till you find the tab with the excessive size.  

Then, copy and paste data out of the bad sheet into a new one, and see if the size decreases.

Embedded graphics or excessive numbers of fonts can send file sizes through the roof.
0
 
andrew_manCommented:
Do you think you can put all of your data to a mdb?
0
 
JohnConnect With a Mentor Business Consultant (Owner)Commented:
Also, if you do need to work in Excel, consider making multiple workbooks with logical pieces in each book and a summary book. This makes the books much smaller and it is fairly easy to reference cells across books for calculation purposes.

Also make sheets small (have more of them) but that will not do as much as separate books.

... Thinkpads_User
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
D_wathiAuthor Commented:
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
0
 
andrew_manCommented:
just use the mdb file to store all the data!  Append, update and delete are using excel.
0
 
D_wathiAuthor Commented:
can you please help in little more detail , should we have to convert present xls file to mdb and work on it, please explain
0
 
andrew_manConnect With a Mentor Commented:
In previous version of excel, we just selected the save as to mdb or dbf.

But, it is impossible now, because excel is too hot!

I suggest you can download the openoffice for free.  It can help to convert the excel file to dbf file.  

Moreover, I just try the online convert at below link (seems very good and fast)!

http://www.zamzar.com/

Then, we can do any analysis (Pivot Table) or updating (vba through ado) on this dbf file.

Thanks!

Andrew
0
 
Rob HensonFinance AnalystCommented:
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
0
 
nigam333Commented:
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,
0
 
Jerry PaladinoCommented:
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.
0
 
MikeKAtLCSCommented:
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.
0
 
Rob HensonFinance AnalystCommented:
Another option - Is this the Office File Validation update?

Check this MS Link

http://support.microsoft.com/kb/2570623/en-us

Thanks
Rob H
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.