I have a large excel file (main sheet is 100 x 200,000) with lot of processed data. The set of formulae are repeated across columns (i.e. F5:CZ5 is same). To make the file light, i could convert it to values.
70MB .xlsb with formulae compresses to 10MB .xlsb with values.
To create an active environment, i'd like to retain formulae. Given that formulae are heavily repeated, is there a way to keep the formulae and decrease file size. Thanks,
Solution: Multi-cell array formula. Not aware for the BIFF format used in .xlsb, but unzipping .xlsx/.xlsm xml reveals the file storage format. File stores both the formula and computed value for each used cell. However, multi-cell formulae are stored only once. So for lengthy formulae used in a large range of cells, by converting to multi-cell array formulae, it stores each formula only once. Though in the xml formats, value and calcChain, continues to be repeat for each cell, we save on formula space.