Slow Calculations - Any advice to help speed a file up?

Hi All,

Hopefully someone can give me some pointers.

I have a file that is made up as follows:

Main Summary sheet, c.5000 rows and columns to AW with 25 of them containing formulas, formulae include, concatenates (to give unique references), index/match, nested If/Sumifs (to allow 2 x sumifs based on the True/False of the the if statement). The sumifs generally contain indirect formulas to direct them to other tabs within the workbook from where to pull the values.

4 x tabs for Data Sources (for the differing data to be pulled from for the main summary as detailed above), Lets call them A/B/C/D.

Tabs A/B/C/D are fairly flat, just hard coded data from other worksheets, the only formulas present on these tabs are there for validation purposes, a concatenate to give the unique reference (for the Main Summary) and a lookup formula to ensure that unique reference is present on the Main summary. Other formulas are just simple stuff for totals/subtotals for when filtering.

There are no external data links (all the info required for the summary is all encompassed on the Data source tabs.

The overall file size is around 6MB.

The file is very slow when calculating any changes, as such we have the file set to manual calculation and have to hit 'calculate now'/press F9 when the file is updated.

I just wondered if anyone could offer any pearls of wisdom on this one.........

Thanks
seash

Does the order of calculation make logical sense? That is, are the calculations progressive as you follow down the worksheets or are they jumping all over the place?

"The sumifs generally contain indirect formulas to direct them to other tabs within the workbook from where to pull the values." When directed to other tabs, is a calculation taking place or is a value being taken from the worksheets? If there are calculations in the Sumifs directed to other worksheets taking place on those worksheets, can you replace any of the calculations that are taking place on the other worksheets by summary cells that would have those calculations already made so that the results just have to be referenced by the Sumifs formulae?

If you can stip down your workbook so that it doesn't have sensitive data and just leave enough data (real or fake) to make sense of the workbook, it would be easier to see how the flow of the calculation is actually taking place. Formulae can also be checked to see if there are more efficient formulae that can be used.

Delete some data until you get below the 1mb limit.

You can upload a zipped file that is over 1MB.

Ok heres the file, reduced in size, but hopefully will still give you an idea.

Thanks
seash

Some things to consider re the performance of your workbook.

The use of Full Column references in the formulae throughout the workbook is in my opinion unnecessary and excessive. Do you really have to reference over 1 million rows every time? That is a lot of wasted calculation resources.

The Lookup Data worksheet has several columns that have no connection to any other cells in the workbook. Are these actually going to be used? If not, they are just taking up space.

You might consider defining names for all the columns on the Lookup_Data and use the names instead of actual cell references in your formulae.

This workbook is approximately 1.3 M and the full workbook is about 6 MB. That isn't overly large by any means and the formulae are not complicated so that leads me to think that the referencing of whole columns may be playing a large role in being slow. Another factor is the computer that this is being used on. Is it up to the task? Does it have enough memory, enough free disk space and is the computer fast enough?