Home → Techniques and Tips → All Products → What happens when you edit your model in Excel 365 and then open it in an older version of Excel?
1.4. What happens when you edit your model in Excel 365 and then open it in an older version of Excel?
Applies to: @RISK 7.x and newer
Dynamic arrays are supported in the latest versions of Excel 365. Dynamic array formulas can automatically populate or "spill" into neighboring blank cells and eliminate the need for legacy Ctrl+Shift+Enter (CSE) array formulas.
When opening a workbook that contains dynamic array formulas in an older version of Excel, they show as a legacy CSE formula. If new dynamic array functions are used, spill range references get prefixed with _xlfn to indicate that this functionality is not supported. A spill range ref sign (#) is replaced with the ANCHORARRAY function.
Most dynamic array formulas (but not all!) will keep displaying their results in legacy Excel until you make any changes to them. Editing a formula immediately breaks it and displays one or more #NAME? error values.
So, if you know you will be sharing dynamic array formula enabled workbooks with someone using non-dynamic aware Excel, it’s better to avoid using features that aren't available for them.
What about @RISK functions?
When you open a workbook containing @RISK functions in an older version of Excel, it is automatically converted to a conventional array formula enclosed in {curly braces}.
So, there are two possible scenarios to analyze considering the @RISK version used:
- @RISK 8. The model will run without problems.
- @RISK 7.x and older. You will get next error message:
One way to solve this problem is inserting the “@” character (aka implicit intersection operator) at the beginning of the @RISK functions when editing the model in Excel 365.
In Excel 365, all formulas are regarded as array formulas by default. The implicit intersection operator is used to prevent the array behavior if you do not want it in a specific formula. In other words, this is done to force the formula to behave the same way as it did in older versions.
So, you can do this manually or programmatically by creating a VBA macro.
There is an alternative option using the Swap-Out functionality available in @RISK, the procedure is explained below:
- On Excel 365, use the Swap-Out functionality of @RISK to preserve the current state of all @RISK functions. No need to include reports so you may want to skip those options.
- Save a copy of this workbook and then open it in an older version of Excel which has @RISK v7 or below.
- It may immediately prompt you to swap in all @RISK functions found. If it doesn’t, close the model and open a blank workbook instead. Run the Swap-Out functionality on the blank workbook and then re-open your model.
- Follow the instructions on screen to complete the Swap-In process.
Last Update: 2020-09-08