Reduce size of (or speed up) excel spreadsheet

[helpcharity]

Is there anything I can do to reduce the size of my Excel file... or make it faster? It is now so big that it takes ages to update etc. when I change a formula.

Thanks in advance.

Harry

Key specs. of spreadsheet file

• File is currently 120MB big
• Has 7 worksheets giving details of every interaction our charity has had with the public over the last 2 years e.g. time and method of contact, demographic of service user, what help was sought, what outcome was acheived, etc.
  • 2 of the work sheets have 150,000 rows of data (up to column AU),
  • 2 have have 20,000 rows of data (up to column AU),
  • 2 worksheets are relatively small.
• The data from these 6 worksheets is then collated into a Master Worksheet which is a list of unique service users and tells me; the total interactons we've had with them over the last two years, total impact, etc.
• The Master worksheet has 500 rows and goes up to Column EZ... and each cell has a formula like those listed below....
  • =IF($B2="","",INDEX('3List_Issues_full_profile'!$AM$2:$AM$299999,MATCH($B2,'3List_Issues_full_profile'!$N$2:$N$299999,0)))
  • =IF(B2="","",AGGREGATE(15,6,Registration_Date!B$2:B$300000/(Registration_Date!$A$2:$A$300000='RAW DATA'!B2)/(Registration_Date!F$2:F$300000="Y"),1))
  • =IF(B2="","",SUM(SUMIFS('1List_Casenotes_full_profile'!P$2:$P$300000,'1List_Casenotes_full_profile'!D$2:D$300000,'RAW DATA'!B2,'1List_Casenotes_full_profile'!O$2:O$300000,Registration_Date!$H$2),SUMIFS('1List_Casenotes_full_profile'!P$2:P$300000,'1List_Casenotes_full_profile'!D$2:D$300000,'RAW DATA'!B2,'1List_Casenotes_full_profile'!O$2:O$300000,Registration_Date!$H$3)))
  • =IF(B2="","",SUMPRODUCT(('1List_Casenotes_full_profile'!$M$2:$M$300000='RAW DATA'!AA$1)*('1List_Casenotes_full_profile'!$D2:$D300000='RAW DATA'!$B2)))
  • =IF((COUNTIFS('3List_Issues_full_profile'!$N:$N,'RAW DATA'!$B3,'3List_Issues_full_profile'!$Q:$Q,'RAW DATA'!AQ$1))>0,"YES", "")
  • =IF($B2="","",SUMIFS('BP1-Debt data'!$O:$O,'BP1-Debt data'!$E:$E,'RAW DATA'!$B2,'BP1-Debt data'!$J:$J,'RAW DATA'!EG$1))

• I'm told I have a relatively powerful PC, (with 16MB Ram, 3GHz processor etc.) so it's not a hardware issue.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help make spreadsheet faster and/or smaller in size?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[helpcharity]

I'm really sorry... I've just been advised on the other site about the protocols/conventions for using forums (and cross posting) and I was just trying to find the edit button (to add this information) when you posted this advice. Once again, sorry.

Addendum to original post...
* I'm using Office Professional Plus 2019
* There are no macros on the spreadsheet

 

[Fluff]

I'm using Office Professional Plus 2019

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[arthurbr]

I would use Excel tables instead of 300000 row long references
Also have a look at Fixing bloated file size and slow calculation in Excel or similar

 

[helpcharity]

Thanks everyone... the script didn't seem to work (it deleted some data... so good call to advise me to save a copy). The use of tables, then pivot tables and the vlookup functions instead of loads of COUNTIFS, SUMIFS formulae seems to have done the trick... I've not replaced them all yet, but from the sheets I've updated I can see (although my file size has increased) a massive improvement in time already. I just need to master the INDEX and MATCH functions for searching pivot tables now.