Calculation Time

[Steve]

Jus a general question that I have not been able to find the answer to
elsewhere.

I currently have a spreadsheet with 20K rows and 286 columns of data.

Each day I download new data and add to the existing data. I then
filter the data to that day's download and calculate the data which
has a number of formulas.

The question is, when I run the calculation is excel calculating only
that filtered dataset (ie that days data download) or is it
calculating across the whole spreadsheet as it takes for ever to
complete !!!

If its the whole spreadhsset (2oK rows and 286 columns) is there any
way I can restrict it to only calculate the filtered dataset. I am
currently using Shift + F9 to start the calculation process as I
thought this would ensure only the filtered dataset would be
calculated.

Thanks in advance

Steve

 

[joeu2004]

The question is, when I run the calculation is excel calculating
only that filtered dataset (ie that days data download) or is it
calculating across the whole spreadsheet as it takes for ever to
complete !!! [....]
I am currently using Shift + F9 to start the calculation process
as I thought this would ensure only the filtered dataset would be
calculated.

When you press shift+F9, it recalculates all formulas that have
changed since the last calculation, and formulas dependent on them,
in the __active_worksheet__.

Not just filtered data. Not other worksheets.

But note that only formulas affected by the latest changes are recalculated,
normally.

And shift+F9 is ineffective if the workbook is in automatic calculation
mode. In that case, all formulas in all worksheets affected by the latest
changes are recalculated, normally.

If its the whole spreadhsset (2oK rows and 286 columns) is there
any way I can restrict it to only calculate the filtered dataset.

Not easily.

You could set manual calculation mode, then develop a macro that calculates
only filtered data.

But that might be ill-advised, depending on the design of the workbook.

If recalculation is taking a long time, the root cause might be the design
of the formulas. Some risky design: a lot of instances of the following.

- Formulas that reference entire columns, e.g. A:A instead of A1:A20000.
This is especially true in Excel 2007 and later.

- Formulas that reference "volatile" functions, notably RAND, RANDBETWEEN,
OFFSET, INDIRECT, TODAY and NOW.

- Look-up functions with "exact" search mode, typically zero in the last
parameter.

That is only the tip of the iceberg.

 

[Steve]

Thanks joeu2004, I think the problem may actually lie with the design
and setup of the workbook..

Thank you for your detailed response

Regards


Steve

The question is, when I run the calculation is excel calculating
only that filtered dataset (ie that days data download) or is it
calculating across the whole spreadsheet as it takes for ever to
complete !!! [....]
I am currently using Shift + F9 to start the calculation process
as I thought this would ensure only the filtered dataset would be
calculated.

When you press shift+F9, it recalculates all formulas that have
changed since the last calculation, and formulas dependent on them,
in the __active_worksheet__.

Not just filtered data. Not other worksheets.

But note that only formulas affected by the latest changes are recalculated,
normally.

And shift+F9 is ineffective if the workbook is in automatic calculation
mode. In that case, all formulas in all worksheets affected by the latest
changes are recalculated, normally.

If its the whole spreadhsset (2oK rows and 286 columns) is there
any way I can restrict it to only calculate the filtered dataset.

Not easily.

You could set manual calculation mode, then develop a macro that calculates
only filtered data.

But that might be ill-advised, depending on the design of the workbook.

If recalculation is taking a long time, the root cause might be the design
of the formulas. Some risky design: a lot of instances of the following.

- Formulas that reference entire columns, e.g. A:A instead of A1:A20000.
This is especially true in Excel 2007 and later.

- Formulas that reference "volatile" functions, notably RAND, RANDBETWEEN,
OFFSET, INDIRECT, TODAY and NOW.

- Look-up functions with "exact" search mode, typically zero in the last
parameter.

That is only the tip of the iceberg.