I work for a company where all our daily reporting is done off one mammoth excel xlsb spreadsheet. Throughout the month it varies is size from 500,000KB to 950,000KB
It has a main data tab with maybe 800k rows, with lots of vlookups, ifs etc. Then there are many pivot tables running off this data tab.
I recently tried splitting the workbook into two so that the data was separate from the pivots, but this made things significantly worse (crashing when saving or refreshing pivots).
We currently have windows 64, since it won't open on 32. I know this is not what excel is used for, however there is no chance of this changing. Given that, any suggestions as to how to make this workable?
"there is no chance of this changing"
You haven't said why there is no chance of changing but one real reason to change is this; a worksheet can only have 1,048,576 rows. If you have 800k rows now and you are still adding to them, then at some point the worksheet will crash. There are also other real limitations to using Excel like this.
If this is a mission critical workbook then its paramount to suicide to continue to misuse Excel in this way. When this whole thing crashes I wouldn't want to be standing to close to the person mandating the use of Excel when the ax falls (it could get messy).
Store your data in a database. At a minimum I suggest moving to MS Access as the data store. Access is not too bad and it has inbuilt support for Excel. Its inexpensive (free with a MS Office) but has limitations too.
You can still have the users inputting the data using excel and create code in the worksheet to push the data in to the Access database. When the user want to view the data have code to pull the data from Access and display it in the spreadsheet. This way they are still using Excel but reducing the stress on the worksheet by not requiring it to hold all of the data.