Some Ways to Make Files Smaller

Now, I’m guilty of making extremely large files that can run pretty slowly because I will work with large datasets. I will download everything in the universe, if I can. I also work on how to present information in a useful, clear and pretty fashion.

Making things pretty can create large files.

Here are a couple of factors, aside from holding lots of data, that I’ve learned will cause files to grow big:

1. Colorful formatting to make things pretty.

2. Conditional formatting is similar to colorful formatting.

3. Lots of array formulas can drag down the computing performance of the file. And,

4. Tables, as in Insert Tables.

Tricks that I’ve used over the years to help reduce the size of files are:

1. Use Access as a database and have Excel connect to the Access database via pivot tables. This is a really neat option but, unfortunately, most people, maybe 99% of the people I know, are uncomfortable with the idea of using Access, although they really are not using Access. They are just connecting to it. So it’s just mainly me using it.

2. Use pivot tables instead of array formulas as much as I can. Pivot tables seem to require less computing power.

3. Of course, when the file starts to run slowly, I start looking for areas where I really do not need a pretty look or colorful highlights. That helps bring down the size.

Now that I’m doing more macros, I find that there are opportunities to create temporary working tabs where formulas are created to do some work and then, when it’s done, the tab and formulas are deleted, leaving behind only the results. This means I can create even more formulas to do even more work which will then be eliminated. Right now this situation works only for me; I think most people are scared of using macros. When building files for others to use, the macros have to be the kind that works unobtrusively and automatically, like when the file is being opened or being closed. The macro cannot be ones that have to be called upon to work.

For myself, I’m going to be working on macros to see if they can help reduce the size of the files as well as speed up things.