Solved

spread sheet  huge size not able to work

Posted on 2013-12-05
12
301 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:
DanCh99 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 90

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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 31

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 31

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now