Solved

spread sheet  huge size not able to work

Posted on 2013-12-05
12
326 Views
Last Modified: 2013-12-29
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,
0
Comment
Question by:D_wathi
  • 3
  • 2
  • 2
  • +5
12 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698190
Do you think you can put all of your data to a mdb?
0
 
LVL 23

Accepted Solution

by:
Danny Child earned 167 total points
ID: 39698194
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
 
LVL 93

Assisted Solution

by:John Hurst
John Hurst earned 167 total points
ID: 39698197
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:D_wathi
ID: 39698204
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
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698217
just use the mdb file to store all the data!  Append, update and delete are using excel.
0
 

Author Comment

by:D_wathi
ID: 39698293
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
 
LVL 4

Assisted Solution

by:andrew_man
andrew_man earned 166 total points
ID: 39698385
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39698501
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
 
LVL 1

Expert Comment

by:nigam333
ID: 39700436
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
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 39700594
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
 
LVL 4

Expert Comment

by:MikeKAtLCS
ID: 39700967
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39701106
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

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question