# Reduce Size Of Workbook



## gjfood (Jan 5, 2023)

I have two workbooks, one is always updated monthly and has over 400,000 records.  It will update multiple customer workbooks for monthly prices which on average they have 4,000 records.  My file is now over 8mb for each customer workbook using the formula below.  Any way to reduce size of file, I have tried saving as binary and no major change.  Is this the wrong formula, or anything else to try?

=INDEX('D:\Price Lists By Month\[Jan 2023 Prices.xlsx]00212198'!$F$2:$F$500000,MATCH(A2:A5880&C2:C5880,'D:\Price Lists By Month\[Jan 2023 Prices.xlsx]00212198'!$A$2:$A$500000&'D:\Price Lists By Month\[Jan 2023 Prices.xlsx]00212198'!$C$2:$C$500000,0))


----------



## sijpie (Thursday at 7:47 AM)

I don't think the formula will have much impact on the file size. 
You can also test if it is the formula, by (in a copy of the workbook) deleting the formulas and saving the workbook, checking for size.

I notice that sometimes something else unexplained causes the workbooks to bloat. 

In one of your customer workbooks press Ctrl-End and see which cell that leads to. Is it the end of your table or way more down / to the right?
That would mean that Excel for some reason stores far more than it needs to. 

If that is the case, then in a copy of the workbook add a new sheet, copy only the table to the new sheet. Delete the old sheet. Rename the new sheet and save the workbook. What is the size now?


----------



## gjfood (Thursday at 7:51 AM)

sijpie said:


> I don't think the formula will have much impact on the file size.
> You can also test if it is the formula, by (in a copy of the workbook) deleting the formulas and saving the workbook, checking for size.
> 
> I notice that sometimes something else unexplained causes the workbooks to bloat.
> ...


Thank you for the reply.  Yes, I tried that and its only 124 kb  I think the issue is the main file i'm pulling data from that file is over 10mb ? I wouldnt have thought that would have made the difference but its the only thing I can see.  I actually deleted half the records and reduced main file to 4mb and then the other file did get smaller.


----------



## Joe4 (Thursday at 7:55 AM)

Just something to think about.

If you have a ton of data records and and a lot of matching formulas, what you are really describing is a relational database model.
While you may be able to use Excel for such purposes, it really isn't what Excel was designed for, so it can often be clunky and slow to try to use Excel in this manner.
It would be far better to use a program designed for relational databases, like Microsoft Access, SQL, or Oracle.  Since they are designed for this sort of thing, they typically handle things much more gracefully and efficiently.

It is a matter of trying to use the best tools for the task at hand.  If you are stuck using Excel, you may want to look at Power Query, which allows you to do database type models and operations within Excel.


----------



## gjfood (Thursday at 7:59 AM)

Yes, I agree and have come to that conclusion.  We have over 150 Excel spreadsheets we mail out monthly to customers to use for ordering/review which all use Excel or have access.  I will look into Power Query and see if that will help.  Thanks!


----------

