Incompatibilities with previous versions of excel

Copper Contributor

I have the latest version of excel. And so, I have used Dynamic matrix formulae, such as "=#456*#963". I was wondering if this formulae is compatible with excel 2019. And if not, if there is anyway of becoming it in a easy way in a compatible formulae. 


I would really appreciate any kind of help,


Thank you in advance. 

1 Reply


The dynamic matrix formulas, using the "#" symbol for referencing adjacent cells, are a feature introduced in Excel 2021 (Office 2021) and Excel for Microsoft 365. These formulas allow for flexible array calculations.

If you need to share the workbook or use the formulas in Excel 2019 or earlier versions, which do not support dynamic array formulas, you will need to convert them to compatible formulas.

Excel 2019 is considered a legacy version of Excel where only Ctrl + Shift + Enter array formulas are supported. To convert a dynamic matrix formula to a compatible formula, you will typically need to use array formulas or alternative functions available in Excel 2019. Without knowing the specific calculation you are performing, I can provide a general example using SUMPRODUCT as a substitute for multiplication in this case.

For the formula "=#456*#963" in Excel 2021, you can modify it to a compatible formula in Excel 2019 as follows:

  1. Enter the formula as an array formula by pressing Ctrl+Shift+Enter after typing the formula (this will add curly braces {} around the formula).
  2. Modify the formula to use the SUMPRODUCT function to perform the multiplication:

{=SUMPRODUCT(#456, #963)}

By using SUMPRODUCT, you can achieve a similar result to the dynamic matrix formula in Excel 2021 while maintaining compatibility with Excel 2019.

Please note that the approach provided here is a general example, and the specific formula and calculation you are working with may require a different conversion. You will need to adjust the conversion based on the specific formula and calculation you are dealing with.

It is also worth mentioning that when sharing workbooks between different versions of Excel, it is recommended to thoroughly test the formulas and functionality to ensure they work as expected in the target version. Some features and functions may not be available or may behave differently across different Excel versions.