At what point is file size a problem?

[Glasgowsmile]

Context:

I'm updating a report for someone that is currently 145KB in size. I suggested doing various changes including the introduction of VBA to automate some of the manual tasks.

Currently, the file size now after my adjustments is 800KB. They're worried about file size but I don't think this size is an issue but I wanted to get some additional thoughts.

 

[RoryA]

Unless you're running a steam powered computer, or using a dial-up connection to your office, I can't see that file size being a problem.

 

[Joe4]

I have no idea what your workbook does, but note that if it ever does get to the point where size is a problem, and the workbook runs very slowly (i.e. because the amount of data and interaction between sheets), that is often an indicator that you may be using the wrong tool for the job. It is often indicative that what you have is a relational database, which would perform much better in a relational database program like Microsoft Access or SQL.

While you CAN use Excel as a relational database, it isn't really designed for that. So, it is often clunky, cumbersome, and inefficient.
That being said, using Power Query in Excel often allows you to do database-type operations in Excel more efficiently than trying to use a bunch of native Excel functions for database-type purposes.

 

[Alex Blakenburg]

Just to put it in perspective, I have seen 20Mb files that run fine.
Lots of Lookups expecially where whole column references are used can bring a spreadsheet to its knees and I think this is what @Joe4 is referring to too.
Volatile functions (this includes conditional formatting used at a transactional level) and external links are also killers.

 

[Joe4]

Lots of Lookups expecially where whole column references are used can bring a spreadsheet to its knees and I think this is what @Joe4 is referring to too.

Yep. If you have tons of lookups, that usually means you actually have a relational database (in which case, a relational database program would be a much better program to use than Excel).

 

[Glasgowsmile]

I have no idea what your workbook does, but note that if it ever does get to the point where size is a problem, and the workbook runs very slowly (i.e. because the amount of data and interaction between sheets), that is often an indicator that you may be using the wrong tool for the job. It is often indicative that what you have is a relational database, which would perform much better in a relational database program like Microsoft Access or SQL.

While you CAN use Excel as a relational database, it isn't really designed for that. So, it is often clunky, cumbersome, and inefficient.
That being said, using Power Query in Excel often allows you to do database-type operations in Excel more efficiently than trying to use a bunch of native Excel functions for database-type purposes.

It's going to have VBA to import some data from existing spreadsheets, VBA to hide columns/rows that are empty, and mostly xlookups and index match match formulas in a few hundred cells. That's about it really.

 

[mysticmario]

My workbook is now about 9mb, and it can run slow on some machines.
But my excel right now is basically a complete data analysis program, with hundreds of sheets, tons of VBA - poorly written probably cause I am a first time - self taught VBA user.
I trim it from time to time, by creating smaller archives of this file with older data. However I'm not even a year into this job and i already made 3 archives :D. VBA has it's downsides for data analysis,however it's the only language I know and understand how it suppose to work compared to other maybe more suitable languages.

 

[Joe4]

It's going to have VBA to import some data from existing spreadsheets, VBA to hide columns/rows that are empty, and mostly xlookups and index match match formulas in a few hundred cells. That's about it really.

Yes, a bunch of lookups/index match is usually indicative of a relational database.
However, if you only have a few hundred, you should be fine.
If you start getting into the tens of thousands (or more), then you might start to notice some performance issues, at which point it would probably make sense to investigate other solutions.