Feedback on Excel File - Getting Larger Each Save

wardy0601

Board Regular
Joined
Sep 24, 2011
Messages
117
Hi team,

Have an excel file that gets larger every time I save it. It did have circular refs in it that have now been repaired (no errors or circ ref's any more) and the file size has gone from 800kb to around 2.5mb in 3 days. Each time I save it with new data input into it, it grows around 500kb and is now taking 20 mins to save.

Any ideas as to what could be causing the issue so I can find and repair it?

All the calculations inside the workbook are formulas. It is a property acquisition sales and cash flow modelling tool that I have got an automated report (text and formulas) in. It also has dynamic graphs, but nothing that should be making the sheet expand like this.

Thanks in advance.
 
@MARK858
A case of file bloat maybe ???
I couldn't open it at all with Excel 07 or 13

Not sure, the file is only 2.51mb , the usedranges aren't excessive..

$B$8:$C$213
$A$2:$J$88
$A$1:$K$145
$A$1:$AF$66
$A$1:$AF$134
$A$1:$AE$101
$B$1:$M$123
$A$1:$T$57
$A$1:$BH$128
$A$1:$AE$69
$A$1:$Q$46
$A$1:$AR$1580

The only thing other than the unreadable content (that I still haven't worked out what that content is) is that there are a lot of named ranges with some that are using the likes of OFFSET (Portfolio_Analysis!Print_Titles might be an issue with how many cells it refers to but not looking at it today)
.

Excel Workbook
ABJL
1NameRefersToLocalCellsRefersToRange
2_xlfn.IFERROR=#NAME?
3Beg_Bal=PPOR_Mortgage!$C$20:$C$15791560[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$C$20:$C$1579
4Buffered_Rate=OFFSET(Portfolio_Analysis!$AV$9,,,COUNT(Portfolio_Analysis!$AV$9:$AV$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$AV$9:$AV$29
5CapGrowth_Current='Wealth CALC Detail'!$C$701[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Wealth CALC Detail'!$C$70
6CapGrowth_Ill1='Wealth CALC Detail'!$C$711[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Wealth CALC Detail'!$C$71
7Cflow_Target=OFFSET(Portfolio_Analysis!$AL$9,,,COUNT(Portfolio_Analysis!$AL$9:$AL$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$AL$9:$AL$29
8Clow_Atax=OFFSET(Portfolio_Analysis!$AK$9,,,COUNT(Portfolio_Analysis!$AK$9:$AK$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$AK$9:$AK$29
9Ctive_Debt_No_Offset=OFFSET(Portfolio_Analysis!$V$9,,,COUNT(Portfolio_Analysis!$V$9:$V$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$V$9:$V$29
10Ctive_Debt_Offset=OFFSET(Portfolio_Analysis!$U$9,,,COUNT(Portfolio_Analysis!$U$9:$U$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$U$9:$U$29
11Current_Income='Wealth CALC Detail'!$C$91[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Wealth CALC Detail'!$C$9
12Eligible_Income=OFFSET(Portfolio_Analysis!$AU$9,,,COUNT(Portfolio_Analysis!$AU$9:$AU$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$AU$9:$AU$29
13Equity_Cash=OFFSET(Portfolio_Analysis!$X$9,,,COUNT(Portfolio_Analysis!$X$9:$X$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$X$9:$X$29
14Extra_Pay=PPOR_Mortgage!$E$20:$E$15791560[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$E$20:$E$1579
15Gross_Cflow=OFFSET(Portfolio_Analysis!$AJ$9,,,COUNT(Portfolio_Analysis!$AJ$9:$AJ$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$AJ$9:$AJ$29
16Int=PPOR_Mortgage!$H$20:$H$15791560[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$H$20:$H$1579
17Interest_Rate=PPOR_Mortgage!$D$81[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$8
18LMI=Tables!$B$21:$E$2832[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Tables'!$B$21:$E$28
19Loan_Amount=PPOR_Mortgage!$D$71[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$7
20Loan_Start=PPOR_Mortgage!$D$111[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$11
21Loan_Years=PPOR_Mortgage!$D$91[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$9
22LVR=OFFSET(Portfolio_Analysis!$Y$9,,,COUNT(Portfolio_Analysis!$Y$9:$Y$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$Y$9:$Y$29
23Num_Pmt_Per_Year=PPOR_Mortgage!$D$101[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$10
24Num_Props=OFFSET(Portfolio_Analysis!$AR$9,,,COUNT(Portfolio_Analysis!$AR$9:$AR$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$AR$9:$AR$29
25Pay_Num=PPOR_Mortgage!$A$20:$A$15791560[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$A$20:$A$1579
26Portfolio=OFFSET(Tables!$B$73,,,Tables!$C$72)3[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Tables'!$B$73:$B$75
27Portfolio_Value=OFFSET(Portfolio_Analysis!$O$9,,,COUNT(Portfolio_Analysis!$O$9:$O$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$O$9:$O$29
28Princ=PPOR_Mortgage!$G$20:$G$15791560[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$G$20:$G$1579
29Cap Growth Calc'!Print_Area='Cap Growth Calc'!$A$1:$I$46414[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Cap Growth Calc'!$A$1:$I$46
30Current!Print_Area=Current!$B$1:$I$51,Current!$K$1:$T$45858[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Current'!$B$1:$I$51,$K$1:$T$45
31Inputs!Print_Area=Inputs!$A$1:$K$1441584[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Inputs'!$A$1:$K$144
32Portfolio_Analysis!Print_Area=Portfolio_Analysis!$B$4:$J$72,Portfolio_Analysis!$M$5:$BD$502645[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$B$4:$J$72,$M$5:$BD$50
33Portfolio_Anual - Tax Benefits'!Print_Area=Portfolio_Anual - Tax Benefits!$B$1:$P$32,Portfolio_Anual - Tax Benefits!$A$34:$K$66,Portfolio_Anual - Tax Benefits!$M$34:$W$66,Portfolio_Anual - Tax Benefits!$A$68:$K$100,Portfolio_Anual - Tax Benefits!$M$68:$W$100,Portfolio_Anual - Tax Benefits!$A$102:$K$134,Portfolio_Anual - Tax Benefits!$M$102:$W$134,Portfolio_Anual - Tax Benefits!$R$1:$AA$9,Portfolio_Anual - Tax Benefits!$AC$1:$AF$321932[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Anual - Tax Benefits'!$B$1:$P$32,$A$34:$K$66,$M$34:$W$66,$A$68:$K$100,$M$68:$W$100
34Prop Investment Costs & Income'!Print_Area='Prop Investment Costs & Income'!$A$1:$H$34,'Prop Investment Costs & Income'!$J$1:$AF$431261[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Prop Investment Costs & Income'!$A$1:$H$34,$J$1:$AF$43
35Report_Client!Print_Area=Report_Client!$B$1:$B$57,Report_Client!$B$59:$C$97,Report_Client!$B$99:$D$160,Report_Client!$B$162:$E$213529[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Report_Client'!$B$1:$B$57,$B$59:$C$97,$B$99:$D$160,$B$162:$E$213
36SMSF!Print_Area=SMSF!$M$1:$AE$45,SMSF!$B$1:$K$641495[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]SMSF'!$M$1:$AE$45,$B$1:$K$64
37SMSF - Tax Benefits'!Print_Area='SMSF - Tax Benefits'!$A$1:$O$32,'SMSF - Tax Benefits'!$Q$1:$AA$9,'SMSF - Tax Benefits'!$AB$1:$AE$32,'SMSF - Tax Benefits'!$A$35:$J$67,'SMSF - Tax Benefits'!$L$35:$U$67,'SMSF - Tax Benefits'!$A$69:$J$1011697[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]SMSF - Tax Benefits'!$A$1:$O$32,$Q$1:$AA$9,$AB$1:$AE$32,$A$35:$J$67,$L$35:$U$67,$A$69:$J$101
38Tables!Print_Area=Tables!$B$2:$G$9,Tables!$B$11:$G$17,Tables!$B$19:$E$28,Tables!$B$30:$C$31,Tables!$B$33:$F$44,Tables!$B$46:$F$57,Tables!$B$59:$F$70,Tables!$B$72:$B$88331[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Tables'!$B$2:$G$9,$B$11:$G$17,$B$19:$E$28,$B$30:$C$31,$B$33:$F$44,$B$46:$F$57,$B$59:$F$70,$B$72:$B$88
39Wealth CALC Detail'!Print_Area='Wealth CALC Detail'!$B$6:$C$77144[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Wealth CALC Detail'!$B$6:$C$77
40Portfolio_Analysis!Print_Titles=Portfolio_Analysis!$M:$N2097152[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$M:$N
41Repayments=OFFSET(Portfolio_Analysis!$AT$9,,,COUNT(Portfolio_Analysis!$AT$9:$AT$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$AT$9:$AT$29
42Sched_Pay=PPOR_Mortgage!$D$20:$D$15791560[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$20:$D$1579
43Tax_Table=Tables!$B$13:$G$1730[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Tables'!$B$13:$G$17
44Total_Pay=PPOR_Mortgage!$F$20:$F$15791560[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$F$20:$F$1579
45Values_Entered=IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)
46Year=OFFSET(Portfolio_Analysis!$N$9,,,COUNT(Portfolio_Analysis!$N$9:$N$40))21[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$N$9:$N$29
Names 2018-Dec-30




Not really in the mode to delve deeper at the moment but might look at it again tomorrow
 
Last edited:
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
PPOR_Mortgage is the only sheet of significant size 2MB+

But the problems probably lie with the workbook errors which repair doesn't fix.
 
Upvote 0
IS there any code in the workbook ??
Workbook_open or Workbook_calculate
 
Upvote 0
Maybe PPOR_Mortgage usedrange needs to be copied to a new workbook, and see if that reduces the size and issues
 
Upvote 0
PPOR_Mortgage is the only sheet of significant size 2MB+

But the problems probably lie with the workbook errors which repair doesn't fix.

Portfolio_Analysis looks the largest to me (still agree that the corruption is the issue and it will probably need a rebuild).

Excel Workbook
ABCDEFGHIJKLMNOP
14Cell CountsCondFormatCalc Time (Millisec)SheetMTCMicroSecsSheetShtBytesUnused Cells
15SheetnameUsedConstCondFmtFormulaEnabledRangeRe CalcFull Calc% VolatileFactor/FormulaMem(K)per Cell%Waste%Sparse
16Report_Client1,065041TRUE1.970.7257.127.5%0.047.200.040%
17Tables8800123TRUE8.70.1252.00.070.500.00%
18PPOR_Mortgage69,5203,12036,006TRUE125.30.0106.01.22.92,06058.630%
19Portfolio_Analysis8,2806,1881,898TRUE12.770.578.490.0%0.26.733,22017,922.70%
20Current1,14053494TRUE7.30.170.10.114.900.00%
21Portfolio_Anual - Tax Benefits4,2881502,646TRUE8.00.067.70.13.06424.80%
22Cap Growth Calc782369301TRUE0.20.067.30.00.864217.729%
23Wealth CALC Detail1,599120170TRUE0.30.067.30.01.800.00%
24SMSF - Tax Benefits3,1311551,567TRUE6.80.167.00.14.300.00%
25Inputs2,465553390TRUE153.10.165.22.3167.164168.035%
26Prop Investment Costs & Income2,1120239TRUE0.50.064.30.02.100.030%
27SMSF2,139292940TRUE1.60.062.40.01.76469.70%
28Other0.11.21.333,804
FastXLBook1
 
Upvote 0
Ah, thanks so much experts! This is really handy.

No macros in the workbook is correct. The workbook has been built on formulas and conditional formatting only.

Based on @MARK858 analysis, my assumption in that the portfolio analysis page was the issue seems correct.

If I was to delete all of the formulas and rebuild them in that sheet, would that work? Or do I need to scrap the entire sheet and start it from scratch again?

Lastly, how does a file become corrupted? I would hate to rebuild it to then make the same mistake and have it happen again.

Thanks again so much for the feedback. I was literally bashing my head against the desk not knowing why this is happening!
 
Upvote 0
try Inquire - Workbook Analysis to see what is or what is not correct

btw. why there is the same workbook from two different location is linked?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]C:\Team Drives\First Step Finance\First Step Finance\Financial\Calculators\IPA Calc\SS_IPA_2018-11-17_V44_Branded_I.xlsb[/td][/tr]

[tr=bgcolor:#FFFFFF][td]G:\Team Drives\First Step Finance\First Step Finance\Financial\Calculators\IPA Calc\SS_IPA_2018-11-17_V44_Branded_I.xlsb[/td][/tr]
[/table]
 
Upvote 0
If I was to delete all of the formulas and rebuild them in that sheet, would that work? Or do I need to scrap the entire sheet and start it from scratch again?

You can try it (and also try deleting the charts) but I still think you will probably be better off doing a full rebuild of the workbook (much as a pain that is)

Lastly, how does a file become corrupted? I would hate to rebuild it to then make the same mistake and have it happen again.

There is no agreed answer on this, you will find a fair few answers if you Google it but there is definitive answer.
 
Upvote 0
try Inquire - Workbook Analysis to see what is or what is not correct
@wardy0601, a good suggestion above if you have it available, afraid that I can't do this for you as I only have Excel 2010 in front of me and Inquire - Workbook Analysis didn't come in until Excel 2013.
 
Upvote 0

Forum statistics

Threads
1,223,959
Messages
6,175,645
Members
452,663
Latest member
MEMEH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top