Size of Excel file getting unmanagable...

[Bravo2003]

I've just tried to rebuild from scratch my Master File that I use for sales reporting..
The original was getting over 6MB and is so slow to process even the simplest of changes, and can crash my excel if I have other files open.

I haven't even started adding all the required formulas yet and only input half of the master data so far and the file is already 3MB!?

What creates such large data requirements?
I've checked for Last Cell contents on all sheets (CTRL - END) and that looks fine
I try to avoid formulas with full column checks (A:A - using only applicable row requirements A1:A987 etc.)

Only thing I can now think causing the issue is from using colour in my headers/columns?

Any suggestions to help keep the file manageable?

 

[RoryA]

Pretty hard to comment based on that. How much data do you have in the workbook?

 

[Bravo2003]

Pretty hard to comment based on that. How much data do you have in the workbook?

I have a Sheet Tab for each Year 2013-2019 that has sales numbers and figures. average 600 rows
columns headed for each month (Jan - Dec) with 6 columns per month for Cost/Value/Qty/etc.. then a Totals for each year at the end,
there is Company data (number/Name/Product) in the first few columns
At the end of the data are about 10 columns that is to calculate extra details from the existing data, (for example: average cost for year - X/Y)

Tested the size by deleting a sheet = 318kb

 

[RoryA]

3MB doesn't sound unreasonable to me for that amount of data. That's not a particularly big file and shouldn't really cause problems.

I created a sample file of 7 sheets, with 600 rows and 75 columns of data in each, and it was a little over 4MB.

 

[Bravo2003]

3MB doesn't sound unreasonable to me for that amount of data. That's not a particularly big file and shouldn't really cause problems.

I created a sample file of 7 sheets, with 600 rows and 75 columns of data in each, and it was a little over 4MB.

Yeah, my original master file is at 5.5MB and is a nightmare, if I have other files open it just freezes and crashes. (even without other files open!!)

It's way to big to email out and if I change data it takes an age to process.

Excel needs to improve processor speed!!

 

[JazzSP8]

If the processing is slow then it could be down to the Formulas you are using, especially if they are Volatile? (=NOW(), =TODAY(), =INDIRECT() etc)

Might it be worth seeing if there are any improvements that could be made there to give you some speed back?

Or even have VBA Solutions for some of the data humping instead?

 

[RoryA]

It sounds to me like there is either something wrong with your installation, or you have some awful formulas. I regularly run workbooks more than 10 times the size of that with no problems.

If you open Excel in Safe mode (hold down the Ctrl key while starting Excel), do you still have the same issues? If not, a startup file is causing the problem; if you do still have the issue, the problem is with your workbook.

 

[Bravo2003]

It sounds to me like there is either something wrong with your installation, or you have some awful formulas. I regularly run workbooks more than 10 times the size of that with no problems.

If you open Excel in Safe mode (hold down the Ctrl key while starting Excel), do you still have the same issues? If not, a startup file is causing the problem; if you do still have the issue, the problem is with your workbook.

Same problems in Safe Mode - slow response time, notresponding, crashes Excel.

I know I have volatile (INDIRECT) formulas in use, but thedata is so variable each update it avoids having to manually change 1000+ cellreferences in formulas to (initially to help speed up time spent making chamges!)
But seems I spend the time waiting for the file to free upafter typing a comment in a cell!!

I do try to find new improvements to formulas where I can,but this could take a while as I am trying to improve my formula skills.

 

[RoryA]

Volatile formulas have to recalculate every time you change anything, which is your problem.

Can you give an example of why you have to use INDIRECT? (I can probably count on the fingers of one hand the number of workbooks I have ever created that use INDIRECT)

 

[Bravo2003]

Volatile formulas have torecalculate every time you change anything, which is your problem.

Can you give an example of why you have to use INDIRECT? (I can probably counton the fingers of one hand the number of workbooks I have ever created that useINDIRECT)

=SUMIFS(INDIRECT("'2018Sales'!$BZ$1:$BZ"&A15_2018_Sales),INDIRECT("'2018 Sales'!$B$1:$B"&A15_2018_Sales),$C3,INDIRECT("'2018Sales'!$D$1:$D"&A15_2018_Sales),"Result")

The data in column BZ (example above) is imported daily and is variable tonumber of rows and can grow (or shrink) daily
I use the Indirect to avoid having to a) check theBZ1:BZ? covers the number of rows required followed by b) amending BZ? to thecorrect number.
Not only for the one row of formulas, but this is usedfor each month and totals, and also each year, so at least 13 columns pushingto 65 columns using this formula

There are 565 rows using these formulas
That would be a lot of changing BZ? to BZ666 if I swappedback to fixed ranges?

But I’m willing to try anything to fix this ruddyspreadsheet!!!