I have inherited an Excel file that has only five tabs..and about 1000 rows.
This is a file that has been used abused and reused for years....
and it's 28 megs?? seems waay too large for the amount of data.
There are no data connections, it's all local data.
Problem is it takes thiry minutes to do anything...what can I do to shrink this file?
Is it just Data? If it is then you can't really clean it up unless there are formulas of course and then theres lots you can do.
Thanks for answering aiwnjoo
it's all formulas!!
are there some most likely suspects I could look at to shrink this beast?
Try the format cleaner at the below. will clean it right up, if the problems is excess formatting
http://www.microsoft.com/downloads/d...displaylang=en
check the right scroll bar on each tab of your workbook- this should only come down as far as where you actually have data- if it goes further than that then you have excess formatting
I downloaded the macro ran it and saved the file..
no difference in size
In each sheet,
Select the last column (IV) and do Edit > Clear > All
Select the first unused column, Ctrl+Shift+right arrow, Edit > Delete
Select the last row (65536) and do Edit > Clear > All
Select the first unused rows, Ctrl+Shift+down arrow, Edit > Delete
Save, close, and reopen.
Entia non sunt multiplicanda sine necessitate
tried everything in each sheet..
still large..
If the sheets aren't interdependent, create a new workbook with the requisite number of sheets, copy the used range of each sheet in the old workbook to a new sheet, save and close.
If what shg gave you does not work, then it's probably your formulas. The most likely suspects are volatile functions. Most common would be; NOW(), TODAY(), INDIRECT() and OFFSET(). Is your spreadsheet using lots of those?
Another possibility is links to other workbooks? Are there links in the workbook?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
YES gotta be formulas!
formulas would make the file size bigger?? doesn't seem possible
no of those are there:
there's:
MAX
ISBLANK
EOMONTH
MONTH
PMT
ROUNDUP
ISERROR
IFERROR
INDEX
MATCH
SUMPRODUCT
SUMIF
XMPV
IFERROR? That's an Excel 2007+ function, and your profile shows 2003.
Sorry..my profile is incorrect...(I just signed up)
We are using Excel 2007 and even trying to use Excel 2010
Hmmm .....
I've seen this type of problem before, if an old .xls file is saved as xlsx or .xlsm then the size should drop in size by some 60%.
Don't know if it is in the zip structure of the Excel file or what.
I can't be of much help with this, but it might jog someones' memory
krypto, would you please change your profile?
i updated my profile
I have a large excel file I'm trying to slim down and have narrowed it down to this formula.
Can you guys tell me if there's a better way this could be done?
=IFERROR(MAX((('Am Sched'!BR2-('Payment Sched'!BU2-(('Payment Sched'!$D2*30/360)*'Am Sched'!BR2)))*
IF(SUM('C:\Documents and Settings\ypandey\Desktop\[Mod List as of 12-31-09 6-22-2010 Supplement - TDRs Only.xlsx]$ Defaults'!$AJ2:BR2)>=0.95*$AJ2-SUM('C:\Documents and Settings\ypandey\Desktop\[Mod List as of 12-31-09 6-22-2010 Supplement - TDRs Only.xlsx]$ Loss'!$AJ2:BR2),1,(1-(IF('Payment Sched'!$J2="Mod",PV!AM$3,PV!AM$5)*-MIN(((0.9*'Payment Sched'!$C2)-('Am Sched'!BR2+SUM('C:\Documents and Settings\ypandey\Desktop\[Mod List as of 12-31-09 6-22-2010 Supplement - TDRs Only.xlsx]$ Loss'!$AJ2:BR2)))/('Am Sched'!BR2+SUM('C:\Documents and Settings\ypandey\Desktop\[Mod List as of 12-31-09 6-22-2010 Supplement - TDRs Only.xlsx]$ Loss'!$AJ2:BR2)),0))))
),0),0)
Could/should I use VBA for this?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks