Reducing file size from 38,305KB (A lot of conditional formatting & functions)

Hi all,

I work for a courier company that uses a calendar built by me in Excel to log all the deliveries that we do, amongst other things. It's getting to the point where the file is just too big; currently 38,305KB.
There are no macros, just a lot of hyperlinks, conditional formatting and overly complicated functions.

Every day of the calendar keeps track of how much each driver and vehicle earns, and also how much each customer has spent with us.
e.g. =IF($I5="","",IF($I5=$AA$4,$O5+$P5,""))

Additionally, there is conditional formatting that indicates rows (one job per row, 72 rows per worksheet) that firstly; we have received the paperwork for
=$Q76="x"
To be applied to; =$I$76:$U$76

And secondly; if any information has been omitted regarding that job
=AND($Q76="x",OR($I76="",$J76="",$O76="",$R76="",$S76="",$T76=""))
Also to be applied to; =$I$76:$U$76

So far the file consists of individual worksheets for 182 days that get increasingly complex as the year progresses (I've been working on it as we go).
Additionally there are 6 sheets consisting of data lists for dropdowns, an overview of each week’s turnover and a navigation page, and 3 pages that I use to experiment on.

What I'm looking for is either some suggestions on how to reduce the size/complexity of the conditional formatting/functions I’ve used, or just some general advice on reducing file size (this I have researched but nothing so far has worked).

Please let me know if you need me to clarify anything, or require some additional information in order to help.

Many thanks in advance,

Ben

Ok, so I've just spent some time removing those extra IF statements from the calendar, there must have been a couple of thousand, and the file has decreased by -378kb. Yep, the file has actually Increased in size and it's now 38,683kb...

Can't believe it after all that effort; I thought that less functions = reduced file size?

Any other ideas, anyone?

Thanks for the help so far,

Ben

This problem is affecting the business I work for and I need to find a solution ASAP. I do not want to go down the route of splitting the calendar into smaller chunks (e.g. having a 3 or 6 month calendar) as it will mean having to load extra documents in order to view relatively recent jobs, reducing the amount of jobs we can search through when producing quotes for people and if I'm off ill for any length of time (there is a high possibility of this) then the office staff could end up calendar-less.

Since condensing formulas hasn't worked I can only think of a couple more things that may help reduce file size. Firstly, can anyone tell me if hyperlinking either by function or by right-clicking increases file size? Most of the hyperlinks I've used are by function simply because there are so many of them and it is so much easier/faster to add/edit them.

There may also be another formula I can condense but I'm reluctant to spend time on that since changing the others didn't make a difference.

Thanks in advance,

Ben

Thanks for the reply OnErrorGoto0.

I've just tried out the first suggestion. With conditional formatting the file was 38,990kb, without it was 38,593kg. Just to be clear, yes I did remove all of the conditional formatting, one page at a time.
As for the second, the file type I'm using is .xlsm because there was, at some point, an issue with drop-down menus and this seemed to solve it.

Sorry for the slow reply, work's been chocker the past few days.

Many thanks,

Ben

This may or may not be a suitable suggestion but could exporting the excel file into a database solve any issues you may be having?

Originally Posted by OnErrorGoto0

It's not the CF then. Try copying each sheet separately to a new book and saving the individual sheet workbooks and see if one is much bigger than the others. Have you also checked for hidden sheets?

I was a bit dubious about having to save each sheet in separate workbooks because of the large quantity involved but I did make a break through; I found that one blasted page was responsible for over 9000kb of the file size, and it was only used to store dates! So I cleared it, added the data back in and saved; it dropped down to 13kb!!

I haven't done all of the sheets yet but I did notice a bit of a jump between my two most recent templates;
The previous template used for days in the calendar was 62kb,
The most recent template, that used for the past couple of months, was 245kb.

I'm presuming that the size difference is due to a large number of IF formulas I added to template 245. I wanted a system in place that would show me the total turnover for each driver, vehicle and customer within each day. When we add a job, a customer is chosen in a drop down menu, as well as a vehicle, driver and price; all information for that job is contained on the one row. In template 62kb I created a row of SUMs where each cell equalled a drivers initials taken from a separate worksheet, with a couple referring to empty cells to allow for additional drivers. Using =IF($I17=$AA$4,$O17+$P17,"") I made each cell look for the initials at the top of it's column within the appropriate cell on it's row and, if it found the initials, to equal the price of the delivery.

This worked perfectly and so I also made the same thing for vehicles and customers, this is template 245kb. There are 30 driver columns and 30 vehicle columns, but 246 customer columns, with 72 rows full of the above formula. If there's an easier way for me to get the same results another way then please let me know!

As for hidden sheets, I don't recall ever making any but am not sure how to double check this; could you explain how please?

Many thanks,

Ben

Originally Posted by ironaces

This may or may not be a suitable suggestion but could exporting the excel file into a database solve any issues you may be having?

There are too many other excel files that draw data from this calendar to make something like that an option I'm afraid. I used to use Access quite a lot when I was younger (I created a database of my coin collection, because I was THAT cool), but haven't used it or anything like it was a long time. I might have to consider looking into it when I have more time...

Thanks for the suggestion,

Ben