Dynamic Array Compatibility Between Excel 2021 and 2016

Question

Hello,

I recently upgraded to Microsoft Excel 2021 (Version 2108) on Windows 11 and created a workbook utilizing various Dynamic Array Functions, such as UNIQUE, SORT, SORTBY, FILTER, and XLOOKUP.

I intend to share this workbook with colleagues who are using Excel 2016. We have a mix of employees using Excel 2016 and Excel 2021.

Could you clarify what will happen to these Dynamic Array Functions when the file is shared?

Will these functions still operate as expected?

Should I save the file in an earlier version for compatibility? If I don’t save it to an older version and they open it, will the workbook automatically launch in Compatibility Mode?

Thank you

Answer 1

Hi, I can confirm that if you use Excel 2021 to open an Excel files created with Excel 2016, it will open in compatibility mode, I do it all the time, mostly with .doc 2003 files.

This is an old article: Manage compatibility mode for Office - Office | Microsoft Learn here is a newer one Office Home & Student 2021 files now only working in compatibility mode - Microsoft Q&A Stefan contributes on this forum.

PS: if you click on File>Info>You will see the option to convert

Answer 2

If the workbook doesn't require recalculation for your colleagues to work with it, then you should make a share-able versin with all those formulas converted to values. Otherwise, the functions will return errors.

You could also try to upload the file to OneDrive.Live.Com, which will launch 365 if the workbook is opened in a browser by your colleagues - though their ability to do that may depend on a number of other factors, but it is worth a try.