I have a large number of array formulae in my sheet. I update the supporting
data and the formulae do not recalculate. The only way i can get a recalc is
to F2 and then ctrl+shift+enter. How can I get all of the formulae to
recalc. Manual recalc does not work.

It sounds like your Excel settings may be set to manual calculation mode, which means that formulas will not automatically recalculate when the underlying data changes. Here's how you can change your settings to automatic calculation mode:

1. Click on the "File" tab in the ribbon at the top of the screen.
2. Click on "Options" in the left-hand menu.
3. In the Excel Options dialog box, click on "Formulas" in the left-hand menu.
4. Under "Calculation options", make sure that "Automatic" is selected.
5. Click "OK" to save your changes.

Once you've changed your settings to automatic calculation mode, your should recalculate automatically when the underlying data changes. If you're still having trouble, you can try pressing the "F9" key to force a recalculation of all formulas in the sheet.

Does Automatic Calculation mode work?

If excel is confused about whether it should recalculate a formula, this
sometimes wakes excel up and stops the confusion.

Select all the cells on that sheet
(ctrl-a a few times should do it)

Edit|replace
what: = (equal sign)
with: =
replace all

Excel will see that you're changing all your formulas and know to recalc each
because of the change.

Thanks wrote:

I have a large number of array formulae in my sheet. I update the supporting
data and the formulae do not recalculate. The only way i can get a recalc is
to F2 and then ctrl+shift+enter. How can I get all of the formulae to
recalc. Manual recalc does not work.

--

Dave Peterson

You could try
Ctrl-Alt-F9 to force a Full calculation

or if the dependency tree is messed up you can rebuild it using
ctrl-shift-alt-F9 - sometimes after this a normal F9 will work.

If this does not work you may have hit one of the array formulae
limits.

For excel versions prior to Excel 2007 there is a limit to the number
of array formulae that can refer to other sheets.

from KB 166342

Maximum array formulas
In Excel 2003 and in earlier versions of Excel, a single worksheet may
contain a maximum of 65,472 array formulas that refer to another
worksheet. If you want to use more formulas, split the data into
multiple worksheets so that there are fewer than 65,472 references to
a single worksheet.

For example, in Sheet1 of a workbook, you can create the following
items:

* 65,472 array formulas that refer to Sheet2
* 65,472 array formulas that refer to Sheet3
* 65,472 array formulas that refer to Sheet4

(If you are using multi-cell array formulae I suspect that the limit
is actually 65472 cells)

regards
Charles

I have a large number of array formulae in my sheet. I update the supporting
data and the formulae do not recalculate. The only way i can get a recalc is
to F2 and then ctrl+shift+enter. How can I get all of the formulae to
recalc. Manual recalc does not work.