Feedback on Excel File - Getting Larger Each Save

[wardy0601]

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]

@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
	A	B	J	L
1	Name	RefersToLocal	Cells	RefersToRange
2	_xlfn.IFERROR	=#NAME?
3	Beg_Bal	=PPOR_Mortgage!$C$20:$C$1579	1560	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$C$20:$C$1579
4	Buffered_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
5	CapGrowth_Current	='Wealth CALC Detail'!$C$70	1	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Wealth CALC Detail'!$C$70
6	CapGrowth_Ill1	='Wealth CALC Detail'!$C$71	1	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Wealth CALC Detail'!$C$71
7	Cflow_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
8	Clow_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
9	Ctive_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
10	Ctive_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
11	Current_Income	='Wealth CALC Detail'!$C$9	1	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Wealth CALC Detail'!$C$9
12	Eligible_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
13	Equity_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
14	Extra_Pay	=PPOR_Mortgage!$E$20:$E$1579	1560	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$E$20:$E$1579
15	Gross_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
16	Int	=PPOR_Mortgage!$H$20:$H$1579	1560	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$H$20:$H$1579
17	Interest_Rate	=PPOR_Mortgage!$D$8	1	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$8
18	LMI	=Tables!$B$21:$E$28	32	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Tables'!$B$21:$E$28
19	Loan_Amount	=PPOR_Mortgage!$D$7	1	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$7
20	Loan_Start	=PPOR_Mortgage!$D$11	1	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$11
21	Loan_Years	=PPOR_Mortgage!$D$9	1	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$9
22	LVR	=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
23	Num_Pmt_Per_Year	=PPOR_Mortgage!$D$10	1	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$10
24	Num_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
25	Pay_Num	=PPOR_Mortgage!$A$20:$A$1579	1560	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$A$20:$A$1579
26	Portfolio	=OFFSET(Tables!$B$73,,,Tables!$C$72)	3	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Tables'!$B$73:$B$75
27	Portfolio_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
28	Princ	=PPOR_Mortgage!$G$20:$G$1579	1560	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$G$20:$G$1579
29	Cap Growth Calc'!Print_Area	='Cap Growth Calc'!$A$1:$I$46	414	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Cap Growth Calc'!$A$1:$I$46
30	Current!Print_Area	=Current!$B$1:$I$51,Current!$K$1:$T$45	858	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Current'!$B$1:$I$51,$K$1:$T$45
31	Inputs!Print_Area	=Inputs!$A$1:$K$144	1584	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Inputs'!$A$1:$K$144
32	Portfolio_Analysis!Print_Area	=Portfolio_Analysis!$B$4:$J$72,Portfolio_Analysis!$M$5:$BD$50	2645	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$B$4:$J$72,$M$5:$BD$50
33	Portfolio_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$32	1932	[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
34	Prop Investment Costs & Income'!Print_Area	='Prop Investment Costs & Income'!$A$1:$H$34,'Prop Investment Costs & Income'!$J$1:$AF$43	1261	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Prop Investment Costs & Income'!$A$1:$H$34,$J$1:$AF$43
35	Report_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$213	529	[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
36	SMSF!Print_Area	=SMSF!$M$1:$AE$45,SMSF!$B$1:$K$64	1495	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]SMSF'!$M$1:$AE$45,$B$1:$K$64
37	SMSF - 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$101	1697	[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
38	Tables!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$88	331	[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
39	Wealth CALC Detail'!Print_Area	='Wealth CALC Detail'!$B$6:$C$77	144	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Wealth CALC Detail'!$B$6:$C$77
40	Portfolio_Analysis!Print_Titles	=Portfolio_Analysis!$M:$N	2097152	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Portfolio_Analysis'!$M:$N
41	Repayments	=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
42	Sched_Pay	=PPOR_Mortgage!$D$20:$D$1579	1560	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$D$20:$D$1579
43	Tax_Table	=Tables!$B$13:$G$17	30	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]Tables'!$B$13:$G$17
44	Total_Pay	=PPOR_Mortgage!$F$20:$F$1579	1560	[FSF_IPA_2018-11-17_V50_Circ_Ref_Fix.xlsm]PPOR_Mortgage'!$F$20:$F$1579
45	Values_Entered	=IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)
46	Year	=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

 

[JumboCactuar]

PPOR_Mortgage is the only sheet of significant size 2MB+

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

 

[Michael M]

IS there any code in the workbook ??
Workbook_open or Workbook_calculate

 

[MARK858]

IS there any code in the workbook ??
Workbook_open or Workbook_calculate

No, either Workbook, Worksheet or regular and no userforms.

 

[Michael M]

Maybe PPOR_Mortgage usedrange needs to be copied to a new workbook, and see if that reduces the size and issues

 

[MARK858]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
14		Cell Counts				CondFormat	Calc Time (Millisec)			Sheet	MTC	MicroSecs	Sheet	ShtBytes	Unused Cells
15	Sheetname	Used	Const	CondFmt	Formula	Enabled	Range	Re Calc	Full Calc	% Volatile	Factor	/Formula	Mem(K)	per Cell	%Waste	%Sparse
16	Report_Client	1,065		0	41	TRUE	1.9	70.7	257.1	27.5%	0.0	47.2	0	0.0	40%
17	Tables	880		0	123	TRUE	8.7	0.1	252.0		0.0	70.5	0	0.0	0%
18	PPOR_Mortgage	69,520		3,120	36,006	TRUE	125.3	0.0	106.0		1.2	2.9	2,060	58.6	30%
19	Portfolio_Analysis	8,280		6,188	1,898	TRUE	12.7	70.5	78.4	90.0%	0.2	6.7	33,220	17,922.7	0%
20	Current	1,140		53	494	TRUE	7.3	0.1	70.1		0.1	14.9	0	0.0	0%
21	Portfolio_Anual - Tax Benefits	4,288		150	2,646	TRUE	8.0	0.0	67.7		0.1	3.0	64	24.8	0%
22	Cap Growth Calc	782		369	301	TRUE	0.2	0.0	67.3		0.0	0.8	64	217.7	29%
23	Wealth CALC Detail	1,599		120	170	TRUE	0.3	0.0	67.3		0.0	1.8	0	0.0	0%
24	SMSF - Tax Benefits	3,131		155	1,567	TRUE	6.8	0.1	67.0		0.1	4.3	0	0.0	0%
25	Inputs	2,465		553	390	TRUE	153.1	0.1	65.2		2.3	167.1	64	168.0	35%
26	Prop Investment Costs & Income	2,112		0	239	TRUE	0.5	0.0	64.3		0.0	2.1	0	0.0	30%
27	SMSF	2,139		292	940	TRUE	1.6	0.0	62.4		0.0	1.7	64	69.7	0%
28	Other						0.1	1.2	1.3				33,804
FastXLBook1

 

[wardy0601]

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!

 

[sandy666]

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?

C:\Team Drives\First Step Finance\First Step Finance\Financial\Calculators\IPA Calc\SS_IPA_2018-11-17_V44_Branded_I.xlsb

G:\Team Drives\First Step Finance\First Step Finance\Financial\Calculators\IPA Calc\SS_IPA_2018-11-17_V44_Branded_I.xlsb

 

[MARK858]

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.

 

[MARK858]

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.