-3

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?

ratherstrange
  • 117
  • 4
  • 10
  • Way to vague and broad for Stack Overflow. You might have better luck on [Database SE](http://dba.stackexchange.com/) if you want a better Database solution for your company, but otherwise this does not fall under the [Help Guidelines](http://stackoverflow.com/help/on-topic) of Stack Overflow. – Chrismas007 Dec 29 '14 at 19:58
  • Frankly, my advice, assuming the budget permits (and even if it doesn't), start looking into the idea of having a database-backed system built for you (assuming you can't find something off the shelf). This kind of thing is usually a massive red flag, and the problems are only going to increase. – Paul Richter Dec 29 '14 at 20:00
  • 2
    If possible, find a new job. I'm not kidding; leave the sinking ship while you can. – Ruud Helderman Dec 29 '14 at 20:02
  • I might agree with Ruud, especially if the "there is no chance of this changing" is a commonly held view and/or decree from management. – Paul Richter Dec 29 '14 at 20:12

1 Answers1

2

"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.

scott_f
  • 838
  • 8
  • 17